Help understanding variant arrays.

raven_squire

Board Regular
Joined
Jan 13, 2013
Messages
99
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.
 
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() ???????
 
Last edited:
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,279
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top