AutoSaveOn issue with Excel 2013 and 2016

[ThomasFoster]

Sorry if this is in the wrong location, this is the first time I am posting on this forum.

My issue is a regarding a "Compile Error: Method or data member not found" that happens when an excel file is opened by some users. These users have either excel 2013 or 2016 installed.

The compile error is related to the "ThisWorkbook.AutoSaveOn" command in the below code that runs in the WorkBook_Open() Sub.

If Val(Application.Version) > 15 Then        If ThisWorkbook.AutoSaveOn Then ThisWorkbook.AutoSaveOn = False
    End If

I have googled this issue and found references to this not working well with Excel 2013 but nothing regarding issues with Excel 2016.
It is also not even going to my error handler function it is failing right away.

I am at a bit of a loss as to how to fix this in such a way that lets the file work on Excel 2013/2016 and yet still disables the autosave when running in later versions where the code works no problem.

Is this something that can be fixed or do I need to rip out this section for it to work on older versions.

Thanks for your help.

 

[vincehardwick]

I ran into this issue recently - the problem is that you are directly accessing the ThisWorkbook module. Older versions of Excel don't like it much for some reason.

Dim and Set a Workbook variable to ThisWorkbook, then use that in your IF block to access the AutoSaveOn property:

Dim wbThis As Workbook
Set wbThis = ThisWorkbook

If Val(Application.Version) > 15 Then
    If wbThis.AutoSaveOn Then wbThis.AutoSaveOn = False
End If

Hope that does the trick.

 

[RoryA]

In 2013 and 2016 there is no AutoSaveOn property for a Workbook, so the error will be a compile error, not a runtime one.

 

[vincehardwick]

In 2013 and 2016 there is no AutoSaveOn property for a Workbook, so the error will be a compile error, not a runtime one.

You're right, but by setting the a Workbook variable wbThis to ThisWorkbook, it prevents the compile error that you would get by directly trying to access a non-existent property with ThisWorkbook.AutoSaveOn. My code compiles in Excel 2010, then I handle the runtime error with an OnError statement.

 

[RoryA]

then I handle the runtime error with an OnError statement.

Ah, OK. That will work, but the Val(Application.Version) test above won't, since 2016+ is version 16.

 

[vincehardwick]

Hmmm, are you sure? I just repeated the OP's code for the version check, but my equivalent code is checking for

Val(Application.Version) > 14

and seems to work well. I thought the AutoSaveOn property of the Workbook object was introduced in Excel 2013 (Application.Version = 15), could be wrong though.

 

[RoryA]

Nope. I tested in 2016 and the property doesn't exist, so you get a 438 error.

 

[vincehardwick]

Nope. I tested in 2016 and the property doesn't exist, so you get a 438 error.

OK, worth knowing. I've looked around online a few times previously to try to find out when this property came into existence, couldn't find anything definitive. Good job I'm using OnError in conjunction with the version check and setting a Workbook object to avoid the compile error. Should probably increase by Application.Version test number to > 15 though by the sounds of it.

 

[RoryA]

If you've got an On Error handler for it, you don't really need the other test (which is flawed anyway).

 

[vincehardwick]

If you've got an On Error handler for it, you don't really need the other test (which is flawed anyway).

Yeah, the more I read about this, the less relevant Application.Version becomes after Excel 2016.

This page suggests an alternative method of programmatically determining the version of Excel you're running, but by the sounds of the comment section underneath the article, it's not watertight...

Check the application version in Modern Office - The Excelguru Blog