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, […]
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: […]
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 […]
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 […]
