CODEWITHSUNDEEP
excelvbadeclarationvariable-declaration
Kouichi C. Nakamura
Kouichi C. Nakamura

Reputation: 1108

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

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)

Upvotes: 19

Views: 55889

Answers (2)

FFFffff
FFFffff

Reputation: 1070

In VBA, when you declare

Dim a, b, c As Single

What it does is equivalent to this:

Dim a As Variant, b As Variant, c As Single

I believe the best practice is to always declare variable in a separate row. It prevents this bug and also allows for faster scanning of the code. So the code would look like this:

Dim a As Single
Dim b As Single
Dim c As Single

Upvotes: 8

jsheeran
jsheeran

Reputation: 3037

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

Upvotes: 33

Related Questions