Excel VBA Examples: The Four Most Common

Excel VBA is the programming language for Excel. VBA stands for Visual Basic for Applications. Many times, Excel VBA is used to automate repetitive tasks so that you don’t have to continue typing the information into the cells of the spreadsheet. Mr. Excel is a leader in Excel education and the course offered from Udemy on Excel VBA is a great way to learn Visual Basic.

Create Macros

A macro is a way to give the software instructions when you type in a specific set of keys or a shortcut, which is very helpful if you must type in the same thing over and over. Macros are used for basic functions that come with VBA so knowing the basic Excel functions is necessary.

To create a macro, you must first turn on the Developer tab in Excel. To do this, right click on the ribbon and click Customize the Ribbon. When the dialog box appears, select Main tabs from the right side, if required. Then click the box next to Developer and click OK.

The Developer tab will now be at the end of the tabs list.

Next, you need to create a command button. Click on the Developer tab and click “insert”. The Command Button will be in the ActiveX Controls group. Place your mouse on the command button and drag the button to your worksheet.

Then, you will want to assign a macro. To do this, select design mode on the Developer tab and right click on the CommandButton1. When the drop-down box appears, click View Code, which will show the Visual Basic Editor. You will need to place the cursor between the “Private Sub CommandButton1_Click()” command and  the “End Sub” command. You will also need the Project Explorer window, which shows Sheet1, Sheet2, etc. If this is not available, you can click View, Project Explorer.

Once you have the Project Explorer window open, click for which sheet you want to add code, such as Sheet1. Now, you can close the Visual Basic Editor.

Click on the CommandButton1 again, ensuring that Design Mode is not selected.

MsgBox

This is a helpful dialog box that can inform other users of what you have done. For example, if you create a math problem, you can allow the user to work the problem, and then click the command button to find the right answers. These can be tough to master, but courses like Ultimate Excel VBA cover message boxes and many other advanced VBA techniques.

First, you must place a command button on the worksheet. Then, you must add the code lines into the button. You must always start with “MsgBox” typed exactly this way. Next, you will use beginning and end quotes around the message. For example, if you put the math problem in Cell A1. Create a command button and place this message: MsgBox “Answer to Math Problem is 12.”

You can use & to join to strings or when you want to use a typed message and a range or cell. For example: MsgBox “Entered Value is “ & Range(“A1”).Value

In the above example, the command button would state: Entered Value is ___. The space would be whatever A1’s value was.

Range Object

One of the most important objects in Excel VBA is the Range Object, which is the representation of a cell on the worksheet.

For example, if you place a command button on the worksheet and use the following code: Range(“B3”).Value = 2, you would have the number two in B3’s cell.

If you use code: Range(“A1:A5”).Value = 15, you would have placed three’s in A1, A2, A3, A4, and A5.

You can also use one range object and one command button to place a number in multiple cells. For example, Range(“A1:A2,B4:C5”).Value = 20, would place the number 20 in A1, A2, B4, B5, C4 and C5.

You can also use the same concept with Cells. If you type in the code: Cells(3, 2).Value = 2, you would place the number two in B3. The first number in the parentheses, in this case three, means row three, while the two means column two.

You can also place the number five in A1, A2, A3, and A4 in a different way. By using the code: Range(Cells(1, 1), Cells(4, 1)) = 5. Cell(1, 1) would be A1 and Cells(4, 1) would be A4. Because it starts with “Range”, this would mean place the number five in all of column A, starting with one and ending with four.

You can also declare a range object by using the keywords Set and Dim. For example:

Dim example as Range

Set example = Range(“A1:C4”)

Example.Value = 8

In this example, the word example is in place of range and you have set example to be the range. You always type in “.Value” and include the value.

The above example can also be used to select a specific range. If you would type in “example.Select” after typing in the Dim and Set parameters above, you would have selected all of those cells.

Loop

This is a powerful programming technique, which allows you to go through a range of cells with one code called a loop.

A single loop is quite easy. To do this, place the command button on the worksheet and input the following lines of code:

Dim i as Integer

For i = 1 To 4

Cells(i, 1).Value = 50

Next i

The value can be set to anything you wish. When you click the command button, you will receive the number 50 in A1, A2, A3, and A4.

For i = 1 To 6: This tells Excel that there will be six executions of the value. The number one tells Excel that the value will be placed at the intersection of row one and column one, or A1. The Next command tells the program to go back to the “for” statement and continue on until it reaches the sixth execution.

A double loop can be used to loop through two columns of data. To do this, place a command button on the worksheet and add these code lines:

Dim i As Integer, j As Integer

For i = 1 To 4

For j = 1 To 2

Cells(i, j).Value = 50

Next j

Next i

The above code will place “50” in cells A1 through A4 and in B1 through B4.

The above ways to use Excel VBA are not the only ones available, but they are easy to use and very helpful.

Udemy offers many different courses on VBA that can help you learn Excel fast. With a one and two part course on VBA and Macros from Udemy, you will learn all the basics and more!