Excel 宏，在运行时插入国际有效的公式 [英] Excel Macro, inserting internationally valid formula during run-time

问题描述

我有一个 Excel 电子表格，带有一个宏，可以插入条件格式，如下所示:

I've got an Excel spreadsheet, with a Macro, that inserts a conditional formatting, like this:

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=UND($A3=""" & lastName & """; $B3=""" & firstName & """)"

如您所见，我对AND"(即UND")使用了德语公式，很明显，一旦我在法语或英语版本的 Excel 上使用此代码，它就不起作用.通常公式会自动本地化，但如何在运行时插入适用于所有版本的公式?

As you can see, I've used the German formula for "AND" (i.e. "UND"), and obviously, this code doesn't work as soon as I use it on a French or English version of Excel. Usually formulas are localized automatically, but how can I insert a formula during run-time that will work on ALL versions?

推荐答案

好的，谢谢你帮我解决这个问题，你帮我解决了这个问题.

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

只用英文确实不行.在操作公式时可以使用英语，例如.通过设置编码 Range("A1").formula="AND(TRUE)"，但这不适用于 FormatConditions.

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.

我的解决方案是一个函数，将公式临时写入单元格，通过FormulaLocal属性读取它，并返回本地化的公式，如下所示:

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

返回的公式可用于 FormatConditions，当文档稍后在不同语言版本的 Excel 上打开时，将重新本地化或取消本地化.

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.

这篇关于Excel 宏，在运行时插入国际有效的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！