blocks|key|224575|text|将公式(的一个简单版本)存储在工作簿的(隐藏)单元格中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|224576|然后，当您打开工作簿时，该公式将由excel自动为用户翻译。|224577|现在你只需要在你的脚本中剖析这个公式(找到开始的括号"(“，然后在过去的左边：|224578|使用像这样的东西：|224579|strLocalizedFormula+=+Mid(strYourFormula,+2,+InStr(1,+strYourFormula,+"(")+-+2)|offset|length|style|CODE|224580|其中strYourFormula将是工作表中公式的副本。|224581|我希望这能正常工作，因为我只使用英语环境。|224582|读到这篇文章，我想你应该(只)能够使用http://vantedbits.blogspot.nl/2010/10/excel-vba-tip-translate-formulas.html中的单元格公式的英文版本。|224583|entityMap|0|LINK|mutability|MUTABLE|url|http://vantedbits.blogspot.nl/2010/10/excel-vba-tip-translate-formulas.html^0|0|0|0|0|0|27|0|2|E|0|0|J|23|0|0^^$0|@$1|2|3|4|5|6|7|11|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|12|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|13|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|14|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|15|8|@$J|16|K|17|L|M]]|9|@]|A|$]]|$1|N|3|O|5|6|7|18|8|@$J|19|K|1A|L|M]]|9|@]|A|$]]|$1|P|3|Q|5|6|7|1B|8|@]|9|@]|A|$]]|$1|R|3|S|5|6|7|1C|8|@]|9|@$J|1D|K|1E|1|1F]]|A|$]]|$1|T|3|-4|5|6|7|1G|8|@]|9|@]|A|$]]]|U|$V|$5|W|X|Y|A|$Z|10]]]]

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.

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:

<code>strLocalizedFormula = Mid(strYourFormula, 2, InStr(1, strYourFormula, "(") - 2)</code>

where <code>strYourFormula</code> will be a copy from the formula from your worksheet.

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

Also from reading this:
<a href="http://vantedbits.blogspot.nl/2010/10/excel-vba-tip-translate-formulas.html" rel="noreferrer">http://vantedbits.blogspot.nl/2010/10/excel-vba-tip-translate-formulas.html</a>
I am thinking you should (only) be able to use the english version of a cell formula from VBA.

blocks|key|224616|text|也许可以试试这个(未测试，因为我只有英文版本不完整)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|224617|使用Range.Formula将您的国际版本的公式写入不合适的单元格。然后从Range.FormulaLocal读回该字符串，并将该字符串写入FormatConditions|offset|length|style|CODE|224618|entityMap^0|0|2|D|12|I|1Z|G|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@$D|L|E|M|F|G]|$D|N|E|O|F|G]|$D|P|E|Q|F|G]]|9|@]|A|$]]|$1|H|3|-4|5|6|7|R|8|@]|9|@]|A|$]]]|I|$]]

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 <code>Range.Formula</code> . Then read it back from <code>Range.FormulaLocal</code>, and write that string to the <code>FormatConditions</code>

blocks|key|224668|text|好的，谢谢你帮我解决这个问题，你帮我破解了这个问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|224669|仅仅使用英语确实是不可能的。在对公式进行运算时，可以使用英语。通过设置coding+Range("A1").formula="AND(TRUE)"，但这不适用于FormatConditions。|offset|length|style|CODE|224670|我的解决方案是一个函数，它将公式临时写入单元格，通过FormulaLocal属性读取它，然后返回本地化的公式，如下所示：|224671|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|code-block|syntax|javascript|224672|返回的公式可以在FormatConditions上使用，稍后在其他语言版本的Excel上打开文档时，将重新本地化或取消本地化。|224673|entityMap^0|0|16|V|28|G|0|Q|C|0|0|8|G|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@$D|U|E|V|F|G]|$D|W|E|X|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|Y|8|@$D|Z|E|10|F|G]]|9|@]|A|$]]|$1|J|3|K|5|L|7|11|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|12|8|@$D|13|E|14|F|G]]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|R|$]]

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 <code>Range("A1").formula="AND(TRUE)"</code>, but this does not work with <code>FormatConditions</code>.

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

<pre><code>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
</code></pre>

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

blocks|key|442898|text|谢谢大家！我发现这篇文章非常有用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|442899|我的解决方案是其他解决方案的组合，我添加了它，以防有人发现它有用。|442900|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")+%2B+1,+1)

'+Add+formula+in+local+language+to+desired+field.
pvt.CalculatedFields.Add+Name:="NewField",+Formula:="="+&+strlocalform1+&+"FORMULA"+&+strlocalform2+&+")"|code-block|syntax|javascript|442901|希望这能有所帮助！|442902|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Thanks everyone! I found the post very useful.

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

<pre><code>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:="=" &amp; strlocalform1 &amp; "FORMULA" &amp; strlocalform2 &amp; ")"
</code></pre>

Hope this helps!

blocks|key|442915|text|有关更多说明，请参阅链接：https://bettersolutions.com/csharp/excel-interop/locale-culture.htm|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|442916|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;+|code-block|syntax|javascript|442917|entityMap|0|LINK|mutability|MUTABLE|url|https://bettersolutions.com/csharp/excel-interop/locale-culture.htm^0|D|1V|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

