"Dim myarray() as String" VS "Dim myarray() as Variant"

Question

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

Answer 1

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.