CODEWITHSUNDEEP
stringvbaexceldouble
Matteo NNZ
Matteo NNZ

Reputation: 12685

Making sure string numbers operations are culture independent

I download an exchange rate through an XmlHttp request that gets inside the code as a string (being the .innerText of a <div> element) and represents a double type number: 1.525.

When building this script, I've done it on my OS which has the English culture model (i.e. 1.525 means 1 unit and 0.525 decimals). However, this script will now run on a French OS which uses the comma , instead of the . to separate decimals.

Which means, the operation Application.Evaluate(10000/myRate) will fail if the . is instead of the ,.

Easy solution would be to replace the "." with a "," via Application.Evaluate(10000/Replace(myRate,".",","). However, this is clearly not nice because now the script would fail on an English system.

With VB.NET I would be able to make it culture-independent by converting it like: 

myRate.ToDouble(System.Globalization.CultureInfo.InvariantCulture)

I've tried Googling the VBA alternative for a while without success; does anyone know if there's a more elegant way of internationalize my script than just replacing the "." with a ","?

Here's my current solution (that I don't really like): 

On Error Resume Next
test = CDbl(myRate)/2
If Err.Number <> 0 Then
    myRate = Replace(myRate,".",",")
    On Error GoTo 0
End If

Upvotes: 1

Views: 1450

Answers (3)

The Conspiracy
The Conspiracy

Reputation: 3953

In MS Access, we do not have Application.DecimalSeparator nor Application.ThousandsSeparator nor Application.International. The only thing we have is Val(vString) and Str(vNumber). They both convert the argument using the English (invariant) culture.

So, in MS Access the OP's question could be solved like this:

vResult = 10000/Val(myRate)

Upvotes: 0

Maciej Los
Maciej Los

Reputation: 8591

You can temporary change decimal and thousands separator, by using Application object.

To read several current OS (International) settings: Application.International(index) property

To change:

Application.ThousandsSeparator = "."

Upvotes: 3

SierraOscar
SierraOscar

Reputation: 17637

use the Application.DecimalSeparator property?

Application.Evaluate(10000/CDbl(Replace(myRate,".", Application.DecimalSeparator))

Upvotes: 4

Related Questions