Optimizing VBA range referencing

[eadamy]

I've got some heavily used code working and am trying to optimize it for speed.

Please Login or Register  to view this content.

The worksheet range is a database with a variable number of rows and columns. Each column holds one field of stock quote data such as date, open, close. Each row holds the quotes for one day. Each column of quotes to be loaded into the worksheet is in a variable length array over which I have no control ... I can not simply dim the array size to equal the range size and do a block transfer which would be extremely fast. I start at the end of the array and end of the column and iterate backward to load data until I run out of available rows in the worksheet or available quotes in the array.

1) this code transfers the data from the array to the worksheet:

Please Login or Register  to view this content.

2) this code clears any remaining worksheet rows for which there is no data:

Please Login or Register  to view this content.

2a) i've constructed this alternate to 2 which should be much faster for clearing large numbers of rows in a single operation. It compiles but I am getting a "type mismatch" error and the debugger shows that it is referencing cells based on worksheet addresses rather than range relative addresses.

Please Login or Register  to view this content.

Any ideas for speeding this up would be greatly appreciated.

[Steffen Thomsen]

Just a quick question, how are you setting you array?

[Richard Buttrey]

I can not simply dim the array size to equal the range size and do a block transfer which would be extremely fast.

Hi
Why not?

Simply Dim the array first with say a count of 1, then once you've loaded your data and have worked out how many rows it contains and updated a row variable , use the ReDim instruction to reset the size of the array with the row variable.

Probably not relevant here but in case you ever need it, even if you've populated a VBA array and subsequently want to increase it, you can use the ReDim Preserve instruction which will retain the current array details and expand it further.

[eadamy]

Just a quick question, how are you setting you array?

The quote array is loaded from Metastock files by an external DLL which offers two options: 1) a virtually instantaneous block load of all quotes in the file into the array or 2) I can iterate the quote file with VBA and load one quote at a time into the array. For what I am doing, it is substantially faster to load the entire file. Since the length of quote files vary, the array may be of any length.

[Steffen Thomsen]

Ok,

Then when you place your array into the sheets, try this

Please Login or Register  to view this content.

[eadamy]

Hi
Why not?

Simply Dim the array first with say a count of 1, then once you've loaded your data and have worked out how many rows it contains and updated a row variable , use the ReDim instruction to reset the size of the array with the row variable.

Probably not relevant here but in case you ever need it, even if you've populated a VBA array and subsequently want to increase it, you can use the ReDim Preserve instruction which will retain the current array details and expand it further.

The quote array is block loaded from a variable length file by an external DLL so I have no control over the length of the array before it is loaded. When it comes to ReDim Preserve, there is an additional point which is relevant. The oldest data is at (1) while the most recent data is at UBound.

I kicked the tires on ReDim Preserve but concluded that reducing the size of the array would truncate the most recent data at the UBound end rather than the oldest data which I want to discard. Could be wrong on that.

[Richard Buttrey]

Hi,

I'm still confused. What's the ultimate aim?
Are you trying to build up an Excel table of data on a daily basis, adding each new set of data below the previous data, or is this a one off load of data.

Either way if the metastock data is loaded first into a VBA array, why can't you just write the whole array to the worksheet with one instruction?

Can't you import a file containing the metastock data directly to the worksheet?

Regards

[snb]

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

will automatically assign the whole database into an array that is also being dimensioned automatically.

[eadamy]

First, thank you for taking the time to respond and I've learned a couple of tricks; however they were not applicable to my problem. I tried posting something yesterday but the site was down for maintenance.

In looking at my problem statement, I did not make clear that while the number of rows in the worksheet database are variable, all columns within the database are the same length (a rectangular range). Rows represent days and columns represent quotes. The quotes for each symbol are loaded into the array by an external DLL and the length of the array is equal to the number of quotes for the symbol which are variable ... I have no control over the sizing of the array by the DLL. Where the number of quotes to be loaded into a worksheet column is fewer than the number of rows, the unused rows are to be cleared. Where the number of quotes to be loaded into the worksheet column exceeds the number of worksheet rows, the most recent UBound quotes in the array are used. My understanding is that Redim Preserve to decrease the size of the array, removes the items from UBound down whereas I would need to remove items from IBound up.

Absent a way to Redim Preserve the upper end of the quote array (instead of the lower), I have seen no way for me to load the array into the worksheet using "=".

While my code to iterate the desired portion of the array into the worksheet database was working, I was looking for ways to speed it up. I have since made two changes: added With/End With for more efficient addressing of the worksheet range and once I made that change, I was able to use ClearContents to clear the unused portion of the column instead of clearing it one element at a time.

Please Login or Register  to view this content.

These changes have cut the processing time by 2/3 which is nice but the time is still significant. The arrays are loaded instantly so the time consuming process is iterating the quotes from the array into the worksheet. Any more ideas on how to speed that process? Thank you.

Earl Adamy