从 Excel 中的单元格读取值时要使用什么 VBA 变量类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43078263/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What VBA variable type to use when reading values from a cell in Excel?
提问by partiallyfulltime
回答by Mathieu Guindon
You can read a cell value into any type you want, VBA will (try to) implicitly convert it to that type for you.
您可以将单元格值读入您想要的任何类型，VBA 会（尝试）为您隐式地将其转换为该类型。
There are dozens of questions on this site involving run-time errors raised from reading cell values into a specific data type - perhaps you've seen this error message before?
该站点上有许多问题涉及将单元格值读取为特定数据类型时引发的运行时错误 - 也许您以前见过此错误消息？
Type mismatch
类型不匹配
That's the error you get when you try to read a cell containing an error value (e.g. #REF!) into anythingother than a Variant.
这就是当您尝试将包含错误值（例如#REF!）的单元格读取到除 a 之外的任何内容时得到的错误Variant。
So if you read a cell value into, say, a Double, everything will work fine as long as you're reading something that VBA can coerce into that data type. The problem is that, well, data is never 100% clean, worksheets dobreak down, users delete columns and break formulas, lookups fail and the person that wrote the formula didn't bother wrapping it with IFERROR, etc.
因此，如果您将单元格值读入 a Double，那么只要您正在读取 VBA 可以强制转换为该数据类型的内容，一切都会正常工作。问题是，数据从来都不是 100% 干净的，工作表确实会崩溃，用户删除列并破坏公式，查找失败，编写公式的人也懒得用 包装它IFERROR，等等。
That's why you readcell values into a Variant.
这就是为什么您将单元格值读入Variant.
That doesn't mean you workwith a Variant.
这并不意味着你的工作了Variant。
Dim cellValue As Variant
cellValue = someRange.Value
If IsError(cellValue) Then Exit Sub 'bail out before we blow up
Dim workingValue As String
workingValue = CStr(cellValue)
By assigning to another data type, you effectively castthe Variantto that more specific type - here a String. And because you like explicit type conversions, you use VBA's conversion functions to make the conversion explicit - here CStr.
通过分配到另一种数据类型，您可以有效地投了Variant到更具体的类型-这里String。并且因为您喜欢显式类型转换，所以您使用 VBA 的转换函数来使转换显式 - 这里CStr。
Now, in real code, you probably wouldn't even bother reading it into a Variant- you can use IsErrorto test the cell value:
现在，在实际代码中，您可能甚至不会费心将其读入 a Variant- 您可以使用它IsError来测试单元格值：
If IsError(someRange.Value) Then Exit Sub 'bail out before we blow up
Dim cellValue As String
cellValue = someRange.Value ' or cellValue = CStr(someRange.Value)
The flipside here is that you're accessing the cell twice. Whether or not that's better that reading it into a Variantis for you to decide; performance-wise, it's usually best to avoid accessing ranges as much as possible though.
这里的另一面是您要访问单元格两次。这是否比将其读入 a 更好由Variant您决定；在性能方面，通常最好尽可能避免访问范围。
回答by trincot
The value you get from a cell (which is a Range) is a Variantaccording to the documentation:
根据文档，您从单元格（即 a Range）获得的值是 a ：Variant
Range.Value Property (Excel)
Returns or sets a Variantvalue that represents the value of the specified range.
Range.Value 属性 (Excel)
返回或设置表示指定范围值的Variant值。
Since a Variantcan represent different data types, you could looseinformation if you would assign a cell's value to -- for instance -- a variable of type String.
由于 aVariant可以表示不同的数据类型，如果您将单元格的值分配给（例如）类型的变量，您可能会丢失信息String。
The mere fact that there is data type information in a Variantalready means you lose that type of information. If for instance the original type was numeric and you store it in a Stringvariable, there is no way to know from that string value what the original data type was. You could also lose precision (on Datemilliseconds for instance).
仅仅存在数据类型信息这一事实Variant就意味着您会丢失该类型的信息。例如，如果原始类型是数字并且您将它存储在一个String变量中，则无法从该字符串值知道原始数据类型是什么。您也可能会失去精度（Date例如以毫秒为单位）。
Furthermore, a Varianttype value cannot always be cast to the data type of your variable, and so you could get a Type mismatch error. In practice this often happens with the Errorsub data type.
此外，Variant类型值不能总是强制转换为变量的数据类型，因此您可能会收到类型不匹配错误。在实践中，这经常发生在Errorsub 数据类型中。
Only when you know beforehand what the data type is of a certain cell's value, it would be good to define your receiving variable in that data type.
只有当您事先知道某个单元格的值的数据类型是什么时，最好在该数据类型中定义您的接收变量。
回答by Jonathan
Not strictly answering your question, but thought I'd add this for reference anyway.
没有严格回答你的问题，但我想我会添加这个以供参考。
With native Excel functions you can usually provide either a range object or a value directly to a function. For example, you can either write =AVERAGE(A1,A2,A3)or =AVERAGE(10,20,30). If you want to do something similar for any user defined functions, you will need to check the type of object passed to your function:
使用本机 Excel 函数，您通常可以直接向函数提供范围对象或值。例如，您可以编写=AVERAGE(A1,A2,A3)或=AVERAGE(10,20,30). 如果你想对任何用户定义的函数做类似的事情，你需要检查传递给你的函数的对象类型：
Function test(input As Variant)
Dim var As Variant
If TypeName(input) = "Range" Then
var = input.Value
Else
var = input
End If
You may also want to check for other objects if your function can accept them, but doing this will make your functions behave more like users expect them to.
如果您的函数可以接受其他对象，您可能还想检查其他对象，但这样做会使您的函数表现得更像用户期望的那样。
