4 VBA Basics

Visual Basic for Applications (VBA) is a programming language that allows you to automate tasks in Microsoft Office applications, such as Excel, Word, and PowerPoint. VBA is a powerful tool that can help you save time and increase productivity by automating repetitive tasks, creating custom functions, and building user interfaces.

In this chapter, we will cover the basics of VBA programming, including how to write your first VBA program, use comments, and print output to the Immediate Window and message boxes.

4.1 How Programming Languages Work

Programming languages are used to write instructions that tell a computer what to do. These instructions are written in a specific syntax that the computer can understand and execute. When you write code in a programming language, you are essentially giving the computer a set of commands to follow.

Programming languages can be classified based on several criteria, including their level of abstraction, how they are executed, and how they handle data types. In the following, we will discuss some of the key characteristics of programming languages and how they apply to VBA.

High-Level vs. Low-Level Languages

Programming languages can be classified into two main categories based on their level of abstraction and complexity:

High-Level Languages: These languages are closer to human language and are easier to read and write. Examples include Python, Java, and VBA. These languages are designed to be user-friendly and abstract away the complexities of the underlying hardware.
Low-Level Languages: These languages are closer to machine language and are more difficult to read and write. Examples include Assembly language and machine code. These languages are designed to interact directly with the hardware and are used for system-level programming. Their syntax is more complex and requires a deeper understanding of computer architecture.

Therefore, VBA is a high-level language, allowing users to write code in a more human-readable format.

Interpreted vs. Compiled Languages

Programming languages can also be classified based on how they are executed by the computer:

Interpreted Languages: In interpreted languages, the code is executed line by line by an interpreter. The interpreter reads the code, translates it into machine code, and executes it immediately. Examples include Python, JavaScript, and Ruby.
Compiled Languages: In compiled languages, the code is translated into machine code by a compiler before execution. The compiler reads the entire code, checks for errors, and generates an executable file that can be run independently of the source code. Examples include C, C++, and Java.
Hybrid Languages: Some languages, such as VBA, are a combination of interpreted and compiled. When you write VBA code in Excel, the code is stored in the workbook file. When you run the code, Excel compiles the code into an intermediate language called P-code (pseudo-code) and stores it in the workbook. The P-code is then interpreted and executed by the VBA interpreter.

Therefore, VBA is a half-compiled and half-interpreted language.

What is a Compiler?

A compiler is a program that translates source code written in a high-level programming language (such as VBA) into machine code that can be executed by the computer. The compiler checks the syntax of the code and generates an executable program if there are no errors.

Strongly Typed vs. Weakly Typed Languages

Programming languages can also be classified based on how they handle data types:

Strongly Typed Languages: In strongly typed languages, variables must be declared with a specific data type, and the type of a variable cannot change during execution. This helps prevent errors and ensures that variables are used correctly. Examples include Java, C++, and VBA.
Weakly Typed Languages: In weakly typed languages, variables do not need to be declared with a specific data type, and the type of a variable can change during execution. This can lead to errors if variables are used incorrectly. Examples include JavaScript, PHP, and Python.

VBA supports both strong typing and weak typing, depending on how you declare variables. You can enforce strong typing by explicitly declaring the data type of variables, or you can use implicit typing to allow variables to change type dynamically.

Static vs. Dynamic Typing

Programming languages can also be classified based on how they handle data types at compile time and runtime:

Static Typing: In statically typed languages, the data type of variables is checked at compile time. This helps catch errors early and ensures that variables are used correctly. Examples include Java, C++, and C#.
Dynamic Typing: In dynamically typed languages, the data type of variables is checked at runtime. This allows for more flexibility but can lead to errors if variables are used incorrectly. Examples include Python, JavaScript, and Ruby.

VBA is a statically typed language, meaning that the data type of variables is checked at compile time. This helps prevent errors and ensures that variables are used correctly in your code.

What is a Variable?

A variable is a named storage location in memory that holds a value. Variables are used to store data that can be manipulated and changed during program execution. Variables have a name, a data type, and a value. By using variables, you can store and work with different types of data in your programs.

You can think of a variable as a container that holds a value. It is a bin or a box that you can put things in and take things out of. For example, you can create a variable called counter to store a number, or a variable called message to store text. Variables are an essential concept in programming and are used to store and manipulate data in your code.

What is a Data Type?

A data type is a classification that specifies which type of value a variable can hold. Data types define the size, range, and operations that can be performed on the variable. For example, an integer data type can hold whole numbers, while a string data type can hold text. Data types help ensure that variables are used correctly and efficiently in a program.

4.2 Why Learn VBA?

Learning VBA can provide several benefits, including:

