How to avoid using Select in Excel VBA

Question

I ve heard much about the understandable abhorrence of using  Select in Excel VBA  but I am unsure of how to avoid using it  I am finding that my code would be more re-usable if I were able to use variables instead of Select functions  However  I am not sure how to refer to things  like the ActiveCell  etc   if not using Select  I have found this article on ranges and this example on the benefits of not using select  but I can t find anything on how

User · Answer

and am finding that my code would be more re-usable if I were able to use variables instead of Select functions     While I cannot think of any more than an isolated handful of situations where  Select would be a better choice than direct cell referencing  I would rise to the defense of Selection and point out that it should not be thrown out for the same reasons that  Select should be avoided   There are times when having short  time-saving macro sub routines assigned to hot-key combinations available with the tap of a couple of keys saves a lot of time  Being able to select a group of cells to enact the operational code on works wonders when dealing with pocketed data that does not conform to a worksheet-wide data format  Much in the same way that you might select a group of cells and apply a format change  selecting a group of cells to run special macro code against can be a major time saver   Examples of Selection-based sub framework   Public Sub Run on Selected       Dim rng As Range  rSEL As Range     Set rSEL   Selection     store the current selection in case it changes     For Each rng In rSEL         Debug Print rng Address 0  0           cell-by-cell operational code here     Next rng     Set rSEL   Nothing End Sub  Public Sub Run on Selected Visible        this is better for selected ranges on filtered data or containing hidden rows columns     Dim rng As Range  rSEL As Range     Set rSEL   Selection     store the current selection in case it changes     For Each rng In rSEL SpecialCells xlCellTypeVisible          Debug Print rng Address 0  0           cell-by-cell operational code here     Next rng     Set rSEL   Nothing End Sub  Public Sub Run on Discontiguous Area        this is better for selected ranges of discontiguous areas     Dim ara As Range  rng As Range  rSEL As Range     Set rSEL   Selection     store the current selection in case it changes     For Each ara In rSEL Areas         Debug Print ara Address 0  0           cell group operational code here         For Each rng In ara Areas             Debug Print rng Address 0  0               cell-by-cell operational code here         Next rng     Next ara     Set rSEL   Nothing End Sub   The actual code to process could be anything from a single line to multiple modules  I have used this method to initiate long running routines on a ragged selection of cells containing the filenames of external workbooks   In short  don t discard Selection due to its close association with  Select and ActiveCell  As a worksheet property it has many other purposes    Yes  I know this question was about  Select  not Selection but I wanted to remove any misconceptions that novice VBA coders might infer

User · Answer

This is an example that will clear the contents of cell  quot A1 quot   or more if the selection type is xllastcell  etc    All done without having to select the cells  Application GoTo Reference  Workbook WorkbookName  Worksheets WorksheetName  Range  quot A1 quot   Range Selection selection selectiontype   clearcontents

User · Answer

These methods are rather stigmatized  so taking the lead of Vityata and Jeeped for the sake of drawing a line in the sand  Call  Activate   Select  Selection  ActiveSomething methods properties Basically because they re called primarily to handle user input through the application UI  Since they re the methods called when the user handles objects through the UI  they re the ones recorded by the macro-recorder  and that s why calling them is either brittle or redundant for most situations  you don t have to select an object so as to perform an action with Selection right afterwards  However  this definition settles situations on which they are called for  When to call  Activate   Select   Selection   ActiveSomething methods properties Basically when you expect the final user to play a role in the execution  If you are developing and expect the user to choose the object instances for your code to handle  then  Selection or  ActiveObject are apropriate  On the other hand   Select and  Activate are of use when you can infer the user s next action and you want your code to guide the user  possibly saving him her some time and mouse clicks  For example  if your code just created a brand new instance of a chart or updated one  the user might want to check it out  and you could call  Activate on it or its sheet to save the user the time searching for it  or if you know the user will need to update some range values  you can programmatically select that range

User · Answer

Two main reasons why  Select   Activate  Selection  Activecell  Activesheet  Activeworkbook  etc  should be avoided  It slows down your code  It is usually the main cause of runtime errors   How do we avoid it  1  Directly work with the relevant objects Consider this code Sheets  quot Sheet1 quot   Activate Range  quot A1 quot   Select Selection Value    quot Blah quot  Selection NumberFormat    quot   quot   This code can also be written as With Sheets  quot Sheet1 quot   Range  quot A1 quot        Value    quot Blah quot       NumberFormat    quot   quot  End With  2  If required declare your variables  The same code above can be written as Dim ws as worksheet  Set ws   Sheets  quot Sheet1 quot    With ws Range  quot A1 quot        Value    quot Blah quot       NumberFormat    quot   quot  End With

