0

I have an Excel spreadsheet that uses the new XLOOKUP function. Despite a written warning on the main page, users keep trying to run the macros in older versions of Excel and they get weird results.

Is there a way to have the macro check for compatibility at the start of the script and give users a warning if they are using an older version of Excel? Essentially I want the macro to abort if the user is running anything other than Microsoft 365 or Excel 2021.

The Application.Version number approach won't work anymore because Microsoft stopped numbering at 16.

Oregonian
  • 1
  • 1
  • As a developer, you have to handle backwards compatibility using a method better than just not running code. My advice would be to use INDEX/MATCH in place of the XLOOKUP so that compatibility isn't an issue. – Frank Ball Aug 12 '23 at 16:48
  • What do you mean by `run the macros in older versions of Excel and they get weird results.` – Black cat Aug 12 '23 at 17:22
  • you can use [application.version](https://learn.microsoft.com/en-us/office/vba/api/excel.application.version) property and give a warning if the version is before xlookup was introduced, though the best solution would be to use index match like Frank suggested – Suraj Shourie Aug 12 '23 at 17:36
  • You could also use Application.Build all you need is the Build number where XLookup was implemented and you're good to go. – RetiredGeek Aug 12 '23 at 18:14
  • @Blackcat It's a population model that runs thousands of individuals through 100 generations of random mating and deaths. Users enter the starting values and then click on the macro to have it run. It should take about 30 seconds and then spit out a graph and some values. I've heard from Excel 2019 users that it runs for the 30 seconds but that the resulting graph is blank. – Oregonian Aug 12 '23 at 19:38
  • @RetiredGeek It seems like it SHOULD be that easy, doesn't it? But I haven't been able to locate any set of build numbers that will work cross-platform with both Excel 2021 and Office 365. If you know of such a set of build numbers, please share! – Oregonian Aug 12 '23 at 20:37
  • Why not just try to use it, and use error checking. If it returns a `438` error then you don't have that function. You can display a MsgBox and exit the macro. Obviously need to check for other errors also and react accordingly. – Ron Rosenfeld Aug 12 '23 at 23:52
  • @RonRosenfeld, I tried that and got my msgbox but when I clicked OK it exited with a syntax error - I used a fake "myLookup()" function since all my versions have xLookup. – RetiredGeek Aug 12 '23 at 23:57
  • @Oregonian, you can google "Update History for Microsoft 365" to get a complete list. I'd assume you can do the same for Excel 2021. Then Google "Announcing xLookup" to find it was added in March of 2019. Hope this gets you on your way. – RetiredGeek Aug 13 '23 at 00:00
  • Then there is something wrong with the code you used. I tried something similar here and had no issues. – Ron Rosenfeld Aug 13 '23 at 00:13
  • Check if the **whole** code does contain an `Application.DisplayAlerts = False` statement. This hide error messages. – Black cat Aug 13 '23 at 04:01
  • If anyone comes up with a way to solve this, I'd still be interested in seeing it. But after giving it some thought I decided that widespread usage was far more important to me than using the newest and latest features in Excel. So I went back and switched everything from XLOOKUP to VLOOKUP and now it works for everyone. – Oregonian Aug 14 '23 at 00:24

1 Answers1

0

If you want a very simple to implement, and completely unambiguous, "Yes/No" with no further research and no "(sigh...) that info was a-l-m-o-s-t right" results from said research, you can use WorksheetFunction to run an XLOOKUP that you know succeeds and see if the variable you place the output in has a correct result.

If yes, then they have XLOOKUP and are good to go. If not, then your next step is to warn them. Or rather, end the macro before it gets going and explain why to them.

(You can use something completely unreliant upon anything in the spreadsheet, for instance:

XLOOKUP(1, {3,2,5,7,1,6,8}, {1,2,3,4,5,6,7})

so you have no unexpected weirdness, and complete control over the inputs.)

However, what would they do if they cannot use the macros? The solution you chose in the end is very much the better solution since all your users have access to all the macros they, and you, rely upon to use.

Jeorje
  • 1