Excel4Macro migration

[iliace]

One of the stated goals of Excel 2010 VBA is the new functions designed to replace/allow migration from XLM to VBA. "
Excel has a macro facility, known as Excel 4 macros (XLM for short), that was the primary macro language before the introduction of VBA in Excel 5.0. Most people have long ago migrated their Excel 4 macros to VBA. However, some Excel 4 macro capabilities were missing from VBA, which made this migration difficult.
In Excel 2010, one of our goals was to remove any remaining barriers that people had to complete the migration of Excel 4 macros to VBA. Excel 2010 does still enable the creation, editing and execution of Excel 4 macros. You can use Excel 2010 to migrate your macros." (Changes in Excel 2010 (for ITPros))

So I was recording a macro the other day - a non-trivial PivotTable manipulation - and Excel crashed. When I reopened it, the following code was recorded:

Sub Macro1()
'
' Macro1 Macro
'


'
    With ActiveChart.PivotLayout.PivotTable.PivotFields("Level")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveChart.PivotLayout.PivotTable.PivotFields("Date").Orientation = xlHidden
    ActiveChart.PivotLayout.PivotTable.PivotFields("Average of Date").Orientation _
        = xlHidden
    ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
        PivotTable.PivotFields("Date"), "Count of Date", xlCount
    ExecuteExcel4Macro _
        "PIVOT.FIELD.PROPERTIES(""PivotTable1"",""Count of Date"",""Average of Date"",,8,Array(2," _
        Level","(previous)",))"
End Sub

I guess they want it phased out, but the macro recorder is still using it.

 

[strive4peace]

well we can't complain too much about the macro recorder too much or we might lose it ... always good to use the recorder for a general outline but then change everything it writes

 

[iliace]

well we can't complain too much about the macro recorder too much or we might lose it ... always good to use the recorder for a general outline but then change everything it writes

Not necessarily complaining, I just found comical the incongruence between MS's stated goal and actual behavior. That's exactly what I was trying to do - without going to a reference book, get the code from the macro recorder so I can change it to fit my scenario. In this case, I wanted to see how the macro recorder would handle a "difference from previous" pivot calculation. Excel4Macro code was not at all what I expected

 

[strive4peace]

the code writer in Access still uses language from the 90s! (some of which is depreciated in 2013) ... back then, the development teams were much bigger. Well Excel didn't have pivot charts then but I can almost guarantee that whatever code was used when pivot tables came out has not changed in the macro recorder -- and you are proof

btw, in Access there is a distinction between macros and VBA -- they are not the same