Excel’s Select and Activate considered harmful

[davies]

This rant is partly OT, but it applies slightly more strongly to Perl than to VBA. This is because VBA programmers have typically been told about the problems early on. Perl programmers are told to use the macro recorder and convert the resulting code to Perl. Unfortunately, though this advice is generally good, it hides the fact that the macro recorder is, to put it kindly, overly inclusive. The overlap between Perl users and VBA users is very small, and simply transliterating bad VBA to Perl produces bad Perl. I find myself repeating in numerous nodes advice not to Select or Activate, and my intention in writing this is to have a single point of reference to which I can point people.

The Selection object in Excel’s VBA is very useful, but it is not something that should be changed, except in a very few cases. The reason is that it does nothing useful and can, in some situations, enable harmful things to be done. Its usefulness is in determining where the user was before invoking a macro. For example, I have a spreadsheet that does the donkey work for Sudokus. Knowing which cell the user was on can be critical. The Selection object tells you this, but if the code changes it without it being stored, valuable data are lost. Worse, the user may reasonably type ahead after invoking the code. If the selection is changed, the user’s entered data will end up in the wrong place, with potentially disastrous consequences.

Further problems arise when any form of single stepping, breakpoints or pausing of code occurs. Usually this is done for debugging, but in these situations, the user may, by accident or design, change the selected cell (or active sheet, workbook or whatever). This can make debugging almost impossible if the Selection or ActiveWhatever object is used.

It is generally good practice to disable screen updating when code is running. This will have the effect of preventing accidental clicks from changing the Selection object (I am told that this is prevented in some other environments, but I have the scars), but the tradeoff is another potential danger. If the end product is a spreadsheet where the cell pointer (i.e. the selected cell) is off screen, this will look strange when the screen updates. It has resulted in calls to support staff complaining that “my cell pointer has vanished”. These, by the way, are the better class of users. More frequently, it will be “my cursor” that is the reported problem.

When, then, should these system variables be changed? The commonest situation is to change what the user sees. One of the things I have in my Personal file (a file loaded automatically, containing all purpose macros) is a macro to put the cell pointer on the same cell in every sheet. For this, I need to Activate each sheet in turn and Select the same cells in each sheet. This must be done with display updating enabled. Update: in Re^3: Conditional Formating with OLE Excel, I show another situation where it is necessary to Select. /Update

At the start of the previous paragraph, I referred to Selection and ActiveWhatever as “system variables”. They are, of course, objects rather than variables, but it isn’t a bad analogy. They are used by the system. The reason they appear in recorded macros is that the macro recorder never creates variables or objects of its own. This has to be done by the user when editing the recorded macros into a production form. I would therefore urge users to consider changing these “system variables” only if they would change a system variable in Perl. Using the current value of $_ is common, if sometimes frowned upon. Changing it is very rarely correct. Think about Selection and ActiveWhatever in the same way. Remember that Select and Activate change them. By changing them, you are effectively overloading a system variable. It’s almost always better to declare your own variable (or object) and manipulate it as you want. It’s faster, too, as no attempt will be made to change the screen appearance. Finally, it’s less obfuscated.

Regards,

John Davies

[moritz]

So when I want to automate something, and the macro recorder uses Select, how do I rewrite it not to use Select?

It's good to warn against harmful practise, but if you offer no alternatives, your effort will most likely be lost.

[davies]

Create your own object and assign to that. To give an example, instead of

$sht->Range("A1")->Select;

use

my $rng = $sht->Range("A1");

Then, rather than Selection->, use $rng->. Obviously, it's better to use a descriptive name rather than $rng.

Regards,

John Davies

[dasgar]

Usually in the code that I've written, I'm interested in reading from or writing to a cell and I don't need or want to keep a reference around for that cell. In my case, I just skip the selection step altogether with code like:

my $value = $sht->Range("A1")->{Value};

or

$sht->Range("A1")->{Value} = $value;

Just tossing out a suggestion (hopefully a correct and useful one) about how to avoid using Select.