CODEWITHSUNDEEP
vbaexcel
user9103440
user9103440

Reputation: 43

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

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?

Upvotes: 4

Views: 6089

Answers (2)

Vityata
Vityata

Reputation: 43565

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

Upvotes: 8

JohnyL
JohnyL

Reputation: 7122

There's also one type @Vityata didn't mention - Decimal, but you can't declare this type directly (in fact, there's no even keyword for this type). To have Decimal type, you need to do the following:

Dim d As Variant
d = CDec(10.55)
'Check:
MsgBox TypeName(d) 'Shows "Decimal"

Upvotes: 0

Related Questions