0

To give some background, I made some upgrades to an existing macro written by an ex-employee. As such, I didn't touch any of the core functions that were existing. Essentially the macro creates a bunch of reports based on the input.

The macro runs perfectly fine in the UK, however we are trying to expand it to some some teams in Europe. Testing this has resulted in some issues which are dependent on regional settings e.g. when the macro is run in Sweden it provides different result to when it is run in the UK on the same source data, however when the regional settings on the computer in Sweden are changed to the UK the output report matches that in the UK.

I've tried looking at system separators but unfortunately this doesn't seem to be making much of a difference to the results (commas and decimal separators).

I've looked at changing the region settings to the UK and then back to the host country but can't find anything on this. Any ideas or help on how to do this would be great.

Thanks!

EDIT: It's difficult to share the code due to the amount of modules (50). I think the best way may be to look at the format settings for different countries vs. UK and see if there is a way to set the excel workbook to run on the UK settings.

Tony
  • 521
  • 1
  • 6
  • 14
  • With any value really, you should loop through the code and see where the mismatch starts to appear! It's very likely you can tweak around the values to your needs. – JvdV Jun 27 '18 at 08:13
  • 1
    check this post - https://stackoverflow.com/questions/35724156/different-languages-issue-when-inserting-formula-from-vba – tsdn Jun 27 '18 at 08:15
  • Also check this post if it could be a time thing [Converting “= Now” in VBA to EST](https://stackoverflow.com/questions/51042654/converting-now-in-vba-to-est/51043302#51043302) – Darren Bartrup-Cook Jun 27 '18 at 08:32

2 Answers2

2

May be currency setting would be there. if you are able to share the code that is easy to understand and help full to get the solution.

Shyam Pandey
  • 62
  • 1
0

I had similar experience when working on some European projects. The list separator may differs. In some regions it is comma, while in others it is semicolon. If this is not the case, then maybe you can create a function to change the PC's region setting to UK before your reports generation and then retrieve it back to the host region.

Bruce Huang
  • 143
  • 2
  • 9
  • Another idea is that you may comment out the codes like **On Error Resume Next** to look for more clues. Though it is tough... – Bruce Huang Jun 27 '18 at 12:35
  • The code doesn't give any errors, but due to the different formats the end result is different. I did do some research around changing the PC's region setting in VBA but the code goes way over my head, i did see another method which change a regex file. – Tony Jun 27 '18 at 12:43
  • Could you share the codes on region setting you are trying? Or how about the solution here: https://stackoverflow.com/questions/42077145/type-mismatch-on-one-pc-but-not-other-run-time-error-13/42434556#42434556 – Bruce Huang Jun 27 '18 at 12:48
  • I've been looking at this post https://www.mrexcel.com/forum/excel-questions/41533-controlling-windows-regional-settings-vba.html – Tony Jun 27 '18 at 14:00
  • I have faced this since sometimes decimal may be "," instead of "." too, thus, this will give errors in calculations and your code will need to satisfy both regions effectively and VBA will likely work different if you change your regional settings. I think you may want to try the approach on the forum that you posted, you need to either change the regional code for the code, or test for every known configuration – Sgdva Jun 27 '18 at 14:52
  • 1
    @Tony, that's calling the Windows API. I found another post with more info here http://www.andreavb.com/forum/viewtopic_6845.html – Bruce Huang Jun 27 '18 at 15:49
  • One more thing, Excel supports to read the settings via VBA, however, it's read only... Application.International(xlCountrySetting) will return your current setting. – Bruce Huang Jun 27 '18 at 15:52
  • @BruceHuang Thanks for the pointers! May resort to changing the format in regional by calling on the API! – Tony Jun 27 '18 at 16:42
  • @Tony Glad to help :) – Bruce Huang Jun 27 '18 at 16:48