How do you designate a Currency literal in VBA

Question

If I wanted to define a Date literal, I would write the following:

Dim myDate as Date = #1/1/2021#

What character should I use to do an analogous thing for a Currency variable? That is

Dim amt as Currency = ?200.00? What character should "?" be when using VBA?

According to the linked Q and https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/currency-data-type it is `@` — Tim Williams, Feb 04 '21 at 03:23
@TimWilliams I don't think OP is trying to use type hints here. — BigBen, Feb 04 '21 at 03:24
Not your bad... the wording confused me too. Plus the code snippet looks like VB.Net. — BigBen, Feb 04 '21 at 03:25
You can simply assign the currency type a number - VBA will auto cast the number. So at the end of the day, if you define a currency type, anything you assign to it, if legal WILL RESULT in a currency data type - in effect once you defined the data type as currency, then you are good to go. So, simply use + assign the variable a number - any legal currency number and you are good to go. As outlined below, you can use the older legacy #, @ etc. (@ for currency - but it not really required in most cases if you strong typed the variable). — Albert D. Kallal, Feb 04 '21 at 14:25

Mathieu Guindon · Answer 1 · 2021-02-04T04:20:04.383

In VBA a declaration cannot legally be joined with an assignment in the same instruction, so that would be:

Dim myDate As Date
myDate = #1/1/2021#

For the sake of this discussion it's important to differentiate the variable from the literal value, because the declared type of the variable is irrelevant to the type of the literal - this is perfectly confusing, but perfectly legal too:

Dim myDate As Long
myDate = #1/1/2021#

We're talking about the literals here.

A numeric literal is an Integer literal if it's between -32767 and 32767, a Long literal if it's between -2147483647 and 2147483647, and a Double otherwise (the VBE automatically adds a # type hint then).

That means the 200 in myAmount = 200 is an Integer literal, and if we typed 200.00 the VBE would "prettify" it to 200# (making it a Double literal).

The type hint character to use for a Currency literal, is @, so the 200 in myAmount = 200@ is a Currency value that the VBA compiler understands as such.

It doesn't matter though, because VBA will do lots of widening (narrowing too) implicit type conversions for us: if we assign an Integer literal to a Currency variable, the Integer value is converted to "fit" the reserved Currency "bucket". So if we declared myAmount As Currency and then assigned it to the literal value 200 (an Integer literal), if we asked VBA what type myAmount is, we would get the declared type - Currency.

In other words it's perfectly legal to assign an Integer or a Double literal to a Currency variable, and that makes type-hinted literals somewhat awkward beasts that don't really belong, in the vast majority of situations:

Dim myAmount As Currency

myAmount = 200 'integer literal
Debug.Print TypeName(myAmount) 'currency

myAmount = 200# 'double literal
Debug.Print TypeName(myAmount) 'currency

myAmount = 200@ 'currency literal
Debug.Print TypeName(myAmount) 'currency

Things get groovy when we drop the declared type and use a Variant instead (implicit or not):

Dim myAmount 'As Variant

myAmount = 200 'integer literal
Debug.Print TypeName(myAmount) 'integer

myAmount = 200# 'double literal
Debug.Print TypeName(myAmount) 'double

myAmount = 200@ 'currency literal
Debug.Print TypeName(myAmount) 'currency

A date literal needs # "delimiters" on both sides, because the / date separator character has another grammatical meaning in VBA (and many other languages too): in other contexts, it's the division operator: in a sense, # delimiters are more closely related to the " double quotes that delimit string literals, than they are to @ type hints.

Note that type hints can also be used to declare the type of a constant, e.g. `Const myAmount = 5@`. The constant is not a variant in this case. That's one of the few cases where I sometimes use type hints to save some characters. — Erik A, Feb 04 '21 at 07:51
@ErikA a good point, although IMO `As Currency` scores much higher in terms of readability, than `5@` ...the few extra characters to type are worth it ;-) — Mathieu Guindon, Feb 04 '21 at 08:06

score 2 · Answer 2 · answered Feb 04 '21 at 07:50

Here is a list of VBA type-declaration characters I collected:

/ means no type-declaration character exists for that datatype.
Also no type-declaration character exists for any composite data types such as arrays or structures.

Type	Type-declaration character
Boolean	/
Byte	/
Currency	`@`
Date	/
Decimal	/
Double	`#`
Integer	`%`
Long	`&`
LongLong (64-bit VBA only)	`^`
Object	/
Single	`!`
String	`$`

You can prove that for example in die Immediate-Window:

?TypeName(1%) 'Returns Integer
?TypeName(1&) 'Returns Long
?TypeName(1@) 'Returns Currency
?TypeName(1#) 'Returns Double
?TypeName(1^) 'Returns LongLong
?TypeName(1!) 'Returns Single

How do you designate a Currency literal in VBA

2 Answers2