Strange problem entering cell formulas with VBA

[ErikJan]

I have code where I enter formaulas into a sheet. As I'm having Dutch and English Excel users, I (had to) do this like the code below.

This have ALWAYS worked fine on all version of Excel in both languages. A colleague with a Dutch version suddenly gets "#NAME?" (well, actually the Dutch 'version' of this message "#NAAM?") in all cells where this formula is entered.
The odd thing is that if we put the cursor in the formula-bar and press enter, all is OK (without making ANY changes to the formula). Yes, calculation is automatic. I'm not certain if this has anything to do with the language, if might be a local Excel setting on his PC too. This does not (yet) happen on the other -all English- systems... Someone with a clue maybe???

Code: Select all

        If Lang <> 31 Then
            Range("SummIn").Cells(1, 1).Formula = "=IF(OR(H12="""",H12="" ""),"""",H12)"
        Else
            Range("SummIn").Cells(1, 1).Formula = "=ALS(OF(H12="""",H12="" ""),"""",H12)"
        End If

[HansV]

There is no need to use Dutch versions of formulas in VBA. You can assign the English-language formula in VBA regardless of the interface language.

So you should be able to use

Range("SummIn").Cells(1, 1).Formula = "=IF(OR(H12="""",H12="" ""),"""",H12)"

in both the Dutch and the English version of Excel.

[ErikJan]

OK, can try that but I'm SURE I had to do this like this in 2003...

[HansV]

I'm sure you didn't have to. I have workbooks that create English-language formulas in VBA that are being used without problems in both the Dutch and English versions of Excel 2000, 2002 (XP), 2003, 2007 and 2010 (32-bit and 64-bit).

[ErikJan]

Interesting... I'll give it a try

[ErikJan]

OK, just came off a TeamViewer session...

Indeed when I remove the language selection and leave the english part, the error disappears and all is OK (strange as prior beta-test versions had this code and it was used without problems...). Anyway...

So... I thought I'd remove the other place where I had a construction like this as well...

Code: Select all

            If Lang <> 31 Then
                .Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)"
            Else
                .Add Type:=xlExpression, Formula1:="=REST(RIJ();2)"
            End If

That however gave an error when I left only the English line; is that maybe because of the use of the ";" in stead of the "," in the function? This is what I remembered: I had to use this... Were we both right then maybe? (I know you were, but now it looks as if I was too)

[HansV]

This is a different situation. You're not assigning a cell formula, but a formula for conditional formatting or for data validation. It's a strange (and in my opinion very deplorable) anomaly that while cell formulas should always be specified in English, conditional formatting and data validation formulas have to be specified using the interface language and the system settings (in this example: Dutch functions and semi-colon ; as list separator).

[ErikJan]

Makes sense (your explanation, not the MS implementation) - thanks

[Jan Karel Pieterse]

I have reported that silly bug many, many times to MSFT. Without result so far.
The FormulaLocal property of the Name Object has a similar bug, but the other way around. It produces the localised refers to formula, but only accepts the ENglish syntax.

[Zauberkind]

Does the English rule also apply to VBA functions?

I sent someone an Excel 2003 workbook with a bunch of VBA code.
The first time it ran, it bombed on a string function, and complained about a missing library.
I speculated that his German version of Office might have either different names or different signatures for the function calls.
The other possibility is that his system is hosed.

I have no access to the machine, so checking it out means my having to put together a reference machine to test.
I'll do that as soon as I have the time.

I'm sure someone here can answer the question off the top of their head.

TIA
Zk

[Jan Karel Pieterse]

No, this is likely caused by a missing reference in the VBA project. Does your VBA project use any external libraries, like the date and time picker control?

[HansV]

VBA functions aren't localized.

The error message that you mention usually means that the VBA project contains a reference (in Tools | References...) to an object library that VBA can't find, either because it has been installed in a different location than the one it expects, or because it hasn't been installed on the target computer.

In the screenshot below, you'll see that four references have been "set", i.e. their check boxes have been ticked, in the workbook that I happened to have open. The first two, Visual Basic for Applications and Excel n.0 Object Library are always set when you work in Excel; you can't clear their check boxes. The other two can be cleared if you don't need them.

x178.jpg

If VBA wouldn't have been able to find one of the "set" references, its name would be prefixed with MISSING:.
A missing reference almost always causes code to fail, often on a very basic function such as a date or string function.

The user has to check for missing references on his/her own computer.

[Zauberkind]

I'll pass that along.
If I can't get access to the machine, then he'll have to fix it himself.

It's what I thought, but there's often a wide divide between what I think, what I know, and reality.
Learning is building a series of bridges to connect them.
Thanks for another brick.

Zk