语言无关的公式

问题描述

我想准备一个带有一些公式的概述标签，尽管并非所有同事都使用现成的Excel版本-我目前使用的是De-at语言包.因此，我开始使用.FormulaLocal，但在我的情况下却无法正常工作

I'd like to prepare an overview tab with some formulas, though not all colleagues use an en-us version of Excel - I currently use de-at languages pack. Therefore I started using .FormulaLocal but it doesn't quite work in my case

在此示例中，我尝试准备一个公式，该公式汇总来自各个选项卡的所有轻度"错误.我有两个版本

In this example I try to prepare a formula that sums up all "light" errors from various tabs. I have two versions

版本1 (可与De-at Excel一起使用)

Version 1 (does work with de-at Excel)

Sub count_light_errors(tabName)
Dim element As Variant
Dim formula As String
Dim temp As Variant

For Each element In tabName
    temp = temp & "zählenwenn(" & element & "!E:E;""light"");"
Next element

formula = "=summe(" & Left(temp, Len(temp) - 1) & ")"
Worksheets("Overview").Range("C8").FormulaLocal = formula

End Sub

版本2 (不适用于De-at Excel)

Version 2 (does not work with de-at Excel)

Sub count_light_errors(tabName)
Dim element As Variant
Dim formula As String
Dim temp As Variant

For Each element In tabName
    temp = temp & "countif(" & element & "!E:E,""light""),"
Next element

formula = "=sum(" & Left(temp, Len(temp) - 1) & ")"
Worksheets("Overview").Range("C8").FormulaLocal = formula

End Sub

两者之间的区别只是使用德语单词

The difference between those two is just using the German words for

• SUM()-> SUMME()
• COUNTIF()->ZÄHLENWENN()

和;代替，作为分隔符

and; instead of , as seperator

如何正确准备公式，使其独立于用户语言包?

How do I prepare formulas correctly, so they are independent from the user language pack?

感谢一百万！
-克劳斯

Thanks a million!
-Claus

推荐答案

VBA非常以EN-US为中心. VBA的.Formula和.FormulaR1C1需要ROW函数.要使用诸如SUMME之类的区域语言功能风味"，则应改用Range.FormulaLocal属性或Range.FormulaR1C1Local属性.

VBA is very EN-US-centric. VBA's .Formula and .FormulaR1C1 expect the ROW function. To use regional language function 'flavors' like SUMME then the Range.FormulaLocal property or Range.FormulaR1C1Local property should be employed instead.

因此，代替使用.FormulaLocal只是将.Formula与EN语言一起使用，您的代码将以每种语言进行解释.

So instead of use .FormulaLocal just use .Formula with EN language, your code will be interpreted in every language.

这篇关于语言无关的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！