Modify regional settings in Excel vba

Resolved · Medium Priority · Version 2007

Tue 16 Jun 2015, 14:46 Delegate Susanna said...

Susanna has attended:
Excel VBA Intro Intermediate course
Taking Minutes course
Excel VBA Advanced course
Excel PowerPivot course

Modify regional settings in Excel vba

I'm struggling to get one of my macros to run both under a UK and a US environment. A macro running OK in my UK environment produces some #VALUEs in amount fields when run in a US environment - with different decimal and thousands separators.

Wed 17 Jun 2015, 09:42 Trainer Jens said...

RE: Modify regional settings in Excel vba

Hi Susanna,

Thank you for the forum question.

Normally we can have problems with dates when we are working with UK and US macros but why you have problems with the thousand and decimal separator is not a problem I have met before.

I have had the problem with macros shared in Denmark and UK because of the different use of separators in the two countries.

I solved the issue by using the Substitute Function in the visual basic editor to change the separators.

In your cause does Excel change the separators in US?

It could be a formatting issue.

When an amount is selected in the US sheet does Excel see it as a value? (Is it right aligned?).

I am sorry I need a little bit more information to help you.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector