vba 如何在VBA中获取单元格的格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15372349/
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
How do I get the format of a cell in VBA
提问by Fat Owl
When iterating through cells in a worksheet, how can I get what the format setting on the cell is? Because based on this, I would like to build a SQL statement to either add the single ticks or not to the value retreived
遍历工作表中的单元格时，如何获取单元格上的格式设置？因为基于此，我想构建一个 SQL 语句来添加单个刻度或不添加到检索到的值中
回答by nunzabar
Sounds like you need the VarType()function. Vartype(Range("A1"))
听起来您需要VarType()函数。 Vartype(Range("A1"))
OK, so you don'twant to know the format setting for the cell, but whether the value is numeric.
Can you just call IsNumeric(Range("A1"))and quote it if False?
好了，你不要想知道该小区的格式设置，但值是否是数字。如果可以，您可以打电话IsNumeric(Range("A1"))并引用它False吗？
Based on your comment that some numbers are stored as text in the DB, you are not going to solve this by a simple formula. Can't you just quote the values as you build your SQL statement?
根据您对某些数字作为文本存储在数据库中的评论，您不会通过简单的公式来解决这个问题。您不能在构建 SQL 语句时只引用这些值吗？
回答by David Zemens
I don't think there's any property of a cell that indicates whether the cell actually contains a numeric value, although VarType()mighthelp, it gets tricky because Excel will allow a number-formatted cell to contain string, and a text formatted cell to contain numeric values, without overriding the NumberFormatproperty.
我不认为单元格的任何属性可以指示单元格是否实际包含数值，尽管VarType()可能会有所帮助，但它会变得棘手，因为 Excel 将允许数字格式的单元格包含字符串，而文本格式的单元格包含数字值，而不覆盖该NumberFormat属性。
In any case you likely need some independent test to figure out whether a cell IsNumeric (or other criteria) AND whether its NumberFormatis among an enumerated list which you can define.
在任何情况下，您可能都需要一些独立的测试来确定单元格是否为 IsNumeric（或其他条件）NumberFormat以及它是否在您可以定义的枚举列表中。
Sub numFormat()
Dim cl As Range
Dim numFormat As String
Dim isNumber As Boolean
For Each cl In Range("A1")
numFormat = cl.NumberFormat
isNumber = IsNumeric(Trim(cl.Value))
Select Case numFormat
Case "General", "0", "0.0", "0.00" ' <--- modify as needed to account for other formats, etc.
If isNumber Then
Debug.Print cl.Address & " formatted as " & numFormat
End If
Case Else
'ignore all other cases
End Select
Next
End Sub
回答by Dick Kusleika
I don't think the format of the cell is the important thing. Rather, it's the data type of the field in your database. If you have the string 'foobar' in a cell and you create an INSERT INTO sql statement that attempts to put that into a Long Integer field, it's going to fail regardless of tickmarks.
我认为单元格的格式并不重要。相反，它是数据库中字段的数据类型。如果您在单元格中有字符串 'foobar' 并且您创建了一个 INSERT INTO sql 语句来尝试将它放入一个 Long Integer 字段中，那么无论刻度线如何，它都会失败。
Conversely, if a cell contains a numeric value (like 100) that needs to go into a VARCHAR field, it will need tickmarks (like '100').
相反，如果单元格包含需要进入 VARCHAR 字段的数值（如 100），则需要刻度线（如“100”）。
If you're using ADO, check the Type property of the Field object to determine the data type. Use this list http://support.microsoft.com/kb/193947to see what the types are. Then set up the SQL statement according to the field type.
如果您使用 ADO，请检查 Field 对象的 Type 属性以确定数据类型。使用此列表http://support.microsoft.com/kb/193947查看类型。然后根据字段类型设置SQL语句。
回答by Joe Brooks
Try using the following in VBA:
尝试在 VBA 中使用以下内容：
Range("A1").NumberFormat = "0.00" 'Sets cell formatting to numeric with 2 decimals.
Range("A1").Formula = "=Text(6, " & """0.00""" & ")" 'Looks like a number _
' but is really text.
Debug.Print WorksheetFunction.IsNumber(Range("A1")) 'Prints False
Range("A1").Value = 6 'Puts number into the cell, which also looks like 6.00
Debug.Print WorksheetFunction.IsNumber(Range("A1")) 'Prints True
This should tell you if the value is really text or really a number, regardless of the cell's formatting properties.
这应该告诉您该值是真正的文本还是真正的数字，而不管单元格的格式设置属性如何。
The key is that the intrinsic Excel IsNumber() function works better for this purpose than the VBA function IsNumeric. IsNumber() tells you whether the cell's value is a number, whereas IsNumeric only tells you if the cell is formatted for numeric values.
关键是内在 Excel IsNumber() 函数比 VBA 函数 IsNumeric 更适用于此目的。IsNumber() 告诉您单元格的值是否为数字，而 IsNumeric 仅告诉您单元格是否为数值格式。
