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

Answer 1

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?

Answer 2

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.