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

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!