If err.number... gives expected function or variable in Excel 2016

I'm trying to handle some errors using err.Number such as in:

Please Login or Register  to view this content.

This works in excel 2010 but in 2016, I get a compile error with "expected function or variable", so the code won't execute at all.

I hope it's just something that needs enabling in 2016 somewhere, otherwise when we move onto 2016 as a company, a large amount of automation will fail.

Is anyone using 2016 and able to use this function?

There's nothing wrong with that code - 2016 will not have changed that much (and hasn't, tested)

Go to the Tools/References dialog. See if anything is marked as 'MISSING'

I've compared the references between 2010 (works) & 2016 (fails) and the same five are all checked. None are marked as missing.

The only difference is that instead of 'Microsoft Excel 14.0 Object library' (2010) it is 'Microsoft Excel 16.0 Object library' in 2016 (and the same for the office object library). 14.0 is not in the list to select.

This is worrying as although we won't be likely to upgrade until support for Win7 expires (so a few years of life yet) when that day comes, most of the report automation and resource planning tools will just stop working. Not good when there's over 80k employees in the company.

How can I handle specific errors now if I can't use an error handler?

I have seen references to early/late binding to overcome this but can't say I'm clear on what I have to do or if it suits these circumstances.

The (changed) references are correct. 2010 is 14 object library, 2016 is the 16 object library.

The code works - that syntax hasn't changed since the introduction of VBA so the issue is something else in your code/worksheet/environment. Just to make sure:

Please Login or Register  to view this content.

Pops a MsgBox saying '13 Type Mismatch'


Early or Late binding has nothing to do with this. You are not binding to anything in this case.

Well would you Adam and Eve it!

Three hours of frustration at the compile error and it turns out to be 'environmental'!

I ran your code in the current workbook - same issue.
I ran it in a new workbook, worked fine.
I then reopened the original workbook and then the code worked without the compile error.

In a boolean environment, how can true or false become neither?

Thanks alot for this - I'd have been faffing about in the same workbook all afternoon without your help - it triggered me to reopen it and now all is well again in the universe of ones and zeros.

Update and resolution:

I had stupidly named on of my test modules as sub err() and this was where the problem lay.

I renamed it and all worked fine in the latest version of the workbook.

Epic coding fail - schoolboy error of the highest order!