A text box is an empty field where a user can fill in a piece of text. To create a text box in Excel VBA, execute the following steps. 1. On the Developer tab, click Insert. 2. In the ActiveX Controls group, click Text Box. 3. Drag a command button and a text box on […]
Learn how to create ActiveX controls such as command buttons, text boxes, list boxes etc. To create an ActiveX control in Excel VBA, execute the following steps. 1. On the Developer tab, click Insert. 2. For example, in the ActiveX Controls group, click Command Button to insert a command button control. 3. Drag a command […]
Below we will look at a program in Excel VBA that writes an Excel range to a CSV (comma-separated-value) text file. Situation: Place a command button on your worksheet and add the following code lines: 1. First, we declare a variable called myFile of type String, an object called rng of type Range, a variable […]
Below we will look at a program in Excel VBA that reads data from a text file. This file contains some geographical coordinates we want to import into Excel. Situation: 1. First, download the text file and add it to “C:test” Place a command button on your worksheet and add the following code lines: 2. […]
The StatusBar property of the Application object in Excel VBA can be used to indicate the progress of a lengthy macro. This way, you can let the user know that a macro is still running. Situation: The macro we are going to create fills Range(“A1:E20”) with random numbers. Add the following code lines to the […]
WorksheetFunction | ScreenUpdating | DisplayAlerts | Calculation The mother of all objects is Excel itself. We call it the Application object. The application object gives access to a lot of Excel related options. WorksheetFunction You can use the WorksheetFunction property in Excel VBA to access Excel functions. 1. For example, place a command button on […]
You can pass arguments to a procedure (function or sub) by reference or by value. By default, Excel VBA passes arguments by reference. As always, we will use an easy example to make things more clear. Place a command button on your worksheet and add the following code lines: Dim x As Integerx = 10 […]
By default, UDF’s (User Defined Functions) in Excel VBA are not volatile. They are only recalculated when any of the function’s arguments change. A volatile function will be recalculated whenever calculation occurs in any cells on the worksheet. Let’s take a look at an easy example to explain this a bit more. 1. Open the […]
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 and shouldn’t be averaged. Situation: User defined functions need to be placed into a module. 1. Open the Visual Basic Editor and […]
Function | Sub The difference between a function and a sub in Excel VBA is that a function can return a value while a sub cannot. Functions and subs become very useful as program size increases. Function If you want Excel VBA to perform a task that returns a result, you can use a function. […]
One-dimensional Array | Two-dimensional Array An array is a group of variables. In Excel VBA, you can refer to a specific variable (element) of an array by using the array name and the index number. One-dimensional Array To create a one-dimensional array, execute the following steps. Place a command button on your worksheet and add […]
Workbook Open Event | Worksheet Change Event Events are actions performed by users which trigger Excel VBA to execute code. Workbook Open Event Code added to the Workbook Open Event will be executed by Excel VBA when you open the workbook. 1. Open the Visual Basic Editor. 2. Double click on This Workbook in the […]
Year, Month, Day of a Date | DateAdd | Current Date and Time | Hour, Minute, Second | TimeValue Learn how to work with dates and times in Excel VBA. Place a command button on your worksheet and add the code lines below. To execute the code lines, click the command button on the sheet. […]
Join Strings | Left | Right | Mid | Len | Instr In this chapter, you’ll find the most important functions to manipulate strings in Excel VBA. Place a command button on your worksheet and add the code lines below. To execute the code lines, click the command button on the sheet. Join Strings We […]
This chapter teaches you how to deal with macro errors in Excel. First, let’s create some errors. Place a command button on your worksheet and add the following code lines: x = 2 Range(“A1”).Valu = x 1. Click the command button on the sheet. Result: 2. Click OK. The variable x is not defined. Because […]
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 […]
