How to make number format remain same even if vba executed from different geolocations?

............

Hello Richa,

The answer is you can and you can not change these values. Any changes you make in VBA will be undone by Excel. You're probably asking "Why?".

Creating workbooks that will function internationally is not easy. There are many layers to this problem. The system sets the default regional and language settings during installation. This includes the basic formatting for numbers, dates, time, and money based on the user's locale. It would be great if this was all that needed to changed. Wait for it... But, it isn't that easy.

Applications, like Excel, can change these settings according to their own rules. To make things even more interesting the individual user accounts have default settings for these formats also. It does not stop here though. Individual threads can modify these format settings as can certain Windows controls like CombBoxes and ListBoxes. Yeah, in a word it is a mess.

To change these formats requires using the Windows API. This adds a new layer to our onion because these are low level system calls. Documentation exists but it not written for the average user and requires an understanding of Windows at the system level. Error checking is almost non existent, which it makes things exciting. If you do not know what you are doing these calls, you can royally screw the pooch and crash your system. Possibly, even it damage it.

Here is sample of some of the API calls and constants used for changing the money formatting.

Please Login or Register  to view this content.

A few years ago, I created a Gregorian Calendar that would display the months and days in the user's local language and allows the user to change date and time formatting. The calendar was displayed on a VBA UserForm. As long as the UserForm is displayed in the default state (Modal), the UserFrom can change the cell formatting. Modal windows or dialog boxes prevent the user from interacting with the application. Once the window is dismissed, the user can the again interact with the application. This method works because the modal thread is not a part of the Excel application. However, the VBA thread is and once focus is switched back to Excel from VBA then Excel will reset to it format settings.

Hello Richa,

I agree with rorya you should not change the user's settings. Unless you convert the number into a formatted string, I don't know of any other way to accomplish your goal. Of course doing so would prevent you from doing any mathematical operations on the converted numbers.

Hi Richavlaues,

The following does not directly relate to or answer your specific problem. It could help , however in the endless battle against Excel changing Number formats in regard to the decimal and thousand separator_.....

Originally Posted by Richavlaues

....how to change format from comma to dot......

_.........
_.......
I use German and English Excel and, for example scrape websites in English and German
I battle endlessly against the problem.
I have come to the conclusion it is impossible to completely get over the problem
I believe it will always come up
As Leith intimated it is a minefield and there are endless factors affecting, such that there will never be any one solution that always works.
However, amongst the weapons I use in the fights against Excel, I have a function which I wrote which has on many occasions saved the day, and has actually, proved fairly bullet proof.

It is most used when I receive a “number” , to which I have no control over the source and in addition, mostly, find I am unable, consistently to control Excel to manipulate it without , seemingly, at will, changing a comer to a point, or a decimal separator to a thousand separator.

Briefly, the method is simple.

I may receive for example a “Number”, as example:
1.200,001
or
1,200.001

In words this number is
One thousand two hundred , and one thousandths
or
One thousand two hundred and 1/1000

My function will take the number as a string. ( VBA is quite kind here, generally, I find, as it will both take and use a “Number” as a string happily in calculations, provided it “looks” like a number. )***
I simply split by the last separator, regardless of what it is, and ignore all others, so for example in this example I obtain
“1200” and “001”
The code then uses the first string to give directly a simple whole number
1200
Further, a number will be created from the second string , ( for this example ) in this fashion:
=1 / 1000

These numbers are then added mathematically.

_...............
The result of this is that VBA has created the number, in the way I want it. I then have found, up until now, that any pasting out anywhere , or further calculations have come out as I wish them to.
Exactly where and when I use the function can vary. In some cases I will use it more than once, “just to be on the safe side ( “belt and braces “ )” .

The point is that, as VBA created the number itself, it seems then to further behave itself, making the necessary formatting .
_............

I have not concerned myself too much with how a number “looks” in an Excel Spreadsheet. I usually find if my Function has done its job properly, which up until now it appears to have done, then pasting out to, and importantly, later taking from , a Spreadsheet cell, will “behave itself”

I have not done endless tests on my function, so any comments or suggestions for improvement are welcome. Up until now it has never failed me.

Function Code and a demo calling Code

Please Login or Register  to view this content.

Alan

Ref
http://www.eileenslounge.com/viewtop...177530#p177528
http://www.mrexcel.com/forum/questio...l-problem.html



*** P.s.

Originally Posted by Leith Ross

..... convert the number into a formatted string, I don't know of any other way to accomplish your goal. Of course doing so would prevent you from doing any mathematical operations on the converted numbers.

... I am not quite sure I follow this. I have found VBA and Excel very tolerant of taking string which “look” like a number as a number.. In fact I routinely use a string variable , if I have the chance, in any code where I am using a number or wanting to manipulate numbers. Up until now I have only seen advantages. – Such as reducing my problems associated with the issues of this Thread, and allowing easier manipulation of , and use of, the VBA Strings Functions. Sometimes to be on the safe side, in a calculation, I may do a ( pseudo code ) _...
Number = ConvertToDouble(NumberInStringVariable)
_.. however it has never proved necessary