excel - vba: what is 97.45 * 1# =?

Question

some_integer = 97.45 * 1#

what does this notation mean? what will some_integer = ?

Answer 1

Just to expand on what everyone else has added... As mentioned, the hash mark (#) is a Type Declaration Character (TDC) and forces the literal "1" to the type Double. This Data-Type conversion belongs to a class of conversions called “Explicit Conversions”. Also in this class are Casts (such as CStr(), CLng(), etc.).

Explicit Conversion are generally used to avoid an incorrect Implicit conversion. Implicit Conversion being conversions that VBA performs automatically. If you declare and Type all of your variables (example Dim j As Long) it is easier to control how data is interpreted. However, there are still a few edge cases involving how literals (“hard coded numbers”) are handled.

The most common reasons I know to use Type Declaration Characters with a literal are:

1. Forcing Hex and Octal literals to Longs to avoid a known problem. (More here: http://support.microsoft.com/kb/38888)
2. Preventing a common overflow condition caused when a math operation produces a result that is larger that the largest DataType used in the operation.
3. Avoiding a floating-point calculation anomaly.
4. Micro-optimization. Conversions caused by a Type Declaration Character happen at compile time. Casted conversions and implicit conversions happen at runtime. In 99.999% of cases, this will produce no measurable gain whatsoever and is generally a waste of time.
   

As your example doesn’t do anything, I can only guess its not real code. So it makes it hard to intuit the author’s intent. 1 doesn’t apply. Reason 2 is a possibility. I will describe the problem and a possible fix.

There are two things you need to know to understand the overflow issue. How literals are typed and how implicit conversion works. If there is no Type Declaration Character, a Literal will have a type assigned by default. Here are the rules:

1. If the value is in quotes, it is a String even if it is a numeric value.
2. If the value is numeric and has a decimal, it is a Double.
3. If the value is numeric, has no decimal, and is between -32,767 and 32,767 (inclusive) it is of the type Integer.
4. If the value is numeric, has no decimal, and is between -2,147,483,647 and 2,147,483,647 (inclusive) but not between -32,767 and 32,767 it is of the type Long.
5. If the value is numeric, has no decimal, and outside the range of -2,147,483,647 to 2,147,483,647 it is of the type Double.
6. Any Literal in Scientific notation will be a Double.

Now that you know how a literal will be typed, you need to understand implicit conversion. All math operations work on two input values and output one result. The DataType of the result is chosen by analyzing the DataTypes of the input values. The output DataType is chosen based on three rules: 1. If either of the input values is of the DataType Variant VBA will choose the output DataType based on same rules described for literals. 2. If both input values are typed then it will choose the larger of the two data types. 3. If both input values are typed the same then the output DateType will be the same as the input DataType (assuming that type is not “Variant”).

The problem occurs for condition 3. If you have two integers (ex. 500 and 400) and you perform an operation on them (ex. 500*400) that yields a result (200,000) to large to put into the resulting DateType (Integer). Then you get an overflow error. As 500 and 400 are Integers by default you would have to avoid this by explicitly typing one of them to a Long or a Double via a Type Declaration Character (ex. 500& * 400). Then when the output type was chosen it would choose the larger of the two (Long) which would be large enough to hold the result.

I can say for certain that if the reason was 2 then there is no cause for concern (at least in the given example). Any literal with a decimal point is of the type Double by default. As implicit conversion always chooses the largest type in an operation and a Double will always be present there is zero chance whatsoever of producing an overflow no matter what.

If the cause for concern is a Floating Point issue, that would take some more serious consideration with a real example to be able to see if you needed the Type Declaration Character.

If the reason was Micro-Optimization, it really doesn’t matter if you leave it there or not. Technically, it’s better to use a Double with a Double. So there is no harm in leaving it. However, there isn’t any reason to go out of your way to put one in either.

---

Misc

The various type declaration characters are as follows:

Type Declaration Character Data Type
          %                 Integer
          &                 Long
          !                 Single
          #                 Double
          $                 String
          @                 Currency 

To add a little more information: Type declaration character can be used in place their corresponding type in a Dim statement (although this is considered by many a poor practice due). Thus :Dim s As String is the same as Dim s$ s will be created as a String in both cases. Similarly, you can type a Functions Return value using TDCs. So Public Function MyFunc()& is the same as Public Function MyFunc() As Long.

---

Conversion Functions

Function    Return Type
CBool       Boolean
CByte       Byte
CCur        Currency 
CDate       Date 
CDbl        Double 
CDec        Decimal
CInt        Integer
CLng        Long
CSng        Single
CStr        String
CVar        Variant