Resources for 2010>2013/16 VBA changes?

[dw_73]

Hi, Can anyone point me to some good resources which show the changes implemented in VBA for Excel 2013/2016 from 2010 please? I have macros which I've built in 2016 and need to convert to use with 2010, including pivot tables and slicers, but I'm struggling to find anything which helps me do this.

They work fine in 2013 so I assume it's something which was implemented in 2013.

Thanks!

 

[RoryA]

Are you getting errors currently in 2010?

 

[dw_73]

Thanks for your response Rory.

Yes, for some of the macros it's generating "Can't find project or library". There are some which work without errors too though.

I'm wondering if it's my use of the "Sheets" collection which, I think, was introduced in 2013 but I've yet to check this properly.

This one doesn't currently work either (although this does not use "Sheets"):

Sub Shape_Filter_Ageing()


Dim Sh As Shape
Dim button_text As String


Set Sh = ActiveSheet.Shapes(Application.Caller)
button_text = Trim(Sh.TextFrame.Characters.Text)
If button_text <> "(All)" Then button_text = "'" & button_text
    'needs leading apostrophe to prevent reading range (e.g. "8-14") as a date
If Range("Ageing_Cat_To_View_Slicer") <> button_text Then
    Range("Ageing_Cat_To_View_Slicer") = button_text
End If


End Sub

 

[dw_73]

Sorry about the code formatting - it's taken out the tabbing when I pasted it here.

Having thought about it a bit more I wonder if this is only failing because it triggers a separate macro (via detection of a pivot table change) which does contain the "Sheets" collection.

 

[RoryA]

The Sheets collection is definitely not new!

A 'Can't find project' error typically means that you have set a reference to another object library that is not available (eg is a later version) on the other machine. For example, if you had a reference to Outlook set, it would not be backwards compatible. What references are set in your workbook project?

 

[dw_73]

Thanks, I'd picked up the "Sheets" reference somewhere else on the web so thanks for putting me straight on that!

The references in use are:
Visual Basic For Applications
Microsoft Excel 16.0 Object Library (the 2010 machine is using 14.0)
OLE Automation
Microsoft Office 16.0 Object Library (the 2010 machine is using 14.0)
Microsoft Forms 2.0 Object Library
MSExchange 1.0 Type Library (this is not on the 2010 machine)

My 2010 user noticed that the MSExchange library reference said "missing reference" so I'm thinking this is the problem.

I don't know why it would need the MSExchange library as I'm not doing anything with Outlook. I'll try disabling this library in my version, testing everything, saving it down and asking my 2010 user to try it again. Do you know what sort of things I might have done in Excel to make reference to it?

 

[RoryA]

It could have been set by an add-in. Removing the reference and saving should fix things as the other references should adjust for versions automatically.

 

[dw_73]

Thanks Rory - I really appreciate the help. I'll get it tested asap!