How do I get the format of a cell in VBA

Question

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

What format are you trying to check for? In excel the native `Cell()` function provides formatting information. — Alex P, Mar 12 '13 at 21:23
This helped me check text format of cells https://stackoverflow.com/questions/49395950/excel-vba-to-underline-referenced-cell-data — lessthanideal, Apr 23 '20 at 11:22

mr_plum · Answer 1 · 2013-03-13T10:57:34.853

2

Sounds like you need the VarType() function. Vartype(Range("A1"))

OK, so you don't want 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?

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?

edited Mar 13 '13 at 10:57

answered Mar 12 '13 at 21:25

mr_plum

2,448
1
18
31

@Remnant: What I am trying to do is this. If a cell has the format of a number/ decimal then the sql statement built dynamically will not have the '' around the value else it would. So for eg: while iterating through the rows, if cell A1 has Cat, I would logically add 'Cat' but first need to see what A1 is formatted to be. Hope that makes sense – Fat Owl Mar 12 '13 at 21:30
@Remnant: I could use =Cell("format",A1) but how do I assign this to a variable? – Fat Owl Mar 12 '13 at 21:51
the varType is always returning an 8 - string. – Fat Owl Mar 12 '13 at 22:02
value = Trim(sht.Cells(dataStartingRow, c.Column).value) x = VarType(value) – Fat Owl Mar 12 '13 at 22:03
I would think Cell is a worksheetfunction that is callable via Application.WorksheetFunction but this isnt the case either. Any help os greatly appreciated – Fat Owl Mar 12 '13 at 23:20
tried the isNumeric too :) .. unfortunately you can have numeric fields stored as varchar in the DB .. thats the problem. One solution I thought of is in the first column to give the data type concatednated to the field name and I parse that out – Fat Owl Mar 12 '13 at 23:53
The problem is that the 'format' you're trying to obtain is not the way same thing Excel stores as a `NumberFormat`. A NumberFormat of "General" will allow integer, long and double numerics, as well as text. You need a way to account for cells with `.NumberFormat = "General"` IF some other criteria (e.g., IsNumeric) is met, but you ALSO need to capture the more specific cases where a cell has a number-specific format, e.g., `.NumberFormat = "0.00"` etc. but a cell can have a number-specific format, and still contain string data. – David Zemens Mar 13 '13 at 02:59

Joe Brooks · Answer 2 · 2018-06-13T02:03:52.170

Try using the following in 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.

score 0 · Answer 3 · answered Mar 13 '13 at 03:25

I don't think there's any property of a cell that indicates whether the cell actually contains a numeric value, although VarType() might help, 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 NumberFormat property.

In any case you likely need some independent test to figure out whether a cell IsNumeric (or other criteria) AND whether its NumberFormat is among an enumerated list which you can define.

 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

score 0 · Answer 4 · answered Mar 13 '13 at 13:47

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.

Conversely, if a cell contains a numeric value (like 100) that needs to go into a VARCHAR field, it will need tickmarks (like '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/193947 to see what the types are. Then set up the SQL statement according to the field type.

How do I get the format of a cell in VBA

4 Answers4