Accessibility: VBA is integrated with Microsoft Office applications, making it easy to use and learn for users familiar with Excel, Word, and PowerPoint.
Automation: VBA allows you to automate repetitive tasks in Excel, Word, and PowerPoint, saving you time and increasing productivity. With Macro recording, you can record and play back actions to automate tasks.
Customization: VBA enables you to create custom functions and procedures that extend the functionality of Microsoft Office applications.
Integration: VBA can be used to integrate data and processes across different Office applications, allowing you to work more efficiently.
User Interfaces: VBA allows you to create custom user interfaces, forms, and dialog boxes that enhance the user experience in Office applications.
Data Analysis: VBA can be used to perform complex data analysis, manipulate data, and generate reports in Excel.

4.3 Editing VBA Code

Visual Basic for Applications (VBA) code is written in the Visual Basic Editor (VBE) within Microsoft Office applications. VBE is an integrated development environment (IDE), a software application that provides comprehensive facilities to computer programmers for software development. IDEs typically include a source code editor, build automation tools, and a debugger.

We are going to enable the editor in Excel, but VBA can be used in conjunction with any Microsoft Office application. Follow the steps below to enable the editor.

IDEs for VBA

While the Visual Basic Editor (VBE) is the primary editor for VBA code, you can also use other editors to write and edit VBA code. For example, you can extend the capabilities of the VBE with third-party tools like Rubberduck, which provides additional features for VBA development, such as code inspections, refactorings, and unit testing. If you want increase your productivity and enhance your VBA development experience, consider exploring these tools.

Step 1. Install Microsoft Excel

The Microsoft Office 365 is available for UT students; you can access (via Cloud) and/or install:

Word
Excel
PowerPoint
Teams
Outlook
OneDrive

Learn more about obtaining MS Office 365 (Excel) at: https://www.utwente.nl/en/lisa/faq/office365-faq/.

Step 2. Enable the Developer Tab

To code in VBA, you have to enable the Developer tab in Excel to access the Visual Basic Editor (VBE). Follow these steps to enable the Developer tab:

On the File tab of Excel, choose Options to open the Options dialog box.
Choose Customize Ribbon on the left side of the dialog box.
Under Choose commands from on the left side of the dialog box, select Popular Commands.
Under Customize the Ribbon on the right side of the dialog box, select Main Tabs in the drop-down list box, and then select the Developer checkbox.
Choose OK.

4.4 First VBA Program

In the following example, we will write a simple VBA program that displays a message box with the text “Hello, World!” when executed.

Steps:

Open the VBA editor in Excel.
- Way 1: Press Alt + F11.
- Way 2: Go to Developer > Visual Basic (make sure the Developer tab is visible in the ribbon).
Insert a new module.
- Right-click on the VBAProject (e.g., VBAProject (Book1)) in the Project Explorer window.
- Select Insert > Module.
- A new module will be added to the project.

Write the following code:

Sub HelloWorld()
    MsgBox "Hello, World!"
End Sub

Position the cursor inside the HelloWorld subroutine.
Press F5 to run the program or click the Run button in the toolbar.
Save your workbook as a macro-enabled workbook (.xlsm).

Figure 4.1: Creating a Hello World Program in VBA. After creating a module, we rename it to HelloWorld and add the code MsgBox “Hello, World!” to display a message box with the text “Hello, World!”. The program is executed by pressing F5 or clicking the Run button.

The program consists of:

Sub Procedure: A Sub procedure called HelloWorld. Sub procedures are used to define a block of code that performs a specific task such as displaying a message box. The Sub keyword is followed by the name of the procedure (HelloWorld), and the code block is enclosed in Sub and End Sub statements.
MsgBox Function: Displays a message box with the text “Hello, World!”. Functions in VBA are called using the function name followed by parentheses containing any required arguments. In this case, the MsgBox function is used to display a message box with the specified text—enclosed in double quotes ("Hello, World!"). The MsgBox fuction is a built-in function in VBA (i.e., it is part of the VBA language).

The .xlsm File Format

When you save a workbook that contains VBA code, you must save it in the macro-enabled workbook format (.xlsm). This format allows the workbook to contain macros and VBA code. If you save the workbook in a different format (e.g., .xlsx), the VBA code will be removed from the workbook and you will lose your code.

4.5 Configuring the VBA Editor

Before writing VBA code, it’s important to configure the Visual Basic Editor (VBE) to suit your preferences. The VBE provides various options and settings that can be customized to enhance your coding experience.

4.5.1 VBE Window Layout

The VBE window is divided into several panes that help you navigate and edit your VBA code. The main panes in the VBE window are:

Project Explorer: Displays the project structure, including workbooks, modules, and forms. To display the Project Explorer, press Ctrl + R, or go to View > Project Explorer.

