5

I'm always afraid to declare things as just Variants under the assumption that an unnecessary large amount of memory will be allocated.

Recently working to improve performance of a spreadsheet I however got the opposite impression (see edit below): Dim myarray() as Variant was improving performance compared to Dim myarray() as String

What would be the key differences and consequences of the two declarations?

Could not find clear guidance here: https://msdn.microsoft.com/en-us/library/aa711948.aspx

EDIT: Controlled Performance Test

I ran a controlled performance test (take the dim myarray() as Variant version, make a copy and change two variables to Dim myarray() as String)

As you can see below I was mistaken, performance difference is NOT significant.

Dim myarray() as Variant VERSION

Start 4:05:47 PM
FXLoaded 4:05:47 PM 00:00 TDLoaded 4:06:38 PM 00:51 LisofPCTD 4:06:57 PM 00:19 YDLoaded 4:07:47 PM 00:50 LisofPCYD 4:08:14 PM 00:27 PrintCoBTD 4:08:46 PM 00:32 PrintCoBYD 4:09:18 PM 00:32 Total 03:31 03:31

Dim myarray() as String VERSION

Start 4:25:53 PM
FXLoaded 4:25:53 PM 00:00 TDLoaded 4:26:53 PM 01:00 LisofPCTD 4:27:10 PM 00:17 YDLoaded 4:28:07 PM 00:57 LisofPCYD 4:28:32 PM 00:25 PrintCoBTD 4:29:03 PM 00:31 PrintCoBYD 4:29:34 PM 00:31 Total 03:41 03:41

BuckTurgidson
  • 289
  • 3
  • 8
  • 17
  • The only thing you changed was to switch out `Variant` for `String`? Did you time the code to verify your hypothesis that speed was improved? – Chrismas007 Jul 16 '15 at 19:24
  • 1
    The link you gave is to VB.Net which isn't directly relevant. I strongly prefer using variants for arrays in view of their flexibility, especially using the syntax `Dim myarray As Variant` (a variant which can hold an array rather than an array of variants. I've used such things with as many as 100,000 entries (or more) with no problem but YMMV. – John Coleman Jul 16 '15 at 19:27
  • @JohnColeman I don't have a source on this yet, but I think you are speaking to the answer right there. Arrays are the only type I have seen suggested should use the `Variant` type. When you use a `String` type, you have to think of all the extra characters needed to define the string as an array. – Chrismas007 Jul 16 '15 at 19:33
  • 2
    @Chrismas007 I think you are right that holding arrays is the main valid use-case of variants in VBA, although I think that for some reason you need to use variants as the loop variable if you want to loop through a user-defined collection or dictionary using a for-each loop, even if the collection is known to e.g. hold strings. – John Coleman Jul 16 '15 at 19:44
  • @Chrismas007. I'm not 1000% sure that's the only change I did but my code when from 7 to 3 min with basically this change. Its a CSV based data and there are strings, double, longs, integers. It feels like `Dim myarray() as Variant` optimizes each element of the array to the data type need. That is the sense of my question. – BuckTurgidson Jul 16 '15 at 20:18
  • Exactly what are you filling up your array with? – Mathieu Guindon Jul 16 '15 at 21:06
  • 1
    Feels like? You seriously need to benchmark it as it originally was and then make a single change and benchmark again. We humans are notoriously bad at judging time. – RubberDuck Jul 16 '15 at 21:50
  • A Variant array will simply accept whatever you put in it. If you pass non-string data to a string array there will be implicit coercion required, which will have some overhead. Why would you declare as String if that is not the data type? – Rory Jul 16 '15 at 22:37
  • There's a lengthy series of articles about optimizing string handling in Visual Basic [here](http://www.aivosto.com/vbtips/stringopt.html) – barrowc Jul 16 '15 at 23:12
  • If your 8GB PC is struggling it's probably Excel and not your `Variant` array. – Enigmativity Jul 17 '15 at 10:44

1 Answers1

2

It makes sense to declare an array as whatever data type the array will be holding, this provides clarity for anyone looking at your code in the future. It's generally better to give your code readability, sacrificing a slight save on memory (although this can be scenario dependant). For example, if your array will hold only strings, then declare the array as a string. Whereas, if your array will hold a mixture of integers and strings, then a variant should be used.

Essentially, declare the array as the data type which is logical for what the array is going to hold.

If you want to populate an array from a worksheet, then you must declare the variable as a variant. Attempting to populate an array (with values from a worksheet) which has been declared as a string will cause the 'Type mismatch' debug message, regardless of whether the values of the range are all strings.

Here is some MSDN documentation relating to arrays within VBA.

That's what I follow with declaring arrays anyway. Making code easier to read for any future developers > slight memory saves.

luke_t
  • 2,935
  • 4
  • 22
  • 38