Thursday, February 23, 2017

Some Warnings about Using Macros in Excel

  Macros are useful utilities in Excel but they present some additional difficulties which one should be on the watch for. I used a macro to record the steps involved in reducing the displayed precision of a complex number. The selected cell was predetermined in the macro and activated by using the Ctrl-r shortcut key. Here's a screen capture of part of the page before activating the macro. Notice the Undo button is available for use and a formula is present in the formula bar.

After the macro is activated there are changes on the worksheet. The Undo button is no longer available and the formula has been altered.

 It's best to save a worksheet before using a macro.

  The recorded macro only acted on the original cell and did not depend on which cell was selected when the macro was activated so I rewrote it in VBA to make it more useful by allowing changes in the desired number of decimal places rounded to and an arbitrary selection of the cell to be acted on. Macros like the one above might be useful for preparing online versions of a worksheet which pass just the results of a calculation and not the details about the procedures used to arrive at the results if it is desired that they remain privileged.

  When rewriting macros one should take care to make sure that certain assumptions about the cell(s) to be acted on by the macro are fulfilled and that the action is aborted if these assumptions are not met. That's a given in object-oriented programming.

No comments: