Скачать или смотреть Solving the Excel Formula Language Challenge for Multilingual KPIs in Switzerland

Learn how to create a `universal Excel code` that works in both French and German versions. Discover the problem and effective solution to maintain multilingual consistency in your VBA Excel formulas.
---
This video is based on the question https://stackoverflow.com/q/68679075/ asked by the user 'MirageHF' ( https://stackoverflow.com/u/16606189/ ) and on the answer https://stackoverflow.com/a/68679334/ provided by the user 'Maciej Los' ( https://stackoverflow.com/u/2142028/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Excel Formula Traduction (multilingual system)

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Solving the Excel Formula Language Challenge for Multilingual KPIs in Switzerland

Introduction

Working in a bilingual environment can often create complications, especially when it comes to programming and using software like Excel. This is a common situation faced by professionals in Switzerland, where both French and German are widely spoken. A perfect illustration of this challenge comes from a programmer named Mirage, who encountered issues with Excel formulas while developing Key Performance Indicators (KPIs) using VBA. The specific problem arose when translating Excel functions into language-dependent formats that don't work across different language settings.

The Problem at Hand

Mirage’s VBA code utilized local French Excel functions, such as SI() for IF() and SOMME() for SUM(), which functioned correctly on French systems. However, they failed on German systems where the English functions IF() and SUM() are recognized. This created a significant issue as KPIs are needed to work seamlessly across both languages, leading to confusion and inefficiency.

Example of the Original Code

Here’s an example of the code Mirage was initially using:

[[See Video to Reveal this Text or Code Snippet]]

This code works perfectly on French systems, but does not translate well when attempting to operate in a German environment.

The Effective Solution

Fortunately, the solution to this problem is straightforward. Instead of using the language-specific functions, Mirage can utilize the English functions, but in a slightly different syntax. This is a clever workaround for achieving multilingual compatibility in Excel.

Here’s how to implement this solution:

Step 1: Modify the VBA code to use the .Formula property instead of .FormulaLocal:

[[See Video to Reveal this Text or Code Snippet]]

Note: Notice the change of the semi-colon ; to a comma ,, which is the correct syntax for the English version of the formula.

Additional Tip

If you want to verify that a formula is being displayed correctly in the English notation in the active cell, you can use the following code line:

[[See Video to Reveal this Text or Code Snippet]]

This will help you in debugging and provide peace of mind that your formula is structured correctly across different Excel language versions.

Conclusion

By following the solution outlined above, Mirage can create a universal code that is compatible with both French and German versions of Excel. This ensures that KPIs calculated using Excel work correctly, regardless of the language settings on the users’ systems. Dealing with multilingual environments may seem daunting at first, but with a few simple adjustments, the process can become much more streamlined.

With the right approach, you can make your Excel applications truly international!