How To Use Excel Vba

Excel VBA Tutorial For Beginners

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

Basics of VBA

In this commodity, we volition learn about the nuts of VBA. It is the best VBA Tutorial for beginners. In VBA, we create macros, and as defined above, macros are a prepare of instructions that are given to exercise a specific task. There are two ways to requite these instructions for macros. I is through writing codes in VBA itself, while another is washed past recording a macro. In excel, we take 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 information technology, so nosotros write codes in VB Editor where in that location is an ultimate number of opportunities to automate our tasks.

VBA is basically a programming linguistic communication just like C and Java and everything else. Here we tape a gear up of instructions by writing some block of codes, and when nosotros run these blocks of codes, they exercise the specified tasks for us. By default, VBA is not accessible in excel. To access VBA, we demand to follow some steps first, as listed beneath.

Every bit VBA is different from the normal worksheet we work upon, it contains macros, and then saving it is different from other normal workbooks; when nosotros save a workbook that contains macros, we need to relieve it as a macro-enabled workbook to ensure that our macros are saved in the workbook.

If y'all don't meet the developer tab in your excel, follow the below steps to enable the developer tab in excel.

Get to the File tab on the habitation folio of excel.

In the files tab, nosotros will find a section for options in the final from the bottom; click on Options.

Information technology volition open another sorcerer box for u.s.a. as Excel Options. In excel options, click on Customize Ribbon.

When we drag down in the customize ribbon options, we will notice an pick for Developer; we demand to check that box which will enable us to use VBA in excel.

Now nosotros take the developer'due south tab enabled in excel as follows.

How to Utilise VBA in Excel?

There are two methods for using VBA in Excel.

One is by recording macros.
The second is by writing a block of codes.

The well-nigh advisable affair to practice is learn by recording the macro and await at the code to larn the syntax, which will help us in writing the codes of our own.

How to Tape Macros in Excel VBA?

In this Excel VBA tutorial for beginners, before we motility to acquire how to write codes in VBA, let us first with the near basic function of VBA, which is to record a macro. Recording macro is recording steps nosotros perform and let excel do information technology over and over when we give the command to practise it. Let us learn all these in examples which will assist the states to understand information technology better.

You can download this VBA Tutorial Excel Template here – VBA Tutorial Excel Template

VBA Tutorial – Example #one

Permit u.s.a. tape our first macro. For this case, I already have some data in sail one as follows.

Nosotros accept marks of some students and have a formula in cavalcade E that if the percentage is to a higher place 35%, they will declare as laissez passer or as neglect. Currently, in that location is no data in column D, so by default E column value is false. Allow us exercise this by recording a macro.

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

We go iii options, select Record Macro.

This will open a wizard box for the states somewhat like this,

E'er think that a macro proper noun cannot have spaces, requite a name to macro and a shortcut cardinal to run it. The other boxes are optional.

Printing ok, and the macro records every pace, every cell nosotros select or every click we make.

Permit us calculate the per centum for the given an example as follows,

Once we have completed our task now, we need to finish recording a macro. In the aforementioned macro tab from the Views table, nosotros have another choice to Stop Recording. Click on information technology to end recording a macro.

At present to test information technology, remove the values from the pct department and exit it blank.

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

We have given this set of instructions to excel not by code, just excel recorded every pace nosotros followed and made an own lawmaking by itself. We can view the code too. In the macros section, click on View macro, and we will take a listing of macros if we made whatsoever on that workbook as follows.

Click on Edit, and information technology will take u.s. to the code window for this macro, as shown below.

Nosotros did non write this code merely excel recorded our steps and transformed them into codes. This is a very unique characteristic of excel, simply it has some limitations like; if we do an actress pace in excel, we tape the step and execute it every time nosotros run the macro.

How to Make Simple Macros in Excel Using VBA code?

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

VBA Tutorial – Example #2

In the Developer'southward tab, click on Visual Basic, which will open VB Editor for us, where we will write our first macro.

One time the VB Editor is open, we can see many windows around us. On the left-hand side, nosotros have a project window which keeps records for our modules where we write codes and properties of a VBA project; also, we have a toolbar to a higher place with various ranges of tools to employ.

To write a VBA code or macro, nosotros need a platform or a lawmaking 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. Nosotros first name our macro as follows.

Code:

              Sub              MyMacro()              Cease Sub

When nosotros desire to piece of work with variables, we need to declare these variables with information types like in every other programming linguistic communication such equally follows. We utilise the Dim keyword to declare our variables.

Code:

              Sub              MyMacro()              Dim              A, B, C              As Integer              End Sub

Nosotros can perform whatever task with these variables such as follows,

Code:

              Sub              MyMacro()              Dim              A, B, C              Equally Integer              A = ten B = 15 C = A + B              End Sub

To display a value in VBA, we use Msgbox Function equally shown in the paradigm below.

Lawmaking:

              Sub              MyMacro()              Dim              A, B, C              Equally Integer              A = ten B = 15 C = A + B MsgBox C              Stop Sub

Run the lawmaking by hitting the F5 or Run button and come across the output.

Things to Remember

VBA is not enabled by default in Excel; it has to be manually enabled.
Not two macros tin can have the same name.
Macros names cannot have spaces among them.
When nosotros tape a macro, it records every footstep nosotros perform, and so we need to be conscientious with every stride we have.

Recommended Manufactures

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 manufactures related to VBA –

VBA Call Sub
VBA Web Scraping
VBA Clear Contents
VBA XML