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:
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
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
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
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
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
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
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
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
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
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
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:
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
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
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
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
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
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
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
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
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
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,
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
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.
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)
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
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
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.
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
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
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
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
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
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
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
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\
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
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
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 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
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
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
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
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
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:
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
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