Excel Version Compatibility Issues in VBA

Larry Haydn

Board Regular
Joined
Jul 18, 2019
Messages
207
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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....
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Upvote 0
Rory said:
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?
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top