Resource problems

Topic

New Reply

Hi there all!

Sorry for the long explanation, but it a slightly complext situation.

I am running MS Office 2000 Premium on Windows 98 on Pentium III class machines with 128Mb RAM.

Here is the scenario:
I have a 100 to 105 page RTF document containing a statement for x number of people, which is generated by our financial system. I open the RTF documentin Word and then run the VBA code.

My VBA code in Word starts off by opening another Word document which contains a handling table. The handling table contains an entry for each person in the financial report. These reports can be delivered to them by
either be email or fax or snail mail (printed and then posted). It also creates a new document for reporting purposes.

The VBA code then coping and pasting the first page to a new document and replaces some lines which where left out when the financial system exported the data to the RTF document. It then picks up a unique identifier for the
page (the person’s ID number) and the person’s name.
It then finds the corresponding ID number in the handling table and picks out the corresponding action (email/fax/print) out of the table and performs
the appropriate action. and closes and saves the new document. The code then switch to the report document and an entry is written to it.
(Basically action taken, time done, person’s ID number and name).

The rest of the pages in the master RTF document is then processed.

My problem is that if the machine has anything less than 85% to 90% free system resources and 85% to 90% free user resources, the machine runs out of resources and totally freezes up.

My question is: Is there a way to free up system and user resources while processing. Anyone got any good suggestions?

Looking forward for some helpful pointers!

James
(EMail address: james.nesbitt@icon.co.za)

Reply | Quote

Replies

James,

I’d be looking at what the VBA is doing to cut down on the resources rather than anything external.

How are you doing the copying and pasting? If you are doing it onto the clipboard and into the second document, that could be consuming a lot of resources. Even using the “selection” object is probably worse than using the range object (it’s certainly slower).

Just a thought- it depends on how you’re processing though.

Reply | Quote

Initially when I started this project, I was just learning to program in VB iteself, even though I have used a similar package to VB.

To select the page, I am using the Find and Select statement. I am basically starting at the top of the page and find and selecting 1 word to the right until a certain set of characters are found (currently set as ^~), which is currently at the end of the page. Once the end of the page is reached, I then do a Select.Copy, create a new document and paste it into the new document.

I know that I am able to use another method to select the entire page, however, down the line – each report in the document (currently only 1 page each), may have so many transactions which forces it to spill onto a second page.

Any further suggestions?

Reply | Quote

James,

Here’s a quick bit of code for copying all the text from one document to a section in another document, just to give the idea of bypassing cut and paste. This copies text only without formatting, but if you don’t want the formatting, will be probably the most resource-efficient way:

Dim rngFrom As Range
Dim rngTo As Range

Set rngFrom = Documents(“Document1”).Range
Set rngTo = Documents(“Document2”).Bookmarks(“Copyhere”).Range

rngTo.Text = rngFrom.Text

It may be even easier (and will be a lot faster) if you work with strings instead of the document:

dim rngDoc as range
dim strText as string
Set rngDoc = Documents(“Document1”).Range
strText = rngDoc.Text
(Manipulate the text- eg:)
dim iStart as Integer
dim iEnd as Integer
iStart = 1
iEnd = instr(strText, “^~”)

set rngDoc = Documents(“Document2”).Bookmarks(“Copyhere”).Range

‘ Copy in the selected portion of the text
rngDoc.Text = mid$(strText,iStart, iEnd – 1)

set strText to what’s left
strText = mid$(strText, iEnd +2)
iStart = iEnd + 2

Sorry, this is a bit rough, I’m supoosed to be out of here 20 minutes ago, MrsW will not be pleased. But I hope that helps

Reply | Quote

In addition to Geoff’s suggestions, which look really good, you can extend your selection much more simply with something like:

With Selection
    .Extend
    .Extend Character:="^"
    .Extend Character:="~"
    .Copy
    .Collapse
    'etc.
End With

Reply | Quote

Gary,

I didn’t know you could do the “extend” with a character search with the selection object. I haven’t tried it- I would guess you can do it with the range object as well.

I do have a problem with using “Selection”. In many (but not all as I understand) it’s quicker to use Range rather than Selection- particularly as Selection does things to your screen while it’s working, so it’s slowing down with screen refreshes. Maybe “Application.screenupdating = false” will speed things up, but I suspect (don’t know) that using “range” will be much faster.