Please refer to the link for more explanation: <a href="https://bettersolutions.com/csharp/excel-interop/locale-culture.htm" rel="nofollow noreferrer">https://bettersolutions.com/csharp/excel-interop/locale-culture.htm</a>

<pre><code>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; 
</code></pre>

blocks|key|224797|text|我知道这个主题由来已久，有人可能已经找到了一个很好的解决方案，但我只是遇到了同样的问题，我需要在不修改工作表、创建临时单元格内容或命名区域的情况下应用条件格式。所有用户都使用英语版本的Excel，因此公式中使用的函数是相同的，但区域设置因位置而异，因此参数分隔符也不同；在挪威语中，它是";“而不是"，"，我想这很像欧洲其他地方。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|224798|例如，我需要自动创建条件格式，使用Excel公式满足以下条件：|224799|.FormatConditions.Add+xlExpression,+Formula1:="=AND(ISNUMBER(B"+&+I+&+"),B"+&+I+&+">="+&+Ul1+&+")"|code-block|syntax|javascript|224800|其中"Ul1“是在上一步中定义的值，它对解决方案并不重要。|224801|但是，我需要能够在同时使用挪威语和英语设置的计算机上运行此程序|224802|我找到了安德鲁·普尔瑟姆的一个非常简短和简单的解决方案：https://www.mrexcel.com/board/threads/french-vba-vs-english-vba.729570/。他只是把参数分隔符变成了一个变量：|offset|length|224803|If+Application.International(xlDecimalSeparator)+=+","+Then
++++Sep+=+";"
Else
++++Sep+=+","
End+If

Cl1+=+"=AND(ISNUMBER(B"+&+I+&+")"+&+Sep+&+"B"+&+I+&+"<"+&+Ul1+&+")"|224804|对我来说很有吸引力:)|224805|我知道这只解决了问题的一部分，但我假设这可能适用于许多使用本地区域设置的英语Office安装的国际公司。|224806|entityMap|0|LINK|mutability|MUTABLE|url|https://www.mrexcel.com/board/threads/french-vba-vs-english-vba.729570/^0|0|0|0|0|0|S|1Z|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|15|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|16|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|17|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|18|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|19|8|@]|9|@$O|1A|P|1B|1|1C]]|A|$]]|$1|Q|3|R|5|F|7|1D|8|@]|9|@]|A|$G|H]]|$1|S|3|T|5|6|7|1E|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|1F|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|1G|8|@]|9|@]|A|$]]]|X|$Y|$5|Z|10|11|A|$12|13]]]]

I know this thread is ages old, and someone may have found an elegant solution, but I just had the same problem where I needed to apply conditional formatting without modifying the sheet, creating temporary cell contents or named ranges. All users use English language versions of Excel, so the functions used in the formulas are the same, but the regional settings vary by location, and therefore also the parameter separater; In Norwegian, it's &quot;;&quot; instead of &quot;,&quot;, much like the rest of Europe, I guess.
For example, I needed to automatically create conditional formatting, using Excel formula for the following criterion:
<pre><code>.FormatConditions.Add xlExpression, Formula1:=&quot;=AND(ISNUMBER(B&quot; &amp; I &amp; &quot;),B&quot; &amp; I &amp; &quot;&gt;=&quot; &amp; Ul1 &amp; &quot;)&quot;
</code></pre>
Where &quot;Ul1&quot; is a value defined in a previous step, and it's not important for the solution.
However, I needed to be able to run this on computers with both Norwegian and English settings
I and found a very short and simple solution from Andrew Pulsom here: <a href="https://www.mrexcel.com/board/threads/french-vba-vs-english-vba.729570/" rel="nofollow noreferrer">https://www.mrexcel.com/board/threads/french-vba-vs-english-vba.729570/</a>. He just made the parameter separator into a variable:
<pre><code>If Application.International(xlDecimalSeparator) = &quot;,&quot; Then
 Sep = &quot;;&quot;
Else
 Sep = &quot;,&quot;
End If

Cl1 = &quot;=AND(ISNUMBER(B&quot; &amp; I &amp; &quot;)&quot; &amp; Sep &amp; &quot;B&quot; &amp; I &amp; &quot;&lt;&quot; &amp; Ul1 &amp; &quot;)&quot;
</code></pre>
Worked like a charm for me :)
I know that this only solves part of the problem, but I assume that this could apply to many international companies which use English Office installations with local regional settings.

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

<pre><code>Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=UND($A3=""" &amp; lastName &amp; """; $B3=""" &amp; firstName &amp; """)"
</code></pre>

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?

Excel Macro, inserting internationally valid formula during run-time

我有一个带有宏的Excel电子表格，它插入一个条件格式，如下所示：Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=UND($A3=""" & lastName & """; $B3=""" & firstName & """)"如你所见，我使...

问Excel宏，在运行时插入国际有效公式
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Excel宏，在运行时插入国际有效公式EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Excel宏，在运行时插入国际有效公式
EN