Excel VBA Examples

Excel VBA allows the user to make macros that automate processes within Excel. VBA macros are as strong as the user’s ability to code the macros using the Visual Basic language.

Excel VBA Examples – Macros

The best way to learn to design your own Excel macro in VBA is just to jump right into it. Naturally, you’ll need to know how to enter the VBA editor and set up the macro, how to declare variables and how to reference data.  This guide will use Excel VBA examples to ensure you know how to record your own macro!

For this example, let’s assume we have the following data table. We are going to try and insert data into the cells to complete the table. For the purpose of this exercise, we are going to create a macro that will perform a function that can easily and more quickly be done manually. However, creating this macro will allow you to see how to write a macro, and the general logic behind VBA programming.

As you can see below, Turtle Dove and Capital Inc. do not have their stock prices yet. We want to fill this in. Turtle Dove has a stock price of $92.17. Surprisingly, Capital Inc. has the exact same stock price as Nikolai Motors right now.

 

Excel VBA Examples

 

To learn more, launch our online Excel VBA Course!

 

Naming the Macro

To begin creating the macro, we open the VBA Editor by pressing Alt + F11. The first step in creating a macro is naming the macro, as shown below. We’ll name the macro Stock Pricing.

Sub stockPricing()

End Sub

 

Free Excel Tutorial

To master the art of Excel, check out CFI's FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis. 

Browse all of CFI’s Excel courses to take your career to the next level!

Declaring the Variables

The very top of every macro should contain variable declarations. We are going to be using one variable here to store the value of Nikolai Motors stock price. Let’s name this variable Stock Price. Recall that the generally accepted naming convention for variables is to use lower caps if there is only one word, and to introduce every word after the first with a capital letter. Stock Price would be written as stockPrice.

 

Sub stockPricing()

 

Dim stockPrice as Single

End Sub

 

Storing Data in a Variable

Now that we’ve created the stockPrice variable, we want to store a value in it.

Sub stockPricing()

Dim stockPrice as Single

stockPrice = Range(“C4”).Value

End Sub

The macro now has two processes to perform. It creates a new Single variable (a number data type that allows non-integers) named stockPrice. Next, it stores the value found in Cell C4 to the stockPrice variable.

To learn more, launch our online Excel VBA Course!

 

Using Variables and Directly Assigning Values

Finally, we can use the variables to complete the process we need (filling in the stock price). We need to tell Excel to use the stored value and place it in the appropriate cells.

Sub stockPricing()

Dim stockPrice as Single

stockPrice = Range(“C4”).Value

Range(“C8”).Value = stockPrice

Range(“C7”).Value = 92.17

End Sub

 

After storing the value, the macro will now use the variable to fill in the table. First, it will write the value stored in stockPrice to the Cell C8. Next, it will write the directly assigned value of 92.17 into Cell C7. Both methods work.

 

Excel VBA Examples – Alternative Method

You could also skip the variable and assign the value of C4 directly to C8. This is demonstrated below.

Sub stockPricing()

Range(“C8”).Value = Range(“C4”).Value

Range(“C7”).Value = 92.17

End Sub

As you can see in this macro, no variables are used. Instead, data within Excel is directly copied into a different cell. The macro will fill Cell C8 with the value found in C4, since macro’s read from left to right. Next, it will fill Cell C7 with the number 92.17.

To learn more, launch our online Excel VBA Course!

 

Excel VBA Examples – Macro Summary

As you can tell, the VBA programming language is quite easy to follow. You simply need to write your process step by step, and the macro will follow out the process in the proper order. You may also have noticed that there are different ways to code the same process. Certain methods have its benefits, and some may work better than the other. With experience, mastering VBA will allow you to plan and create your own macros that are tailored to the specific needs of your financial model.

 

Learn More!