User · Answer

Avoiding Select and Activate is the move that makes you a bit better VBA developer  In general  Select and Activate are used when a macro is recorded  thus the Parent worksheet or range is always considered the active one   This is how you may avoid Select and Activate in the following cases     Adding a new Worksheet and copying a cell on it   From  code generated with macro recorder    Sub Makro2       Range  B2   Select     Sheets Add After  ActiveSheet     Sheets  Tabelle1   Select     Sheets  Tabelle1   Name    NewName      ActiveCell FormulaR1C1    12      Range  B2   Select     Selection Copy     Range  B3   Select     ActiveSheet Paste     Application CutCopyMode   False End Sub   To   Sub TestMe       Dim ws As Worksheet     Set ws   Worksheets Add     With ws          Name    NewName           Range  B2     12          Range  B2   Copy Destination   Range  B3       End With End Sub   When you want to copy range between worksheets   From   Sheets  Source   Select Columns  A D   Select Selection Copy Sheets  Target   Select Columns  A D   Select ActiveSheet Paste   To   Worksheets  Source   Columns  A D   Copy Destination  Worksheets  Target   Range  a1       Using fancy named ranges  You may access them with     which is really beautiful  compared to the other way  Check yourself   Dim Months As Range Dim MonthlySales As Range  Set Months   Range  Months       Set MonthlySales   Range  MonthlySales    Set Months   Months  Set MonthlySales    MonthlySales    The example from above would look like this   Worksheets  Source   Columns  A D   Copy Destination  Worksheets  Target    A1      Not copying values  but taking them  Usually  if you are willing to select  most probably you are copying something  If you are only interested in the values  this is a good option to avoid select   Range  B1 B6   Value   Range  A1 A6   Value    Try always to reference the Worksheet as well  This is probably the most common mistake in vba  Whenever you copy ranges  sometimes the worksheet is not referenced and thus VBA considers the wrong sheet the ActiveWorksheet    This will work only if the 2  Worksheet is selected  Public Sub TestMe       Dim rng As Range     Set rng   Worksheets 2  Range Cells 1  1   Cells 2  2   Copy End Sub   This works always  Public Sub TestMe2       Dim rng As Range     With Worksheets 2           Range  Cells 1  1    Cells 2  2   Copy     End With End Sub     Can I really never use  Select or  Activate for anything    A good example of when you could be justified in using  Activate and  Select is when you want make sure that a specific Worksheet is selected for visual reasons  E g   that your Excel would always open with the cover worksheet selected first  disregarding which which was the ActiveSheet when the file was closed    Thus  something like the code below is absolutely OK   Private Sub Workbook Open       Worksheets  Cover   Activate End Sub    Another good example is when you need to export all sheets into one PDF file  as mentioned in this case - How to avoid select active statements in VBA in this example  When a command only works with ActiveWindow like ActiveWindow Zoom or ActiveWindow FreezePanes

User · Answer

I m going to give the short answer since everyone else gave the long one   You ll get  select and  activate whenever you record macros and reuse them   When you  select a cell or sheet it just makes it active   From that point on whenever you use unqualified references like Range Value they just use the active cell and sheet   This can also be problematic if you don t watch where your code is placed or a user clicks on the workbook   So  you can eliminate these issues by directly referencing your cells   Which goes    create and set a range Dim Rng As Excel Range Set Rng   Workbooks  Book1   Worksheets  Sheet1   Range  A1    OR Set Rng   Workbooks 1  Worksheets 1  Cells 1  1    Or you could   Just deal with the cell directly rather than creating a range  I want to put the string  Hello  in Range A1 of sheet 1 Workbooks  Book1   Worksheets  Sheet1   Range  A1   value    Hello   OR Workbooks 1  Worksheets 1  Cells 1  1  value    Hello    There are various combinations of these methods  but that would be the general idea expressed as shortly as possible for impatient people like me

User · Answer

