Excel Macro, inserting internationally valid formula during run-time

by

Ok, thanks for helping me with this, you’ve helped me crack this one.

It is indeed not possible to just use English. One can use English when operating on a formula, eg. by setting coding Range("A1").formula="AND(TRUE)", but this does not work with FormatConditions.

My solution is a function that writes a formula temporarily to a cell, reads it through the FormulaLocal property, and returns the localized formula, like so:

Function GetLocalizedFormula(formula As String)
' returns the English formula from the parameter in the local format
  Dim temporary As String
  temporary = Range("A1").formula
  Range("A1").formula = formula
  Dim result As String
  result = Range("A1").FormulaLocal
  Range("A1").formula = temporary
  GetLocalizedFormula = result
End Function

The returned formula can be used on FormatConditions, which will be re-localized or un-localized when the document is later opened on a different-language version of Excel.

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 VBA Formula German/French/Italian/Russian/Dutch/Foreign Function
  4. TextJoin UDF For Excel 2013
  5. VBA code takes long time to execute in excel
  6. Excel formula checklist in employees
  7. Excel VBA – How to add dynamic array formula
  8. What is the lifetime of a global variable in excel vba?
  9. Download attachment from Outlook and Open in Excel
  10. What is the most efficient/quickest way to loop through rows in VBA (excel)?
  11. Embed picture in outlook mail body excel vba
  12. How to check for empty array in vba macro [duplicate]
  13. Use autofilter on more than 2 criteria
  14. Compiler Error: User-defined types not defined
  15. Open a workbook using FileDialog and manipulate it in Excel VBA
  16. Count unique values in Excel
  17. excel hyperlink to nothing
  18. How to add headers to a multicolumn listbox in an Excel userform using VBA
  19. How do I apply a SumIf Formula in a VBA Sub? Getting Error 1004
  20. Want VBA in excel to read very large CSV and create output file of a small subset of the CSV
  21. VBA Macro crashes after 32000 rows
  22. How to break a long string into multiple lines
  23. How to extract text within a string of text
  24. simple vba code gives me run time error 91 object variable or with block not set
  25. Calling regasm without administrative rights for COM interop in Excel VBA
  26. Is there a way for DIR(path) in VBA to handle strings longer than 260?
  27. How do I activate a specific workbook and a specific sheet?
  28. VBA Run-time error 438 appears when “paste” runs
  29. If Not function proceeds when value *is* the one specified
  30. Copy an entire worksheet to a new worksheet in Excel 2010

Leave a Comment