Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What are possible suffixes after variable name in VBA?

Tags:

vba

As I've already figured out, there is at least six of them: !@#$%&.

Here is snip:

Dim A!, B@, C#, D$, E%, F&
Debug.Print "A! - " & TypeName(A)
Debug.Print "B@ - " & TypeName(B)
Debug.Print "C# - " & TypeName(C)
Debug.Print "D$ - " & TypeName(D)
Debug.Print "E% - " & TypeName(E)
Debug.Print "F& - " & TypeName(F)

Outputs

A! - Single
B@ - Currency
C# - Double
D$ - String
E% - Integer
F& - Long

Where is documentation on this syntax sugar?

What other possible suffixes are there?

Is there one for Date?

like image 729
archimed7592 Avatar asked Feb 10 '11 14:02

archimed7592


People also ask

Which can be used as a variable name in VBA?

As of Microsoft Excel 2010, VBA variable names are subject to the following restrictions: They must begin with a letter. Numbers and underscores can be used later in the name. They cannot exceed 255 characters in length.

What is suffix in Visual Basic?

Visual Basic. Dim a() As Byte, u As Long, j As Long, s As Long, n As Long, q As Variant Dim a() As Byte, u&, j&, s&, n&, q.

How do you declare a dynamic variable in VBA?

A dictionary would probably help in this case, it's designed for scripting, and while it won't let you create "dynamic" variables, the dictionary's items are dynamic, and can serve similar purpose as "variables". You can check for existence using the . Exist method if you need to worry about overwriting or not.


1 Answers

These suffixes are type hints, and the link in the accepted answer is outdated.

Dim someInteger% '% Equivalent to "As Integer"
Dim someLong&    '& Equivalent to "As Long"
Dim someDecimal@ '@ Equivalent to "As Currency"
Dim someSingle!  '! Equivalent to "As Single"
Dim someDouble#  '# Equivalent to "As Double"
Dim someString$  '$ Equivalent to "As String"

Dim someLongLong^  '^ Equivalent to "As LongLong" in 64-bit VBA hosts

So you had them all, except ^ for LongLong, introduced in VBA7 for 64-bit host applications. Exactly why Microsoft introduced a new type hint for a new value type is beyond me though.

It's more syntax poison than syntax sugar though, and dates all the way back from ancestral, dinosaurian versions of BASIC before the As clause was a thing, for example in this Commodore 64 BASIC 2.0 fizzbuzz code:

1000 REM INIT VARIABLES
1010 LET FIZZ$ = "FIZZ"
1011 LET BUZZ$ = "BUZZ"
1020 LET FIZZ% = 3
1021 LET BUZZ% = 5
1030 LET MIN% = 1
1031 LET MAX% = 15
1100 PRINT FIZZ$ + ":" + STR$(FIZZ%)
1101 PRINT BUZZ$ + ":" + STR$(BUZZ%)
1102 PRINT FIZZ$ + BUZZ$ + ":" + STR$(FIZZ%*BUZZ%)
1105 PRINT

As you can see, type hints aren't the only paleo-code that VBA supports: line numbers, Rem comments, and explicit Let value assigments were also a thing in 1982. Avoid them at all costs.

In literals, prefer explicit conversions over type hints:

Debug.Print TypeName(32&) 'prints Long
Debug.Print TypeName(CLng(32)) 'prints Long

Ask yourself not whether you can, ask yourself whether you should. -- unknown

like image 60
Mathieu Guindon Avatar answered Oct 15 '22 05:10

Mathieu Guindon