Excel VBA Formula German/French/Italian/Russian/Dutch/Foreign Function

by

You have two main errors in the code – not escapting the " characters and using .Formula instead of .FormulaLocal. The " characters should be written twice to show once in a string. See this code:

Public Sub TestMe()
    Debug.Print "test"""""
End Sub

It prints test"". The last " is for the end of the string. Concerning the formula, use .FormulaLocal if you want to use the German formulas and double the doublequotes:

Range("O9").FormulaLocal = "=WENN(ODER(K9="""";L9="""");"""";WENNFEHLER((L9-K9)/K9;""""))"

In general, avoid using .FormulaLocal and use .Formula, to make your VBA code compatible with a workbook from Italy or France from example. Something like this will work with any local settings in Excel:

Range("O9").Formula = "=IF(OR(K9="""",L9=""""),"""",IFERROR(((K9-K9)/K9),""""))"

What I usually do is the following:

  1. Write the formula, so it works in Excel;
  2. Select it manually;
  3. Run this:
Public Sub PrintMeUsefulFormula()

    Dim strFormula  As String
    Dim strParenth  As String

    strParenth = """"

    strFormula = Selection.Formula
    strFormula = Replace(strFormula, """", """""")

    strFormula = strParenth & strFormula & strParenth
    Debug.Print strFormula

End Sub
  1. It prints the formula as it should look like in the immediate window;
  2. Copy it;

  3. In the code above you may replace Selection.Formula with one of the following three (Consider as an example =IF(B1=C1,""Equal"",""Not Equal"")

    • Selection.FormulaLocal (gets the local formula =WENN() for Germany)
      =WENN(B1=C1;""Equal"";""Not equal"")
    • Selection.FormulaR1C1 (gets formula in R1C1 format)
      =IF(RC[1]=RC[2],""Equal"",""Not equal"")
    • Selection.FormulaR1C1Local (gets R1C1 format with local formulas)
      =WENN(ZS(1)=ZS(2);"Equal";"Not equal")

More Related Contents:

  1. VBA – How to grab the cell below if a cell has text?
  2. Vba Excel do vlookup from a closed file
  3. Excel Macro, inserting internationally valid formula during run-time
  4. TextJoin UDF For Excel 2013
  5. delete Autofilter Field resullt using the header Name
  6. Somehow it’s not copying [closed]
  7. VBA macro that search for file in multiple subfolders
  8. So, I have 6 “master” files to then divide into 40 separate files
  9. Getting ScriptControl to work with Excel 2010 x64
  10. Application.Match gives type mismatch
  11. Count and Highlight keywords within phrases
  12. Protecting cells in Excel but allow these to be modified by VBA script
  13. Saving excel worksheet to CSV files with filename+worksheet name using VB [duplicate]
  14. Detect merged cells in VBA Excel with MergeArea
  15. Find last used cell in Excel VBA
  16. Using VBA code, how to export Excel worksheets as image in Excel 2003?
  17. Parse HTML content in VBA
  18. How to add a DocumentProperty to CustomDocumentProperties in Excel?
  19. VBA EXCEL To Prompt User Response to Select Folder and Return the Path as String Variable [duplicate]
  20. Should I turn .CutCopyMode back on before exiting my sub procedure?
  21. VBA: How to delete filtered rows in Excel?
  22. How to use VBA SaveAs without closing calling workbook?
  23. Removing the password from a VBA project
  24. Removing special characters VBA Excel
  25. Count the number of rows in another sheet [duplicate]
  26. Getting Excel to refresh data on sheet from within VBA
  27. Finding first blank row, then writing to it
  28. How do I get a list of unique values from a range in Excel VBA?
  29. How to get selected value in multicolumn listbox
  30. Query my excel worksheet with VBA

Leave a Comment