Writing a formula from VBA not working on every end-user's PC

I hope someone will be able to help me sort this one out because I've been crawling the Internet for some time now and could not find any obvious reason why the behavior of my macro under Excel works for some users (including me of course) and does not work on some other users' laptops.

My objective:

Write from a VBA SubRoutine a vlookup formula in one cell. Based on that vlookup, either it finds something and it displays a text A or the vlookup throws an error and it then displays a text B.

The situation

Here is the formula if we were writing it directly in the sheet from the end-user's perspective: =IF(NOT(ISERROR(VLOOKUP(C2,DataSet2!$A$2:$A$12,1,0))),"Direct Report","Team member of direct report")

When we copy/paste that formula on a PC where the macro fails directly in the spreadsheet. It works. When we run the macro actually trying to incorporate that formula in that exact same cell, it fails and throws an error 400.

Here is now the VBA code attempting to write the above formula in the cell:

formulaString = "=IF(NOT(ISERROR(VLOOKUP(C" & CStr(counter + 2) & systemListSeparator & "DataSet2!$A$2:$A$" & CStr(2 + totalDirectReportsCount) & systemListSeparator & "1" & systemListSeparator & "0)))" & systemListSeparator & """Direct Report""" & systemListSeparator & """Team member of direct report"")"
Selection.Offset(counter, 0).Formula = formulaString

So the above 2 statements, as you'll have understood probably, are included in a loop and for every single row I have, I want to add that formula in the first cell from the currently selected cell.

What drives me crazy is that: it works fine on my PC, it works fine on my colleague's PC who is in Finland, but on another colleague's PC also in Finland, it does not. I have Windows 10 and Office 2016, she has Windows 10 and office 2016 as well...

So I am really wondering if there could be some regional settings or Excel settings or System Settings which could make HER Excel not interpret my "formulaString" as viable for her laptop. I have made sure to fetch the list separator to avoid those usual regional settings easy traps but would there be other similar traps I am not aware of?

Thanks a lot for any hints I could follow to progress on my investigations. Kind regards, Nicolas C.

Solution

This is really a tricky point, but fortunately VBA provides an excellent solution.

Simply write the formula in Excel, select the cell and run the following code:

Public Sub TestMe()

    With ActiveCell
        Debug.Print .Formula
        Debug.Print .FormulaLocal
        Debug.Print .FormulaR1C1
    End With

End Sub

In German Excel, this is what you would get:

=SUM(C1+D1)
=SUMME(C1+D1)
=SUM(RC[2]+RC[3])

Then rebuild your formulas, using only .Formula or .FormulaR1C1 and the result from the immediate window. Thus your colleagues from all over the world would be really happy. As noted in the 3 examples, the first and the third one always give the same answer, following the en_US format.

Another option is that Excel and VBA do not like your formula, because of the decimal separator in Finland, being different from the decimal separator in the USA. See How to add hardcoded float number values to the formula using VBA to change it a bit.

Solution Nicolas C. edit: All the above comments and hereby answer have helped me solving my issue. Thank you very much guys for your prompt reaction and feedback. You really saved my day. So the root cause was indeed that I had tried to capture the end-user's system preferences on his/her locale and regional settings and making use of his/her list separator (which was ; actually) within the VBA script which was a bad move since, as mentioned by @AxelRichter and @Vityata, within a VBA script, .Formula needs always to be written in the en_US format, that is english function names, commas, etc.

I have now removed my user related list separator and replaced it with traditional commas and it solved my issue.