The main reason never to use Select or Activesheet is because most people will have at least another couple of workbooks open  sometimes dozens  when they run your macro  and if they click away from your sheet while your macro is running and click on some other book they have open  then the  quot Activesheet quot  changes  and the target workbook for an unqualified  quot Select quot  command changes as well  At best  your macro will crash  at worst you might end up writing values or changing cells in the wrong workbook with no way to  quot Undo quot  them  I have a simple golden rule that I follow  Add variables named  quot wb quot  and  quot ws quot  for a Workbook object and a Worksheet object and always use those to refer to my macro book  If I need to refer to more than one book  or more than one sheet  I add more variables  For example  Dim wb as Workbook Dim ws as Worksheet Set wb   ThisWorkBook Set ws   wb sheets  quot Output quot    The  quot Set wb   ThisWorkbook quot  command is absolutely key   quot ThisWorkbook quot  is a special value in Excel  and it means the workbook that your VBA code is currently running from  A very helpful shortcut to set your Workbook variable with  After you ve done that at the top of your Sub  using them could not be simpler  just use them wherever you would use  quot Selection quot   So to change the value of cell  quot A1 quot  in  quot Output quot  to  quot Hello quot   instead of  Sheets  quot Output quot   Activate ActiveSheet Range  quot A1 quot   Select Selection Value    quot Hello quot   We can now do this  ws Range  quot A1 quot   Value    quot Hello quot   Which is not only much more reliable and less likely to crash if the user is working with multiple spreadsheets  it s also much shorter  quicker and easier to write  As an added bonus  if you always name your variables  quot wb quot  and  quot ws quot   you can copy and paste code from one book to another and it will usually work with minimal changes needed  if any

User · Answer

One small point of emphasis I ll add to all the excellent answers given previously  Probably the biggest thing you can do to avoid using Select is to as much as possible  use named ranges  combined with meaningful variable names  in your VBA code  This point was mentioned above  but it was glossed over a bit  however  it deserves special attention  Here are a couple of additional reasons to make liberal use of named ranges  though I am sure I could think of more  Named ranges make your code easier to read and understand  Example  Dim Months As Range Dim MonthlySales As Range  Set Months   Range  quot Months quot     E g   quot Months quot  might be a named range referring to A1 A12  Set MonthlySales   Range  quot MonthlySales quot     E g   quot Monthly Sales quot  might be a named range referring to B1 B12  Dim Month As Range For Each Month in Months     Debug Print MonthlySales Month Row  Next Month  It is pretty obvious what the named ranges Months and MonthlySales contain  and what the procedure is doing  Why is this important  Partially because it is easier for other people to understand it  but even if you are the only person who will ever see or use your code  you should still use named ranges and good variable names because you will forget what you meant to do with it a year later  and you will waste 30 minutes just figuring out what your code is doing  Named ranges ensure that your macros do not break when  not if   the configuration of the spreadsheet changes  Consider  if the above example had been written like this  Dim rng1 As Range Dim rng2 As Range  Set rng1   Range  quot A1 A12 quot   Set rng2   Range  quot B1 B12 quot    Dim rng3 As Range For Each rng3 in rng1     Debug Print rng2 rng3 Row  Next rng3  This code will work just fine at first - that is until you or a future user decides  quot gee wiz  I think I m going to add a new column with the year in Column A  quot   or put an expenses column between the months and sales columns  or add a header to each column  Now  your code is broken  And because you used terrible variable names  it will take you a lot more time to figure out how to fix it than it should take  If you had used named ranges to begin with  the Months and Sales columns could be moved around all you like  and your code would continue working just fine

User · Answer

IMHO use of  select comes from people  who like me started learning VBA by necessity through recording macros and then modifying the code without realizing that  select and subsequent selection is just an unnecessary middle-men    select can be avoided  as many posted already  by directly working with the already existing objects  which allows various indirect referencing like calculating i and j in a complex way and then editing cell i j   etc   Otherwise  there is nothing implicitly wrong with  select itself and you can find uses for this easily  e g  I have a spreadsheet that I populate with date  activate macro that does some magic with it and exports it in an acceptable format on a separate sheet  which  however  requires some final manual  unpredictable  inputs into an adjacent cell  So here comes the moment for  select that saves me that additional mouse movement and click

User · Answer

