1

This question is not for any specific macro code but generalized.

I have Excel 2016 and writing many macros in that. the macros are working fine if we run it in Excel 2016, but I got messages from others that the same macros are throwing error since they are using Excel 2010. Since I don't have 2010, I can not debug and find the error line.

Is there any compatibility chart or method available where I can check if code written by me in Excel 2016 will work in old Excel versions or not?

Thanks,

I suspect the following code line:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange, Version:=6).CreatePivotTable TableDestination:="PivotTable!R1C1", TableName:="PivTab1", DefaultVersion:=6

I also changed the same line to following:

Set PSheet = Worksheets("PivotTable")
Set PCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, Sheets("Temp_Data_Sheet").UsedRange)
Set PTable = PCache.CreatePivotTable(PSheet.Range("A1"), TableName:="PivTab1")
Community
  • 1
  • 1
J.B.
  • 445
  • 1
  • 4
  • 8
  • Are there specific things that could affect compatibility? **Yes**. Is your question answerable in its current form? **No**. –  Feb 16 '18 at 09:22
  • It probably will. VBA is the same. The object model uses versioning so unless your objects contain *numbers* indicating a newer version at the end you won't have issues. Apart from that, just check the documentation of any classes/interfaces you suspect. It lists the versions where each class/interface is supported. – Panagiotis Kanavos Feb 16 '18 at 09:23
  • You might be able to save it as a previous version this way: https://support.office.com/en-us/article/save-an-excel-workbook-for-compatibility-with-earlier-versions-of-excel-169a0336-965b-4430-8554-4e7b5db79947 – Bentaye Feb 16 '18 at 09:23
  • If you want more help, be specific. Post examples or at least the class names you suspect aren't compatible. Of course, if you have such a list, why not *check* first. Finally, if you have an MSDN subscription you can download an older Excel version to check – Panagiotis Kanavos Feb 16 '18 at 09:24
  • You mentioned errors - what do the errors say? That's more helpful than any compatibility list – Panagiotis Kanavos Feb 16 '18 at 09:25
  • @PanagiotisKanavos New classes and new methods to existing classes are routinely added with each Excel release, so while VBA as a language is the same, the object model is not, and it is certainly likely to cause issues if run in a previous Excel. I am not sure how versioning would help with that. – GSerg Feb 16 '18 at 09:27
  • @GSerg modified *interfaces* have number suffixes so are easily discovered. New classes and methods typically refer to new features – Panagiotis Kanavos Feb 16 '18 at 09:29
  • @PanagiotisKanavos I am not aware of Excel interface suffixes visible to VBA developers. If you open the object browser and display the hidden members, there will be a lot of interfaces, but no suffixes. It is certainly transparent from the point of view of the written VBA code, so when you unknowingly rely on a class or even a method that does not exist in a previous Excel, you will not notice anything. – GSerg Feb 16 '18 at 09:35
  • @J.B. There is no way to support Excel 2010 without being able to actually test your program on it. The problem is, even if you find the compatibility chart you are looking for, there will still be changes in behaviour between Excel versions for which you will have to include specific workarounds (`If Application.Version = "..." Then ...`). [Here's one example](https://stackoverflow.com/q/14939292/11683) of a hidden change with release of Excel 2013. Excel 2016 also had a hidden change in how `QueryTable`s work that would permanently corrupt some of your formulas if you were not prepared. – GSerg Feb 16 '18 at 09:52
  • Thanks a lot for guidance shared here by all of you. As suggested by @GSerg I feel it is better to debug the code on computer having Excel 2010 . Thanks, I will be taking remote access of the machine where the error occurred for the root cause analysis. – J.B. Feb 16 '18 at 10:03

0 Answers0