Power Excel vba secret, avoid using select
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.
Check out the video below and read this short article, both tackle the same problem but with different code and projects, it’s well worth you time.
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
01 ' Least amount of code but no variables 02 '(variables are better as they give you more flexibility in larger programs) 03 Sub MinimumAmountOfCode() 04 With ActiveWorkbook.Worksheets(2).Range("C5") 05 .Value = "Enter Numbers" 06 .Offset(1).Value = "1" 07 .Offset(1).Resize(15).DataSeries Step:=1 08 End With 09 End Sub
The "With" statement on lines 4 and 8 above are a useful Object Oriented construct, it means the changes you make on lines 5, 6 and 7 (because they start with the dot operator) are applied to the object referenced on line 4.
This saves you typing and saves the processor having to navigate the object tree each time, very relevent in large loops.Example 3
01 Sub Better() 02 Dim wbk As Workbook 03 Dim rngCell As Range, rngNumbers As Range 04 Dim i As Integer 05 06 ' Set up two references 07 Set wbk = ActiveWorkbook 08 Set rngCell = wbk.Worksheets(2).Range("E5") 09 10 rngCell.Value = "Enter Numbers" 11 12 ' Populate 1 to 15 13 For i = 1 To 15 14 rngCell.Offset(i).Value = i 15 Next 16 ' Done 17 18 '========================================================= 19 ' Following is for extra credit ! 🙂 20 '========================================================= 21 22 ' Get reference to numbers range 23 Set rngNumbers = rngCell.CurrentRegion 24 Set rngNumbers = rngNumbers.Resize(rngNumbers.Rows.Count - 1).Offset(1) 25 26 MsgBox "Numbers entered click OK to try a different way", vbExclamation 27 rngNumbers.Clear 28 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 34 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..
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 😉