Get cell's number format category in VBA?
 

Good day!
Is this possible to do programmatically?

Thanks.

Answer: Get cell's number format category in VBA?
 

Hello! Yes, it is definitely possible to get a cell's number format category in VBA. Here's how you can do it:

1. First, you need to select the cell whose number format category you want to retrieve. You can do this using the Range object in VBA. For example, if you want to select cell A1, you can use the following code:
   
   Formula:

   ---

   Range("A1").Select 


   ---

2. Once you have selected the cell, you can use the NumberFormat property to retrieve its number format. This property returns a string that represents the number format of the cell. For example, if the cell is formatted as a currency, the NumberFormat property will return "$#,##0.00".
   
   Formula:

   ---

   Dim numFormat As String
numFormat = Selection.NumberFormat 


   ---

3. The number format string returned by the NumberFormat property can be quite complex, with various symbols and codes that represent different number format categories. To extract the number format category from the string, you can use the InStr function to search for specific keywords. For example, if you want to check if the cell is formatted as a currency, you can use the following code:
   
   Formula:

   ---

   If InStr(numFormat, "Currency")  0 Then
    MsgBox "The cell is formatted as a currency."
End If 


   ---

4. You can repeat this process for other number format categories, such as dates, percentages, and scientific notation. Just search for the appropriate keywords in the number format string and take the appropriate action based on the result.

Get cell's number format category in VBA?
 

Sub whatformat()
MsgBox (ActiveCell.NumberFormat)
End Sub

--
Gary''s Student - gsnu200785

Get cell's number format category in VBA?
 

This returns the actual number format, not the category, like 'General',
'Percentage', 'Currency', etc...

"Gary''s Student" wrote:

Sub whatformat()
MsgBox (ActiveCell.NumberFormat)
End Sub

--
Gary''s Student - gsnu200785

Get cell's number format category in VBA?
 

I don't think we can get it directly (because catagory is not a property of
the range object). However code can be written to figure it out.

For example, if the format has a % in it it must be Percent; if the format
has a $ in it it must be Currency; etc.

Need to determine some key characters and then do some kind of lookup.

Good Luck
--
Gary''s Student - gsnu200785


"dyowee" wrote:

This returns the actual number format, not the category, like 'General',
'Percentage', 'Currency', etc...

"Gary''s Student" wrote:

Sub whatformat()
MsgBox (ActiveCell.NumberFormat)
End Sub

--
Gary''s Student - gsnu200785

Get cell's number format category in VBA?
 

Excel has its own
=cell("format",a1)
that will return a category (kind of).

Look at excel's help for =cell() and you'll find a list of those "Categories".

Then you can evaluate that formula and look at the resulting string (first
character or whole string???) to see what category you want.

This evaluates that formula:

Dim res As String
With Worksheets("sheet1")
res = .Evaluate("cell(""Format""," & .Range("a1").Address & ")")
End With
MsgBox res

This is from xl2003's (USA version) help:

If the Microsoft Excel format is CELL returns
General "G"
0 "F0"
#,##0 ",0"
0.00 "F2"
#,##0.00 ",2"
$#,##0_);($#,##0) "C0"
$#,##0_);[Red]($#,##0) "C0-"
$#,##0.00_);($#,##0.00) "C2"
$#,##0.00_);[Red]($#,##0.00) "C2-"
0% "P0"
0.00% "P2"
0.00E+00 "S2"
# ?/? or # ??/?? "G"
m/d/yy or m/d/yy h:mm or mm/dd/yy "D4"
d-mmm-yy or dd-mmm-yy "D1"
d-mmm or dd-mmm "D2"
mmm-yy "D3"
mm/dd "D5"
h:mm AM/PM "D7"
h:mm:ss AM/PM "D6"
h:mm "D9"
h:mm:ss "D8"

But the "G" used for both General and fraction seems pretty disconcerting!


dyowee wrote:

Good day!
Is this possible to do programmatically?

Thanks.

--

Dave Peterson

Get cell's number format category in VBA?
 

