VBA OFFICE 365 verses other versions

[MPW]

I am running office 365.
I recorded a macro with a simple sort command.
It ran fine on my machine.
Crashed on machines using Office 10

Comparing the code I found only one difference.
Add2 vs Add

Have they changed VBA in the new version?

 

[Norie]

Where are you seeing Add2 vs Add?

 

[MPW]

This is from my Office 365

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.[B]Add2[/B] Key:=Range("I4:I14") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("I4:I14")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

and this is from one with Office 10 (I believe)

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.[B]Add[/B] Key:=Range("I5:I14") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("I5:I14")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Notice the add key in the 1st line of each.

 

[rlv01]

Add2 is a new SortFields method added to Excel 2016. Here's a discussion:

https://www.mrexcel.com/forum/excel...d2-add-sortfields-add2-vs-sortfields-add.html

 

[Norie]

MPW

Change Add2 to Add.

I recently wrote, not recorded, some code a minor part of which did some sorting.

In my original version Add was used and that worked perfectly fine.

Started to receive complaints about it not working for some people.

Checked it out, somehow Add had been changed to Add2 in the version they were using.

Changed it back to Add and now it works for everyone, again.

 

[MPW]

OK and thanks for the fast response, by the way.

I can easily do a search for the Add2 and replace it with Add. The bigger question would be, Are there other pitfalls like this? Is there a good list of such changes?

My issue is that I have a workbook that has over 27,000 lines of code (yeah I know, crazy right?) This workbook has been used by 100s of people since about 2002. To compound this, it is used in every version of Office from 2003 on up to 2016. This has worked because VBA has not changed that much over the years.

About 6 months ago I changed over to Office 365. I have been developing in it ever since. This is the 1st problem I found, but I might have built in some flaws that might come out of this change over. I would like a good list so that I can check the rest of my code.

Ideas?

 

[Norie]

Quite a few things in Excel VBA have changed over the years, especially things to do with charts and sorting.

The best advice I could give is to develop for the earliest version that might be used, hopefully later versions will be backward compatible.

As for a list of this sort of thing, I've never seen/found anything like that.

 

[MPW]

Actually, I was developing in 2010, keeping the xls instead of going to xlsm. And then just testing it on the older version. Worked quite well until this last hiccup.

As far as the list of changes, I am sure that some brilliant OCD sleep deprived individual has done it, but it just has not gone viral. Perhaps someday . . . when dogs don't bite and politicians don't lie.

Thank you for what you do. You have helped me more than a few times in the past.

 

[Norie]

No problem.

I'm afraid I've given up on finding any sort of reliable source of these kind of lists.

Still trying to find a comprehensive guide for 64 bit Windows API declarations.

PS Not that I use Windows API often, but it would be handy.

 

[MPW]

Trying to keep clear of the whole 64 bit Window and Office. There is a bit of API stuff in the workbook I referred to earlier. One is to get to a 2nd userform to appear on a remote screen and every control is populated and sized when initialized. I would not want to try to take any of it to 64 bit.