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 the items to include in a collection so that the total value is as large as possible and the total weight is […]
Below we will look at a program in Excel VBA that calculates any term Tk and summation of terms up to N. Explanation: the user has the option to enter “All” or “Odd”, to respectively calculate the sum of the first N terms of the series or the sum of only the odd terms up […]
Below we will look at a program in Excel VBA that removes duplicates. Situation: In column A we have 10 numbers. We want to remove the duplicates from these numbers and place the unique numbers in column B. 1. First, we declare four variables. toAdd of type Boolean, uniqueNumbers of type Integer, i of type […]
Below we will look at a program in Excel VBA that randomly sorts data (in this example randomly sorts names). Situation: 1. First, we declare four variables. One variable of type String we call TempString, one variable of type Integer we call TempInteger, one variable of type Integer we call i, and one variable of […]
Below we will look at a program in Excel VBA that sorts numbers. Situation: Place a command button on your worksheet and add the following code lines: 1. First, we declare three variables of type Integer and one Range object. Dim i As Integer, j As Integer, temp As Integer, rng As Range 2. We […]
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 […]
