Use range to fill an array

[jlt199]

I have an array I want to fill with a rane of cells from a worksheet. I can do this using

Please Login or Register  to view this content.

My question is can I use a similar technique to fill the array with strings instead of variants?

[davegugg]

You can use the following, though it is slightly less efficient because it uses a loop:

Please Login or Register  to view this content.

[jlt199]

Is it going to be more efficient to correctly fill the arrays with the right variable types, or perform lots of operations on variants?

Bearing in mind my macro will eventually be quite complex and filling these arrays with variants instead of doubles, longs or strings will result in almost all other variables also having to be variants?

[davegugg]

I think it is much easier to work with an explicitly defined array rather than the array of variants as you have it defined. I can't imagine the code would run noticably slower either.

[jlt199]

I'm inclined to agree. Thanks for your time.

[romperstomper]

It's quicker to do it in one hit, assigning to a Variant, especially if you have a lot of data.

[jlt199]

I agree that is a quicker method for filling the arrays, but what about using and performing operations on the values in the arrays? Surely then it would be quicker, and take less memory, to use the correction data type, wouldn't it?

[romperstomper]

It will probably use a bit more memory (unlikely to be an issue these days) but any performance hit on manipulating the array will most likely be more than offset by the overhead of reading cells one by one.

[DonkeyOte]

Not specific to the content of this post but for those interested Tushar Mehta has a general overview of Variants here

FWIW I agree with romperstomper... if the performance hit of using a Variant array is significant then the reality is probably such that you shouldn't be using Excel / VBA for whatever it is you're doing.

[jlt199]

Thanks.

On a completely different subject anyone know why I can rarely access this forum? More often than not I get a "cannot find this page" browser error. There are posts during this time, so I don't think the forum was down.

[davegugg]

I'm not sure if that is something to just throw in at the end of a thread, but that does indeed happen to me as well. Not sure why.

[jlt199]

I have another question... related this time!

Can I adapt the code in my first post so that the array is filled from values in non-consecutive columns? All the columns have the same number of rows. For example I want an array with values of columns B, M, D, F and K.

Also, how can I fill a column in the worksheet with a single column from a 2D array, for example if I want column L in the worksheet to be filled with column 2 of a (n X 5) array?

Many thanks

[jlt199]

I've tried

Please Login or Register  to view this content.

I've just included the relevent lines of code. But my array seems to be empty. Any suggestions?

Thanks

[DonkeyOte]

Can I adapt the code in my first post so that the array is filled from values in non-consecutive columns?

You can't populate the Array with non contiguous data set I'm afraid - at least NAFAIK - the Array will populate with only the first "area" - ie B2:Bn in your code.

Also, how can I fill a column in the worksheet with a single column from a 2D array, for example if I want column L in the worksheet to be filled with column 2 of a (n X 5) array?

You can use Index to retrieve the appropriate column, eg:

Please Login or Register  to view this content.

[jlt199]

Hmm..

In which case, how do I know if it's going to better to create a seperate array for each of the columns I want, or to create a single array with lots of columns I don't need?

Thanks again

[DonkeyOte]

I can't determine if that's an actual question or not... without knowing the specifics it's hard for anyone here to pass judgement.

Myself, I'm not an expert on VBA optimisation - for simplicity I'd be using a 2d Array with a contiguous range and work with the appropriate columns.

Hopefully romperstomper and the like will elaborate for you regards best practice in such circumstances.

You might also want to check out Chip Pearson's site as he has a lot of good functions etc for use with Arrays

[romperstomper]

How would you define 'better'?

[jlt199]

Well, from being able to follow the code point of view I guess seperate arrays would be 'better'.

However, my spreadsheet which I am using to fill the arrays may have upwards of 20000 rows. If I'm taking a single range to fill the array, which would be simplier and requires less instances of accessing the worksheet (which I understand has a major impact on speed), then realisically at what point (number of columns) am I likely to hit upon memory issues?

Would doubling the number of columns in the array (from 7 to 14 say) have a noticable affect on its operation?

Does there tend to be a convention for single and multiple arrays?

[romperstomper]

The best method will depend to a large extent on what you are doing with the arrays. If you need to work with entire columns as separate entities, then I'd use separate arrays. If not, I'd try to set the worksheet up in such a way as to have all the data in one contiguous range as DO mentioned. If you can't do that, my preference would be to use separate arrays rather than load a bunch of unnecessary data.

[jlt199]

Thanks for all your help. I nearly have it working now!