What are possible suffixes after variable name in VBA?

Question

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?

Run away! Run away! Run away some more! These are awful, horrible leftovers that shouldn't be used, period. The closest I ever come is that I use Right$, Left$, Mid$ and Trim$ to force the return of strings rather than the Variant returned by the versions without $, — RolandTumble
– RolandTumble, Commented Feb 10, 2011 at 18:57
@Roland, agreed! I use them for exactly one purpose: stackoverflow.com/questions/3047239/… — jtolle
– jtolle, Commented Feb 11, 2011 at 1:27
Agree with Roland, but still +1 for the question and the term syntax sugar It's like code giving you a kiss. (Yes, I'm lonely.) — ray
– ray, Commented May 31, 2011 at 14:42

Graham · Accepted Answer · 2017-09-21 17:57:56Z

13

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

edited Sep 21, 2017 at 17:57

Graham

7,86020 gold badges67 silver badges92 bronze badges

answered Dec 8, 2016 at 16:32

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

R.G. Over a year ago

There is a good reason why a suffix for LongLong was added; since VBA does not distinguish between signed and unsigned integers, hexadecimal notations like &hFFFF and &hFFFFFFFF are ambiguous, depending on whether the value is interpreted as Integer, Long or LongLong and the deprecated suffixes are the only way to disambiguate in such cases. Thus, &hFFFFFFFF will be interpreted as Long by default and will result in a value -1, while &hFFFFFFFF^ will be interpreted as LongLong and result in a value 4294967295.

R.G. Over a year ago

Moreover, even in decimal notation, if you input any integer value in your code that cannot be represented as Long, VBA will insist that you explicitly coerce it to Double, Currency or LongLong; e.g. if you try to use a literal like 1000000000000 in your code, the VBA editor will automatically change it to 1000000000000# (Double), unless you explicitly declare it as 1000000000000@ (Currency) or 1000000000000^ (LongLong)

ktharsis · Accepted Answer · 2011-02-10 16:26:53Z

4

A full(?) list is here http://support.microsoft.com/kb/110264 under Variable and Function Name Prefixes. As Remou said - they are not recommended (the article says that use is "discouraged"). I believe you covered them all in your list. Date is technically a variant (stored as a floating point) so no shortcut for that one.

answered Feb 10, 2011 at 16:26

ktharsis

3,1901 gold badge21 silver badges30 bronze badges

2 Comments

Bernardo Dal Corno Over a year ago

broken link, couldnt find new one

NewSites Over a year ago

The linked page is archived at web.archive.org/web/20110116055510/http://support.microsoft.com/… .

Fionnuala · Accepted Answer · 2011-02-10 15:24:19Z

3

These are long since deprecated and only remain for backward compatibility AFAIK. Declare your variables as the type required. You can also coerce variables to type.

edited Feb 10, 2011 at 15:24

answered Feb 10, 2011 at 14:36

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

5 Comments

HolisticElastic Over a year ago

It's pretty handy, you know :). IMHO VB itself is a huge backward compatibility and no one should ever write a program on it, however, things are a little bit different in the tough reality.Have you any reference for me, so I can look it up and decide whether to use it or not?

PowerUser Over a year ago

I script things daily in VBA. 'twould be nice to move to VB.Net, but this isn't a tech company and we probably won't upgrade to a newer version of Office until we absolutely have to. (This doesn't really answer your question. I just felt like saying it).

Fionnuala Over a year ago

You can get it from the VB6 information thevbprogrammer.com/Ch02/02-02-DataTypesNamingConv.htm. Note that not all types are available, which would be a major reason to avoid this notation, quite apart from clarity. All data types are available with intellisense and the use of As.

Fionnuala Over a year ago

@PowerUser It is amazing how often people who criticize VBA forget that it is extremely useful for small companies with no in-house IT and no budget for buying in IT. VBA is a great deal better than repetitive manual work, and a great deal better than nothing at all.

R.G. Over a year ago

While those suffixes may have become redundant for variable declarations, they are still essential to declare the type of your literals. E.g., &hFFFF = &hFFFFFFFF will evaluate to True (the left side being interpreted as Integer and the right side being interpreted as Long, both evaluating to -1), while &hFFFF& = &hFFFFFFFF^ will evaluate to False (the left side being coerced to Long and evaluating to 65535 and the right side being coerced to LongLong and evaluating to 4294967295)

Collectives™ on Stack Overflow

What are possible suffixes after variable name in VBA?

3 Answers 3

2 Comments

2 Comments

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

5 Comments

Linked

Related