Excel Version Compatibility Issues in VBA

[Larry Haydn]

I developed an Excel VBA application on my Windows 10 Office 365 platform.
My client is using Windows 8, Office 2016.
Additional info: their PC language is English, with Japanese as a secondary language.

My application crashed on their system.

Symptoms:
A formula =IFS(something|something|something...)
becomes =_xlfn.IFS(something,something,something...)

The pipe ( | ) changing to comma ( , ) probably means that their Excel is an older version.
But what is the _xlfn. that is inserted into the formula?

Anyone encounter this?
How did you solve this problem?

Other symptons:
I had copied some of the macros from their system to mine PC, and modified them.
Sometimes while editing these macros, my keyboard's double-quote ( " ) and AT ( @ ) keys gets swapped.
Why does this happen?
How to solve it?

 

[Fluff]

The | being changed to , is simply down to the difference in settings between your computer & your clients.
I suspect you have changed you settings to use a | rather than the standard settings.

The _xlfn is because your client doesn't have access to the IFS function. This is standard practice to prevent formulae getting trashed if opened in earlier versions of Xl

As for the last part, I've no idea.

 

[RoryA]

The last part is a keyboard language issue, so I suspect you are hitting the shortcut key to switch languages by mistake (usually left Alt key + Shift).

Just FYI, IFS is quite inefficient as it evaluates all its arguments. Better to use nested IF functions, in my opinion.

 

[Larry Haydn]

The | being changed to , is simply down to the difference in settings between your computer & your clients.
I suspect you have changed you settings to use a | rather than the standard settings.

The _xlfn is because your client doesn't have access to the IFS function. This is standard practice to prevent formulae getting trashed if opened in earlier versions of Xl

As for the last part, I've no idea.

The formula was typed directly in the cell. Seems like a new language feature in VBA.
If I insert the formula using VBA, I would have used the comma as separator.

 

[Larry Haydn]

The | being changed to , is simply down to the difference in settings between your computer & your clients.
I suspect you have changed you settings to use a | rather than the standard settings.

The _xlfn is because your client doesn't have access to the IFS function. This is standard practice to prevent formulae getting trashed if opened in earlier versions of Xl

As for the last part, I've no idea.

I would imagine that Excel 2016 would have the IFS function, which it has for decades.
Strange....

 

[RoryA]

I would imagine that Excel 2016 would have the IFS function, which it has for decades.
Strange....

I think you're confusing IF (which has been around forever) with IFS, which is brand new.

 

[Larry Haydn]

I think you're confusing IF (which has been around forever) with IFS, which is brand new.

Oh, I must have confused it with IIF

 

[Larry Haydn]

I would imagine that Excel 2016 would have the IFS function, which it has for decades.
Strange....

Found out that IFS was introduced in Excel 2016, but in the subscription model (Office 365), not in the standalone version.
My client is using Office Home and Business 2016.
So that is the problem.
I will change the formula to nested IF.

 

[Larry Haydn]

Found out that IFS was introduced in Excel 2016, but in the subscription model (Office 365), not in the standalone version.
My client is using Office Home and Business 2016.
So that is the problem.
I will change the formula to nested IF.

Here is the answer:
https://answers.microsoft.com/en-us...-problem/499c4d75-335a-469c-96bd-9b6a3bcc7935

 

[shg]

IFS is quite inefficient as it evaluates all its arguments.

That's very surprising, considering IF's ruthless efficiency. You see that when it evaluates?