Word VBA: Application.Version Runtime Error 13 with some languages

[Julian Ladbury]

I have a VBA-based product which takes certain actions depending on the Word
version under which it is executing. Specifically, it includes the following
line of code:

If CSng(Application.version) < 9# Then ' use numeric comparison so
""9.0" < "11"

I recently began looking at internationalising the product, and swiftly
found that this statement fails with a Runtime Error 13 when my Regional
Settings were set to French(France).

I have posted a document on my SkyDrive which can be used to reproduce the
problem. It also includes some information about some other Regional Settings
which exhibit (and do not exhibit) the problem. You can find it at
http://cid-75e3e350f569b887.skydrive.live.com/self.aspx/.Public/Bug31/Bug31 Reproduce.doc

 

[Steve Rindsberg]

I have a VBA-based product which takes certain actions depending on the Word
version under which it is executing. Specifically, it includes the following
line of code:

If CSng(Application.version) < 9# Then ' use numeric comparison so
""9.0" < "11"

I recently began looking at internationalising the product, and swiftly
found that this statement fails with a Runtime Error 13 when my Regional
Settings were set to French(France).

I have posted a document on my SkyDrive which can be used to reproduce the
problem. It also includes some information about some other Regional Settings
which exhibit (and do not exhibit) the problem. You can find it at
http://cid-75e3e350f569b887.skydrive.live.com/self.aspx/.Public/Bug31/Bug31 Reproduce.doc

I'm not sure if this'll help with this specific problem but you'll probably want to change
that to

Csng(Val(Application.Version))

Application.Version returns a string which will, in at least one version/SP of Office, includ
a letter, which'll cause Csng(Application.Version) to fail with the same 13 error.

It may well help to use CLng instead of CSng ... I don't know of any case where the number to
the right of the decimal point (or comma in French?) is significant. Or even non-zero.

 

[Julian Ladbury]

Thanks Steve.

Although your suggestion does not help with the Word versions in which I
have encountered the problem, it certainly makes sense. I was unaware of any
versions including a letter until reading your reply, and after a bit of
research found http://support.microsoft.com/kb/248710 which says that Office
97 SR2 returns '8.0b'. I will certainly update my code to incoporate your
suggestion.

It would be useful if
a) anyone could point me to a list of the values returned by each version of
Word, and
b) if Microsoft could describe any standards they will apply to future
releases.

 

[Julian Ladbury]

Steve,

My earlier reply was based on assumption not experiment - I apologise. After
experimenting, I find that using Csng(Val(Application.Version)) does NOT
raise an error in French(France), so your suggestion is probably 100%
worthwhile. I will check out some other languages soon.

 

[Steve Rindsberg]

Steve,

My earlier reply was based on assumption not experiment - I apologise. After
experimenting, I find that using Csng(Val(Application.Version)) does NOT
raise an error in French(France), so your suggestion is probably 100%
worthwhile. I will check out some other languages soon.

Ah, great ... let us know what you learn. A bit of helpspelunking helps to
explain this:

"The Val function stops reading the string at the first character it can't
recognize as part of a number. Symbols and characters that are often considered
parts of numeric values, such as dollar signs and commas, are not recognized."

...

"Note The Val function recognizes only the period (.) as a valid decimal
separator. When different decimal separators are used, as in international
applications, use CDbl instead to convert a string to a number."

Csng doesn't expect a string as input, but VBA "helps" by converting strings
that look like numbers for you. But when the string contains non-numeric
characters, you get a type 13 error. On a French system, you're probably
getting "12,0" instead of "12.0" back from Application.Version.

You might want to debug.print Application.Version to see if that's correct.

Val gets to the comma, stops looking and hands back a 12.

 

[Julian Ladbury]

Using French(france) and the VBA Immediate window,:

Debug.Print Application.Version gives "12.0"
Cutting and pasting the result into Csng gives runtime error 13
Cynicism leads me to paste the apparent full stop (period to some!) into the
Asc() function; this gives 46.
Typing 'asc(".")' also gives 46.
Typing 'Csng("12.0")' I get runtime error 13.

Here is the (annotated) Immediate window output from the above steps for
clarity (hopefully!)
?application.Version
12.0
?csng("12.0") <- runtime error 13
?asc(".") < ' "." pasted from previous line
46
?asc(".") < ' "." typed from keyboard to check for any misleading display
46
?csng("12.0") <- all typed from keyboard. Get runtime error 13
So the root of the problem might be nothing to do with Application.Version
itself, rather the processing of the Csng function in French(france) when a
string contains a full stop.

Another worm to add to the can, having found the following in the word 2007
Help for Type Conversion Functions:

"You should use the data-type conversion functions instead of Val to provide
internationally aware conversions from one data type to another. For example,
when you use CCur, different decimal separators, different thousand
separators, and various currency options are properly recognized depending on
the locale setting of your computer." And indeed this is borne out in the
Immediate window, again using French(france):

?ccur("12,0")
12

