FormatNumber problem 1

[Quimbly]

I'm working with the VBA code behind an Excel report. The report has two entry points: Auto_open and another subroutine which is called by an application. Both entry points are basically indentical.

In one of the date data fields, we're using a Numberformat of "ddd dd". Oddly, sometimes instead of a date (e.g. "Mon 03") it just displays "ddd dd" in the field instead.

We thought that maybe it related to the fact that our client resides in Italy and uses different regional settings. Alternatively, we tried "ggg gg" for giourno, and equivalent Italien format string. We got the same results; sometimes it displayed properly (e.g. "lun 03"), but then sometimes displayed "ggg gg" instead.

Also, it appears that dependant on the way the report is opened (from a directory, using Auto_open, or from the application, using the other entry point), the result is different -- either "ggg gg" or the correctly formatted date.

So, first of all, my question is: why would Excel display the NumberFormat format string instead of the value?

After I understand that, I might be able to proceed.

Any help would be appreciated!

 

[VBAjedi]

You don't say what this "other application" is, but if it isn't Excel, it is possible that the regional date settings somehow aren't getting loaded. When does the date appear correctly - when the code is called by AutoRun or by the outside application?

In either case, that's odd. I don't think I've ever seen the actual "instructions" of a custom format displayed in a cell it's supposed to be formatting.

VBAjedi

 

[Quimbly]

The "other application" is a proprietary application my company sells. One of its features is to open (i.e. launch) the Excel reports from its interface.

Oddly, we have had the format string appear in the cell, instead of the date in BOTH instances: when launched manually, and when launched from our application. The difference in the two attemps was that in one, we used a format string of "ddd dd" and in the other a string of "ggg gg".

Also, interestingly, it looks like the data in the cell after the stored procedure call is a date value in the real-number representation (e.g. 38049.25).

 

[xlbo]

Interesting - what do you see in the cell when it doesn't format properly and what do you see in the formula bar ??

Also, could you post the line(s) of code that set the numberformat and apply the value ???

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Quimbly]

xlbo-

I've tried out some different code, but am now getting the following results:

What I see in the cell: ggg gg
What I see in the format string from the spreadsheet: \g\g\g \g\g
What I see in the formula bar: 30634,12

So, it makes sense why I'm seeing what I'm seeing (i.e. "ggg gg") since the format string is actually telling Excel to print those g's. Also, it seems that Excel is NOT properly interpreting the internal representation of a date (which is a real number, with a comma as a decimal for European regional settings).

So what I think is happening is that Excel is getting confused about the regional settings. It tries to read the internal real-number date representation but thinks its a string since it has a comma (",") in it instead of a period ("."). Then, it changes the format string from "ggg gg" to "\g\g\g \g\g" since that particular format string doesn't make sense for a string data value.


The data is gathered from a stored procedure and is then placed into the spreadsheet using a pretty standard and tested "fill" method.

The format string is applied later, e.g.:

Range("F14").NumberFormat = "ggg gg"

 

[Bowers74]

The reason you are seeing "ggg gg" in the cell and "/g/g/g /g/g" for the number format, is becasue that is what you told Excel to set the number format to:

If you change your code from

[COLOR=red yellow][tt]Range("F14").NumberFormat = "ggg gg"[/tt][/color]

to

[COLOR=green yellow][tt]Range("F14").NumberFormat = "ddd dd"[/tt][/color]

it should remedy your problem.

If you set the .NumberFormat for a cell using VBA, you MUST use the English abbreviations (i.e. d=day, m=month etc). Excel will interpret according to the regional settings of the host computer.

NOTE: I tested this using the German Regional Settings (I can't speak Italian) ;-)



Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884