Figure 4.2: The Project Explorer.
Code Window: Displays the VBA code for the selected module or form. To display the Code Window, double-click a module or form in the Project Explorer, or press F7.

Figure 4.3: The Code Window.
Toolbar: Contains buttons for common tasks such as running code, saving files, and editing code. To display the toolbar, right-click the toolbar area and select the desired toolbar.

Figure 4.4: The toolbar.
Menu Bar: Contains menus for accessing various VBE commands and options. The menu bar is located at the top of the VBE window.

Figure 4.5: The menu bar.

Additional Panes

Additional panes that can be displayed in the VBE window include:

Immediate Window: Allows you to execute VBA statements interactively. To display the Immediate Window, press Ctrl + G, or go to View > Immediate Window.
Properties Window: Displays properties for the selected object or control. To display the Properties Window, press F4, or go to View > Properties Window.
Locals Window: Displays local variables and their values during code execution. To display the Locals Window, press Ctrl + Shift + L, or go to View > Locals Window.

The Immediate, Properties, and Locals Windows require a more advanced understanding of VBA and will be covered in later chapters.

4.6 Comments

Comments are used to document code and provide explanations for future reference. They are ignored by the compiler and are not executed as part of the program.

4.6.1 Why Use Comments?

Comments are essential for the following reasons:

Clarify Code: Comments help explain your code, making it easier to understand later or for others.
Reminders: Use comments to remind yourself why certain decisions were made in the code.
Debugging: Comments can help identify issues or bugs in the code.
Documentation: Comments serve as documentation for the codebase, explaining how it works and why certain choices were made.
Collaboration: Comments facilitate collaboration by allowing multiple developers to understand and work on the same codebase.
Maintenance: Comments make it easier to maintain and update code in the future.
Compliance: Comments can help ensure that code complies with standards and best practices.
Learning: Comments can be used as a learning tool for beginners to understand how code works.
Testing: Comments can be used to temporarily disable code for testing purposes.
Organization: Comments help organize code into logical sections and provide structure.

Figure 4.8: Code should ideally be self-explanatory, but comments can provide additional context and explanations for future reference.

4.6.2 Syntax

In VBA, comments are indicated by an apostrophe (') at the beginning of a line or after a statement. Comments can also be added at the end of a line of code. In Listing 4.1, the comments are highlighted in green. Note that, if an apostrophe is used inside quotation marks, it is treated as part of the text and not as a comment.

Listing 4.1: Example of comments in VBA code. Comments are indicated by an apostrophe (') at the beginning of a line or after a statement. Comments are ignored by the compiler and are not executed as part of the program. Comments can be used to provide explanations, document code, and add context to the codebase.

'This is a comment, it will not be executed
'The procedure below aims to demonstrate the use of comments in VBA'
Sub Comment()
    'This will not run, it is a comment
    'Comments are ignored by the compiler, they are "grayed out"
    MsgBox "This will run because it is not a comment"
    MsgBox "This will also run, even though there is a comment at the end" 'This is another comment
    MsgBox "This will run too, although there is an apostrophe (') in the text"
    'MsgBox "This will not run because it is a comment"
End Sub

In VBA, the purpose of a Sub or Function is commonly described in a comment above the procedure declaration. For example:

'Procedure to calculate the sum of two numbers
Sub CalculateSum()
    'Code to calculate the sum
End Sub

4.6.3 Commenting Guidelines

Identify Yourself: Include your name as the author in comments for future reference.
Procedure Descriptions: Briefly describe the purpose of each Sub or Function.
Track Changes: Use comments to note changes in your code.
Explain Unusual Code: Comment on nonstandard or unique code constructs.
Describe Variables: Add comments for variable descriptions if names aren’t self-explanatory.
Document Workarounds: Note any workarounds for Excel bugs.
Comment As You Code: Write comments during development, not at the end.

4.7 Conventions for Writing VBA Code

Consistent coding conventions make your code more readable and maintainable. Follow these guidelines when writing VBA code:

Indentation: Use consistent indentation to make the code structure clear. Tip: Use the Tab key to indent code blocks (select multiple lines to indent all at once), and the Shift + Tab keys to unindent.
Naming Conventions: Use meaningful names for variables, functions, and procedures. In VBA, use camelCase for variable names and PascalCase for function and procedure names (e.g., myVariable, MyFunction, MyProcedure). For constants, use UPPERCASE_WITH_UNDERSCORES (e.g., MY_CONSTANT, ANOTHER_CONSTANT).
Modularity: Break down code into smaller, reusable functions and procedures. Refactor code to improve readability and maintainability.
Lenghth of Lines: Keep lines of code short (around 80 characters) to improve readability. Break long lines into multiple lines if necessary. Use line continuation characters (_) to split lines.