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

i'm afraid declare things variants under assumption unnecessary large amount of memory allocated.

recently working improve performance of spreadsheet got opposite impression (see edit below): dim myarray() variant improving performance compared dim myarray() string

what key differences , consequences of 2 declarations?

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

edit: controlled performance test

i ran controlled performance test (take dim myarray() variant version, make copy , change 2 variables dim myarray() string)

as can see below mistaken, performance difference not significant.

dim myarray() 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() 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

it makes sense declare array whatever data type array holding, provides clarity looking @ code in future. it's better give code readability, sacrificing slight save on memory (although can scenario dependant). example, if array hold strings, declare array string. whereas, if array hold mixture of integers , strings, variant should used.

essentially, declare array data type logical array going hold.

if want populate array worksheet, must declare variable variant. attempting populate array (with values worksheet) has been declared string cause 'type mismatch' debug message, regardless of whether values of range strings.

here msdn documentation relating arrays within vba.

that's follow declaring arrays anyway. making code easier read future developers > slight memory saves.