**Our Take**
This Ruby developer ran into a problem that has tripped up anyone who has ever tried to automate Excel across language boundaries: formulas that work perfectly on one machine fail with `#NAME?` errors on another. The culprit was locale settings, specifically, an Italian-language Excel installation that expects `=NUM.SETTIMANA.ISO` instead of `=ISOWEEKNUM`. The developer found `FormulaLocal` as a workaround but rejected it, sensing it was not production quality. They were right to trust that instinct.
The core issue here is that Excel's COM automation surface is not locale-agnostic by default. When you write `Range.Formula = '=ISOWEEKNUM(B1)'`, Excel silently translates that formula into the user's language on input. On an Italian machine, it stores `=NUM.SETTIMANA.ISO(B1)`. When the file is opened on an English machine, Excel cannot resolve `NUM.SETTIMANA.ISO` and throws `#NAME?`. The `FormulaLocal` property exists precisely to handle this, but relying on it means your automation code must know the locale of every target machine. That is fragile. It introduces a per-machine dependency that defeats the purpose of automation.
What the developer needs is a locale-agnostic approach that separates the formula logic from the display language. The cleanest path is to use `Range.FormulaR1C1` instead of `Range.Formula`. `FormulaR1C1` uses Excel's internal R1C1 reference style, which is language-independent. For the ISO week number example, you would write `worksheet.Range('B2').FormulaR1C1 = '=ISOWEEKNUM(RC[-1])'`. The function name `ISOWEEKNUM` is still English, but because you are setting it via `FormulaR1C1`, Excel's COM layer treats it as an English-language formula and does not attempt a locale-based translation. The file opens correctly on any language version.
This is not a workaround. It is the intended method for cross-locale automation. Microsoft's own documentation for `FormulaR1C1` states that it "returns or sets the formula for the object, using R1C1-style notation, in the language of the macro." The macro language is always English. That makes `FormulaR1C1` the correct production tool for this exact scenario. The developer's instinct to reject `FormulaLocal` was sound, but the solution was already in the API they were using. They just needed to switch the property.
For anyone building Excel automation that must work across language boundaries, the rule is simple: use `FormulaR1C1` for all formula writes. It eliminates the locale variable entirely. Your code remains clean, your files open correctly on any Excel version, and you avoid the maintenance nightmare of locale-specific formula strings. That is not a workaround. That is engineering with the platform, not against it.