Intro to Excel VBA
By Richard Rost
3 years ago
Introduction to Microsoft Excel VBA and Macros
In this Microsoft Excel tutorial we will learn the basics of programming in Visual Basic for Excel. By the end of this tutorial, you will have a good understanding of how to use the macro recorder to automate repetitive tasks, write your own code in the VBA editor, and create event-driven macros. We will begin by opening up Excel and recording a simple macro, and then we will explore the code that was generated by the macro recorder. From there, we will move on to writing our own code in the VBA editor, starting with a basic message box and then moving on to more complex macros that are triggered by worksheet and workbook events. By the end of this tutorial, you will have the tools you need to start automating tasks in Excel with code.
Pre-Requisite
Links
Want More Excel?
Most of what I do is Microsoft Access, so if if you want to see me post more Excel videos, make sure to comment below: "I want more Excel!"
Topics Covered
- Enabling the Developer tab in Excel
- Opening the Visual Basic Editor
- Recording a macro
- Running a macro using keyboard shortcuts or buttons
- Saving a macro-enabled workbook as an XLSM file
- Viewing and modifying macro code in the Visual Basic Editor
- Using worksheet events such as Selection Change to trigger actions
- Using the target parameter to get information about the selected cell or range
- Writing custom VBA code to display message boxes with useful information
- Working with workbook events such as Open to execute code when the workbook is opened.
Keywords
excel 2021, excel 2016, excel 2019, excel 365, microsoft excel, ms excel, ms excel tutorial, #msexcel, #microsoftexcel, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, An Introduction to VBA for Excel, The Basics To VBA Coding In Excel, Excel VBA Beginner Tutorial, How do I start learning Excel VBA, Is it easy to learn Excel VBA, VBA Course, Excel VBA Tutorial, Visual Basic, Excel, macros, VBA, programming, automation, recording, editing, workbook, module, events, selection change, message box, developer tab, ribbon, customize, worksheet, cell formatting, keyboard shortcuts, date and time functions, debugging
Intro In this video, we will take a quick look at getting started with Microsoft Excel VBA and macros. We will talk about what VBA is, how to record a simple macro to automate tasks, how to view and edit the code behind your macros in the Visual Basic for Applications Editor, and how to use events like Worksheet_SelectionChange and Workbook_Open to add custom functionality to your workbook. You will also learn how to save macro-enabled workbooks and unlock some of the basics of customizing Excel with your own code.Transcript Welcome to another FAST Tips video brought to you by ExcelLearningZone.com. I am your instructor, Richard Rost. In today's FAST Tip video, just a quick introduction to Excel, VBA, and Macros.
Lots of you email me questions. How do I get started? How do I do this in Excel? A lot of this will involve some VBA. So this video is just going to give you a quick intro, teach you how to get started, and how Excel VBA works.
I am going to use this video in my future FAST Tip videos when I say you need to know a little Excel VBA. So this one will get you started. Here we go.
Now, of course, it goes without saying, if you do not know Excel, if you have not used Excel, go watch my free Excel Level 1 class. It will teach you all the basics, everything you need to know to get started before you start trying to program in Excel.
I am not going to spend a ton of time on definitions and concepts and all that stuff. My full course is for that. Today we are going to go over the quick basics.
So, Excel VBA. VBA just stands for Visual Basic for Applications. That simply means it is a special version of the Visual Basic Programming language written specifically for Excel. So this is a language you are going to use to make Excel do stuff. So let us make Excel do some stuff.
Let us start with a blank workbook. A good way to start learning how to program in VBA is to take a look at some existing code. The best way to get some existing code is to record a macro. You can use the macro recorder that is built into Excel to have Excel write some VBA for you.
A macro is basically just a series of instructions to do this, then do this, then do this. You use it to automate repetitive tasks.
Let us say I had some text here, like my name. And I want to take that text and I want to bold it, and I want to make it yellow and red and maybe change the font to 16 and put a border around it. And I want to do that a lot. Pretend you do not know what the format painter is, and you want to be able to say click here and apply the same format to that cell. Well, you can use a macro or some VBA code to do all of that.
Let me hit undo a bunch of times. Control Z, Control Z. I am going to type in Richard again, and now I am going to record a macro that is going to record that series of steps that I just did.
Go to View, then Macros, then Record Macro. The Record Macro window appears. You can give the macro a name if you want. I will call this MyMacro. You can give it a meaningful name if you would like. You can assign it a shortcut key so that you can just press that key combination on the keyboard to run that macro. Let us put an O in there, Control O. Pick something that is not already used.
Store the macro in this workbook. That is fine. Give it a description if you would like to, and then hit OK.
Now your macro is currently recording, so everything you do will be saved in this macro. So be careful. I am going to click on C2. I am going to go back to the Home tab and let us bold it, put a border around it, make it yellow, make it red, change the font to 16, maybe center it. And now I am done.
You can do lots more than just formatting in here too. Macros are very versatile.
Let us go back over to View, Macros, and then Stop Recording. All that has been saved now in the macro recorder.
Let me delete this row: right click and then delete. It is gone.
Let me run that macro again. There are a couple different ways you can run it. You can go back over here, drop this down, go to View Macros, there is MyMacro where it is saved, and then hit Run. There it goes.
Notice I did not get Richard and that is very important because I typed in Richard before I started recording the macro. I typed in Richard, then I started the macro recorder. So you only get, while the macro is recording, what is saved in there.
Again, let me delete that.
The second way you can run the macro is using that keyboard shortcut, Control O. And there it runs it.
There are two ways you can run it. I like the keyboard shortcuts myself. You can also assign them to buttons, but that is for a different class.
Let us save our workbook. Control S to save our workbook. What are we going to call it? Let us call it MyWorkbook, whatever you want to call it. It is going to save in my G drive. Hit Save.
Notice the workbook file is an XLSM file; it is macro enabled. If you try to save this (I am going to go to Save As) as just a regular Excel workbook, XLSX, and hit Save, you get a warning message that says you can not save VB projects in an XLSX file.
Microsoft does that for safety. You have to specifically use an XLSM file. I will say No right now, and then close that.
Now, we used the macro recorder. The macro recorder wrote a bunch of code for us. Let us go take a look at it. Drop this down, go to View Macros, click on your macro, and then click on Edit. That opens this guy up: the Microsoft Visual Basic for Applications Editor. You can see all the code that that macro ran.
Now, if you do not see this over here, this VBA Project pane, you might see just this. That is OK. Go to View, then Project Explorer. That turns that on. Sometimes that gets turned off.
This is all of the code that the macro recorder wrote for you. I am scrolling down; there is a lot of it. We are not going to go over all of this today, but just take a general look at it. You can see right here: Range("C2").Select. That means it selected cell C2. It set the font to bold. It set the borders, set some colors down here. You can go read through this if you want to.
That is what the macro recorder does. It records all of your steps and translates that into VBA code.
Right now, the first thing it does is select cell C2. So if I come back over here and let us say I want to apply that macro to G6, if I hit Control O, it goes right back to C2. That is the first command in the macro.
Let us go back over here and just delete that line. Backspace over it or delete it. Save it, Control S.
Back over to Excel, and now if I click over here and I go Control O, look at that. It applied the formatting to the cell I was on because I removed the command to go back to cell C2 first. That is how easy it is to modify these macros.
Let us come back over here. I want you to close this window. Here is the top of it. Just close that window down. I want to show you an easier shortcut way to get into the VB editor.
We are going to turn on the Developer tab. Normally they do not want just anybody poking around in the VB editor, so they hide the Developer tab by default. But we can turn it on by going to File, then Options, and then Customize the Ribbon. Come right over here, scroll down, there is Developer. Notice it is turned off. Turn that on. Hit OK, and now you get a brand new tab up here with a whole new ribbon.
The easiest way to get back to where we were is just click on Visual Basic. Look at that – pops it right up.
All of the macros that you record, if you record multiple macros, you can make more of them. Those will be stored in modules down here. The reason why they are in modules is so that anybody can use them. Any object up here can use them in that module inside of that workbook.
What do I mean by objects? There are different objects. There is a ThisWorkbook object that is for the entire workbook file. Then you have individual objects for each sheet. We only have one sheet in our workbook, but if we added more, you would see more sheets appear right here.
Let us take a look at the code for a sheet. I am going to double-click on Sheet1. That is going to open up the Sheet1 code. It is blank right now. There is nothing in there.
There is a General section, which is code for the entire worksheet. You can put your own functions and subs in there. That is a topic for a future video.
If you drop this down, you will see a Worksheet object. Click on that, and now it creates something called Worksheet_SelectionChange.
There are a bunch of events that will fire at different times during the life of this sheet. The SelectionChange event happens when you move which cell is selected or select a range or something like that. There are all kinds of Table updates, recalculations, calculations, and all kinds of stuff in here.
Worksheet_SelectionChange happens when you move from one cell to another or select a range. Now we can put our own code in here.
Let us do this: put a couple of blank lines, tab in – always tab in – and type in the command MessageBox, MSGBOX, then a space, and then your prompt.
The first thing any programmer always learns is inside quotes: Hello World. That is the first program any programmer is supposed to write.
That is it. That is all we need. Now save it, Control S, back over to your spreadsheet. Now look at that – as soon as I click on it, it says Hello World.
That is because the selection moved over here to A3. Click on a different cell – Hello World. That is kind of annoying, but it gives you an idea of what is happening. Anytime I click on a cell, that event runs. What is that event? It is the SelectionChange event. We have changed the selection.
Let us make this a little more useful. Let us change the prompt and say: You selected row, and then a space, close the quotes, ampersand (that is Shift 7), ampersand – that is the concatenation operator, meaning we are going to put two things together – and I want you to type in Target.Row. Press Enter.
What does Target.Row mean? Well, if you look up here, the Target gets sent into this subroutine by Excel, and the Target is a range indicating what cell you are in, basically. So Target.Row is the row that you are on.
Or, more specifically, they should say: You selected a cell in row, and then the row.
Let us see what happens now. Come back over here, click, and look at that. You selected a cell in row 8. Click up here: You selected a cell in row 2. So we can use the Target parameter inside here. You can also use the column: Target.Column.
You selected a cell in column. Save it, come back out here. Column 1. It does not use letters; it uses the number of the column. They call them 1, 2, 11. Yes, there is a way to switch to it. That will be a future video.
You can see how you can give messages with useful information in it.
Remember this guy that we recorded earlier called MyMacro? Watch this. Go back to Sheet1 and our SelectionChange. Get rid of this MessageBox command and just type in MyMacro in there. Save it, come out here. Every time you click, it changes that cell with the formatting properties of your MyMacro macro. You click, it changes a selection, so SelectionChange runs MyMacro.
Where is MyMacro? Well, it is right here inside of Module1. You can find it by right clicking and going to Definition, and it puts you right on your macro.
Let us go back to Sheet1 and get rid of this. Let us delete that event. I do not want that to run anymore. I am going to delete the whole thing, everything from the Worksheet_SelectionChange all the way down to End Sub. Just delete that or backspace over it. Now that is gone.
Now that will not run if I click in here. See, it is not running.
Let us get rid of all this stuff. Delete. Goodbye. Fresh worksheet, basically.
Back over to your VB editor. In addition to the sheet, there is also the workbook. Double click on ThisWorkbook. Again, General will be different functions and subs that you can write yourself or drop this down and pick Workbook.
Now you are in Workbook_Open. If you look over here, there are a ton more events that happen when the workbook does stuff. When you open the workbook, when you create a new chart or a new sheet, when you deactivate it, all kinds of stuff in here. When the window is resized, you name it. There are lots of events after you save your file.
But we want to work in Workbook_Open. This happens once when you open the workbook. I am going to come in here and always tab in, type MessageBox: Greetings Richard. Today is (space), close your quotes, ampersand to put two strings together, and then Date – just the word Date – then press Enter. Just that.
Save it, close that, and let us close this. Now let us open up Excel again and open up MyWorkbook. Look at that: in the Open event for the workbook, my little MessageBox runs and says: Greetings Richard. Today is, and the date. I use the ISO date standard, which is year-month-day. That way it is universal for everybody around the planet. That is actually a Windows setting; you can set it in your control panel. I have a whole video that describes that, so watch this if you are interested. I will put a link down below.
There we go. We got our message box. I will hit OK.
If I want to go modify that code, how do I get there? Developer, Visual Basic. It is in ThisWorkbook right there. There it is. My modules are right here with my macros in them still.
There we go.
That is it. You are a programmer now. You now know how to program in Visual Basic for Excel. That is all you have to do.
There is your FAST Tip for today. I hope you learned something. Live long and prosper, and I will see you next time.
So that is it. Want to learn more Excel? Be sure to like this video and subscribe to my channel. Stop by my website to watch my free Excel Level 1 course. It is over 90 minutes long, and it covers all the basics.
If you want me to post more Excel videos, I need to hear from you. About 90 percent of what I do is Microsoft Access, but I am also a published Excel author, and I love Excel. If you want to see me post more free Excel videos, post a comment below and let me know. Say, hey, I want more Excel.Quiz Q1. What does VBA stand for in the context of Excel?
A. Visual Basic for Applications
B. Visual Binary Access
C. Visual Batch Automation
D. Virtual Basic Arrangement
Q2. What is the primary purpose of recording a macro in Excel?
A. To automate repetitive tasks by recording a sequence of actions
B. To create pivot tables automatically
C. To secure Excel workbooks
D. To run complex mathematical formulas instantly
Q3. Which Excel file type supports macros and VBA code?
A. XLSM
B. XLSX
C. CSV
D. TXT
Q4. When recording a macro, what happens if you assign a keyboard shortcut that is already in use by Excel?
A. It might override an existing shortcut
B. It will always result in an error
C. It will not assign the shortcut
D. It will save the macro but disable the shortcut
Q5. How do you access the Visual Basic for Applications Editor in Excel?
A. By using the Developer tab and clicking Visual Basic
B. By choosing Save As from the File menu
C. By pressing F1
D. By selecting Insert Table
Q6. What initial step must you take to make the Developer tab visible in Excel?
A. Enable it through File, Options, and Customize the Ribbon
B. Download it from Microsoft Marketplace
C. Install additional Excel plugins
D. Change the default font
Q7. What does the macro recorder do?
A. Translates your actions in Excel into VBA code
B. Deletes all formatting in your worksheet
C. Converts macros to formulas
D. Changes your spreadsheet to read-only
Q8. What is the role of the Worksheet_SelectionChange event in a worksheet object?
A. Runs VBA code whenever the user selects a different cell or range
B. Renames all worksheets automatically
C. Deletes the selected range
D. Prevents the user from editing cells
Q9. In the Worksheet_SelectionChange event, what does Target.Row refer to?
A. The row number of the selected cell
B. The column letter of the selected cell
C. The first cell in the worksheet
D. The value inside the selected cell
Q10. Where are macros stored within an Excel workbook VBA project?
A. In Modules
B. In the ThisWorkbook object
C. In the Data tab
D. On the Quick Access Toolbar
Q11. Which type of object would you use to respond to events that affect the whole workbook, like opening it?
A. ThisWorkbook object
B. Sheet object
C. Range object
D. Module object
Q12. What happens if you try to save a macro-enabled workbook as a regular XLSX file?
A. Excel warns you that macros will not be saved
B. All formatting will be lost
C. The file will be converted to CSV
D. Excel will create a backup automatically
Q13. What is the purpose of the ampersand (&) in a VBA MessageBox prompt?
A. To concatenate (join) strings together
B. To represent a comment
C. To indicate a new line
D. To repeat a string
Q14. What is the effect of removing the Range("C2").Select line from a macro?
A. The macro applies formatting to whichever cell is currently selected
B. The macro deletes cell C2
C. The macro only runs once
D. The macro stops working entirely
Q15. What is the file extension for a macro-enabled Excel workbook?
A. .xlsm
B. .xlsx
C. .exc
D. .docm
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Excel Learning Zone offers a brief introduction to Excel, VBA, and Macros. Many of you have reached out with questions like how to get started with Excel or how to use certain features, and quite often the answer involves some VBA. My aim with this FAST Tip is to give you the essential foundation to understand what VBA is, how it works in Excel, and how you can begin automating your tasks.
First and foremost, if you have not used Excel before or are unfamiliar with the basics, I strongly recommend watching my free Excel Level 1 class first. That video covers everything you need as a foundation before moving on to programming in Excel.
In this introduction, I am not going to spend too much time going into lengthy definitions or deep explanations. My full course focuses on those. For now, we are sticking with the essentials.
VBA stands for Visual Basic for Applications. It is a version of the Visual Basic programming language, tailored specifically for Microsoft Office applications like Excel. VBA allows you to write instructions that tell Excel what to do, so you can build automation directly into your workbook.
Let's begin with a new Excel workbook. If you are new to programming in VBA, an excellent way to learn is by recording a macro. The macro recorder in Excel will generate VBA code for any steps you perform, so you can see real examples in action. Macros are essentially a list of instructions to automate tasks you find yourself performing repeatedly.
For example, if you often want to apply certain formatting to a cell – perhaps bolding the text, changing colors, increasing font size, and adding a border – you could record those steps as a macro. Even if you are not familiar with the format painter, macros allow you to repeat your favorite formatting actions with just a click or key press.
To show you how this works, I started by entering some text and applying a variety of formatting changes. To record that process, I typed in "Richard" and then used Excel's macro recorder (found under the View tab, then Macros). When prompted, I gave the macro a name and assigned it a keyboard shortcut for quick access. After selecting my cell and applying the formatting changes while recording, Excel saved all my actions as a new macro.
Once the recording was stopped, the macro was ready to run at any time. You can execute it from the Macros menu, or by using the keyboard shortcut you assigned. One thing to notice is that anything you do before starting the recording, such as typing text, will not be included in the macro. Only steps performed while recording will be saved.
If you want to save your workbook with this macro, you need to use the XLSM file format, which is macro-enabled. If you try to save it as a standard XLSX file, Excel will warn you that VB projects cannot be saved in that format. This is a security feature.
Now, after recording the macro, you might want to look at the code itself. By opening the Macros menu and selecting Edit, you can bring up the Visual Basic for Applications Editor. In the editor, you will see the VBA code that was generated. The code may include commands to select cells, change formatting, and more. You are free to read or even modify this code as you wish.
For example, if your macro always starts by selecting a specific cell before applying formatting, you can remove that line of code. After saving, the macro will now apply its changes to the currently selected cell, instead of always jumping back to the original one.
To make working with the VB Editor easier, it helps to enable the Developer tab, which gives you quick access to advanced features. You can enable this tab in Excel's options, under Customize the Ribbon. Once enabled, the Developer tab lets you go straight to the Visual Basic Editor.
In the VBA Editor, macros you record are stored in modules, which can be used by any object in your workbook. Objects in Excel include the entire workbook and each individual worksheet. Each worksheet and the workbook itself has its own section where you can place VBA code for specific events.
For example, you can add code to a worksheet so that it reacts to certain events. By opening the code window for a worksheet and selecting the Worksheet and SelectionChange event, you can run VBA code every time the user selects a different cell. A classic example is to show a message box that displays which row or column the user has clicked on. You can make these messages more dynamic by including details about the selected cell, such as its row or column number, by referencing properties like Target.Row or Target.Column in your code.
If you want to run one of your recorded macros automatically when a user performs an action, you can call the macro from within an event, such as SelectionChange. You can always find and modify any of your macros in the modules section of the VBA Editor.
Aside from worksheet events, the workbook itself has events you can use. For instance, by going into the ThisWorkbook section in the editor and looking up the Workbook_Open event, you can make code run as soon as the workbook is opened. A simple use is to display a greeting with the current date, which will appear every time the file is opened.
To revisit or update your VBA code, simply return to the Developer tab and launch the Visual Basic Editor. Your modules and events will be organized there for you to edit as needed.
That covers the essentials on getting started with Macros and VBA in Excel. If you are interested in learning more, take a look at my free Level 1 course on my website. If you enjoyed this tip and want to see more Excel videos from me, let me know by leaving a comment. Most of my training is on Microsoft Access, but I also enjoy teaching Excel and am happy to make more tutorials if there is enough interest.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Introduction to Excel VBA and Macros
Using the Macro Recorder to Create Macros
Assigning Shortcut Keys to Macros
Running Macros in Excel
Understanding Macro Storage and Workbook Types (XLSM vs XLSX)
Viewing and Editing Macros in the Visual Basic for Applications Editor
Navigating the VBA Project Explorer and Modules
Modifying Recorded Macro Code
Enabling the Developer Tab in Excel
Using the Visual Basic Button in the Developer Tab
Understanding VBA Objects: ThisWorkbook and Worksheets
Adding Event-driven Code: Worksheet_SelectionChange
Using the Target Parameter in Worksheet Events
Displaying MessageBox in VBA
Accessing Row and Column Numbers Programmatically
Calling Macros from Worksheet Events
Deleting and Cleaning Up VBA Event Code
Adding Code to the Workbook_Open Event
Displaying Dynamic Date Messages on Workbook Open |
