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 variables of type Integer. One named i and one named j. Dim i As Integer, j As Integer 2. Second, we add […]
You can use the Step keyword in Excel VBA to specify a different increment for the counter variable of a loop. 1. Place a command button on your worksheet and add the following code lines: Dim i As Integer For i = 1 To 6 Step 2 Cells(i, 1).Value = 100Next i Result when […]
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 Loop will be repeated until the part after Do Until is true. Place a command button on your worksheet and add the […]
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 your worksheet and add the following code lines: 1. First, declare a variable called i of type Long. We use a variable […]
Below we will look at a program that loops through a defined range. For example, when we want to square the numbers in Range(“A1:A3”). Did you know you can also loop through a dynamic range? Situation: Place a command button on your worksheet and add the following code lines: 1. First, we declare two Range […]
Single Loop | Double Loop | Triple Loop | Do While Loop Looping is one of the most powerful programming techniques. A loop in Excel VBA enables you to loop through a range of cells with just a few codes lines. Single Loop You can use a single loop to loop through a one-dimensional range […]
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 the variable counter with value 0. Dim counter As Integer, i As Integercounter = 0 2. Next, we check for each cell […]
Below we will look at a program in Excel VBA that sums numbers by color. Situation: You have lended money to two twelve people. Some people have given it back (in black) and some still owe you money (red). You want to know how much money you still receive. 1. First, we declare two variables […]
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 Range objects rng and cell. One double variable we call highestValue, and one double variable we call secondHighestValue. Dim rng As Range, […]
Below we will look at a program in Excel VBA that checks whether a number is a prime number or not. Before you start: in mathematics, a prime number is a number that has exactly two distinct number divisors: 1 and itself. The smallest twenty-five prime numbers are: 2, 3, 5, 7, 11, 13, 17, […]
Below we will look at a program in Excel VBA that calculates the tax on an income. The following tax rates apply to individuals who are residents of Australia. Situation: 1. First, we declare two double variables. One double variable we call income, and one double variable we call tax. Dim income As DoubleDim tax […]
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 the command button on the sheet: Explanation: 7 is divided by 2 (3 times) to give a remainder of 1. Code line: […]
Instead of multiple If Then statements in Excel VBA, you can use the Select Case structure. Situation: Place a command button on your worksheet and add the following code lines: 1. First, declare two variables. One variable of type Integer named score and one variable of type String named result. Dim score As Integer, result […]
Logical Operator And | Logical Operator Or | Logical Operator Not 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 on your worksheet and add the following code lines: Dim score1 […]
If Then Statement | Else Statement Use the If Then statement in Excel VBA to execute code lines if a specific condition is met. If Then Statement Place a command button on your worksheet and add the following code lines: Dim score As Integer, result As String score = Range(“A1”).Value If score >= 60 Then […]
Integer | String | Double | Boolean This chapter teaches you how to declare, initialize and display a variable in Excel VBA. Letting Excel VBA know you are using a variable is called declaring a variable. Initializing simply means assigning a beginning (initial) value to a variable. Place a command button on your worksheet and […]
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 to read this example first. Situation: Note: the only unique value in this example is the 3 since all other values occur […]
This example illustrates the Areas collection in Excel VBA. Below we have bordered Range(“B2:C3,C5:E5”). This range has two areas. The comma separates the two areas. Place a command button on your worksheet and add the following code lines: 1. First, we declare two Range objects. We call the Range objects rangeToUse and singleArea. Dim rangeToUse […]
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. Place three command buttons on your worksheet and add the following code lines: 1. The code line below sets the background […]
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. Color property To change the color of an Excel range, […]
