Does anyone know if there is an easy or automated way to take a format string
for a numeric cell and determine if it's an Int, Float (with num decimals),
Date, or Time?

Right now we are using a large switch, but there are always new formats that
fall through and end up with the wrong type.

Thanks.

Hi ALV,

Not quite sure what you mean. A numeric cell has a value, say 39113.542130787
The Numberformat for this cell only determins how this number is to be
displayed on the sheet, but it does not change the value itself. The format
is related to the meaning of the value. My example could mena today's date
and time, or my bank balance (not really) and I would format it accordingly.

It appears you want to go the other way. You expect the format of a cell to
indicate the meaning of the value, which could work (depending on the source
of the data) but only if the few standard formats are being used. As soon as
a cell is formatted with a Custom-format, which by definition is flexible,
there will always be new formats.

Not sure what you mean by integer (no decimals displayed?) or float
(scientific?).

Do you mean the type of a variable? This could be interger. If so, see
vartype in VBA help.



--
Gerd


"ALV" wrote:

> Does anyone know if there is an easy or automated way to take a format string
> for a numeric cell and determine if it's an Int, Float (with num decimals),
> Date, or Time?
>
> Right now we are using a large switch, but there are always new formats that
> fall through and end up with the wrong type.
>
> Thanks.
>

Number as stored as doubles in Excel. How they are display is determined by
the NumberFormat property.
Not sure what you are after, but maybe one/some of these will be useful.

Range("A1").Value = 123
Debug.Print VarType(Range("A1").Value)
Debug.Print TypeName(Range("A1").Value)
Debug.Print Range("A1").Text
Debug.Print Range("A1").NumberFormat

NickHK

"ALV" <(E-Mail Removed)> wrote in message
news:092D4B30-3922-449D-ACF6-(E-Mail Removed)...
> Does anyone know if there is an easy or automated way to take a format
string
> for a numeric cell and determine if it's an Int, Float (with num
decimals),
> Date, or Time?
>
> Right now we are using a large switch, but there are always new formats
that
> fall through and end up with the wrong type.
>
> Thanks.
>

I should have been more specific...we are converting data to export to
another app which has its own set of data types.

So we want to determine if the Excel user intends for each cell to show as a
Time, Date, Int, Float, Dollar, or Percent.

I was wondering if you can take a FormatString and easily determine if it is
one of those.

"NickHK" wrote:

> Number as stored as doubles in Excel. How they are display is determined by
> the NumberFormat property.
> Not sure what you are after, but maybe one/some of these will be useful.
>
> Range("A1").Value = 123
> Debug.Print VarType(Range("A1").Value)
> Debug.Print TypeName(Range("A1").Value)
> Debug.Print Range("A1").Text
> Debug.Print Range("A1").NumberFormat
>
> NickHK
>
> "ALV" <(E-Mail Removed)> wrote in message
> news:092D4B30-3922-449D-ACF6-(E-Mail Removed)...
> > Does anyone know if there is an easy or automated way to take a format
> string
> > for a numeric cell and determine if it's an Int, Float (with num
> decimals),
> > Date, or Time?
> >
> > Right now we are using a large switch, but there are always new formats
> that
> > fall through and end up with the wrong type.
> >
> > Thanks.
> >
>
>
>