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

问题描述

我有一个Excel电子表格，一个宏，它插入一个条件格式，如下所示：

 选择。 FormatConditions.Add类型：= xlExpression，Formula1：== UND（$ A3 =& lastName&; $ B3 =& firstName&）
您可以看到，我使用德语公式为AND（即UND），显然， ，一旦我在法语或英语版本的Excel中使用该代码，这段代码就不起作用。 
通常公式是自动本地化的，但是如何在运行时插入一个可在所有版本上运行的公式？
解决方案
好的，谢谢帮助我，你帮我解决了这个问题。
 
 
 
确实不可能只使用英文。在公式上操作时可以使用英文，例如。通过设置编码 Range（A1）。formula =AND（TRUE），但这不适用于 FormatConditions 。
 
 
 
我的解决方案是将公式暂时写入单元格的函数，通过 FormulaLocal 属性，并返回本地化公式，如下所示：
 函数GetLocalizedFormula（公式As String）
'返回英文公式从本地格式的参数
 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 
结束函数
  
返回的公式可以在 FormatConditions 中使用，这将被重新定位或者当文档稍后在不同语言版本的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 & """)"
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.

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.

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