Take your Excel VBA skills to the next level! 🚀 You can find related examples and features on the right side of each chapterat the bottom of each chapter. Below you can find a complete overview.
1. Create a Macro
- Swap Values: This example teaches you how to swap two values in Excel VBA. You will often need this structure in more complicated programs as we will see later.
- Run Code from a Module: As a beginner to Excel VBA, you might find it difficult to decide where to put your VBA code. This example teaches you how to run code from a module.
- Macro Recorder: The Macro Recorder, a very useful tool included in Excel VBA, records every task you perform with Excel. All you have to do is record a specific task once. Next, you can execute the task over and over with the click of a button.
- Use Relative References: By default, Excel records macros in absolute mode. However, sometimes it is useful to record macros in relative mode.
- FormulaR1C1: This example illustrates the difference between A1, R1C1 and R[1]C[1] style in Excel VBA.
- Add a Macro to the Toolbar: If you use an Excel macro frequently, you can add it to the Quick Access Toolbar. This way you can quickly access your macro.
- Enable Macros: Enable macros in Excel when the message bar appears. Change your macro security settings in the Trust Center. To create macros, turn on the Developer tab.
- Protect Macro: Just like you can password protect workbooks and worksheets, you can password protect a macro in Excel from being viewed (and executed).
2. MsgBox
- MsgBox Function: The MsgBox function in Excel VBA can return a result while a simple MsgBox cannot.
- InputBox Function: You can use the InputBox function in Excel VBA to prompt the user to enter a value.
3. Workbook and Worksheet Object
- Path and FullName: The Path property in Excel VBA returns the complete, saved path to the workbook (Excel file). The FullName property in Excel VBA returns the complete, saved path, including the name of the workbook.
- Close and Open: The Close method in Excel VBA is used to close workbooks. The Open method allows you to open existing workbooks.
- Loop through Books and Sheets: We will look at a program in Excel VBA that loops through all open workbooks and worksheets, and displays all the names.
- Sales Calculator: Learn how to create a program in Excel VBA that calculates the total sales of each employee over a period of three years.
- Files in a Directory: Use Excel VBA to loop through all closed workbooks in a directory and display the names of their worksheets.
- Import Sheets: In this example, we will create a VBA macro that imports sheets from other Excel files into one Excel file.
- Programming Charts: Use Excel VBA to create two programs. One program loops through all charts on a sheet and changes each chart to a pie chart. The other program changes some properties of the first chart.
4. Range Object
- CurrentRegion: You can use the CurrentRegion property in Excel VBA to return the range bounded by any combination of blank rows and blank columns.
- Dynamic Range: We will look at a program in Excel VBA that colors the maximum value of a dynamic range.
- Resize: The Resize property in Excel VBA makes a range a specific number of rows and columns smaller or larger. The Resize property always takes the top left cell of a range as the starting point.
- Entire Rows and Columns: This example teaches you how to select entire rows and columns in Excel VBA. Are you ready?
- Offset: The Offset property in Excel VBA takes the range which is a particular number of rows and columns away from a certain range.
- From Active Cell to Last Entry: This example illustrates the End property of the Range object in Excel VBA. We will use this property to select the range from the Active Cell to the last entry in a column.
- Union and Intersect: The Union method in Excel VBA returns a Range object that represents the union of two or more ranges.
- Test a Selection: This program in Excel VBA uses the Count property, IsNumeric function, IsEmpty function and Intersect method to test a selection.
- Font: The Font property of the Range object in Excel VBA gives access to a lot of other properties. That is because the Font property returns an object itself; the Font object. The Font object has many properties like the Color property and the Bold property.
- Background Colors: Changing background colors in Excel VBA is easy. Use the Interior property to return an Interior object. Then use the ColorIndex property of the Interior object to set the background color of a cell.
- Sort a Range: You can use the Sort method in Excel VBA to sort ranges in Excel. This article provides clear and practical examples to help you get started with sorting ranges using Excel VBA.
- Areas Collection: This page illustrates the Areas collection in Excel VBA.
- Compare Ranges: Learn how to create a program in Excel VBA that compares randomly selected ranges and highlights cells that are unique.
5. Variables
- Option Explicit: We strongly recommend to use Option Explicit at the start of your Excel VBA code. Using Option Explicit forces you to declare all your variables.
- Variable Scope: The scope of a variable in Excel VBA determines where that variable may be used. You determine the scope of a variable when you declare it. There are three scoping levels: procedure level, module level, and public module level.
- Life of Variables: Sometimes you want to retain the value of a variable in Excel VBA when a procedure ends. You can achieve this by using the Static keyword.
- Type Mismatch: The type mismatch error in Excel VBA occurs when you try to assign a value to a variable that isn't of the correct type.
6. If Then Statement
- Logical Operators: The three most used logical operators in Excel VBA are: And, Or and Not. As always, we will use easy examples to make things more clear.
- Select Case: Instead of multiple If Then statements in Excel VBA, you can use the Select Case structure.
- Tax Rates: We will look at a program in Excel VBA that calculates the tax on an income.
- Mod Operator: The Mod operator in Excel VBA gives the remainder of a division.
- Prime Number Checker: Learn how to create a program in Excel VBA that checks whether a number is a prime number or not.
- Find Second Highest Value: Use two If Then Statements in Excel VBA to find the second highest value in a randomly selected range.
- Sum by Color: Learn how to create a program in Excel VBA that sums numbers by color.
- Delete Blank Cells: In this example, we will create a VBA macro that deletes blank cells. First, we declare two variables of type Integer.
7. Loop
- Loop through Defined Range: Use Excel VBA to loop through a defined range. For example, when we want to square the numbers in the range A1:A3.
- Loop through Entire Column: We will look at a program in Excel VBA that loops through the entire first column and colors all values that are lower than a certain value.
- Do Until Loop: VBA code placed between Do Until and Loop will be repeated until the part after Do Until is true.
- Step Keyword: You can use the Step keyword in Excel VBA to specify a different increment for the counter variable of a loop.
- Create a Pattern: You can create a pattern of colors on your worksheet by using a double loop, the Step keyword and the Offset property in Excel VBA.
- Sort Numbers: In this example, we will create a VBA macro that sorts numbers. First, we declare three variables of type Integer and one Range object.
- Randomly Sort Data: We will look at a program in Excel VBA that randomly sorts data (in this example randomly sorts names).
- Remove Duplicates: Use Excel VBA to remove duplicates. In column A we have 10 numbers. We want to remove the duplicates from these numbers and place the unique numbers in column B.
- Complex Calculations: Learn how to create a program in Excel VBA that calculates any term Tk and summation of terms up to N.
- Possible Football Matches: We will look at a program in Excel VBA that shows a print preview of all the possible football matches from a list of teams.
- Knapsack Problem: Learn how to create a program in Excel VBA that solves a small instance of a knapsack problem.
8. Macro Errors
- Debugging: This example teaches you how to debug code in Excel VBA.
- Error Handling: Use Excel VBA to create two programs. One program simply ignores errors. The other program continues execution at a specified line upon hitting an error.
- Err Object: When an error in Excel VBA occurs, the properties of the Err object are filled with information.
- Interrupt a Macro: You can interrupt a macro in Excel at any time by pressing Esc or Ctrl + Break.
- Subscript Out of Range: The 'subscript out of range' error in Excel VBA occurs when you refer to a nonexistent collection member or a nonexistent array element.
- Macro Comments: A macro comment is a piece of text in a macro which will not be executed by Excel VBA. It is only there to provide you information about the macro.
9. String Manipulation
- Separate Strings: Let's create a program in Excel VBA that separates strings. Place a command button on your worksheet and add the following code lines.
- Reverse Strings: We will look at a program in Excel VBA that can reverse strings.
- Convert to Proper Case: This page illustrates how to create a program in Excel VBA that converts text to proper case. That is, the first letter of each word is in uppercase, and all other letters are in lowercase.
- InStr: Use InStr in Excel VBA to find the position of a substring in a string. The InStr function is quite versatile.
- Count Words: Learn how to create a program in Excel VBA that counts the number of words in a selected range. One or more spaces are assumed to separate words.
10. Date and Time
- Compare Dates and Times: This example teaches you how to compare dates and times in Excel VBA.
- DateDiff Function: The DateDiff function in Excel VBA can be used to get the number of days, weeks, months or years between two dates.
- Weekdays: We will look at a program in Excel VBA that calculates the number of weekdays between two dates.
- Delay a Macro: To execute a sub in Excel VBA after a specific time, use onTime, Now and the TimeValue function. First, place a sub into a module.
- Year Occurrences: Learn how to create a program in Excel VBA that counts the number of year occurrences.
- Tasks on Schedule: Use Excel VBA to set the background color of tasks that are on schedule to green, and the background color of tasks that are behind schedule to red.
- Sort Birthdays: Let's create a program in Excel VBA that sorts birthdays by months first and days second (so we ignore years).
- Date Format: Changing the date format in Excel VBA is easy. Use the NumberFormat property in Excel VBA to set the desired date format.
11. Events
- BeforeDoubleClick Event: Code added to the Worksheet BeforeDoubleClick Event will be executed by Excel VBA when you double click a cell on a worksheet.
- Highlight Active Cell: Learn how to create a program in Excel VBA that highlights the row and column of the Active Cell (selected cell). This program will amaze and impress your boss.
- Create a Footer Before Printing: We will look at a program in Excel VBA that creates a footer before printing a workbook.
- Bills and Coins: Use Excel VBA to create a program that splits an amount of money into bills and coins.
- Rolling Average Table: Learn how to create a program in Excel VBA that creates a rolling average table.
12. Array
- Dynamic Array: If the size of your array increases and you don't want to fix the size of the array, you can use the ReDim keyword. Excel VBA then changes the size of the array automatically.
- Array Function: The Array function in Excel VBA can be used to quickly and easily initialize an array.
- Month Names: You can use the Array function in Excel VBA to return the names of the months.
- Size of an Array: To get the size of an array in Excel VBA, use the UBound function and the LBound function.
13. Function and Sub
- User Defined Function: Excel has a large collection of functions. In most situations, those functions are sufficient to get the job done. If not, you can use Excel VBA to create your own function.
- Custom Average Function: Learn how to create a User Defined Function that calculates the average of a randomly selected range excluding one or more values that are outliers and shouldn't be averaged.
- Volatile Functions: By default, UDFs (User Defined Functions) in Excel VBA are not volatile. They are only recalculated when any of the function's arguments change. A volatile function will be recalculated whenever calculation occurs in any cells on the worksheet.
- ByRef and ByVal: You can pass arguments to a procedure (function or sub) by reference or by value. By default, Excel VBA passes arguments by reference.
14. Application Object
- StatusBar: The StatusBar property of the Application object in Excel VBA can be used to indicate the progress of a lengthy macro. This way, you can let the user know that a macro is still running.
- Read Data from Text File: Use Excel VBA to read data from a text file. This file contains some geographical coordinates we want to import into Excel.
- Write Data to Text File: Use Excel VBA to write an Excel range to a CSV (comma-separated-values) text file.
- Vlookup: Use the WorksheetFunction property in Excel VBA to access the VLOOKUP function. All you need is a single code line.
15. ActiveX Controls
- Text Box: A text box is an empty field where a user can fill in a piece of text. To create a text box in Excel VBA, execute the following steps.
- List Box: Use Excel VBA to place a list box on your worksheet. A list box is a list from where a user can select an item.
- Combo Box: A combo box is a drop-down list from where a user can select an item or fill in his/her own choice.
- Check Box: A check box is a field which can be checked to store information. To create a check box in Excel VBA, execute the following steps.
- Option Buttons: If you have more than one option button, only one of the option buttons can be selected.
- Spin Button: A spin button can be used to increment a number in a cell.
- Loan Calculator: This page teaches you how to create a simple loan calculator in Excel VBA. The worksheet contains the following ActiveX controls: two scrollbars and two option buttons.
16. Userform
- Userform and Ranges: You can use a RefEdit control in Excel VBA to get a range from a user. The Userform we are going to create colors the minimum value of the range stored in the RefEdit control.
- Currency Converter: Use Excel VBA to create a Userform that converts any amount from one currency into another.
- Progress Indicator: Learn how to create a progress indicator in Excel VBA. We've kept the progress indicator as simple as possible, yet it looks professional. Are you ready?
- Multiple List Box Selections: The MultiSelect property in Excel VBA allows a user to select multiple items in a list box.
- Multicolumn Combo Box: Learn how to create a program in Excel VBA which creates a Userform that contains a multicolumn combo box.
- Dependent Combo Boxes: This page illustrates how to create a Userform that contains dependent combo boxes.
- Loop through Controls: With just a few lines of code, we can easily loop through controls on an Excel VBA Userform.
- Controls Collection: When creating Userforms in Excel VBA, you can use the Controls collection to easily loop through controls and set a property of each control to a specific value.
- Userform with Multiple Pages: Learn how to create a program in Excel VBA which creates a Userform that contains multiple pages. This userform also contains images.
- Interactive Userform: Learn how to create an interactive Userform!
Visit our next section: 300 Examples.
