How to Create, Modify, and Display VBA Procedures in the Window (Excel)

Written by Kasper Langmann

Visual Basic for Applications (VBA) is a powerful tool that allows you to automate tasks in Microsoft Excel. It’s a programming language that’s built into Excel, and it’s used to create macros, or sets of instructions, that Excel can execute. In this guide, we’ll explore how to create, modify, and display VBA procedures in the Excel window.

Understanding VBA Procedures

A VBA procedure is a set of instructions that tells Excel what to do. There are two types of procedures in VBA: Sub procedures and Function procedures. Sub procedures perform actions but don’t return a value, while Function procedures perform actions and return a value. Procedures are stored in modules, which are containers for code.

Before you can create a procedure, you need to open the VBA editor. You can do this by pressing Alt + F11 in Excel. This will open a new window where you can write and edit VBA code.

It’s important to note that VBA is a case-insensitive language, meaning that it doesn’t distinguish between uppercase and lowercase letters. This can be helpful when writing code, as you don’t need to worry about capitalization errors.

Creating a VBA Procedure

Creating a VBA procedure involves writing a set of instructions in the VBA editor. Let’s look at how to create a simple Sub procedure.

Writing a Sub Procedure

To create a Sub procedure, you start by typing the word “Sub” followed by the name of your procedure. For example, you might name your procedure “MyFirstProcedure”. After the name, you add a pair of parentheses and then a line break. Your code should look something like this:

Sub MyFirstProcedure()

End Sub

Between the “Sub” and “End Sub” lines, you write the instructions that you want Excel to execute. For example, you might want Excel to display a message box. Your code would look like this:

Sub MyFirstProcedure()
    MsgBox "Hello, world!"
End Sub

Once you’ve written your procedure, you can run it by pressing F5 in the VBA editor. This will execute the instructions in your procedure.

Writing a Function Procedure

Creating a Function procedure is similar to creating a Sub procedure, but with a few key differences. A Function procedure returns a value, so you need to specify the type of value that it returns. You do this by adding “As” followed by the type of value after the parentheses. For example, if your function returns a number, you would write “As Double”.

Here’s an example of a Function procedure that calculates the square of a number:

Function SquareNumber(x As Double) As Double
    SquareNumber = x * x
End Function

Just like with a Sub procedure, you can run a Function procedure by pressing F5 in the VBA editor.

Modifying a VBA Procedure

Once you’ve created a VBA procedure, you might need to modify it. This could involve adding, removing, or changing instructions. To modify a procedure, you simply open the VBA editor, find the procedure that you want to modify, and make your changes.

For example, you might want to modify the “MyFirstProcedure” Sub procedure to display a different message. To do this, you would change the text inside the quotation marks in the MsgBox instruction.

Here’s what the modified procedure might look like:

Sub MyFirstProcedure()
    MsgBox "Hello, Excel!"
End Sub

After you’ve made your changes, you can run the procedure again to see the results.

Displaying a VBA Procedure

To display a VBA procedure in the Excel window, you can use the Call statement. The Call statement runs a procedure and then returns control to the procedure that called it.

For example, you might want to display the “MyFirstProcedure” Sub procedure in the Excel window. To do this, you would write a Call statement like this:

Call MyFirstProcedure

This would run the “MyFirstProcedure” Sub procedure and display the message box in the Excel window.

In conclusion, VBA procedures are a powerful tool for automating tasks in Excel. By understanding how to create, modify, and display these procedures, you can greatly enhance your Excel skills and efficiency.