How to declare multiple variables with specifying type using As in VBA?

excel vba declaration variable-declaration
Kouichi C. Nakamura picture Kouichi C. Nakamura · Mar 8, 2019 · Viewed 14.1k times · Source

According to this documentation by Microsoft, the following code can be used to make sure;

a, b, and c are all Single; x and y are both Double 

Dim a, b, c As Single, x, y As Double, i As Integer  
> a, b, and c are all Single; x and y are both Double

The logic behind this is as follows

You can specify different data types for different variables by using a separate As clause for each variable you declare. Each variable takes the data type specified in the first As clause encountered after its variable name part.

However, when I checked with the debugger or MsgBox VarType(a) output, this is not the case.

enter image description here

As you can see, it appears that As is only working for the variables right before itself, ie., c, y and i. All others are Variant/Empty and VarType returns 0.

Is this just the documentation being wrong, or am I missing something obvious?

Microsoft Visual Basic for Application 7.1.1056 Excel 2016 (Windows 10)

Answer

jsheeran picture jsheeran · Mar 8, 2019

The documentation you've linked to isn't wrong, but it's written for VB.NET and not VBA.

In VBA, as you've observed, any variable declarations that aren't immediately followed by As <type> will be Variant.

Therefore you'd need to write: 

Dim a As Single, b As Single, c As Single, x As Double, y As Double, i As Integer

Related questions

Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
How do I declare a global variable in VBA?

I wrote the following code: Function find_results_idle() Public iRaw As Integer Public iColumn As Integer iRaw = 1 iColumn = 1 And I get the error message: "invalid attribute in Sub or Function" Do you know what I did wrong? I tried …

Read More
How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

How can I use regular expressions in Excel and take advantage of Excel's powerful grid-like setup for data manipulation? In-cell function to return a matched pattern or replaced value in a string. Sub to loop through a column of data …

Read More