Fix errors and incompleteness in macro recording
The macro recorder is a highly valued feature of Excel and is used by novices to generate code to automate repetitive operations and by more expert users as a quick way to confirm the appropriate properties and methods to use to achieve a desired effect.
There are some operations that the macro recorder does not record at all and others which it records incorrectly. This suggestion is that all such errors and omissions should be fixed when they are identified.
The latest to come to my attention was assigning a formula to a shape, to link its text to a cell.
The recorded code was:
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 1")).Formula = "=Sheet1!$B$1"
which fails on being replayed because a ShapeRange has no Formula property.
Correct options include:
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 1")).Select
Selection.Formula = "=Sheet1!$B$1"
ActiveSheet.DrawingObjects(“Rounded Rectangle 1”).Formula = “=Sheet1!$B$1”
ActiveSheet.Shapes("Rounded Rectangle 1").OLEFormat.Object.Formula = "=Sheet1!$B$1"
Ken P commented
In terms of completeness of macro recording, I would add correctly recording conditional formatting. Specifically, if I apply a conditional number format to a cell (for example, if A1 = "a" then the format for B1 is '#,##0.00_);(#,##0.00);"-"??_);@_)') it works correctly, but when recording the steps the format is applied unconditionally, and there is a line in the macro, ExecuteExcel4Macro "(2,1,""#,##0.00_);(#,##0.00);""-""??_);@_)"")", that fails. This makes no sense to me and should be considered an unacceptable result. Anything that is recorded should be able to be executed, given an identical starting workbook.