Excel VBA Tutorial – Easy and Complete Tutorial

Improve your Excel VBA skills with advanced techniques. 🚀 Relevant examples and features are provided on the right side of every chapter. The complete overview of the Excel VBA Tutorial is shown below.

1. Create a Macro

– Swap Values: This example shows how to exchange two values using a basic Excel VBA Sub procedure. This technique is useful and will be used in more advanced programs later.

– Run Code from a Module: This example shows how to execute code stored in a standard module using a procedure.

– Macro Recorder: The Macro Recorder records your actions in Excel and creates VBA code you can reuse later. You only need to record the task once, and then you can repeat it anytime with a single click.

– Use Relative References: By default, Excel records macros in absolute mode, capturing exact cell references for every action. However, recording in relative mode is sometimes more useful, depending on the task.

– FormulaR1C1: Learn the use of A1, R1C1 and A[1]C[1] style in Excel VBA and why you need to know these three styles.

– Add a Macro to the Toolbar: In this tutorial you will learn how to add a macro to the Excel toolbar for quick access. This makes it easier to run.

– Enable Macros: To run macros in Excel, click “Enable Content” in the yellow message bar at the top. For permanent changes, adjust macro settings through Excel’s Trust Center options. Ensure the Developer tab is enabled in Excel to access the tools needed for creating macros.

– Protect Macro: Similar to how you set a password for worksheets or workbooks, you can set a password for a macro in Excel to stop others from accessing or executing it.

2. MsgBox

– MsgBox Function: This tutorial will teach you how to use the MsgBox function in Excel VBA and capture the user’s response. A basic MsgBox just shows a message and does not return any value.
– InputBox Function: In Excel VBA, the InputBox function is used to prompt the user to give input.

3. Workbook and Worksheet Object

– Path and FullName: In Excel VBA, the Path property gives the full folder location of the active workbook. The FullName property shows where the workbook is saved and its file name.

– Close and Open: In VBA, Workbooks.Open opens Excel files programmatically. The Close method is used to close a workbook, with options to save changes or discard them.

– Loop through Books and Sheets: Learn to create a loop to list all open workbook and worksheet names.
– Sales Calculator: Learn how to write a basic Excel VBA script to calculate and display total sales per employee by summing sales data from the past three years.

– Files in a Directory: This guide shows you how to create an Excel VBA macro that opens workbooks from a folder and lists the names of all worksheets.

– Import Sheets: This macro combines multiple sheets from different Excel files into one single workbook using VBA.

– Programming Charts: The first macro automatically changes all charts on the worksheet into pie chart format instantly. The second macro should update certain properties (like title or color) of the first chart on the sheet.

4. Range Object

– CurrentRegion: The CurrentRegion property in Excel returns a range of continuous data around the active cell, stopping at any completely empty row or column.

– Dynamic Range: We’ll learn to create a VBA program in Excel that identifies the highest value in a dynamic range and highlights it using color formatting.

– Resize: In this tutorial you will learn how to change the size of a range without moving it using the resize in Excel VBA.

– Entire Rows and Columns: Learn to use VBA to select full rows and columns in Excel.

– Offset: With the Offset property in Excel VBA, you can select a cell by specifying how many rows and columns away it is from your starting point.

– From Active Cell to Last Entry: This example demonstrates using the End property to locate the last used cell in a range. It selects all cells downward from the active to the last filled cell.

– Union and Intersect: In VBA, Union combines multiple ranges into one, allowing you to perform actions on all at once. The Intersect function identifies the common cells that exist in all the specified ranges.

– Test a Selection: This VBA code checks selected cells for numbers, blanks, and overlaps using Count and validation functions.

– Font: The Font property lets you access and modify various text formatting settings in a range. This article explains other property like the color property and the bold property, and the font property gives access to in Excel VBA.

– Background Colors: In Excel VBA, change a cell’s background color by accessing the Interior property, then set the desired color using the ColorIndex property for easy formatting.

– Sort a Range: The Sort method in Excel VBA helps you arrange values in a selected range. This article presents clear examples to guide you through the sorting process.

– Areas Collection: This page shows how to use Excel VBA’s Areas collection to manage multiple cell ranges efficiently.

– Compare Ranges: Learn to create a VBA program in Excel that selects two random ranges, compares their values, and highlights the cells where the differences are found.

5. Variables

– Option Explicit: Helps prevent bugs by making variable declarations mandatory in VBA. This prevents typing mistakes and makes your code more reliable, organized, and easier to debug.

