vba：什么是97.45 * 1＃=？ [英] vba: what is 97.45 * 1# =?

问题描述

some_integer = 97.45 * 1#

符号是什么意思？什么将some_integer =？

what does this notation mean? what will some_integer = ?

推荐答案

只是为了扩展所有其他人添加...如上所述，哈希标记（＃ ）是类型声明字符（TDC），并将字面量1强制为Double类型。此数据类型转换属于称为显式转化的一类转换。在这个类中也是Casts（如CStr（），CLng（）等））。

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.).

显式转换通常用于避免错误的隐式转换。隐式转换是VBA自动执行的转换。如果您声明并键入所有变量（例如 Dim j As Long ），则更容易控制数据的解释方式。然而，仍然有一些涉及文字（硬编码数字）处理的边缘案例。

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. 将Hex和Octal文字强制为Longs以避免已知问题。 （更多信息： http://support.microsoft.com/kb/38888 ）


2. 防止在数学运算产生比操作中使用的最大DataType大的结果时引起的常见溢出条件。


3. 避免浮点计算异常。


4. 微型优化。类型声明字符引起的转换在编译时发生。投放转换和隐式转换在运行时发生。在99.999％的情况下，这不会产生任何可衡量的收益，通常是浪费时间。
   

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.
   

作为你的例子不做任何事情，我只能猜测它不是真正的代码。所以这使得很难直观的看到作者的意图。 1不适用。原因2是可能的。我会描述问题和一个可能的修复。

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. 如果值为引号，它是一个字符串，即使它是一个数字值。


2. 如果值为数字，并且为小数，则为Double。


3. 如果值为数字，则否并且在-32,767和32,767（含）之间，它是整数类型。


4. 如果值为数字，则具有否十进制，并且在-2,147,483,647之间和2,147,483,647（含），但不是在-32,767和32,767之间，它的类型为Long。


5. 如果值为数字，则否 decimal和 范围为-2,147,483,647至2,147,483,647，属于Double类型。


6. 科学符号中的任何字面值将为双倍。 li>
   

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.

现在，您知道如何输入文字，您需要了解隐式转换。所有的数学运算工作在两个输入值上，并输出一个结果。通过分析输入值的DataTypes来选择结果的DataType。输出DataType是基于三个规则选择的：
1.如果任何一个输入值都是DataType Variant VBA，则会根据为文字描述的相同规则选择输出DataType。
2.如果两个输入值都输入，那么它将选择两个数据类型中较大的一个。
3.如果两个输入值都输入相同，那么输出的DateType将与输入的DataType相同（假设该类型不是Variant）。

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").

出现条件3的问题。如果有两个整数（例如500和400），并且对它们执行一个操作（例如500 * 400），产生一个大的结果（200,000），以将它放入最终的DateType （整数）。那么你会得到溢出错误。由于默认情况下，500和400是整数，所以您必须通过类型声明字符（例如500& * 400）将其中一个明确地键入长或双倍来避免这种情况。然后当选择输出类型时，它将选择两个（Long）中较大的一个（足够大）以容纳结果。

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.

我可以肯定地说，如果原因是2，那么就不用担心（至少在给定的例子中）。任何具有小数点的文字的默认值都为Double。由于隐式转换总是选择操作中最大的类型，并且Double将始终存在，所以无论什么都产生溢出，无论如何都不会产生溢出。

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.

如果原因是Micro-Optimization，那么如果你离开它就没关系。在技​​术上，最好使用双人双人间。所以离开没有任何伤害。但是，没有任何理由可以放弃你的方式。

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.

其他

各种类型的声明字符如下：

The various type declaration characters are as follows:

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

要添加更多信息：
类型声明字符可以在Dim语句中使用它们对应的类型（虽然这被许多不好的做法考虑）。因此： Dim s As String 与相同，在这两种情况下，Dim s $ 将被创建为一个String。类似地，您可以使用TDC键入函数返回值。所以公共函数MyFunc（）& 与 Public Function MyFunc（）As Long 相同。

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.

转换功能

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

这篇关于vba：什么是97.45 * 1＃=？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！