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 from a drop-down list. As a result, the user can select an animal from a second drop-down list. The user selects Sports […]
Add the Controls | Show the Userform | Assign the Macros | Test the Userform This chapter teaches you how to create an Excel VBA Userform. The Userform we are going to create looks as follows: Add the Controls To add the controls to the Userform, execute the following steps. 1. Open the Visual Basic […]
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 […]
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 […]
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 […]
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 […]
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 […]
Range Examples | Cells | Declare a Range Object | Select | Rows | Columns | Copy/Paste | Clear | Count The Range object, which is the representation of a cell (or cells) on your worksheet, is the most important object of Excel VBA. This chapter gives an overview of the properties and methods of […]
Object Hierarchy | Collections | Properties and Methods Learn more about the Workbook and Worksheet object in Excel VBA. Object Hierarchy In Excel VBA, an object can contain another object, and that object can contain another object, etc. In other words, Excel VBA programming involves working with an object hierarchy. This probably sounds quite confusing, […]
The MsgBox is a dialog box in Excel VBA you can use to inform the users of your program. Place a command button on your worksheet and add the following code lines: 1. A simple message. Result when you click the command button on the sheet: 2. A little more advanced message. First, enter a […]
Developer Tab | Command Button | Assign a Macro | Visual Basic Editor With Excel VBA you can automate tasks in Excel by writing so called macros. In this chapter, learn how to create a simple macro which will be executed after clicking on a command button. First, turn on the Developer tab. Developer Tab […]
