Excel VBA Tutorial – #5 Most commonly used Excel VBA commands
Summary: In the first four tutorials of this series we learned the basics of programming Excel using the VBE (Visual Basic Editor), but most of the time we relied on macro recordings of Excel’s built in functions.
Now it’s time to go deeper into the coding world and get to know the most relevant commands and macros you should use as a beginner.
Here you will find valuable commands, best-practice tips and professional ways to solve common tasks in Excel with VBA. Let’s go learn!
Download the Sample Workbook
Download the sample file with VBA code
Most_Valuable_Commands (90 Kb)
Click on a link to jump to that section…
#1 Finding the last used row number
#2 MsgBox and InputBox
#3 Cells.Find function to search for values
#4 Avoiding Copy and Paste by directly assigning values
#5 Disabling Screen Updating to speed up macros
#7 About the Author
#1 – Finding the last used row number
Working with big datasets in Excel requires a lot of scrolling with the mouse. You need to make sure you select the last row of data and not miss any valuable information.
When you write VBA macros, it’s similarly important to define the last row of a dataset. Macro execution is just too fast to check if everything goes well, and the right range is selected. So don’t just rely on hard coded ranges.
This is where finding the last row number comes in handy: we use a variable to find and store the number of the last used row on the sheet.
This is dynamic, so if you use it on another sheet, it will store that sheet’s last row number.
The code to do this is really short:
Dim lrow As Long lrow = Range("I1048576").End(xlUp).Row
The first line is the variable declaration, which you should write at the start of the macro. Here we define “lrow” as a variable with Long type.
What does Long type mean? It can store whole numbers from -1,048,576 to +1,048,576.
The second line is the function that finds the last used row in column “A”. You can of course select any column, just change the letter “A” in the command.
The interesting thing is that you can also record this kind of function in Excel: start recording a macro, then select the last unused cell on a sheet, and press Ctrl+UpArrow, then stop recording the macro.
In Excel 2016 version every sheet has 1,048,576 rows and this is the maximum you can use. So if you select Cell(A1048576) and go up with the keyboard shortcut until the last used cell, you will get the following recorded VBA code:
We combined these two lines into one line, using the “.Row” property of the last used cell. And the result will be stored in our variable called “lrow”.
lrow = Range("A1048576").End(xlUp).Row
This is a very effective and useful command, and that’s why it’s my first topic for this article. I can hardly imagine more complex macros where something like this is not used.
In the downloadable .xlsm workbook you will find Sheet1 has lots of “a” characters typed in column I. And you can test out the last used row finder function by running the Sub LastRowFinder() in Module2. Have fun! 😊
(Note: for more advanced ways to find the last used row, read this in-depth article… Excel VBA Tutorial: Find the Last Cell, Row or Column on an Excel worksheet)
#2 – MsgBox and InputBox
We already used MsgBox and InputBox commands in our previous macro from article #4 – Editing recorded macro code.
There I mentioned how InputBox differs from MsgBox – if both are streets, then MsgBox is a one-way street, while InputBox is a two-way street.
MsgBox writes information to the screen. It can work without variables using text strings to tell the user something.
InputBox asks for information from the user – if your program needs a value, which only the user knows, then the user can give it in an InputBox. Then it will be stored and used later. That is why InputBox can’t work without variables. Without one, there is no place to store the typed in value.
In reports, or database macros, where the date or month number must always be specified, I prefer using InputBox instead of dropdown-lists in Excel. An InputBox looks more serious, and can’t be forgotten, because the macro won’t execute the next command until some value is typed in.
Sub MsgBoxAndInputBox() Dim TodayDate As String Message = InputBox("Please, type in todays date!", "Date of today") MsgBox Message, , "Date of today" End Sub
You can try out my macro in Module2 of the downloadable workbook.
Here are pictures of the results:
#3 – Cells.Find function to search for values
The search function in Excel is also one of the most often used tools in the VBA programmers’ toolbox. If you do not know exactly where the needed cell is in the dataset, you can always turn to Cells.Find.
In the previous article we looked at how to use the search function to locate the date among cells containing the November 2018 calendar…
… but we didn’t go deep with the arguments, which are the different input values you can use to customize the behavior of the function.
Here I’ll go through the different options for finding a value.
This example searches all the cells of the active worksheet to find the number 21:
Sub SearchDate() Cells.Find(What:="21", After:=Range("A1"), LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate End Sub
- What:= [required] the main point of the function – this is what you’re looking for. It can be a string or a variable as well. Strings between double quotes!
- After:= [optional] after which cell should the search start. Normally this is not a vital parameter, because it determines the start of the search and not the end of it. Search is recursive, so if it did not find the target after the specified cell, it searches before it as well. If you do not specify this argument, the search starts after the cell in the upper-left corner of the range.
- LookIn:= [optional] you can specify if you want to search in Values only or Formulas only. If you look for a string, it doesn’t really matter, because strings are considered values AND formulas as well.
- LookAt:= [optional] significant argument, because it determines if you want to get only full identical results, or partially similar as well.
- SearchOrder:= [optional] search by rows or by columns
- SearchDirection:= [optional] search forwards or backwards
- MatchCase:= [optional] if the case of letters also matters, switch it to True
- Searchformat:= [optional] this allows you to search for a cell format such as font type or cell color.
Range.Find to search in a range
You don’t have to search the whole sheet, you can work in a smaller range.
In this case you should determine a Range type variable, then specify the area of the range.
After this you can use the RangeName.Find() function, where the RangeName is the name of your Range type variable.
Here is an example:
Private Sub SearchDateRange() ' Like SearchDate but for specific range Dim RngSearch As Range Set RngSearch = Sheet1.Range("A1:G6") RngSearch.Find(What:="21", After:=Range("A1"), LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate End Sub
This gives you faster results because of the smaller area to search through.
#4 – Avoiding Copy and Paste by directly assigning values
Copy-Pasting values between sheets and workbooks – everyone does this. I’m sure it’s one of Excels most used functions.
Why do I say then that you should avoid it in macros? Because it can slow down your code, and you can frequently achieve the same result in a better way.
In the downloadable workbook, Module2 contains a CopyPaste macro, which is 6 lines long.
Here’s the code which copies cell A1 on Sheet1 and pastes to cell A1 on Sheet2:
Sub CopyPaste() Sheets("Sheet1").Select Range("A1").Select Selection.Copy Sheets("Sheet2").Select Range("A1").Select ActiveSheet.Paste End Sub
You might say that 6 lines of code isn’t much… why should we use another way?
Because later on, you will be copying massive datasets full of complicated Excel formulas, and you may have many sheets and workbooks to work with, so every single simplification in the code can save you minutes or even hours!
As you can see in the CopyPaste macro, copy-pasting requires cell selection.
This is one of its biggest flaws… When the program selects a cell, it is a waste of processor time.
Instead of the selection command, the processor could do something more valuable, which in fact does something to your data, and not just selects a cell.
In our case it’s better to assign values directly.
To copy of value in cell A1 of Sheet1 to cell A1 of Sheet2, we can use one line of code:
Sheets("Sheet2").Cells(1, 1).Value = Sheets("Sheet1").Cells(1, 1).Value
This is the professional 😊 way of copying values from sheet to sheet.
It also works for Ranges, so we can do this to copy range A1:G6 of Sheet1 to range A1:G6 of Sheet2:
Sheets(“Sheet2”).Range("A1:G6").Value = Sheets(“Sheet1”).Range("A1:G6").Value
Note on using Sheet Index numbers
I could also write the above line of code this way:
Sheets(3).Range("A1:G6").Value = Sheets(2).Range("A1:G6").Value
Note in this line of code that I didn’t write the sheet names inside the brackets, just their index numbers.
This is a simpler way of coding, but it has risks as well.
In particular if I reorder the sheets, the macro runs on the wrong sheets!
So, if you don’t specify the sheets by their names, be careful, and always make sure that the order of the sheets is not changed.
#5 – Disabling Screen Updating to speed up macros
To make your code run smoother and a lot faster, you should disable the screen updating.
With this option you lose the ability to follow what the macro is doing on screen – but with today’s quad-core (and more) processors you would barely catch anything happening anyway.
The command is as follows:
Application.ScreenUpdating = False
The second option is shorter. If you code a lot, after a certain time you will seek the opportunity to use shorter codes, to spare typing time.
You can of course enable the screen updating after it has been disabled. The command is the same, just the value is opposite:
Application.ScreenUpdating = True
You should always re-enable screen updating at the end of the macro, so it won’t affect other macros. This “switch” holds its effect, until the opposite command is executed – you should always use the command in pairs. After disabling, always enable when you are finished.
#6 – Summary
We covered 5 best practice code snippets that I use in my daily work:
- Finding the last used row number to work with datasets that vary in size
- Giving messages to the user with MsgBox, and getting inputs from the user with InputBox
- Searching for values with the Cells.Find function
- Avoiding Copy and Paste by directly assigning values
- Making your code run faster by disabling screen updating
I’m sure you can make good use of them as well, because they are very common functions that you can apply on many tasks in Excel!
Learning and using these methods means you’re no longer an Excel VBA beginner – these are intermediate level commands.
They are shorter, more effective and more elegant solutions for frequent tasks. So, I advise you to use them for your own good, and don’t be afraid to modify them to suit your needs!
#7 – About the Author
Daniel Lajosbanyai – I work as a controller and accountant for a company with a bunch of international subsidiaries. In my daily job I work A LOT with Excel and my tasks are quite repetitive (and sometimes boring!)
To boost my processes and spare some time and energy, I started learning Excel Macros. My first teacher was a university professor, who showed me how to get started. I am really thankful to him, because without this knowledge I might have quit my job years ago.
Now I enjoy writing macros for every task I can automate and feel really happy to have learned this skill! Why should we do repetitive things, when our computers can do them quicker for us? We only need to learn how to give them instructions to follow!
- How to do 10 Hours work in 10 Seconds – using this Powerful Excel skill
- Excel Tutorial: How to use VBA to delete filtered rows (fast!!) … but not delete hidden rows
- Excel VBA Tutorial – #2 Macro recording (including the difference between absolute and relative)
- VBA Case Study #4 – Invoice Generator Macro