"Dim myarray () as a string" VS "Dim myarray () as a variant"

Question

I am always afraid to declare things as simple as Options under the assumption that an unnecessary large amount of memory will be allocated.

While working on improving spreadsheet performance recently, I got the opposite impression (see below): Dim myarray() as Variant improved performance compared to Dim myarray() as String

What will be the key differences and consequences of these two statements?

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

EDIT: Controlled Performance Test

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

As you can see below, I was wrong, the difference in performance 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 any data type that will hold the array, which gives clarity to anyone looking at your code in the future. As a rule, it is better to give readability of the code, sacrificing a small amount of memory storage (although this may be script dependent). For example, if your array contains only strings, then declare the array as a string. Whereas if your array contains a mixture of integers and strings, then you should use the option.

Essentially, declare the array as a data type that is logical for what the array will hold.

If you want to fill an array from a sheet, you must declare the variable as an option. Attempting to populate an array (with values ​​from a worksheet) that was declared as a string will debug the "Mismatch Type" message, regardless of whether the range values ​​are all strings.

Here are some MSDN documentation regarding arrays in VBA.

What will I follow with declaring arrays anyway. Make code easier to read for future developers> Saves small memory.