Help understanding variant arrays.

[raven_squire]

Hello,

I taught myself to make macros in Excel VBA and I have some gaps in my knowledge. I recently looked at optomising some of my code and learnt that you can use a variant array to store varing data types in the same array. Previously I used a string array for both text and numeric values. As I understand it Variant is a data type where excel automatically manages the data type depending on how it evaluates the data.

Is there a way to force a variant to be treated in a certan way e.g. specify what data type that variant is? I want to use variants so that I can have an array of mixed data types. But I also want to avoid a situation where I get unexpected results because excel is treating the data as a different data type than what I was expecting.

I thought maybe I could do data type conversions to ensure the correct data type is used however this would also degrade performance. Many of my macros work on large data sets and take some time to complete as it is.

What is the best way to use variant types? Any help, tips or links to explanations would be apreciated.

 

[raven_squire]

I don't think you quite understand.

Yes using arrays in memory is much quicker than using ranges on the sheet. When I first developed my macro I was using data from the worksheet and I calculated it would take 36 hours to process 24 hours worth of data. Most of my data is being improted form CSV files and is not on the worksheet in the first place. All of my data is being processed in arrays stored in memory.

The real question is why is a Variant Array in memory quicker than an array in memory of a defined data type e.g.

Dim Slow As single() VS Dim Fast as variant() ???????

 

[offthelip]

Once you have got an array into memory I would expect a defined data type calculation to be either the same speed as a variant array or possible even slightly faster. It sounds as though you are already doing the right thing by doing all the calculation in memory rather than on the worksheet. So really the issue is only about how you load the array, if you are doing that efficiently then there is no advantage (only disadvantages as you have found out) in using variant arrays.