VBA : ActiveCell vs. Selection

Magpie Bavarde
Magpie Bavarde used Ask the Experts™
on
Hello the experts,

byundt has been kind enough to help me improve several VBA codes

He suggests each time to use "ActiveCell" rather than "Selection" but also tells that if I got empty cells, then "ActiveCell" won't work and it's better to use "Selection"

As I often have empty cells in my files I'm tempted to always choose the "Selection" option

I would just like to know why "ActiveCell" is better and what are the risks with "Selection" ?

Thank you very much for your help,

Mélanie
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I don't know exactly what byundt said and I don't know what you want to do with the active (blank) cell, but you can still refer to it's address, value or any other property.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
The thing you should avoid if at all possible is selecting things in your code, because the user may change what is selected while the macro is running which may lead to unexpected results, and also "selecting" takes time. That time may be very small but it can add up.
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
Many different kinds of objects can be selected: worksheets, cells, ranges of cells, shapes, even characters within the text of a cell. The four macros you posted all assumed that the selection was a cell or range of cells. If that's what you had selected before running the macro, then the macro will work great. But if you had selected one of the other kinds of object, your macro would have failed with a runtime error.

If I am writing code for one person only to use, the possibility of having the wrong kind of object selected when the macro runs is remote. But when you start letting other people use the macros, they may inadvertently screw it up by breaking the assumption of pre-selecting some cells before running the macro.

ActiveCell avoids that potential problem. Even if you select a different kind of object, there are always an ActiveWorkbook, ActiveSheet and ActiveCell.

The question is really would you prefer the convenience of selecting a range of cells that might include blanks, or would you rather have a macro that won't blow up when somebody tries to use it? Given the way you describe using the macros, you are better off pre-selecting a range of cells and using the Selection object. But that fact wasn't clear to me when I first started trying to suggest solutions to your questions.
Magpie BavardeExecutive Assistant

Author

Commented:
That's crystal clear. Thank you so much !!
Magpie BavardeExecutive Assistant

Author

Commented:
Thank you very much !
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
C'est rien. Mon plaisir.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial