3

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

GeekyFreaky
  • 283
  • 1
  • 2
  • 9
  • 5
    8.45673E11 *is* 845673000000. No conversion is occurring. The `#` means that it is a double literal. See this: https://stackoverflow.com/q/3301733/4996248 – John Coleman Aug 27 '18 at 17:03
  • Have you tried `CDbl(ws.Cell().Value)?` – jcrizk Aug 27 '18 at 17:03
  • Hi John, yes I understand they are the same number but I am wondering why it's happening. Thanks for the post link. I'll check it out. – GeekyFreaky Aug 27 '18 at 17:06
  • The point is that not only are they the same number -- they are the same datatype. It might be a problem if the VBE automatically converted a double or float to an integer type. It is somewhat annoying that it converts the number into a less readable form. – John Coleman Aug 27 '18 at 17:15
  • Yes, I agree! Also, I am wondering if you know why and under what circumstances it does this? Is it just automatic for any literal in scientific notation? I'm playing around and when I declare a variable as Double and try to initialize it with a number, it still adds the # at the end of the number - regardless if it's in scientific notation or typed as a standard number. Seems overkill-redundant? – GeekyFreaky Aug 27 '18 at 17:25
  • 1
    If you type an integer value that's less than `2^31-1`, then the VBE won't be adding any *type hints* - the conversion (from `Long`, or from `Integer` if the literal value is less than `2^15-1`) will only happen later, when the expression is evaluated. With the type hint, conversion happens at compile-time - it's basically the compiler adding shortcuts for itself in the source code. – Mathieu Guindon Aug 27 '18 at 17:38

1 Answers1

5

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.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • 1
    Yay, it's Mathieu! Once again, thank you for a well-explained answer. Just one quick question: What does RHS stand for? :) – GeekyFreaky Aug 27 '18 at 17:45
  • 1
    @GeekyFreaky that's *right-hand side*, sorry :) there's also LHS for *left-hand side* - these terms are often thrown in whenever an *expression* involving an *operator* is involved, to refer to each part of the expression. So an assignment expression like `foo = 42` has LHS `foo` and RHS `42`. – Mathieu Guindon Aug 27 '18 at 17:57
  • OMG, duh! I should have figured that out. hahaha Thanks.^^ – GeekyFreaky Aug 27 '18 at 18:03