Why does Visual Basic Editor convert scientific notation to a number with # at the end?

user_10239691 0
Viewed 518

I can't find any information on this:

I am initializing a variable and when I enter a number in scientific notation such as 8.45673E11, but it converts it to its standard form with a compound sign at the end 845673000000# - I would like to know if this is supposed to happen? Is there a setting that controls this automatic conversion? What does the compound sign indicate?

Thanks so much! Lana

vbe vba excel
1 Answers

It's happening because, when you type code in the VBE, what you see is NOT what you get.

The current line of code is just plain text: it's not understood as code until you hit ENTER or navigate away from that line. Then, several things happen under the hood:

  • The VBE parses the current line of code, and determines if it can be compiled.
    • If it's an invalid statement, it either pops a "compile error" message box, or highlights the statement in red (depending on your VBE "compile on demand" settings).
  • The valid code is compiled to P-Code instructions, and stored alongside the source in the host document - although it's all still only in memory at this point.
  • The P-Code is translated back into VBA source, and the line of code you just entered is re-written in-place.

So when you write Foo = 8.45673E11, the VBE determines that the RHS of the assignment is a double literal, and compiles the corresponding P-Code instruction; when that instruction is translated back into VBA source code, it's "re-written" as an explicit double literal, 845673000000# (# suffix being a type hint meaning "that's a Double") because P-Code doesn't care for the representation of a number (e.g. scientific notation), only the number itself; the type hint character is added so that the type is already known and doesn't need to be re-evaluated again next time the expression is compiled.

And when you write Foo = 8.45673E11, you still get Foo = 845673000000#, because the P-Code doesn't care for that whitespace.

That's also why given Public Foo As Double if you type foo = 123 you'll get Foo = 123, because the internal symbol table has Foo with an uppercase F.

user_11885130
Related