Excel VBA: "&" after variable in DIM statement like "name&"

Question

I came across a DIM statement:

Dim a() As Byte, u&, j&, s&, n&, q

The first part defining an array of bytes is straight forward enough, but what does the rest of it do? And why the "&"s after the variable names?

Answer 1

In VBA, if you declare Dim a, b, c as Long only the last variable is declared as Long, the other two - a and b are declared as Variant. Thus, some people use specific shortcuts, when they want to declare multiple variables on a single line. These shortcuts or abbreviations are also known as Type Declaration Suffixes.

One of these shortcuts is &, which declares the variable as Long. If you do not write the & at the end it would be declared as a Variant.

Thus, your line Dim a() As Byte, u&, j&, s&, n&, q is the same as:

Dim a() As Byte, u As Long, j As Long, s As Long, n As Long, q As Variant

In general, this method is not advised, because you should always remember what the signs are for. However, it is shorter and it works. This is a summary of the shortcuts for variable declaration:

Dim iNumber%     'Integer  
Dim lAverage&    'Long  
Dim sngTotal!    'Single  
Dim dbTotal#     'Double  
Dim cProfit@     'Currency  
Dim sFirstName$  'String  
Dim llDiscount^  'LongLong on 64 bit  

You can also declare the type of a literal, when you assign it to a Variant type. E.g., in this case 10 would be of type Double, because of the #:

Public Sub TestMe()
    Dim myVariant
    myVariant = 10#
    Debug.Print VarType(myVariant) = vbDouble 'returns True
End Sub

Source