Error formatting with type "General"

[Ecrivaine]

Hi,

I ran into an unexpected behaviour of the Format function in Excel-vba.
I want to read the content of a cell into a string parameter exactly in the way the user sees this content in his worksheet. To do this, I use the Format function and the cells NumberFormat. If the cell contains a number and the cell format is "General", the Format function returns "Ge0eral" i.s.o the number. So when a number is present in cell A1 and the format of cell A1 is set to "General" the function:
Format(Range("A1").Value, Range("A1".NumberFormat)
returns: Ge0eral.

The same occurs when typing: Format(2016,"General") in the Direct window of the Visual Basic editor.
This problem occurs on two different computers using different releases of Excel.

Can anybody explain this behaviour and/or knows a solution?

Thanks.

 

[Rick Rothstein]

Hi,

I ran into an unexpected behaviour of the Format function in Excel-vba.
I want to read the content of a cell into a string parameter exactly in the way the user sees this content in his worksheet. To do this, I use the Format function and the cells NumberFormat. If the cell contains a number and the cell format is "General", the Format function returns "Ge0eral" i.s.o the number. So when a number is present in cell A1 and the format of cell A1 is set to "General" the function:
Format(Range("A1").Value, Range("A1".NumberFormat)
returns: Ge0eral.

The same occurs when typing: Format(2016,"General") in the Direct window of the Visual Basic editor.
This problem occurs on two different computers using different releases of Excel.

The VB Format function is not related in anyway to the worksheet, so it does not respond to cell formatting Type patterns. You should call out to the worksheet's TEXT function instead...

StringVariable = Application.Text(Range("A1").Value, Range("A1").NumberFormat)

 

[Ecrivaine]

The VB Format function is not related in anyway to the worksheet, so it does not respond to cell formatting Type patterns. You should call out to the worksheet's TEXT function instead...

StringVariable = Application.Text(Range("A1").Value, Range("A1").NumberFormat)

Hi Rick,

Indeed that is the solution. Fantastic.
Thanks for your fast response! I really appreciate it. It kept me puzzled for quite a while but never thought of this reason.
Hope you have a very nice Chrismas.