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.

 

[PhilS2520]

Hi raven_squire,

Is there a way to force a variant to be treated in a certan way e.g. specify what data type that variant is?

If you know the data type, you should declare the variable to that data type.

If arrays are what is missing in your body of knowledge try this link:

https://excelmacromastery.com/excel-vba-array/

 

[raven_squire]

Thanks for the link Phil. I had seen that article quite a few times. This time I read it end to end and got a few nuggets of wisdom, however it didn’t clarify storing multiple data types in an array.
I understand the different the data types (except for variants that is) and I very much prefer to properly declare my variables.
Can I ask how you would store data of different types in the same array?
It seems to me that I have to hide my numbers in a string array or deal with excel determining what data type my data is in a variant array.
I try to avoid variants in fear of having unexpected outcomes, but is this something I should let go. Is using variants a good practice?
I deal with two dimensional dynamic arrays with mixed data types often, so I thought it would be best to figure out the ideal way to handle them.

 

[raven_squire]

Just an update.
I started using option explicit and cleaned up my variables a lot. I converted my string arrays with mixed data to variant arrays. Option explicit then started identifying type miss matches for me so I could do type conversions if necessary.

 

[offthelip]

I use variant arrays all the time in EXCEL, the reason is the EXCEL cell is of type variant. you can type 43267 into a cell and it will appear as 43267 however if you now format as short date, it will appear as todays date, if you format it as text it will jump from being right justified to left justified. I think this is the main reason for using variant types, it makes it very easy to read from and write to the worksheet and this makes it a much faster way of programming that operating on the worksheet.. What is a slight deficiency in eXCEL is that whenever you load a variable from the worksheet the variable type will be variant, you can't load a variable and automatically get a varaible of the type which ties up with the formatting of the cell.
I notice your comment:

Many of my macros work on large data sets and take some time to complete as it is.

If you are not using variant arrays to speed up your VBA then I am not surprised that your code is slow. This is the second good reason to use variant arrays, it is usually much faster ( a factor of up to 1000) to load a whole worksheet into a variant array , processthe array in memory and then write the resilts out to another variant array before finally writing the array back to the worksheet.

 

[=ODIN=]

in excel, i very rarely declare arrays in size or type... usually i'm feeding a worksheet range into an array. to do this for range a1 to c5 it would look like this:

sub arrTest()
dim theArray
theArray = activeworkbook.activesheet.range("a1:c5")
end sub

by using code like that, it will load a 2d array with the values in range a1:c5 with whatever values are there -- text or numbers or double... whatever.

 

[raven_squire]

So the typical workflow of one of my main spread sheets goes like this.

• Load a CSVa file and create an array 7 x 333,000.
• Loop through that array and determine where different data categories start and make an array 3 x 70 to be used for indexing.
• Load multiple CSVb files and build an array 8 elements wide by 100,000 long.
• Loop through CSVb and match each of the 100,000 to CSVa using the index to help speed things up. Discard out of bounds data and add matches to a new array 16 x 10,000 (discard the old array)
• Load another array 7 x 2000, loop through the 10,000 array find matches and dump out of bounds data and end up with an array of 22 x 8,000
• Output that 22x8,000 to a sheet and save it.
• Filter the new sheet for exceptions and move about 200 rows to another spread sheet that collects exceptions every time the macro is executed.

So that is the volume of data I am working with. The matching criteria are long winded but involve calculating distances using GPS coordinates etc. This takes about 2 minutes to process and is processed daily. I have other projects that require more processing but they are mainly executed only once so they could take an hour and I would still be happy.

 

[raven_squire]

OffTheLip, I think you make my origional point for me. An array is the equivelent of data type "General". General can be a pain to work with on a sheet as is drops leading and trailing zeros. It converts dates to US time format, when using > text gets evaluated different to numbers etc. Variants might make it easy to store data but it comes at a cost when you expect the data to be handled in a set way.

I know know why I only get variant arrays when loading data from the spread sheet. Thank you.

I do have to ask why and how does using a variant array speed up processing over using a defined data type??

 

[raven_squire]

=ODIN= By specifying the size of the range you are in effect specifying the size of the array. I use this method often to collect configureation variables, but I mostly process data from CSV files and use the spread sheet to output the data in a conveniant way.

 

[offthelip]

Using variant arrays speeds up your code because it allows you to do the same calculations in memory rather that operating on the worksheet. Every access to worksheet takes along time so minimises the number of access will speed up your code. You have to use variant arrays because this is the only type that Excel allows to be read from and written to the worksheet. as an example have a look at and try running these two subroutines which basically do the same ( pointless) calculation:

Sub slow()

tt = Timer()
'initialise
 For j = 1 To 10
  Cells(j, 1) = 0
 Next j
For i = 1 To 1000
 For j = 1 To 10
  Cells(j, 1) = Cells(j, 1) + 1
 Next j
Next i
MsgBox (Timer() - tt)


End Sub


Sub fast()
tt = Timer()
Dim outarr(1 To 10, 1 To 1) As Variant



'initialise
 For j = 1 To 10
  outarr(j, 1) = 0
 Next j
Range(Cells(1, 1), Cells(10, 1)) = outarr

For k = 1 To 500
inarr = Range(Cells(1, 1), Cells(10, 1))
For i = 1 To 1000
 For j = 1 To 10
  inarr(j, 1) = inarr(j, 1) + 1
 Next j
Next i


Next k
Range(Cells(1, 1), Cells(10, 1)) = inarr
MsgBox (Timer() - tt)


End Sub

Note that the variant array is doing the loop 500 times compare to the cells access