Automatically adjust the "RC" in an English formula based on the user's client language

Question

So I was surprisingly getting error reports from many users for a new tool I made. Those users all have the German version of Excel installed. After a quick glance, I figured out the problem:

I used this formula as a named function to get the color of the adjacent cell (don't ask me why that's important, but things depend on the result):

=GET.CELL(63;OFFSET(INDIRECT("RC";FALSE);0;1))

Now in the German version, the "RC" remains, but would have to change to "ZS" for the formula to not throw a reference error.

It's written the way it is to reference the cell the function is in.

Is it possible to rewrite this formula in a way that does not "hard code" the "RC" so it can change depending on the localization of the user (Chinese and Czech would be relevant too)? Or is it possible to replace the named formula with a bit of VBA code that can check for the language version of the user?

If I can't adjust this bit of the tool I'll have to go with a less attractive workaround (Have multiple Y/N checks instead of assigning colors).

Answer 1

You can then find the user's LCID using a vba code such as

dim lcode as long
lcode = Application.LanguageSettings.LanguageID(msoLanguageIDUI)

You can then us a simple if then else and connect the LCID to appropriate strings using (this). Example:

If lcode = "0407" Then
Cells(5, 5).Formula = "=GET.CELL(63;OFFSET(INDIRECT(""ZS"";FALSE);0;1))"
ElseIf lcode = "1033" Then
Cells(5, 5).Formula = "=GET.CELL(63;OFFSET(INDIRECT(""RC"";FALSE);0;1))"
End If

Alternatively, you can also define a string i and based on the LCID you can attach i the appropriate value based on the result and use:

Cells(5, 5).Formula = "=GET.CELL(63;OFFSET(INDIRECT(" & i & ";FALSE);0;1))"

Answer 2

Well, this may not actually answer my question, but it does solve my problem. Turns out that entering

=GET.CELL(63;Sheet!B1)

In the name manager while having A1 selected delivers exactly the same result as

=GET.CELL(63;OFFSET(INDIRECT("RC";FALSE);0;1))

Being that it gets the cell color number from the cell one to the right, no matter where you then insert this function.

Answer 3

This is easily solved with a UDF

Option 1: pass the cell you want the color of as a parameter (this cell can be anywhere, not limited to the next cell to the right)

Function GetColor(r As Range) As Variant
    Application.Volatile
    GetColor = r.Interior.ColorIndex
End Function

Option 2: get the color of the next cell to the right of the cell containing the formula

Function GetColorNextCell() As Variant
    Application.Volatile
    GetColorNextCell = Application.ThisCell.Offset(, 1).Interior.ColorIndex
End Function

That said, using format as data is a bad idea. Both these formula (and your named range version) won't update when you just change the color of a cell. They all required you to force a recalc to update (changing format does not trigger a recalc).

I've made them Volitile so they at least update on all recalcs, but even then they won't update if you just change the color.

You are representing something with that color. I'd suggest you change that to use a specific value in the cell to represent whatever that is. You can always use Conditional Formatting to also color the cell based on that value.