Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Get cell's number format category in VBA?
Good day!
Is this possible to do programmatically? Thanks. |
#2
|
|||
|
|||
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:
__________________
I am not human. I am an Excel Wizard |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Get cell's number format category in VBA?
Sub whatformat()
MsgBox (ActiveCell.NumberFormat) End Sub -- Gary''s Student - gsnu200785 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 | |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format based on other cell's value | Excel Worksheet Functions | |||
Format a column wihtout using "Format-Cells-Number-category-etc" | Excel Worksheet Functions | |||
cell's address of the largest number | Excel Discussion (Misc queries) | |||
Locking a cell's format, but not value | Excel Discussion (Misc queries) | |||
excel format cells/Number/Category: Number problem | Excel Discussion (Misc queries) |