Learning materials for Excel VBA
Learning materials for Excel VBA
A excel with VBA that able to export content to json and a C# enum class file
A excel with VBA that able to export content to json and a C# enum class file
How To Use VBA Convert to Proper Case In Excel
Below we will look at a program in Excel VBA that converts text to proper case. That is, the first letter in each word in uppercase, and all other letters in lowercase. Situation:
How To Use VBA Controls Collection In Excel
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. The Userform we are going
How To Use VBA Complex Calculations In Excel
The kth term, Tk, of a certain mathematical series is defined by the following formula: Tk = k2 + 6k + 1 9k + 7 The first term, T1, of the series is obtained by substituting k
How To Use VBA Select The Current Region In Excel
This example illustrates the CurrentRegion property in Excel VBA. The current region is a range bounded by any combination of blank rows and blank columns. Can you find the current region of cell
How To Use VBA Dynamic Array In Excel
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
How To Use VBA Do Until Loop In Excel
Although not used very often on this site, you might find yourself in a situation where you want to use the Do Until Loop in Excel VBA. Code placed between Do Until and
How To Use VBA Dependent Combo Boxes In Excel
Below we will look at a program in Excel VBA which creates a Userform that contains dependent combo boxes. The Userform we are going to create looks as follows: The user selects Animals
How To Use VBA Delete Blank Cells In Excel
Below we will look at a program in Excel VBA that deletes blank cells. Situation: 1. First, we declare two variables of type Integer. One named counter and one named i. We initialize
How To Use VBA Delay a Macro In Excel
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. 1. Open the Visual Basic Editor and click
How To Use VBA Debugging In Excel
This example teaches you how to debug code in Excel VBA. Single Step By pressing F8, you can single step through your code. The is very useful because it allows you to see
How To Use VBA DateDiff Function In Excel
The DateDiff function in Excel VBA can be used to get the number of days between two dates. Place a command button on your worksheet and add the following code lines: Dim firstDate
How To Use VBA Custom Average Function In Excel
Below we will look at a program in Excel VBA which creates a User Defined Function that calculates the average of a randomly selected range excluding one or more values that are outliers
How To Use VBA Currency Converter In Excel
Below we will look at a program in Excel VBA which creates a Userform that converts any amount from one currency into another. The Userform we are going to create looks as follows:
How To Use VBA Create a Pattern In Excel
Below we will look at a program in Excel VBA that creates a pattern. Situation: Place a command button on your worksheet and add the following code lines: 1. First, we declare two
How To Use VBA Create a Footer Before Printing
Below we will look at a program in Excel VBA that creates a footer before printing a workbook. Create a Workbook BeforePrint Event. Code added to the Workbook BeforePrint Event will be executed
How To Use VBA Count Words In Excel
Below we will look at a program in Excel VBA that counts the number of words in a selected range. One or more spaces are assumed to separate words. Situation: 1. First, we
How To Use VBA Dynamic Range In Excel
Below we will look at a program in Excel VBA that colors the maximum value of a dynamic range. Situation: Each time we add a number and we click the command button, we
How To Use VBA Compare Ranges In Excel
Below we will look at a program in Excel VBA that compares randomly selected ranges and highlights cells that are unique. If you are not familiar with areas yet, we highly recommend you
How To Use VBA Compare Dates and Times In Excel
This example teaches you how to compare dates and times in Excel VBA. Dates and times are stored as numbers in Excel and count the number of days since January 0, 1900. What
How To Use VBA Combo Box In Excel
A combo box is a drop-down list from where a user can select an item or fill in his/her own choice. To create a combo box in Excel VBA, execute the following
How To Use VBA Close and Open workbooks In Excel
The Close and Open Method in Excel VBA can be used to close and open workbooks. Remember, the Workbooks collection contains all the Workbook objects that are currently open. Place a command button
How To Use VBA Range.Font property In Excel
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
How To Use VBA List Files in a Directory In Excel
Below we will look at a program in Excel VBA that loops through all closed workbooks and worksheets in a directory, and displays all the names. Download Book1.xlsx, Book2.xlsx, Book3.xlsx,
How To Use VBA Handling Error In Excel
Below we will look at two programs in Excel VBA. One program simply ignores errors. The other program continues execution at a specified line upon hitting an error. Situation: Both programs calculate the
How To Use VBA From Active Cell to Last Entry In Excel
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.
How To Use VBA Formula VS Formula R1C1 in Excel
This example illustrates the difference between A1, R1C1 and R[1]C[1] style in Excel VBA. 1. Place a command button on your worksheet and add the following code line (A1 style)
How To Use VBA Find Second Highest Value In Excel
Below we will look at a program in Excel VBA that finds the second highest value. Situation: 1. First, we declare two Range objects and two variables of type Double. We call the
How To Use VBA Entire Rows and Columns In Excel
This example teaches you how to select entire rows and columns in Excel VBA. Are you ready? Place a command button on your worksheet and add the following code lines: 1. The following
How To Use VBA Check Box In Excel
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. 1. On the Developer tab, click Insert. 2.
How To Use Variable Scope and Variable Lifetime In Excel
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. 1. Place a command button on your
How To Use Knapsack Problem In Excel
Below we will look at a program in Excel VBA that solves a small instance of a knapsack problem. Definition: Given a set of items, each with a weight and a value, determine
How To Use Interactive Userform In Excel
Below we will look at a program in Excel VBA that creates an interactive Userform. The Userform we are going to create looks as follows: Explanation: whenever you enter a value in the
How To Use InputBox Function In Excel
You can use the InputBox function in Excel VBA to prompt the user to enter a value. Place a command button on your worksheet and add the following code lines: 1. First, declare
How To Use Highlight Active Cell in Excel
Below we will look at 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. Situation: Each time
How To Use Excel VBA Error Handling
If you are not familiar with Error Handling yet, we highly recommend you to read this example first. When an error in Excel VBA occurs, the properties of the Err object are filled
How To Stop a Macro from Running in Excel
You can interrupt a macro in Excel at any time by pressing Esc or Ctrl + Break. Place a command button on your worksheet and add the following code lines: Dim x As Long
How to import data In Excel VBA
Below we will look at a program in Excel VBA that imports sheets from other Excel files into one Excel file. Download Book4.xlsx, Book5.xlsx and add them to “C:\test\
How To Use Loan Calculator In Excel
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. Note: the instructions below do not
How To Create A List box In Excel
A list box is a list from where a user can select an item. To create a list box in Excel VBA, execute the following steps. On the Developer tab, click Insert. In
How to using Macro Recorder in Excel
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
How To Using logical operators and functions in Excel
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. Logical Operator And Place a command button
How to use the Message Box Function in Excel
The MsgBox function in Excel VBA can return a result while a simple MsgBox cannot. Situation: Place a command button on your worksheet and add the following code lines: 1. First, we declare
How to Use The Macro Security in Excel
To set up the macro security settings in Excel, execute the following steps. 1. On the Developer tab, click Macro Security. 2. Here you have four options. The first option will disable all
How To Use Mod Operator In Excel
The Mod operator in Excel VBA gives the remainder of a division. Place a command button on your worksheet and add the following code line: MsgBox 7 Mod 2 Result when you click
How To Use Macro Comments In Excel's Vba
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. To insert a
How to Use Loop through Books and Sheets in Excel
Below we will look at a program in Excel VBA that loops through all open workbooks and worksheets, and displays all the names. Situation: Add the following code lines to the command button:
How To Use Excel VBA For Loop through Entire Column
Below 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. Place a command button on
How To Generate Month Names In Excel
Below we will look at a program in Excel VBA which creates a User Defined Function that uses the Array function to return the names of the months. User defined functions need to