I was also trying to avoid the “copy” in this situation, as I suspect it also is responsible for consuming a big hunk of the system resources.

Some time ago, I was constructing a large table, by adding one row at a time to a table, and pasting in data. It took a fair while.

Instead, I set up an array, and set values in the array. It was only a one-dimensional array, with tabs separating what would later be columns in the table. It was only when the array was finished that I copied in the entire array, and then constructed the table around it. It was much quicker.

In Excel (off-topic but relevant) I’ve had huge performance improvements by setting up a 2- dimensional array, then using “range” to copy in the whole array. For up to (I think) about 6 columns, there was not much of a performance difference between doing it that way and setting up individual cells; but adding more cells really degraded the performance.

Reply | Quote

Hi Geoff,

Your point is taken that it’s better to avoid the Selection object in favor of the Range object when possible. And I appreciated the neat methods in your previous post.

Like many self-taught Word VBA’ers I started out very dependent on the Selection object. It’s the easiest to get started with and the easiest to understand in terms of relating the processes in your program , with what you would be doing in the user interface (it is after all, what the macro recorder uses when it produces code, which tells you something about the centrality of the Selection object in the Word object model’s design). It’s also easier to debug and to follow program flow.

It’s only fairly recently that I’ve weaned myself more consistently away from good ol’ Selection; I feel real good about myself when I can do a whole routine without using Selection. In the case of Selection.Extend, the thought that came to mind was something from Word’s user interface, which is the Selection Extend mode (F8 key), and which has the neat feature of allowing you to extend a selection to a character you type on the keyboard. I actually recorded the code initially(!) to see what would happen. I don’t think there is an equivalent for the Range object but not sure.

Another angle on Selection vs. Range is that folks who learn VBA via Excel get very familiar with the Range object cause that’s the main way to do things in Excel VBA; Excel-bred VBA’ers tend to use Range when doing Word VBA, more than do Word-bred VBA’ers. No question getting at things via Range is higher on the programming evolutionary scale, but it’s worth reflecting on the close connection between Word and Selection.

A really interesting gloss on this is to go back and see Woody’s discussion of Selection vs. Range (which runs to 6 pages) in Word 97 Annoyances – it’s very Word/Selection-centric. He surmised that the new emphasis (in things like MS help) on Range in Word 97 was the outcome of some occult struggle within MS between the forces of Excel and the forces of Word, with the Excelites winning the day. And he provides some detailed analysis before concluding with the advice that Word VBA’ers should forget about Range, and just use Selection – he really gets worked up about it! Not sure I agree with it all, but it makes good reading.

Well, I better get off the line. Interesting topic though.

Gary

Reply | Quote

Hi,

Just another thought, along the lines of Geoff’s suggestion: try temporarily remming out selected parts of the code (starting I’d say with the e-mail/fax/print part) and see how the temporary changes affect resource use when the code is run.
This might help narrow down which specific process(es) are causing the problem.

Another permutation, if you can coordinate it, would be to try stepping through the code, while watching a resource meter (I’ve never tried that one though so not sure it’s doable!).

Gary

Reply | Quote

I’ll try something like that.

Thanks

James

Reply | Quote

A minor consideration: remove the Office Shortcut bar. It’s less than 2MB, but it’s something. Since you did not give any info re: software installed on the PC, it’s hard to give any more advice. How many icons in the taskbar icon tray? There could be lots of stuff there you could pitch, maybe. Then again, your VBA app may have references to dlls, OCXs, etc. that are UNnecessary. As an experiment, you could install another 128MB in a PC and see if that really solves the problem.

Reply | Quote

Kevin, thanks for your comments. Here is a couple of finer points which I have tried and found.
1) I have trimmed down and closed everything that may be open to free up resources to the point that it could be considered a freshly installed machine. This increases the resources and, therefore, the number of pages processed.

2) Even though there is 128Mb RAM on the machine, I have had to limit it to only using 48Mb RAM. This not only gives more resources, but it also improves Windows 98’s memory and resource management. This is a proven fact with Windows 98. (With Windows 95 as well)

Reply | Quote