– Variable Scope: In Excel VBA, variable scope defines where a variable can be used: within a procedure, across a module, or throughout all modules using Public.

– Life of Variables: In Excel VBA, use the Static keyword to keep a variable’s value even after a procedure ends, allowing it to retain data between calls.

– Type Mismatch: When a value is assigned to a variable that doesn’t match the expected data type, Excel VBA displays this error.

6. If Then Statement

– Logical Operators: Excel VBA uses And, Or, and Not for making logical decisions. We’ll explain each using simple examples to help you understand them better.

– Select Case: Use the Select Case structure in Excel VBA to replace multiple If Then statements, making your code cleaner, easier to read, and more organized.

– Tax Rates: This Excel VBA program calculates tax from income using simple logic and formulas, automating tax computation quickly and accurately.

– Mod Operator: This is used to determine if a number is divisible by another or to calculate the remainder.

– Prime Number Checker: Learn to create an Excel VBA program that checks if a number is prime by testing divisibility and returning results based on mathematical conditions.

– Find Second-Highest Value: In this tutorial, we will discuss a VBA program to track the second-highest values inside a randomly selected range.

– Sum by Color: Learn to build an Excel VBA program that adds cell values based on their background color, automating color-based calculations for better data analysis.

– Delete Blank Cells: This example demonstrates how to create a VBA macro that deletes empty cells. We start by declaring two variables of type Integer.

7. Loop

– Loop through Defined Range: Excel VBA lets you loop through a range, like A1:A3, to perform calculations, such as squaring each cell’s value.

– Loop through Entire Column: This tutorial will explain an example of a VBA program that first loops through the entire column and then colors the cell that has a value below the reference value.

– Do Until Loop: In this tutorial, we explain an Excel VBA program that shows how to use the Do Until loop.

– Step Keyword: Excel VBA allows you to use the Step keyword in a loop to increase or decrease the counter variable by a value other than 1.

– Create a Pattern: Use nested loops in VBA with the Step keyword and Offset property to move across cells and apply a color pattern.

– Sort Numbers: This example creates a VBA macro to sort numbers using three Integer variables and one Range variable in Excel.

– Randomly Sort Data: This technique is useful for creating randomized lists, selecting random samples, or shuffling items in Excel.

– Remove Duplicates: This example demonstrates how to use Excel VBA to eliminate duplicate numbers in column A. After removing the duplicates, the unique values will appear in column B.

– Complex Calculations: With Excel VBA, make a simple program to compute any term (Tk) and the total sum from the first term to the Nth term.

– Possible Football Matches: In this tutorial, we will learn to create a VBA program that can generate all possible matches when a list of teams is provided.

– Knapsack Problem: Understand how to create a basic solution for the knapsack problem using programming in Excel VBA.

8. Macro Errors

– Debugging: This example introduces the basics of debugging in Excel VBA.

– Error Handling: Create two simple programs using Excel VBA. The first program should ignore any errors and continue running. The second program should continue execution from a specific line when an error occurs.

– Err Object: The Err object in Excel VBA stores details like error number and description whenever an error happens.
– Interrupt a Macro: At any point during macro execution, you can press Esc or Ctrl + Break to interrupt it.

– Subscript Out of Range: “Subscript out of range” in Excel VBA means you’re referencing a non-existent array or collection item.

– Macro Comments: A macro comment is a helpful note added to the code. It is not run by Excel VBA and is only there for your reference.

9. String Manipulation

– Separate Strings: To create a VBA program that splits strings, add a command button to your Excel worksheet and enter the code given below.

– Reverse Strings: Learn VBA in Excel to create a program that reverses text.

– Convert to Proper Case: Learn to write Excel VBA code for proper case formatting.

– InStr: With the InStr function in VBA, you can find the position of a word or phrase in a larger block of text. The InStr function is quite versatile.

– Count Words: Learn to use Excel VBA for creating a program that counts words in selected cells. One or more spaces are considered word separators.

10. Date and Time

– Compare Dates and Times: Learn how to use Excel VBA to check if one date or time is different from another.

– DateDiff Function: As the name suggests, this function calculates the number of days, months, or years between two dates.

– Weekdays: We will work on a VBA program in Excel that helps us determine how many weekdays lie between two specific dates.

– Delay a Macro: Use OnTime with Now and TimeValue to run a VBA sub after a specific time. The subroutine should be written in a module before use.

– Year Occurrences: Learn Excel VBA to count yearly occurrences in your dataset automatically.

– Tasks on Schedule: In this tutorial, we will create a VBA program using a worksheet change event that sets the background colour of on-time tasks to green and late tasks to red.