Always state the workbook  worksheet and the cell range   For example   Thisworkbook Worksheets  fred   cells 1 1  Workbooks  bob   Worksheets  fred   cells 1 1    Because end users will always just click buttons and as soon as the focus moves off of the workbook the code wants to work with then things go completely wrong   And never use the index of a workbook   Workbooks 1  Worksheets  fred   cells 1 1    You don t know what other workbooks will be open when the user runs your code

User · Answer

To avoid using the  Select method  you can set a variable equal to the property that you want    For instance  if you want the value in Cell A1 you could set a variable equal to the value property of that cell   Example valOne   Range  quot A1 quot   Value    For instance  if you want the codename of  Sheet3you could set a variable equal to theCodename  property of that worksheet   Example valTwo   Sheets  quot Sheet3 quot   Codename

User · Answer

Please note that in the following I m comparing the Select approach  the one that the OP wants to avoid   with the Range approach  and this is the answer to the question   So don t stop reading when you see the first Select  It really depends on what you are trying to do  Anyway  a simple example could be useful  Let s suppose that you want to set the value of the active cell to  quot foo quot   Using ActiveCell you would write something like this  Sub Macro1       ActiveCell Value    quot foo quot  End Sub  If you want to use it for a cell that is not the active one  for instance for  quot B2 quot   you should select it first  like this  Sub Macro2       Range  quot B2 quot   Select     Macro1 End Sub  Using Ranges you can write a more generic macro that can be used to set the value of any cell you want to whatever you want  Sub SetValue cellAddress As String  aVal As Variant      Range cellAddress  Value   aVal End Sub  Then you can rewrite Macro2 as  Sub Macro2       SetCellValue  quot B2 quot    quot foo quot  End Sub  And Macro1 as  Sub Macro1       SetValue ActiveCell Address   quot foo quot  End Sub

User · Answer

How to avoid copy-paste  Let s face it  this one appears a lot when recording macros  Range  quot X1 quot   Select Selection Copy Range  quot Y9  Select Selection Paste  While the only thing the person wants is  Range  quot Y9 quot   Value   Range  quot X1 quot   Value  Therefore  instead of using copy-paste in VBA macros  I d advise the following simple approach  Destination Range Value   Source Range Value

User · Answer

Working with the  Parent feature  this example shows how setting only one myRng reference enables dynamic access to the entire environment without  any  Select   Activate   Activecell   ActiveWorkbook   ActiveSheet and so on   There isn t any generic  Child feature   Sub ShowParents       Dim myRng As Range     Set myRng   ActiveCell     Debug Print myRng Address                      An address of the selected cell     Debug Print myRng Parent name                  The name of sheet  where MyRng is in     Debug Print myRng Parent Parent name           The name of workbook  where MyRng is in     Debug Print myRng Parent Parent Parent name    The name of application  where MyRng is in        You may use this feature to set reference to these objects     Dim mySh  As Worksheet     Dim myWbk As Workbook     Dim myApp As Application      Set mySh   myRng Parent     Set myWbk   myRng Parent Parent     Set myApp   myRng Parent Parent Parent     Debug Print mySh name  mySh Cells 10  1  Value     Debug Print myWbk name  myWbk Sheets Count     Debug Print myApp name  myApp Workbooks Count        You may use dynamically addressing     With myRng          Copy            Pastes in D1 on sheet 2 in the same workbook  where the copied cell is          Parent Parent Sheets 2  Range  quot D1 quot   PasteSpecial xlValues            Or myWbk Sheets 2  Range  quot D1 quot   PasteSpecial xlValues            We may dynamically call active application too          Parent Parent Parent CutCopyMode   False            Or myApp CutCopyMode   False     End With End Sub

User · Answer

