Japanese Excel 2010 TickLabels.NumberFormat problem

First post (other than in the Intro section).

My suspicion is that this may be a Microsoft bug in Excel 2010, but if not I would be happy to receive some guidance.


Originally this is to do with driving Excel as an automation server from our own application, but since the problem can be reproduced using VBA in Excel 2010 I will restrict it to that for the moment.

We are trying to set the format of the bottom axis tick labels to "General". I know about the Application.International(xlGeneralFormatName) feature as we needed this in Excel 2007 to get the code working on true Japanese Excel /Japanese XP systems. However now on Excel 2010 irt seems to have broken.

When we use the code

Please Login or Register  to view this content.

The tick labels end up messed up. When we look at the formatting of the tick labels it appears that we have now added a "Custom" format.

In order to see what is going on I added a message box

Please Login or Register  to view this content.

When this is run on Western systems the message box happily shows the message

Application.International(xlGeneralFormatName) = 'General'

Everything works fine. On the Japanese system it shows the following string (I am not sure it will display, but lets try).

G/標準

That string matches perfectly the string which I see in the TickLabel format box for "General" format. So at this stage it would appear all is fine.

However once the variable s is assigned to Selection.TickLabels.NumberFormat and I inspect the variable in the debugger, the string is different. It is similar, but not the same.

g/"標""準"


It may be some issue of unicode conversion, but since it is all within VBA, I would have hoped it would be dealt with. It is disappointing that code that worked fine in Excel 2007 now does not work in 2010. Unfortunately it is also not the first time this has happened. We had code that worked in 2003, and broke in 2007. We put in a workaround, and then in the first 2007 Service Pack they "fixed" it and broke our patch, so we could in fact return to the older code. I wonder if the same is going to happen again here.

I am not sure if I can attach a worksheet yet as this is my first post, but unless you have have Japanese Excel 2010 it will probably not help.

Thanks in anticipation.

Originally Posted by JosephP

does it work if you use

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Thanks for the reply

Interesting....

Actually since my first post I had found the answer to the first suggestion (just out of interest). It is "yes". So using "General" works in 2010 and 2003, but not in 2007 (or maybe it does now since service packs?). Ho hum. Not ideal but we could make it work in our app. Would prefer not to have more version specific code, but if it has to be...

More interesting......

I had not found the second option in the help files, and all my Google searching round this time, and when the 2007 issue came up never showed this method. The answer is "Yes" it does work in 2010, so I am in your debt. Or at least perhaps I should go back and try and test in 2007 as we have to support all versions. It does work in 2003.

If I find it is OK for 2007 I will mark the tread as solved. In the mean time, thanks for the suggestions.