63

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?

Community
  • 1
  • 1
srinivasan
  • 823
  • 1
  • 8
  • 14

1 Answers1

106

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

Other type declaration characters are:

  1. Integer %
  2. Long &
  3. Currency @
  4. Single !
  5. Double #
  6. 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 :)

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • +1 Even since the old VB times, I've found these shortcuts very handy. :) – Pradeep Kumar Jun 05 '12 at 05:16
  • 4
    @PradeepKumar: Yeah these shortcuts are handy but I never liked them :) I have always been very "expressive" with my declarations, I guess :P – Siddharth Rout Jun 05 '12 at 05:49
  • 1
    Very well explained. I've seen a lot of developers getting confused with this. They don't seem to be able to distinguish between the fact that the memory variable used for calculation is different from the the target variable of assignment, and their data-types can be different. :) – Pradeep Kumar Jun 05 '12 at 05:53
  • 3
    +1 I wouldn't have the strength to explain all that, I would have just said "don't do it" :-P – JimmyPena Jun 05 '12 at 14:02
  • what? 3276700 is 0,15% of the max value of an int? – Rasmus Damgaard Nielsen Jul 09 '15 at 11:26
  • 3
    +1 fantastic explanation, would just like to add that the hash symbol can also be used as a preprocessor command in vba if used at the beginning of the line http://stackoverflow.com/questions/6325486/if-else-end-if-what-do-the-hash-signs-mean-in-vba – Clyde Mar 23 '16 at 13:51