This is becoming a larger can of worms than I would like. For my part, I
will be happy to know what strings are returned by the various service levels
of the different Word versions, and how MS expect to structure them in
future. Once I know that, I can code what I need to accordingly. As an MSDN
subscriber, I know that MS are already looking at these posts, and look
forward to their comments.

 

[Colbert Zhou [MSFT]]

Hello Julian,

I searched a lot, inside and outside, but did not find any document lists
all values returned by various of versions Word application. I am afraid,
there is not such a list.

And also based on my currently research, in all of cases I observed, the
Application.Version returns the version in English locale string format,
"11.0" for Office 2003, and "12.0" for Office 2007, "14.0" for Office 2010.
(Maybe there are some cases it retuned a string including character as you
said, but based on my observations, I haven't encountered that case). But
it never returns "12,0" even I run a French version of Word.

The problem we encounter is because,
1.Application.Version always returns English locale string, "12.0"
2.We set the Regional Setting to French
3.VBA function Csng() is locale aware.

So Csng in French regional setting machine expects a string in French
locale format "12,0", but Application.Version gives the English locale
string "12.0". So the error happens. In my opinion, I do not think this is
fault of either Office object model Application.Version, or VBA Csng()
function. It is just Csng is not appropriate to be directly used here when
considering different locale setting.

I understand the document says that we'd better to use concersion functions
instead of Val because Val is not locale aware. But as to this particular
scenario, to convert a fixed English locale string to single type, I think
this is where we need the Val() function. What's your opinion?


Best regards,
Ji Zhou
Microsoft Online Community Support

 

[Julian Ladbury]

Hello Colbert,

I agree. I will use Csng(Val(application.Version)) in my code. Thanks to
Steve for starting us off on that track.

While researching this, I found a number of KB articles describing a variety
of methods to find the version of Office which is running and/or installed.
For the benefit of anyone interested, I will list them below. Before that
though, I would draw your attention to the fact that the Office 97 one is the
only one which documents what Application.Version returns; may I suggest that
the Office team is asked to update the newer KB articles with that
information - and also to point out that its return is independent of locale?
It would have saved us all a lot of time, and could well save time for others
in the future.

Office 2000: http://support.microsoft.com/default.aspx/kb/255275
Office XP: http://support.microsoft.com/kb/291331/
Office 2003: http://support.microsoft.com/kb/821549/en-us
Office 2007: http://office.microsoft.com/en-us/help/HP100474501033.aspx#20

 

[Julian Ladbury]

Sorry, forgot the Office 97 link: http://support.microsoft.com/kb/248710

Also, on the related topic of Csng behaviour in different regions, this link
is useful;
http://brannickdevice.blogspot.com/2005/04/vb6-bug-of-day-csng-function-problems.html

 

[Steve Rindsberg]

I think you've landed on it with this:

So the root of the problem might be nothing to do with Application.Version
itself, rather the processing of the Csng function in French(france) when a
string contains a full stop.

I'm guessing that CSng is locale-aware, so expects a comma rather than a
period/full-stop as the decimal character. This does seem to fit with observed
behavior ... in the immediate window, when locale is set to France:

? Csng("12.0") --> Error
? Csng("12,0") --> 12

After setting it back to English/US

? Csng("12.0") --> 12
? Csng("12,0") --> 120
( comma is a thousands separator, so doesn't upset Csng )

This is becoming a larger can of worms than I would like. For my part, I
will be happy to know what strings are returned by the various service levels
of the different Word versions, and how MS expect to structure them in
future. Once I know that, I can code what I need to accordingly. As an MSDN
subscriber, I know that MS are already looking at these posts, and look
forward to their comments.

I'm not sure anyone at MS can predict the future re this kind of thing. My
guess is that adding the "b" to Office 97 SP2 turned out to be A Big Ooosie and
that they won't do it again. Then again, probably anyone familiar with the
history's moved on to some other project; it might be just a matter of time
before some bright new bulb reinvents the wheel. Er. The whoops.

 

[Steve Rindsberg]

Thanks for the additional information, Colbert.

That bears out both Julian's tests and mine.

I agree with you that VAL in this case is appropriate to use.

 

[Steve Rindsberg]

FWIW, I've been using Application.Version in my PowerPoint add-ins since VBA was
introduced in Office '97. All versions of PowerPoint since then return "X.0" as
the version except for that one oddball in 97 SP2. That's the only one I ran
into issues with when I was using Application.Version w/o first using VAL on it.

No issues since I ran into that and fixed it with VAL.

 

[Tony Jollans]

Just to add a little to this ..

In some ways it was lucky you tried in French and got an error rather than
just the wrong answer you would have got in, say, German.

The various locale aware functions recognise both decimal separators and
digit grouping characters. Because the French thousands separator is a space
Csng("12.0") gives an error. In German, where the thousands separator is a
full stop (period), CSng("12.0") returns 120.

 

[Colbert Zhou [MSFT]]

Hi Julian and Steve,

Thanks very much for your input on this! Yes, I second that the document
should be more detailed. I have added a comment to the Application.Version
document so that developers can be aware of the issue,
http://msdn.microsoft.com/en-us/library/bb179596.aspx


Best regards,
Ji Zhou
Microsoft Online Community Support