VBA Tutorial

What is Excel VBA Tutorial for Beginners?

You can use a VBA tutorial to improve your skills and work on various projects. In VBA, you can code in some user-defined functionalities in Excel at your convenience. The best part about the VBA Tutorial point is that it is easy for beginners to grasp code.

Consider the following example:

It implements a Message box printing the message.

It is also possible to get a VBA tutorial free of cost by reading Microsoft Documentation or reputed websites that explain VBA code. You can learn VBA tutorial in the way you prefer.

What are the Macros in Excel?

Macros in Excel are a powerful feature that allows you to automate repetitive tasks by recording a series of actions you perform in Excel and replaying them whenever needed. Macros are created using Visual Basic for Applications (VBA), a programming language developed by Microsoft specifically for automating tasks in Office applications like Excel. They are important to learn about when searching for a good VBA Tutorial.

Excel macros are a powerful tool for improving productivity and efficiency when working with Excel spreadsheets. They can save you significant time by automating tasks that would otherwise require manual intervention. However, using macros responsibly and being cautious about security when enabling and running them is essential.

The basics you start in a VBA Tutorial is to view and run macros easily. To do so, we need the “Developer” tab. But when you start with Excel, it is disabled by default.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples

Let’s review some interesting cases where you can create macros to start your Advanced VBA Tutorial.

Example #1

You have a table with the values for a right-angled triangle. Here, the values for the base and perpendicular are given.

You need to find the hypotenuse of the triangle. To do so, we implement Pythagoras’ theorem.

We know that by Pythagoras’ theorem, in a right-angled triangle, the:

Square of Hypotenuse = Square of Base + Square of Perpendicular

This formula can be implemented in Excel while creating the macro. There is a function to find the square root of a number with the SQRT() function from Excel. The square of a number in Excel can be found by using the “^” symbol after the number with the power coming after the symbol as shown below:

5^2

With these in mind, we can write the Excel Formula to find the hypotenuse of the triangle with the formula:

=SQRT((SUM(x^2,y^2)))

where x and y are substituted with cell values.

Now, you can start creating a macro by following the steps below.

• Step 1: Click on Record Macro. Go to the Developer tab and click “Record Macro.”

• Step 2: Give Name to Macro.

Here, we find both the Hypotenuse and the area of the Triangle. We know how to find both with formulae.

• Step 3: Click on OK to start the recording.

Here, the shortcut key to run this macro is Ctrl+Shift+q. Describe the macro so that when others access the workbook, they will not be confused with the available macros.

• Step 4: Find the triangle’s hypotenuse with the above formula once the recording starts.

Now you are done. Stop the recording by clicking “Stop Recording” in the “Developer” tab.

• Step 5: In the “View” tab, click “View Macros”.

It will open a popup. Click the “Edit” button.

It will open the VBA Editor.

Keep the cursor on the Hypotenuse_finder() subroutine.

• Step 6: Click the run button on the VBA editor to run the Macro.

Alternatively, use the shortcut keys you’ve defined for the macro. Select the active cell and then press the shortcut keys.

Similarly, drag the values across all the rows.

With this, you have discovered a triangle’s hypotenuse given its values. You have learned one aspect of the VBA Tutorial: performing a Macro on a single row. Next, you must learn how to execute a macro for multiple lines. A good VBA tutorial can teach you how to do that.

Example #2

In a school, you are given a list of students’ marks, and they’ve provided you with the grade parameters and a short description to be added. You will need to automate this so that they can apply the same feature later. It is possible with examples from VBA Tutorial Point.

These are the parameters given by the school management, and you’ll have to implement them. Note the cell numbers these are in. The cells will play an important role when you’re recording a macro.

• Step 1: To automate printing the description and the grades of the marks given to you, go to the “View” tab and click the “Macros” button.

• Step 2: If you want to ensure your Macro works for multiple rows, it is imperative that you should turn on relative references. This can be viewed by going into the “Developer” tab and then clicking “Use Relative References”.

• Step 3: Click “Record Macro” to start recording the macro.

• Step 4: Highlight what your Macro will do since you will be sending your Workbook to multiple people, and it will be helpful to let them know what your Macro does. Remembering this is fundamental, especially when you are early into your VBA tutorial.

