VBA

A collection of 102 posts

How To Use VBA Controls Collection In Excel

When creating Userforms in Excel VBA, you can use the Controls collection to easily loop through controls and set a property of each control to a specific value. The Userform we are going
24 October 2020

How To Use VBA Do Until Loop In Excel

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
23 October 2020

How To Use VBA Delete Blank Cells In Excel

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
23 October 2020

How To Use VBA Debugging In Excel

This example teaches you how to debug code in Excel VBA. Single Step By pressing F8, you can single step through your code. The is very useful because it allows you to see
23 October 2020

How To Use VBA Currency Converter In Excel

Below we will look at a program in Excel VBA which creates a Userform that converts any amount from one currency into another. The Userform we are going to create looks as follows:
23 October 2020

How To Use VBA Create a Pattern In Excel

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
23 October 2020

How To Use VBA Create a Footer Before Printing

Below we will look at a program in Excel VBA that creates a footer before printing a workbook. Create a Workbook BeforePrint Event. Code added to the Workbook BeforePrint Event will be executed
23 October 2020

How To Use VBA Count Words In Excel

Below we will look at a program in Excel VBA that counts the number of words in a selected range. One or more spaces are assumed to separate words. Situation: 1. First, we
23 October 2020

How To Use VBA Dynamic Range In Excel

Below we will look at a program in Excel VBA that colors the maximum value of a dynamic range. Situation: Each time we add a number and we click the command button, we
23 October 2020

How To Use VBA Compare Ranges In Excel

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
22 October 2020

How To Use VBA Handling Error In Excel

Below we will look at two programs in Excel VBA. One program simply ignores errors. The other program continues execution at a specified line upon hitting an error. Situation: Both programs calculate the
21 October 2020

How To Use Knapsack Problem In Excel

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
21 October 2020

How To Use Interactive Userform In Excel

Below we will look at a program in Excel VBA that creates an interactive Userform. The Userform we are going to create looks as follows: Explanation: whenever you enter a value in the
21 October 2020

How To Use InputBox Function In Excel

You can use the InputBox function in Excel VBA to prompt the user to enter a value. Place a command button on your worksheet and add the following code lines: 1. First, declare
21 October 2020

How To Use Highlight Active Cell in Excel

Below we will look at a program in Excel VBA that highlights the row and column of the Active Cell (selected cell). This program will amaze and impress your boss. Situation: Each time
21 October 2020

How To Use Excel VBA Error Handling

If you are not familiar with Error Handling yet, we highly recommend you to read this example first. When an error in Excel VBA occurs, the properties of the Err object are filled
21 October 2020

How to import data In Excel VBA

Below we will look at a program in Excel VBA that imports sheets from other Excel files into one Excel file. Download Book4.xlsx, Book5.xlsx and add them to “C:\test\
21 October 2020

How To Use Loan Calculator In Excel

This page teaches you how to create a simple loan calculator in Excel VBA. The worksheet contains the following ActiveX controls: two scrollbars and two option buttons. Note: the instructions below do not
20 October 2020

How to use the Message Box Function in Excel

The MsgBox function in Excel VBA can return a result while a simple MsgBox cannot. Situation: Place a command button on your worksheet and add the following code lines: 1. First, we declare
19 October 2020

How To Use Mod Operator In Excel

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
19 October 2020

How To Generate Month Names In Excel

Below we will look at a program in Excel VBA which creates a User Defined Function that uses the Array function to return the names of the months. User defined functions need to
19 October 2020