Overriding the regional settings (commas vs semicolons in formulas)

[skowi]

Hello,
This is my first post here. So far, I've been ending up on this forum always to find the solution (and I'm really grateful for that!). However this time I have a more difficult task to complete.

I need to distribute one macro'ed spreadsheet which will be used to generate list of roles and names with these roles. All based on lots of ifs formulas. After creating such list, users will use a macro to send it to a sharepoint as an ordinary XLSX filled with values only. I managed to get it running, but it turns out that more or less half of recipients who will be submitting these spreadsheets have commas set up in their regional settings, and the other half - semicolons.

I thought that a simple macro replacing these characters could work, but well - as suspected - it doesn't touch formulas. So, I thought that I could create a hidden sheet with the same content, but formulas would base on commas. Again - Excel obviously won't let such thing through trying either convert formulas into text, or blocking them. My last resort is, ByVal argument, or disassembling the xlsm file into an archive with xml files to replace contents manually which I don't really want to do as it's complicated and it can break the file easily. My question: is there any way to deal with this? Asking people to change their settings is out of option - they do already use other macros.

I would be very grateful for a hint.

 

[RoryA]

I don't see where the problem is. Formulas already in the sheet will translate between regional settings (other than where you need to use literal string arguments that may be region specific) and formulas entered in code will use US regional settings regardless of the user's settings, unless you are using the FormulaLocal properties (in which case, don't. ).

 

[skowi]

After your reply I got back to some of recipients and the problem was, that they 'forgot' to tell me that they use Office 2010 which doesn't support IFS function. I'm not sure if I should be happy about that, or sad - but, now I know what to do. Sadly. We can close the thread, sorry for the trouble!

 

[RoryA]

IFS is a pretty useless function anyway, IMO.

 

[skowi]

I dare you to repeat that after nesting like 10 IF's with a specific conditions order based on multiple cells values and containing series of index-match lookups

 

[RoryA]

I accept. IFS will evaluate every single argument you pass to it. It will not short-circuit like IF does, so it's even worse for the situation you describe.