Use of symbol # (hash) in VBA Macro

excel, vba

Solution

The type-declaration character for Double is the number sign (#). Also called HASH

Other type declaration characters are:

- Integer %

- Long &

- Currency @

- Single !

- Double #

- String $

Don't understand the significance of # here.

It implies that when the expression is evaluated, the number in front of the type declaration character is treated as a specific data type instead of as a Variant.

See this example, which are basically the same.

Sub Sample1()
    Dim a#

    a = 1.2

    Debug.Print a
End Sub

Sub Sample2()
    Dim a As Double

    a = 1.2

    Debug.Print a
End Sub

EDIT

Let me explain it a little more in detail.

Consider this two procedures

Sub Sample1()
    Dim a As Double, b As Integer

    b = 32767
    a = b * 100

    Debug.Print a
End Sub

Sub Sample2()
    Dim a As Double, b As Integer

    b = 32767
    a = b * 100#

    Debug.Print a
End Sub

Question: One of them will fail. Can you guess which one?

Ans: The 1st procedure `Sub Sample1()` will fail.

Reason:

In `Sample2`, when you do `b * 100#` the result of calculation will be of type `Double`. Since it is within the limits of Double, so the calculation succeeds and the result is assigned to variable `a`.

Now in `Sample1`, when you do `b * 100` the result of calculation will be of type `Integer`, since both the operands are of type integer. But the result of calculation exceeds the limits of Integer storage. As a result it will error out.

Hope it helps :)

Problem

What is the meaning of the use of the `#` symbol in Excel VBA? It is used like this: ``` a = b /100# ``` I don't understand the significance of `#` after the `100`?

Original source

Related problems