vba Excel 宏，在运行时插入国际有效的公式

Excel Macro, inserting internationally valid formula during run-time

提问by grovel

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

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

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?

如您所见，我使用了德语公式“AND”（即“UND”），很明显，只要我在法语或英语版本的 Excel 上使用此代码，它就不起作用。通常公式会自动本地化，但如何在运行时插入适用于所有版本的公式？

采纳答案by grovel

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.

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

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

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

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.

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

回答by Marcus Mangelsdorf

I just found a very elegant solution to the problemin a German Excel forum. This doesn't write to a dummy cell but rather uses a temporary named range. I used the original idea (credit to bst) to write a translating function for both directions.

我刚刚在德国 Excel 论坛中找到了一个非常优雅的解决方案。这不会写入虚拟单元格，而是使用临时命名的 range。我使用最初的想法（归功于 bst）为两个方向编写了一个翻译函数。

Convert localized formula to English formula:

将本地化公式转换为英文公式：

Public Function TranslateFormula_LocalToGeneric(ByVal iFormula As String) As String
    Names.Add "temporaryFormula", RefersToLocal:=iFormula
    TranslateFormula_LocalToGeneric = Names("temporaryFormula").RefersTo
    Names("temporaryFormula").Delete
End Function

Convert English formula to localized formula:

将英文公式转换为本地化公式：

Public Function TranslateFormula_GenericToLocal(ByVal iFormula As String) As String
    Names.Add "temporaryFormula", RefersTo:=iFormula
    TranslateFormula_GenericToLocal = Names("temporaryFormula").RefersToLocal
    Names("temporaryFormula").Delete
End Function

This is very handy if you need to deal with formulas in conditional formatting, since these formulas are always stored as localized formulas (but you could need their generic version, e.g. to use Application.Evaluate(genericFormula)).

如果您需要处理条件格式中的公式，这非常方便，因为这些公式始终存储为本地化公式（但您可能需要它们的通用版本，例如使用Application.Evaluate(genericFormula)）。

回答by K_B

Store (a trivial version of) the formula in a (hidden) cell in your workbook.

在工作簿的（隐藏）单元格中存储（简单版本）公式。

Then when you open the workbook that formula will be translated automatically by excel for the user.

然后，当您打开工作簿时，excel 将为用户自动翻译该公式。

Now you just have to dissect this formula in your script (find the opening bracket "(" and take the past left of that:

现在你只需要在你的脚本中剖析这个公式（找到左括号“（”并从它的左边过去：

Use something like:

使用类似的东西：

strLocalizedFormula = Mid(strYourFormula, 2, InStr(1, strYourFormula, "(") - 2)

strLocalizedFormula = Mid(strYourFormula, 2, InStr(1, strYourFormula, "(") - 2)

where strYourFormulawill be a copy from the formula from your worksheet.

strYourFormula工作表中公式的副本在哪里。

I hope this works as I only use an English environment.

我希望这有效，因为我只使用英语环境。

Also from reading this: http://vantedbits.blogspot.nl/2010/10/excel-vba-tip-translate-formulas.htmlI am thinking you should (only) be able to use the english version of a cell formula from VBA.

也从阅读这个：http: //vantedbits.blogspot.nl/2010/10/excel-vba-tip-translate-formulas.html我想你应该（只）能够使用英文版的单元格公式来自VBA。

回答by chris neilsen

Maybe try this (untested as I only have English version insatlled)

也许试试这个（未经测试，因为我只有英文版安装）

Write your international version of the formula to an out of the way cell using Range.Formula. Then read it back from Range.FormulaLocal, and write that string to the FormatConditions

使用Range.Formula. 然后从 读取它Range.FormulaLocal，并将该字符串写入FormatConditions

回答by user9479152

Please refer to the link for more explanation: https://bettersolutions.com/csharp/excel-interop/locale-culture.htm

更多解释请参考链接：https: //bettersolutions.com/csharp/excel-interop/locale-culture.htm

CultureInfo baseCulture = System.Threading.Thread.CurrentThread.CurrentCulture; 
Thread.CurrentThread.CurrentCulture = new CultureInfo(xlapp.LanguageSettings.LanguageID(Office.MsoAppLanguageID.msoLanguageIDUI)); 
// do something 
System.Threading.Thread.CurrentThread.CurrentCulture = baseCulture; 

回答by Diego

Thanks everyone! I found the post very useful.

谢谢大家！我发现这个帖子非常有用。

My solution is a combination of others, I add it in case somebody finds it useful.

我的解决方案是其他的组合，我添加它以防有人发现它有用。

Dim tempform As String
Dim strlocalform1 As String
Dim strlocalform2 As String

' Get formula stored in WorksheetA Cell O1 =IFERROR(a,b)
tempform = Worksheets("Sheet").Range("O1").Formula

' Extract from the formula IFERROR statement in local language.
strlocalform1 = Mid(tempform, 2, InStr(1, tempform, "(") - 1)

' Extract from the formula separator , (comma) in local settings.
strlocalform2 = Mid(tempform, InStr(1, tempform, "a") + 1, 1)

' Add formula in local language to desired field.
pvt.CalculatedFields.Add Name:="NewField", Formula:="=" & strlocalform1 & "FORMULA" & strlocalform2 & ")"

Hope this helps!

希望这可以帮助！