Use of symbol # (hash) in VBA Macro

by

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 🙂

More Related Contents:

  1. Loop through files in a folder using VBA?
  2. How to error handle 1004 Error with WorksheetFunction.VLookup?
  3. How can Excel VBA open file using default application
  4. How to compare two entire rows in a sheet
  5. Run same excel macro on multiple excel files
  6. how to detect whether VBA excel found something?
  7. VBA : save a file with UTF-8 without BOM
  8. Excel VBA: Can’t get a match, error “Unable to get the Match property of the WorksheetFunction class”
  9. Excel VBA looping through multiple worksheets
  10. When one should use Set [e.g for SpecialCells return value]?
  11. Excel: macro to export worksheet as CSV file without leaving my current Excel sheet
  12. Is there a faster CountIF
  13. Excel VBA How to detect if something was pasted in a Worksheet
  14. Getting error Procedure too large in VBA Macros (Excel)
  15. How to add data validation to a cell using VBA
  16. How to do late binding in VBA?
  17. How to break long string to multiple lines
  18. How to copy a chart from Excel to PowerPoint?
  19. vba paste not working
  20. How to add items to a combobox in a form in excel VBA?
  21. mac excel vba loop : from list & then export as pdf
  22. Can Advanced Filter criteria be in the VBA rather than a range?
  23. How do I avoid run-time error when a worksheet is protected in MS-Excel?
  24. In Excel VBA, what is the way to check if the web page is fully loaded?
  25. Excel Useform: How to hide application but have icon in the taskbar
  26. Reading Web Pages using Excel VBA
  27. Excel VBA: auto click and open file from website
  28. How to have vba execute every 10 minutes?
  29. Read and write from/to registry in VBA
  30. Unable to read data from a CSV using ADO due to the driver thinking I am working with integers/number and showing nulls instead of text

Leave a Comment