Power Excel vba secret, avoid using select

By on May 9, 2011

I see it all the time, code that selects one thing, then another, then selects something else in order to navigate and write data in an Excel spreadsheet.

Instead understand that the Microsoft Excel object model and your vba code will be more professional, robust and mantainable if you don’t select anything but instead interact with the Excel objects directly.

Basically you will be able to do more cool stuff because you are now programming the application rather than just emulating user keystrokes.

Example 1

01  Sub NotGood()
02    Dim i As Integer
03    ActiveWorkbook.Worksheets(2).Select
04    Range("A5").Select
05    Selection.Value = "Enter Numbers"
06    For i = 1 To 15
07       ActiveCell.Cells(2).Select
08       Selection.Value = i
09    Next
10  End Sub 

Example 2

01  Sub Better()
02    Dim wbk As Workbook
03    Dim rngCell As Range, rngNumbers As Range
04    Dim i As Integer
06    ' Set up two references
07    Set wbk = ActiveWorkbook
08    Set rngCell = wbk.Worksheets(2).Range("E5")
10    rngCell.Value = "Enter Numbers"
12    ' Populate 1 to 15
13    For i = 1 To 15
14      rngCell.Offset(i).Value = i
15    Next
16    ' Done
18    '=========================================================
19    ' Following is for extra credit !  :-)
20    '=========================================================
22    ' Get reference to numbers range
23    Set rngNumbers = rngCell.CurrentRegion
24    Set rngNumbers = rngNumbers.Resize(rngNumbers.Rows.Count - 1).Offset(1)
26    MsgBox "Numbers entered click OK to try a different way", vbExclamation
27    rngNumbers.Clear
29    MsgBox "Range Cleared, now lets populate it a different way", vbExclamation
30    ' Enter numbers without needing to loop  
31    rngNumbers.Resize(1, 1).Value = 1
32    rngNumbers.Resize(15).DataSeries Step:=1
35    ' Now put those numbers somewhere else
36    rngNumbers.Offset(, 4).Value = rngNumbers.Value
37  End Sub

Which of the above examples are best? Well it depends…

Example 1 is the worst as it is just a “macro” (copying keystrokes) and thus has the least options for building a strong application with options for future extendibility.

We need to choose then between Example 2 and Example 3.

Example 2 does not use any variables, this is good if you need code to run really really fast (unless it is in a huge loop you ain’t gonna notice the difference.) but not so good from a readability point of view (especially when you get into hundreds of lines of code.)

Example 3 uses variables “Dim wbk As Workbook” etc so instead of writing..

wbk.Worksheets(2).Range("E5").value="Enter Numbers"

You can instead just use

10    rngCell.Value = "Enter Numbers"

(very useful if you are writing to that cell in different parts of the code) this is also faster for the processor as it does not have to navigate across each dot.

Example 3 gets my vote, because as your program does more things (and therefore get more complex) this coding style will be the easiest to maintain and help you to continue thinking like a human rather than a machine spewing 001001001110101101111001 😉

This book is a good reference for these topics

About Sean Johnson


  1. Kevin

    October 8, 2012 at 1:49 am

    good post. Answered my question. I still am not that good at excel for using variables all the time. But I will endeavour to get there!!! :) tks

    • Sean Johnson

      October 8, 2012 at 8:29 am

      You are very welcome kevin.

      If you have any questions feel free to ask, gives me ideas for content 😉

  2. Eric Light

    May 9, 2013 at 9:36 pm

    Hi Sean,

    Well said – I was searching for an example of why VBA dev’s should avoid using the .Select statement (to save me from having to type up something myself), and your post came up.

    Couldn’t have said it better myself. Thanks!

    • Sean Johnson

      May 13, 2013 at 2:42 pm

      Hi Eric

      Thanks for your kind words.
      I keep meaning to build this blog out more.

      Oh well good intentions…


  3. Pingback: Question with selected ranges VBA

  4. Pingback: Excel macro - Avoiding using Select - Excel Solutions - Developers Q & A

  5. Pingback: Excel macro – Avoiding using Select | Ask Programming & Technology

  6. Ashish Rathi

    February 24, 2014 at 4:58 am

    Hello Sean,

    How do you avoid using select in an operation like copy pasting a range of cells from one workbook to another.
    For example, I have such a piece of code in my macro –

    Range(Cells(rownumber, 3), Cells(rownumber, 38)).Select
    Range(Cells(count2 + 1, 4), Cells(count2 + 1, 39)).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    inputfilename is the input excel file from which I am copying cells (these cells have formulae in them).
    outputfilename is the output excel file where data is pasted as values
    rownumber and count2 are variables of a ‘do while’ loop

    How can one avoid a select command here?


    • Sean Johnson

      February 24, 2014 at 10:23 am

      Hi Ashish
      This is your answer I believe…

      Sub CopyPasteCells()
       dim wbkSource as workbook, wbkTarget as workbook
       dim rngSource as range, rngTarget as range
        ' other do while code here ...
        set wbkSource = workbooks.open("path to workbook source")
        'or if already open then...
        set wbkSource = workbooks("Name of workbook")
        set rngSource = wbkSource.Worksheets("Sheet1").range(cells(rownumber,3), cells(rownumber, 38))
        set wbkTarget = workbooks.open("path to workbook target")
        'or if already open then...
        set wbkTarget = workbooks("Name of workbook")
        set rngTarget = wbkTarget.worksheets("sheet1").range(Cells(count2 + 1, 4), Cells(count2 + 1, 39))
        rngTarget.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        ' or if you do  not need the formatting this is better.
        rngTarget.value = rngSource.value ' no copy paste clipboard delegation needed here.
        ' other do while code here....
      loop while ' some condition is true 

      Many thanks for your question.



Leave a Reply

Your email address will not be published. Required fields are marked *