CODEWITHSUNDEEP
excelvba
Fat Owl
Fat Owl

Reputation: 93

How do I get the format of a cell in VBA

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

Upvotes: 4

Views: 61736

Answers (4)

Joe Brooks
Joe Brooks

Reputation: 11

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.

Upvotes: 1

Dick Kusleika
Dick Kusleika

Reputation: 33145

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.

Upvotes: 0

mr_plum
mr_plum

Reputation: 2437

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?

Upvotes: 2

David Zemens
David Zemens

Reputation: 53623

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

Upvotes: 0

Related Questions