Excel VBA: Variants in Array Variables

Question

A question on variants. Im aware that variants in Excel vba are both the default data type and also inefficient (from the viewpoint of overuse in large apps). However, I regularly use them for storing data in arrays that have multiple data types. A current project I am working on is essentially a task that requires massive optimistaion of very poor code (c.7000 lines)- and it got me thinking; is there a way around this?

To explain; the code frequently stores data in array variables. So consider a dataset of 10 columns by 10000. The columns are multiple different data types (string, double, integers, dates,etc). Assuming I want to store these in an array, I would usually;

dim myDataSet(10,10000) as variant

But, my knowledge says that this will be really inefficient with the code evaluating each item to determine what data type it is (when in practise I know what Im expecting). Plus, I lose the control that dimensioning individual data types gives me. So, (assuming the first 6 are strings, the next 4 doubles for ease of explaining the point), I could;

dim myDSstrings(6,10000) as string
dim myDSdoubles(4,10000) as double

This gives me back the control and efficiency- but is also a bit clunky (in practise the types are mixed and different- and I end up having an odd number of elements in each one, and end up having to assign them individually in the code- rather than on mass). So, its a case of;

myDSstrings(1,r) = cells(r,1)
myDSdoubles(2,r) = cells(r,2)
myDSstrings(2,r) = cells(r,3)
myDSstrings(3,r) = cells(r,4)
myDSdoubles(3,r) = cells(r,5)
..etc...

Which is a lot more ugly than;

myDataSet(c,r) = cells(r,c)

So- it got me thinking- I must be missing something here. What is the optimal way for storing an array of different data types? Or, assuming there is no way of doing it- what would be best coding-practise for storing an array of mixed data-types?

Empery

JMax has an answer for what is probably your real question. For a discussion of the tradeoffs when using variants, see here: stackoverflow.com/q/5488314/58845 – Clari

You could make an array of arrays, with each contained array being of a specific type. You should be able to index via BigArray(3)(4) for example. Dick's comments still apply. – Trounce

the datatype in variant arrays is auto-adjusted, which I understand as if Excel can figure out the DataType for each element it will adjust it automatically as Variant arrays can store elements of different types – Selwyn

Answer 1

Never optimize your code without measuring first. You'll might be surprised where the code is the slowest. I use the PerfMon utility from Professional Excel Development, but you can roll your own also.

Reading and writing to and from Excel Ranges is a big time sink. Even though Variants can waste a lot of memory, this

Dim vaRange as Variant
vaRange = Sheet1.Range("A1:E10000").Value
'do something to the array
Sheet1.Range("A1:E10000").Value = vaRange

is generally faster than looping through rows and cells.

My preferred method for using arrays with multiple data types is to not use arrays at all. Rather, I'll use a custom class module and create properties for the elements. That's not necessarily a performance boost, but it makes the code much easier to write and read.

Anderson

Thanks Dick- this is the type of things I was after. Both the looping and degree of optimisation required are sort of irrelevant in this- the code takes 15 minutes to run and I have a fairly detailed view of which bits are causing the bottleneck (overuse of variants is certainly not the significant cause!). It was more a generic question; I hadnt thought of using class modules to build properties- I may post a new question on this approach depending on what resources I can find. Many thanks. – Empery

Found this Q that has fairly good example of how it can be done. #2162166 – Empery

Answer 2

I'm not sure your bottleneck comes from the Variant typing of your array.

By the way, to set values from an array to an Excel range, you should use (in Excel 8 or higher):

Range("A1:B2") = myArray

On previous versions, you should use the following code:

Sub SuperBlastArrayToSheet(TheArray As Variant, TheRange As Range)
  With TheRange.Parent.Parent 'the workbook the range is in
    .Names.Add Name:="wstempdata", RefersToR1C1:=TheArray
    With TheRange
      .FormulaArray = "=wstempdata"
      .Copy
      .PasteSpecial Paste:=xlValues
    End With
    .Names("wstempdata").Delete
  End With
End Sub

from this source that you should read for VBA optimization.

Yet, you should profile your app to see where your bottlenecks are. See this question from Issun to help you benchmark your code.

Discount

And the other way round: myArray = range("a1:b2") !! – Matelot

myArray = range("a1:b2").value – Carapace