How to Structure VBA Code and Macros in Excel - VBA Tutorial

When creating macros and writing VBA, it is important to logically structure your code to maximize readability and ensure your code is as efficient as can be.

Contents:

1. How to Structure Your VBA Code

1.1. Declaring Variables

1.2. Set Variable Values

1.3. Perform Calculations

1.4. Display Outputs

2. Structured Code Example

How to Structure Your VBA Code

Structuring your VBA code properly is an important part of writing efficient and readable code. Here is some advice on how to structure your VBA code, including steps for declaring variables, performing calculations, and displaying outputs.

1. Declare Your Variables

Before you can use a variable in your code, you need to declare it. This tells VBA what type of data the variable will hold (e.g. string, integer, etc.) and gives it a name. You can declare a variable using the Dim keyword, followed by the variable name and data type. For example:

Dim x As Integer

2. Set Variable Values

After declaring your variables, you can set their initial values. This can be done by using the assignment operator (=) to assign a value to the variable. For example:

x = 10

3. Perform Calculations

Once you have set the initial values of your variables, you can perform calculations using those values and generate new values that can be used in your code. This can be done using standard arithmetic operators (e.g. +, -, *, /) and other VBA functions and methods. For example:

result = x + 5

4. Display the Outputs

Finally, you can display the results of your calculations using the appropriate VBA function or method. For example, if you want to display a message in a pop-up window, you can use the MsgBox function. If you want to display the value of a variable in the Immediate window, you can use the Debug.Print method. For example:

MsgBox "The result is: " & result

Structured Code Example

Here is an example macro incorporating what we've learned above into a complete piece of code:

Sub structured_example()

    Dim x As Integer
    Dim y As Integer
    Dim result As Integer

    x = 10
    y = 5
    
    ' Add x and y
    result = x + y

    ' Subtract y from x
    result = x - y

    ' Multiply x and y
    result = x * y

    ' Divide x by y
    result = x / y

    ' Display the value of the "result" variable in a message box
    MsgBox "The result is: " & result

End Sub

This code follows the four rules and steps that we laid out in the previous section.

1. We've declared our variables and their data types using 'Dim' and 'As'

2. Next, we've assigned values to the declared variables

3. Then we're performing any necessary calculations

4. Finally, we're outputting our results.

By following these four steps, our code is laid out in a logical way that makes it easy to review and de-bug. Every step depends on the step before it, meaning if we re-arranged these steps, our code would either not run, or it would be much harder to comprehend.

There are some exceptions to this, but in general this is the most optimal way to lay out your code.