VBA variables being set by language, how to convert to USA?

I have an Excel application that is programmed in English (USA). I have a couple of users who use their system in Portuguese. When the app. is run on their machines REGARDLESS of the language setting they're using, the below variable sngBreak ALWAYS returns a value formatted in Portuguese, for example, if the cell contains 1.5 (language setting to English) the variable contains 1,5; if the cell contains 1,5 (language setting Portuguese) the variable still contains 1,5.

Sample code:

Please Login or Register  to view this content.

I've tried the below code when the workbook opens and it doesn't affect the VBA variable settings:

Please Login or Register  to view this content.

This works visually in the spreadsheet cells just fine but it doesn't affect the internal VBA variable representation of the number. Also, so no one will mention it, trying to Replace commas with decimal points only affects the visual contents of the sheet i.e., it doesn't work, it's not a solution.

How do I get the sngBreak variable to register its values as English? Is there a way to force Excel to run as English (maybe a solution)?

Hi,

Untested, but does

Please Login or Register  to view this content.

do the trick?

Rgds

if the cell contains 1.5 (language setting to English) the variable contains 1,5; if the cell contains 1,5 (language setting Portuguese) the variable still contains 1,5.

I don't understand that, unless the number is stored as Text. Numeric values on the worksheet are always stored as Doubles, which is completely independent of language. Formatting is only for appearance.

Try this:

Please Login or Register  to view this content.

What does that show in the Immediate window?

Originally Posted by shg

I don't understand that, unless the number is stored as Text. Numeric values on the worksheet are always stored as Doubles, which is completely independent of language. Formatting is only for appearance.

I think the reason is because her system boots up in Portuguese and not English.

Originally Posted by shg

Try this:

Please Login or Register  to view this content.

What does that show in the Immediate window?

Thanks for the tips everyone. I'll try to get some time on her machine and play around some more. I'll keep you updated.

Originally Posted by Richard Buttrey

Hi,

Untested, but does

Please Login or Register  to view this content.

do the trick?

Rgds

Nope. I had tried that and variations of it.

Please Login or Register  to view this content.

returns 5. Looking that up, it means a Double even though the variable is Dimensioned as a Single (the code isn't extensive and easy to check, trust me, it's set as a Single data type).

Additionally,

Please Login or Register  to view this content.

returns 1.5
Good, right? No. Because if put into a LET statement, like so:

Please Login or Register  to view this content.

the variable becomes 15
Even wrapping the statement in CDBL() and VAL() doesn't work, the variable always returns a result of 1,5 e.g., in the immediate window ?sngBreak

Her system does boot up in Portuguese. I have access to her system this afternoon and I'm going to try changing the boot up language to English (change the settings from the Language Bar) and see what that does.

Booting up in Portuguese or English doesn't matter. I tried it w/her machine and same issue. Other agents boot up in English and they aren't having the same issue...which is really odd. They are using a Portuguese keyboard.

Please Login or Register  to view this content.

returns 5. Looking that up, it means a Double even though the variable is Dimensioned as a Single (the code isn't extensive and easy to check, trust me, it's set as a Single data type).

ALL numbers stored on a worksheet are Doubles. You can convert them to anything you want in VBA, but when you write them back to the worksheet, the values are converted back to Doubles.

@Marcol

That's the correct answer.(one of those eggheads ?)

And that implicates that you should be happy to find that comma in that cell: it means that the value is unaltered, although the appearance has. It will be treated as you intended (your 1.5 in US is equivalent to the European 1,5). So I wonder why should you want to change it ?

Originally Posted by snb

@Marcol

That's the correct answer.(one of those eggheads ?)

And that implicates that you should be happy to find that comma in that cell: it means that the value is unaltered, although the appearance has. It will be treated as you intended (your 1.5 in US is equivalent to the European 1,5). So I wonder why should you want to change it ?

Marcol,
Thanks. Hmmm...the agent came from Brazil and I wonder if her system did as well. Should I have IT reinstall Windows but in English and not Portuguese? The other agents use the Windows language bar to toggle between English and Portuguese w/o issue (though I think they mainly stay in Portuguese).

Snb,
Well, I've never run into an issue like this so, uhm, well, the company I work for is based in the USA and uses English. A couple of our agents in the customer support team we have for Latin America are the ones having the issue (the Spanish agents don't have this issue). What brought this about was that the numbers are being fed to a Microsoft Access (English version) database table that is expecting numbers. When the Portuguese number - for example, 1,5 - is added to the string being sent, instead of six numbers there are seven numbers being sent since the comma is the field separator.

Example:
English 2 and 1.5 being sent, two fields expected by the database table:
2,1.5
Same data being sent but with Portuguese formatting:
2,1,5
See...three fields are "seen" by the table and not the expected two.

Two things I haven't tried yet:
.Range(...).NumberFormatLocal
.Value2
I don't think the first one will work since the local format would be Portuguese.

Well, now that you know what I'm up against, suggestions for a fix? Maybe something at the database feeding end? I haven't had much time to dedicate to this as it's a low priority

Shg,
I did not know that, thanks!

Not really my field but it is possible that the Language Settings are only set for Portugese, try adding a second language to the settings, I think 3 settings is the max, I believe that more than this confuses Office.

See if this link, and its' hyperlinks to other other pages, helps
http://office.microsoft.com/en-us/of...001136389.aspx

[EDIT]
Perhaps I did make myself clear enough earlier.
When I was refering to "Version of Office" I meant the installed version, not the options selected during or after installation.
Some sources install strange choices of the product, based on cost to them, and dare I say even pirated versions.
This can lead to unexpected results with language options.

See this link for "Mixed language versions of the 2007 Office system"
http://technet.microsoft.com/en-us/l...ffice.12).aspx

You can always use:

Please Login or Register  to view this content.

but it seems the listseparator is more a problem:

Please Login or Register  to view this content.

snb, I have to leave the separators as commas since a string is built that INSERTs or UPDATEs the database with the info.

After staring at how the code is built it dawned on me that the places where I needed to pass a string I surrounded the data by a single quote. So, by changing my variable data types to String, adding a data check to replace an empty string with zero, I could export the .Formula of the cell contents and pass the data to my db as planned.

Here's a snippet of what I did. First is to make the data types String instead of Single:

Please Login or Register  to view this content.

Get the .Formula of the cell. This is the magic that works; I'm not an Excel MVP so I'll let them explain how it works. Next, an empty string will create an error for this code so turn an empty string into a zero:

Please Login or Register  to view this content.

Then the rest of the code:

Please Login or Register  to view this content.