VBA Tutorial in Excel for Beginners
If are new to VBA and you don’t know anything about VBA then this is the best VBA Tutorial for beginners to start off their journey in Excel VBA macros. Ok, let’s start the journey of your VBA tutorials today.
What is VBA in Excel?
VBA is Visual Basic for Applications is the Microsoft programming language for Microsoft products like Excel, Word, and PowerPoint. All the programming we wish to do will be done in the VBE (Visual Basic Editor). VBE is the platform to write our code of task to execute in excel.
What are the Macros in Excel?
A macro is nothing but a line of code to instruct the excel to do a specific task. Once the code is written in VBE you can execute the same task any time in the workbook.
The macro code can eliminate repetitive boring tasks and automate the process. To start off with VBA Macro coding technique lets record the macro.
VBA coding is available under DEVELOPER tab in excel.
If you don’t see this developer tab in your excel follow below steps to enable developer tab in excel.
Step 1: Go to FILE -> Under FILE go to OPTIONS.
Step 2: You will see the Excel Options window. Select Customize Ribbon.
Step 3: Check the box DEVELOPER to enable it.
Step 4: Click on OK to enable it. Now you should see the Developer tab.
How to Record Macros in Excel VBA? (with Examples)
In this Excel VBA tutorial for beginners, we will learn how to record a macro in Excel with practical examples.
VBA Tutorial Example #1
Ok, we will start straight away by recoding the Marco. Under developer, tab clicks on Record Macro.
As soon as you click on the Record Macro, you will see excel asks you to give a name to your macro.
Give a proper name to macro. The macro should not contain any space characters and special characters. You can give underscore (_) as the word separator.
Click on OK to start the recording. From now onwards macro recorder keeps recording all the activities you do in the excel sheet.
Firstly I will select cell A1.
Now I will type “Welcome to VBA” in A1 cell.
Now I will stop recording under the developer tab.
So, Excel stops recording the activities we do in the excel. Now let’s see how excel recorded the activities. Under Developer tab click on Visual Basic.
As soon as you click on Visual basic we will see below the window.
Double Click on Modules.
Now we will see the code on the right-hand side.
Macro code started with the word SUB in VBA.
All the macro has two parts to it one is Head and another one is Tail. Every macro has a name.
In between head and tail of the macro, excel recorded all the activities.
First thing we did after start recording the macro is we have selected the cell A1 and excel recorded it as Range (“A1”).Select
Second activity was we have entered the value “Welcome to VBA”. As soon as we selected then it becomes active cell, so excel recorded the activity as ActiveCell.FormulaR1C1 = “Welcome to VBA”. Note: R1C1 is row 1, column 1.
The third activity is after typing the word “Welcome to VBA” we hit enter and excel selected A2 cell. So excel recorded as the activity as Range (“A2”).Select
Like this Macro Recorder recorded all the activities we have done in the excel sheet. Now delete the word in cell A1.
After deleting the word, once again go to VBE where our code is. There click on the RUN button to once again enter the same text value to cell A1.
Note: Shortcut key to run the code is F5.
So macro is executed and we got the same value again. Like this, we can automate our daily routine work to save a lot of time and get rid of boring tasks every day.
VBA Tutorial in Excel – Example #2
Now let’s record one more macro to understand better. In this recording, we will insert serial numbers from A1 to A10.
Go to DEVELOPER tab and click on the record macro option.
Click on Ok to start the recording. I will enter 1, 2, 3 then I will drag the fill handle to inert serial numbers.
Now click on stop recording.
Go to Visual Basic Editor and see what the code is.
Let’s look at the code now.
- Firstly we have selected cell A1.
- Secondly, we have inserted 1 to the active cell.
ActiveCell.FormulaR1C1 = "1"
- The third activity was we have selected the cell A2.
- Fourth activity was we have inserted 2 to the active cell.
ActiveCell.FormulaR1C1 = "2"
- Fifth activity was we have selected the cell A3.
- Sixth activity was we have inserted 3 to the active cell.
ActiveCell.FormulaR1C1 = "3"
- Then we have selected the range of cell from A1 to A3.
- After selecting the cells we have filled the serial numbers using fill handle.
Selection.AutoFill Destination:=Range("A1:A10"), Type:=xlFillDefault
- So finally we have selected the range A1 to A10.
So, now whenever we want to insert serial numbers from 1 to 10 in cell A1 to A10, you can run this code.
How to Save Macro Workbook?
Excel workbook which contains macro code should be saved as Macro-Enabled Workbook. Click on Save As and select the extension of the file as “Macro-Enabled Workbook”.
Things to Remember in This VBA Tutorial
- This is just the introduction part to the Excel VBA Tutorial. Keep following our blog to see more posts going forward.
- Recording macro is the best initialization to start the journey of macros.
- Record more and more activities and see what the code is.
This has been a guide to Excel VBA Tutorial for beginners. Here we discuss steps to enable the developer tab and also learn how to record macros in Excel VBA along with practical examples. Below you can find some useful excel VBA articles –