Some examples of how to avoid select Use Dim d variables Dim rng as Range  Set the variable to the required range  There are many ways to refer to a single-cell range  Set rng   Range  quot A1 quot   Set rng   Cells 1  1  Set rng   Range  quot NamedRange quot    Or a multi-cell range  Set rng   Range  quot A1 B10 quot   Set rng   Range  quot A1 quot    quot B10 quot   Set rng   Range Cells 1  1   Cells 10  2   Set rng   Range  quot AnotherNamedRange quot   Set rng   Range  quot A1 quot   Resize 10  2   You can use the shortcut to the Evaluate method  but this is less efficient and should generally be avoided in production code  Set rng    A1  Set rng    A1 B10   All the above examples refer to cells on the active sheet  Unless you specifically want to work only with the active sheet  it is better to Dim a Worksheet variable too  Dim ws As Worksheet Set ws   Worksheets  quot Sheet1 quot   Set rng   ws Cells 1  1  With ws     Set rng    Range  Cells 1  1    Cells 2  10   End With  If you do want to work with the ActiveSheet  for clarity it s best to be explicit  But take care  as some Worksheet methods change the active sheet  Set rng   ActiveSheet Range  quot A1 quot    Again  this refers to the active workbook   Unless you specifically want to work only with the ActiveWorkbook or ThisWorkbook  it is better to Dim a Workbook variable too  Dim wb As Workbook Set wb   Application Workbooks  quot Book1 quot   Set rng   wb Worksheets  quot Sheet1 quot   Range  quot A1 quot    If you do want to work with the ActiveWorkbook  for clarity it s best to be explicit  But take care  as many WorkBook methods change the active book  Set rng   ActiveWorkbook Worksheets  quot Sheet1 quot   Range  quot A1 quot    You can also use the ThisWorkbook object to refer to the book containing the running code  Set rng   ThisWorkbook Worksheets  quot Sheet1 quot   Range  quot A1 quot    A common  bad  piece of code is to open a book  get some data then close again This is bad  Sub foo       Dim v as Variant     Workbooks  quot Book1 xlsx quot   Sheets 1  Range  quot A1 quot   Clear     Workbooks Open  quot C  Path To SomeClosedBook xlsx quot       v   ActiveWorkbook Sheets 1  Range  quot A1 quot   Value     Workbooks  quot SomeAlreadyOpenBook xlsx quot   Activate     ActiveWorkbook Sheets  quot SomeSheet quot   Range  quot A1 quot   Value   v     Workbooks 2  Activate     ActiveWorkbook Close   End Sub  And it would be better like  Sub foo       Dim v as Variant     Dim wb1 as Workbook     Dim  wb2 as Workbook     Set wb1   Workbooks  quot SomeAlreadyOpenBook xlsx quot       Set wb2   Workbooks Open  quot C  Path To SomeClosedBook xlsx quot       v   wb2 Sheets  quot SomeSheet quot   Range  quot A1 quot   Value     wb1 Sheets  quot SomeOtherSheet quot   Range  quot A1 quot   Value   v     wb2 Close   End Sub  Pass ranges to your Subs and Functions as Range variables  Sub ClearRange r as Range      r ClearContents           End Sub  Sub MyMacro       Dim rng as Range     Set rng   ThisWorkbook Worksheets  quot SomeSheet quot   Range  quot A1 B10 quot       ClearRange rng End Sub  You should also apply Methods  such as Find and Copy  to variables  Dim rng1 As Range Dim rng2 As Range Set rng1   ThisWorkbook Worksheets  quot SomeSheet quot   Range  quot A1 A10 quot   Set rng2   ThisWorkbook Worksheets  quot SomeSheet quot   Range  quot B1 B10 quot   rng1 Copy rng2  If you are looping over a range of cells it is often better  faster  to copy the range values to a variant array first and loop over that  Dim dat As Variant Dim rng As Range Dim i As Long  Set rng   ThisWorkbook Worksheets  quot SomeSheet quot   Range  quot A1 A10000 quot   dat   rng Value    dat is now array  1 to 10000  1 to 1  for i   LBound dat  1  to UBound dat  1      dat i 1    dat i  1    10   Or whatever operation you need to perform next rng Value   dat   put new values back on sheet  This is a small taster for what s possible

User · Answer

I noticed that none of these answers mention the  Offset Property  This also can be used to avoid using the Select action when manipulating certain cells  particularly in reference to a selected cell  as the OP mentions with ActiveCell    Here are a couple examples   I will also assume the  ActiveCell  is J4   ActiveCell Offset 2  0  Value   12   This will change the cell J6 to be a value of 12  A minus -2 would have referenced J2   ActiveCell Offset 0 1  Copy ActiveCell Offset  2    This will copy the cell in k4 to L4  Note that  0  is not needed in the offset parameter if not needed   2  Similar to the previous example a minus 1 would be i4   ActiveCell Offset   -1  EntireColumn ClearContents   This will clear values in all cells in the column k    These aren t to say they are  better  than the above options  but just listing alternatives

[excel] How to avoid using Select in Excel VBA

Examples related to excel

Examples related to vba