Formula error due to different Languages in VBA Excel

Question

I have a vba code to fill dynamically conditionnal formatting. The problem is that my code works fine on english version of Excel, but if I send to someone with the french excel version, it's giving them an error. I realized that the problem is that I put in my vba code a comma ',' in english version, but in french version it excpects a ";" separator to work.

How can I solve this so that my code works on any excel version ? I can't create an excel for each user, based on his local version.

To make things clear so that you may help :

here is the part of my code that is causing me the problem

sFormula is a string contaning :

sFormula=AND(OR($AP14="",$AR14=""),NOT($C14=""))

Range(sMyRange).FormatConditions.Add Type:=xlExpression, Formula1:=sFormula

In enlglish version of Excel All works fine

In french version I have to replace in my VBA code the ',' in sFormula by ';' to makes it work, otherwise I get a runtime error when the Macro code is executed

If I replace in my code the previous sFormula, by the following one (just changing the , by ;) it works fine on french version

sFormula=AND(OR($AP14="";$AR14="");NOT($C14=""))

Thanks in advance for your help.

see: https://stackoverflow.com/questions/13247771/excel-macro-inserting-internationally-valid-formula-during-run-time You will need to use a work around. — Scott Craner, Jun 30 '20 at 19:22
ok thanks I will check that, but there is no something like an excel property that we can set in VBA to tell Excel use the englsh formula format whatever is the user language ? — JustGreat, Jun 30 '20 at 19:24
I see so if I wasn't using it with conditionnal formatting they could be a way, but in conditionnal formatting there is a work arround to make it works ? right ? thanks a lot and I will check all that, but please by curiosity can you tell me if it wasn't for conditionnal formatting what can we do to solve such problem, because I may face it later on with normal formulas as well — JustGreat, Jun 30 '20 at 19:28
When placing the formula in the cell use `.Formula` instead of `.FormulaLocal` Then you just us the en-us version of the formula. — Scott Craner, Jun 30 '20 at 19:36
so for normal formulas, if I simply use .Formula and I type my formula in english format it will work on any version ? and if I put .formulalocal it will works only on local version of excel on which I made my formula ? — JustGreat, Jun 30 '20 at 20:41
Correct. That is why using `.Formula` is better when the workbook is to be shared across different languages. It expect EN-US format no matter the local settings. — Scott Craner, Jun 30 '20 at 20:42
thanks a lot, you provided to me lots of usefull information. By the way, in the link you provided there is an intelligent solution based on named range, but some people said it doesn't work, so I may use the first solution even if I prefere to one of named range — JustGreat, Jun 30 '20 at 20:49

score 0 · Answer 1 · answered Jul 01 '20 at 00:48

0

I think you can write code by judging the language when installing Excel.

    Dim sFormula As String
    
    If Application.LanguageSettings.LanguageID(msoLanguageIDInstall) = msoLanguageIDEnglishAUS Then
        sFormula = "=AND(OR($AP14="""",$AR14=""""),NOT($C14=""""))"
    ElseIf Application.LanguageSettings.LanguageID(msoLanguageIDInstall) = msoLanguageIDFrench Then
        sFormula = "=AND(OR($AP14="""",$AR14="""");NOT($C14=""""))"
    End If
    
    Range(sMyRange).FormatConditions.Add Type:=xlExpression, Formula1:=sFormula

answered Jul 01 '20 at 00:48

Dy.Lee

7,527
1
12
14

Thanks for trying to help. I will go for the suggestion of Scott above, but your idea is very good. The only problem is that it's limited to french and english so if one day you send it to a german it won't work and you have to add more code. The suggestions of Scott work for any langage so it's better. In your french formula replace the ",$AR14" by ";$AR14" to make it work, we never know maybe someone will see your code one day and wants to use it – JustGreat Jul 01 '20 at 14:11

score 0 · Accepted Answer · answered Jul 03 '20 at 12:17

Based on the comments of @Scott Craner , I solved my problem using the following functionThanks Again to Scott Craner and to @Dy.Lee for their help.

Here is my function so that it may help someone else :

Public Function GetLocalFormula(sformula As String, Optional sCell As String = "A1") As String
'This function will  Convert and English formula to localized formula
'SCell is the temporary cell that we will use to convert the Formula, by default it's A1
  
  Dim temporary As String
  Dim result As String
    
  
  With Range(sCell)
  
  temporary = .formula
  .formula = sformula
  result = .FormulaLocal
  .formula = temporary
  GetLocalFormula = result
  
  End With 'With Range(sCell)
  
  
End Function

Formula error due to different Languages in VBA Excel

2 Answers2