– Sort Birthdays: We are going to develop an Excel VBA program that sorts birthdays based on the month and day, without taking the year into account.

– Date Format: Excel VBA helps you quickly change how dates are displayed by adjusting the format. You can easily set your preferred date format using the NumberFormat property.

11. Events

– BeforeDoubleClick Event: This tutorial helps you understand a macro program that explains the uses of the BeforeDoubleClick event in Excel VBA.

– Highlight Active Cell: Automatically highlight the entire row and column of the active cell using Excel VBA code. This simple yet effective tool can make your work look more dynamic and professional.

– Create Footer before Printing: We will learn how to write a VBA program in Excel that sets the footer before printing.

– Bills and Coins: Use VBA to show how money can be split into different notes and coins.

– Rolling Average Table: Use simple Excel VBA to build a table that shows rolling averages on its own.

12. Array

– Dynamic Array: Use ReDim to resize an array in VBA when its size needs to change dynamically. Excel VBA resizes dynamic arrays automatically based on the data assigned or the operation performed.

– Array Function: You will learn how to use array functions in Excel VBA in this tutorial, such as creating an array and getting single values from it.

– Month Names: Excel VBA’s Array function helps store and list all the months’ names in a sequence.

– Size of an Array: Use LBound for the array’s first index and UBound for its last index retrieval.

13. Function and Sub

– User Defined Function: Excel provides a large number of functions to perform various tasks. When built-in functions are limited, use VBA to write your own custom Excel functions easily.

– Custom Average Function: Understand how to develop a User Defined Function that selects a random range and calculates its average, while automatically excluding any outliers.

– Volatile Functions: By default, Excel VBA functions you create (UDFs) do not recalculate unless one of their arguments is changed. These are non-volatile. Volatile functions refresh on any change in the sheet, even if it’s not related.

– ByRef and ByVal: Excel VBA lets you pass arguments to procedures by value (copy) or by reference (original). The default method is by reference.

14. Application Object

– StatusBar: In Excel VBA, the StatusBar property under the Application object is useful for displaying progress messages. Learn to create a macro that updates the status bar to show its progress during execution.

– Read Data from Text File: Learn Excel VBA to read geographic coordinate files and display location data directly in Excel.

– Write Data to Text File: Use VBA to export a selected Excel range as a CSV file with comma-separated values.

– Vlookup: To perform a VLOOKUP in Excel VBA, make use of the WorksheetFunction object. You can complete this task using just a single line of VBA code.

15. ActiveX Controls

– Text Box: A text box is an empty space where users can input text. This tutorial guides you to create a functional text box using VBA.

– List Box: Use a simple VBA code to insert a list box that lets users select predefined options.

– Combo Box: A combo box allows both selecting from a dropdown and entering custom text.

– Check Box: A check box lets users choose or remove a selection by clicking a small square box. Follow these easy steps carefully to create it successfully.

– Option Buttons: When multiple option buttons exist, choosing one will automatically clear the selection of the others.

– Spin Button: Learn how to insert a spin button in your worksheet and use the spin button to increase or decrease values in a cell using Excel VBA.

– Loan Calculator: This tutorial guides you to build a basic loan calculator using Excel VBA step by step. The worksheet uses ActiveX to add two scrollbars and two option buttons.

16. Userform

– Userform and Ranges: RefEdit in Excel VBA lets you pick cells from the worksheet using a form. The UserForm highlights the cell containing the minimum value based on the user’s input.

– Currency Converter: Use Excel VBA to make a UserForm that allows users to convert between different currencies.

– Progress Indicator: This tutorial shows you how to make a basic yet professional progress indicator in Excel VBA. It’s simple to implement. Are you ready to start?

– Multiple List Box Selections: The MultiSelect property in Excel VBA enables a list box to support selecting several items at once.

– Multicolumn Combo Box: Learn how to build a VBA program in Excel that creates a UserForm with a combo box showing multiple columns.

– Dependent Combo Boxes: This article covers how to create a UserForm where one combo box changes based on the other.

– Loop through Controls: A simple loop in Excel VBA lets you access every control placed on a UserForm easily.

– Controls Collection: In Excel VBA, the Controls collection lets you loop through all controls on a UserForm and assign the same property value to each control efficiently.

– Userform with Multiple Pages: Understand how to develop a UserForm in Excel VBA that includes more than one page. This userform also contains images.

– Interactive Userform: Learn to create an interactive UserForm that improves data entry and enhances user interaction efficiently.

Visit our next section: 300 Examples.