Why does "12:00:00 AM" returns a "G"? =(

"Dave Peterson" wrote:

Excel has its own
=cell("format",a1)
that will return a category (kind of).

Look at excel's help for =cell() and you'll find a list of those "Categories".

Then you can evaluate that formula and look at the resulting string (first
character or whole string???) to see what category you want.

This evaluates that formula:

Dim res As String
With Worksheets("sheet1")
res = .Evaluate("cell(""Format""," & .Range("a1").Address & ")")
End With
MsgBox res

This is from xl2003's (USA version) help:

If the Microsoft Excel format is CELL returns
General "G"
0 "F0"
#,##0 ",0"
0.00 "F2"
#,##0.00 ",2"
$#,##0_);($#,##0) "C0"
$#,##0_);[Red]($#,##0) "C0-"
$#,##0.00_);($#,##0.00) "C2"
$#,##0.00_);[Red]($#,##0.00) "C2-"
0% "P0"
0.00% "P2"
0.00E+00 "S2"
# ?/? or # ??/?? "G"
m/d/yy or m/d/yy h:mm or mm/dd/yy "D4"
d-mmm-yy or dd-mmm-yy "D1"
d-mmm or dd-mmm "D2"
mmm-yy "D3"
mm/dd "D5"
h:mm AM/PM "D7"
h:mm:ss AM/PM "D6"
h:mm "D9"
h:mm:ss "D8"

But the "G" used for both General and fraction seems pretty disconcerting!


dyowee wrote:

Good day!
Is this possible to do programmatically?

Thanks.

--

Dave Peterson

Get cell's number format category in VBA?
 

Maybe because there is text, not a "real" Excel date and time
You can easily check with the ISTEXT() function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"dyowee" wrote in message ...
| Why does "12:00:00 AM" returns a "G"? =(
|
| "Dave Peterson" wrote:
|
| Excel has its own
| =cell("format",a1)
| that will return a category (kind of).
|
| Look at excel's help for =cell() and you'll find a list of those "Categories".
|
| Then you can evaluate that formula and look at the resulting string (first
| character or whole string???) to see what category you want.
|
| This evaluates that formula:
|
| Dim res As String
| With Worksheets("sheet1")
| res = .Evaluate("cell(""Format""," & .Range("a1").Address & ")")
| End With
| MsgBox res
|
| This is from xl2003's (USA version) help:
|
| If the Microsoft Excel format is CELL returns
| General "G"
| 0 "F0"
| #,##0 ",0"
| 0.00 "F2"
| #,##0.00 ",2"
| $#,##0_);($#,##0) "C0"
| $#,##0_);[Red]($#,##0) "C0-"
| $#,##0.00_);($#,##0.00) "C2"
| $#,##0.00_);[Red]($#,##0.00) "C2-"
| 0% "P0"
| 0.00% "P2"
| 0.00E+00 "S2"
| # ?/? or # ??/?? "G"
| m/d/yy or m/d/yy h:mm or mm/dd/yy "D4"
| d-mmm-yy or dd-mmm-yy "D1"
| d-mmm or dd-mmm "D2"
| mmm-yy "D3"
| mm/dd "D5"
| h:mm AM/PM "D7"
| h:mm:ss AM/PM "D6"
| h:mm "D9"
| h:mm:ss "D8"
|
| But the "G" used for both General and fraction seems pretty disconcerting!
|
|
| dyowee wrote:
|
| Good day!
| Is this possible to do programmatically?
|
| Thanks.
|
| --
|
| Dave Peterson
|

Get cell's number format category in VBA?
 

Just to add to Niek's response...

I entered 12:00:00 AM (as a real time) and got D9.

dyowee wrote:

Why does "12:00:00 AM" returns a "G"? =(

"Dave Peterson" wrote:

Excel has its own
=cell("format",a1)
that will return a category (kind of).

Look at excel's help for =cell() and you'll find a list of those "Categories".

Then you can evaluate that formula and look at the resulting string (first
character or whole string???) to see what category you want.

This evaluates that formula:

Dim res As String
With Worksheets("sheet1")
res = .Evaluate("cell(""Format""," & .Range("a1").Address & ")")
End With
MsgBox res

This is from xl2003's (USA version) help:

If the Microsoft Excel format is CELL returns
General "G"
0 "F0"
#,##0 ",0"
0.00 "F2"
#,##0.00 ",2"
$#,##0_);($#,##0) "C0"
$#,##0_);[Red]($#,##0) "C0-"
$#,##0.00_);($#,##0.00) "C2"
$#,##0.00_);[Red]($#,##0.00) "C2-"
0% "P0"
0.00% "P2"
0.00E+00 "S2"
# ?/? or # ??/?? "G"
m/d/yy or m/d/yy h:mm or mm/dd/yy "D4"
d-mmm-yy or dd-mmm-yy "D1"
d-mmm or dd-mmm "D2"
mmm-yy "D3"
mm/dd "D5"
h:mm AM/PM "D7"
h:mm:ss AM/PM "D6"
h:mm "D9"
h:mm:ss "D8"

But the "G" used for both General and fraction seems pretty disconcerting!


dyowee wrote:

Good day!
Is this possible to do programmatically?

Thanks.

--

Dave Peterson


--

Dave Peterson