VBA Tutorial for Beginners

Excel VBA Tutorial For Beginners

VBA or Visual Basic Applications is a part of Microsoft Excel. In Excel, we do our task manually or by formulas but the tasks which are repetitive needs to be done again and again. But by using VBA we can make our jobs easier as we can make macros which will do our repetitive tasks for us. What is a macro again? Macro is a set of instructions which are given to do a specified task.

Basics of VBA

In this article, we will learn about the basics of VBA. It is the best VBA Tutorial for beginners. In VBA we create macros and as defined above macros are a set of instructions which are given to do a specific task. There are two ways to give these instructions for macros. One is through writing codes in VBA itself while another is done by recording a macro. In excel we have a special feature in which we record our steps and excel does the same steps for us over and over when giving excel command to run that code. But it has certain limitations to it so we write codes in VB Editor where there is an ultimate number of opportunities to automate our tasks.

VBA is basically a programming language just like C and Java and everything else. Here we record a set of instructions by writing some block of codes and when we run these block of codes they do the specified tasks for us. By default, VBA is not accessible in excel. To access VBA we need to follow some steps first as listed below.

As VBA is different from normal worksheet we work upon it contains macros so saving it is different from other normal workbooks. When we save a workbook which contains macros we need to save it as a macro-enabled workbook to ensure that our macros are saved in the workbook.

If you don’t see developer tab in your excel follow below steps to enable developer tab in excel.

• Go to the File tab on the home page of excel.

• In the files tab, we will find a section for options in the last from the bottom, Click on Options.

• It will open another wizard box for us as Excel Options. In excel options click on Customize Ribbon.

• When we drag down in the customize ribbon options we will find an option for Developer we need to check that box which will enable us to use VBA in excel.

• Now we have developer’s tab enabled in excel as follows.

How to Use VBA in Excel?

There are two methods for using VBA in Excel.

1. One is by recording macros.
2. Second is by writing a block of codes.

The most advisable thing to do is learn by recording the macro and look at the code to learn the syntax which will help us in writing the codes of our own.

How to Record Macros in Excel VBA?

In this Excel VBA tutorial for beginners, before we move to learn how to write codes in VBA let us start with the most basic function of VBA which is to record a macro. Recording macro is recording steps we perform and let excel do it over and over when we give the command to do it. Let us learn all these in examples which will help us to understand it better.

VBA Tutorial – Example #1

Let us record our first macro. For this example, I already have some data in sheet one as follows.

We have marks of some students and have a formula in column E that if the percentage is above than 35% they will declare as pass or as fail. Currently, there is no data in column D so by default E column value is false. Let us do this by recording a macro.

• In the View tab, we have a Macros section click on it.

• We get three options, select Record Macro.

• This will open a wizard box for us somewhat like this,

• Always remember that a macro name cannot have spaces, give a name to macro and a shortcut key to run it. The other boxes are optional.

Press ok and the macro records every step, every cell we select or every click we make.

• Let us calculate the percentage for the given example as follows,

• Once we have completed our task now we need to stop recording a macro. In the same macro tab from Views table, we have another option to Stop Recording. Click on it to stop recording a macro.

• Now to test it, remove the values from the percentage section and leave it blank.

• Press CTRL+J as CTRL+J was the shortcut for our macro and see the result.

We have given these set of instructions to excel not by code, but excel recorded every step we followed and made an own code by itself. We can view the code too. In the macros section click on View macro and we will have a list of macros if we made any on that workbook as follows.

Click on Edit and it will take us to the code window for this macro as shown below.

We did not write this code but excel recorded our steps and transformed it into codes. This is a very unique feature of excel but it has some limitations like if we do an extra step excel we record the step and execute it every time we run the macro.

How to Make Sample Macros in Excel Using VBA code?

Below is the Excel VBA tutorial example to make our first sample macro in VBA by writing code by ourselves. To write a code in VBA we must know this every macro starts and ends with SUB () function.

VBA Tutorial – Example #2

• In the Developer’s tab click on Visual Basic which will open VB Editor for us where we will write our first macro.

• Once the VB Editor is open we can see many windows around us. On the left-hand side, we have project window which keeps records for our modules where we write codes and properties of a VBA project, also we have a toolbar above with various ranges of tools to use.

• To write a VBA code or macro we need a platform or a code window to write it. We write codes in modules. Modules can be accessed from the Insert tab.

• When we click on Module and double click on it then another window opens for us which is our platform to write code. We first name our macro as follows.

Code:

Sub MyMacro()

End Sub

• When we want to work with variables we need to declare these variables with data types like in every other programming language such as follows We use Dim keyword to declare our variables.

Code:

Sub MyMacro()

Dim A, B, C As Integer

End Sub

• We can perform any task with these variables such as follows,

Code:

Sub MyMacro()

Dim A, B, C As Integer
A = 10
B = 15
C = A + B

End Sub

• To display a value in VBA we use Msgbox Function as shown in the image below.

Code:

Sub MyMacro()

Dim A, B, C As Integer
A = 10
B = 15
C = A + B
MsgBox C

End Sub

• Run the code by hitting F5 or Run button and see the output.

Things to Remember

• VBA is not enabled by default in Excel, it has to be manually enabled.
• Not two macros can have the same name.
• Macros name cannot have spaces among them.
• When we record a macro it records every step we perform so we need to be careful with every step we take.

Recommended Articles

This is a guide to Excel VBA Tutorial For Beginners. Here we discuss the steps to enable the developer tab and learn how to record macros and also learn to make sample macros in Excel VBA along with practical examples and downloadable excel template. Below are some useful excel articles related to VBA –

1. VBA Call Sub
2. VBA Web Scraping
3. VBA Clear Contents
4. VBA XML