After editing, click “OK” to continue recording.

• Step 5: After doing so, go to the “Grade” column and type the formula given at the end of this step.

But before doing so, understand what you are trying to do.

First, we are declaring an IF function in Excel to check the parameters.

=IF($H2=$A$2, $C$2

We use the IF statement to check the values in the parameter table in columns A-D.

We write the formula and the “$” sign to tell Excel these are constant values; hence, they will not change according to the current cell position.

Here, we are declaring the grade for marks 0-100. We compare each student’s marks with the values in Columns A and B and assign the grade in Column C.

It can be done by implementing the AND function and a nested IF statement.

We add another IF statement in the Else part of the first IF function to subsequently return the other grades if one condition fails. For the first Else part, we write the statement,

IF(AND($H2>=$A$3,$H2<=$B$3)

This block is executed if the first part fails. Here, we move to cell A3, check if the values are similar or are between the numbers 91 and 99, and return the grade as “B” in cell C3. Similarly, add another IF part in the Else section of this code block to check the Else formulae.

IF(AND($H2>=$A$4,H2<=$B$4)

This way, the whole formula to find the student’s grade is calculated and printed automatically.

Final Code:

=IF($H2=$A$2,$C$2,IF(AND($H2>=$A$3,$H2<=$B$3),$C$3,IF(AND($H2>=$A$4,H2<=$B$4),$C$4,IF(AND($H2>=$A$5,H2<=$B$5),$C$5,IF(AND($H2>=$A$6,$H2<=$B$6),$C$6,IF(AND($H2>=$A$7,$H2<=$B$7),$C$7))))))

We do not want the cell values referring to the Parameter table to change. Hence, we use the “$” sign before the number; for example, “$A$2” ensures that the value will always refer to the cell value of A2 regardless of the cell it is called.

• Step 6: Reflect the grades of the entire row by dragging the cursor down till the last row of the table.

• Step 7: Stop recording the macro.

If we click the “Visual Basic” button, we’ll be able to see the code generated by Excel.

• Step 8: Similarly, print the values for “Description” in the table.

• Step 9: Use the Formula below to print the description.

=IF($H2=$A$2,$D$2,IF(AND($H2>=$A$3,$H2<=$B$3),$D$3,IF(AND($H2>=$A$4,H2<=$B$4),$D$4,IF(AND($H2>=$A$5,H2<=$B$5),$D$5,IF(AND($H2>=$A$6,$H2<=$B$6),$D$6,IF(AND($H2>=$A$7,$H2<=$B$7),$D$7))))))

It uses the concept like the one explained in Step 5.

• Step 10: After recording the macro, click on the first row and press the shortcut keys assigned to the macros. Here it is Ctrl+Shift+O and Ctrl+Shift+E. The output can be viewed as shown below.

In this way, you can automate printing values for the table by recording macros.

How to Save Macro Workbook

To save a Macro WorkBook, follow the steps below.

• Step 1: Go to FILE.

Select “Save as”

• Step 2: Select the file or folder you want to save.

Here, the “Downloads” folder is chosen.

• Step 3: After selecting the folder, select the file type you want the Excel Workbook to be saved as.

Since we’re dealing with macros, save it with the file “Excel Macro-Enabled Workbook,” abbreviated as “.xlsm.”

• Step 4: After making these changes, click “Save”.

You can view your file in the “Excel files copy.”

Important Things To Note

• Avoid running macros from untrusted sources to prevent potential security risks.
• Implement error-handling routines to identify and address issues in your macros. Use the VBA debugger to step through code for troubleshooting.
• Make backups of your Excel files before running macros, especially if they substantially change your data.
• Ensure your macros are compatible with the versions of Excel that others may use.
• Keep macros simple and easy to understand to minimize errors and facilitate maintenance.
• Test macros on sample data to verify they work as expected before applying them to critical files.
• Anticipate potential changes to data or requirements and design flexible macros for easier future updates.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA Tutorial, with its features and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA Tutorial. We learn what are Macros, how to record, use & save them, along with examples & points to remember. You can learn more from the following articles –

• VBA Delete Row
• Vlookup in VBA Excel
• VBA String to Integer