A beginner’s and intermediate guide to VBA programming

VBA or Visual Basic for Applications is Microsoft’s implementation of their event driven programming language available within Excel and some of Microsoft other office products such as Word, PowerPoint etc. The programming language also comes with an integrated development environment (IDE) that provides access to key functionality that you will use to develop your programs.

During this session we will be reviewing some of the lessons learned from years of using VBA to develop custom program solutions. My first approach is always to try and accomplish a task using standard Excel functionality that are available to any regular user before I crack open the code book. Using standard functionality makes the solution easier to support by someone without any prior coding experience so that you may not have to be the one to support that program indefinitely. Also, why spend the time developing a program if Microsoft have already provided that standard capability to you for your use.

However, when you do actually have to develop a program, do not take any shortcuts. Always assume that the project will grow in scope and complexity and that you will be the one supporting the application. Use copious amounts of comments and plan you design before you even start writing a single line of code. Also, do not hesitate to leverage and retool other programs that you have written to accomplish your tasks and remember that the internet provides a wealth of examples and documentation that you can use.

During this session we will review the general layout of the integrated development environment (IDE) that Microsoft provides within their Visual Basic for Applications (VBA) programming language.

We will review the Project Explorer, used to navigate between the various Excel objects such as workbooks and worksheets and then move onto the Properties Window. We will also look at the debug or immediate window and provide a cursory review of the menus that are available.

Overview of the File Menu, specifically focused on learning how to:

1. Save a project file
2. Export files from your project for future use
3. Import files into your existing project to accelerate your development efforts

Overview of the Edit Menu, specifically focused on learning how to:

1. Deploy bookmarks to navigate within your program code
2. Find and replace text within a function and project
3. Use the standard text capabilities such as copy, cut, paste, undo and redo

Overview of the View Menu, specifically focused on learning how to:

1. Utilize the Object Browser to navigate code libraries
2. Toggle between the VBA editor and the Excel application
3. Access and configure the various editor tools

Overview of the View Menu, specifically focused on learning how to:

1. Utilize the Object Browser to navigate code libraries
2. Toggle between the VBA editor and the Excel application
3. Access and configure the various editor tools

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

Overview of the Insert Menu, specifically focused on learning how to:

1. Insert Procedures, Modules, UserForms and Class Modules into your project
2. Rename Modules using the properties window

Overview of the Format Menu, specifically focused on learning how to:

1. Insert a UserForm into your project and adding a couple of controls
2. Select and change focus on any selected controls
3. Resize and align controls
4. Equalize vertical and horizontal spacing

Review the basic capabilities of the Debug, Run and Tools menus, including how to reference new code libraries from within your current VBA project.

During this session we will be reviewing a practical example of how to debug your program using the key concepts we covered in the previous training session. We will review practical examples of how you will:

1. Compile a program prior to code execution
2. Step into, out of and over code execution
3. Learn how to toggle watch expressions
4. Learn how to employ breakpoints to pause code execution for debugging purposes
5. Learn how to utilize the watch window to monitor the variables tagged with watch expressions

During this session we will be reviewing a practical example of how to debug your program using the key concepts we covered in the previous training session. We will review practical examples of how you will:

1. Compile a program prior to code execution
2. Step into, out of and over code execution
3. Learn how to toggle watch expressions
4. Learn how to employ breakpoints to pause code execution for debugging purposes
5. Learn how to utilize the watch window to monitor the variables tagged with watch expressions

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

During this training session we will be reviewing our progress to date and what you can expect from the next part of this training course.

In summary, we have covered the basics of VBA programming; lessons learned and reviewed the layout and main menu options available with the VBA integrated development environment (IDE).

Going into the next section we will be focusing on the basic functions available to control program flow in you projects. We will start off by reviewing various presentations describing the key concepts, functionality and syntax to use and then go into reviewing practical examples for each of the concepts covered.

During this session we will review the various variable data types available within VBA. Specifically we will be reviewing the key characteristic of each variable and the value ranges or limits enforced.

We close this training session by looking at the concepts associated with the constant variable data types.

During this session we will review a practical example of how to declare and initialize variables prior to using them.

The variable’s scope dictates its accessibility to your code statements within your project. Within this session we will review a practical example of how the concept of variable scope works, which includes the review of public, private and local variables.

The variable’s scope dictates its accessibility to your code statements within your project. Within this session we will review a practical example of how the concept of variable scope works, which includes the review of public, private and local variables.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

Constant variables are similar to variables in that they are used to store values. However constant values are defined prior to program execution and cannot be changed during code execution. Within this session we will review a practical example of how to declare and use constant variable values within your project and discuss the general rule of thumb of when to deploy them within your project.

Functions and sub procedures consist of a series of VBA statements enclosed ether within a function statement (Function…End Function) or a sub procedure statement (Sub…End Sub). Within this session we will review the main differences and uses of both sub procedures and functions.

Passing and returning values to Functions is critical to developing complex, modular and self-contained programs. During this session we will review practical examples of how to pass and return values from Functions using the default ByVal method.

Passing and returning values to Functions is critical to developing complex, modular and self-contained programs. During this session we will review practical examples of how to pass and return values from Functions using the default ByVal method.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

Building upon the previous session, we will review a practical example of how to use the ByRef method to pass variable references to functions. Passing variables by reference ensures that any changes made to those variables within the receiving function are instantly reflected within those variables.

A sub procedure consists of a series of VBA statements enclosed within a sub procedure statement (Sub…End Sub). Within this session we will review a practical example of how to declare and use a sub procedure within your project.

During this session we will review the IF statement, which is used to execute a group of VBA statements based on the determination of True or False based on a defined expression.

Syntax:

1. IF…Then…End IF
2. IF…Then…Else…End IF
3. IF…Then…ElseIF…End IF

During this session we will review a practical example of how construct and use a basic IF statement.

During this session we will build on the previous IF statement example and review a practical example of using the ElseIF statement to enhance a basic IF statement and include multiple conditional statements.

The Select Case statement is similar to the If statement in that it is used to evaluate a condition for True or False and then allows you to execute a series of VBA statements based on that determination.

The Select Case statement share the basic functionality as a basic IF statement. During this session we will review a practical example of using a Select Case statement to execute code statements based on list of defined expressions.

Loop statements are used to cycle through and repeat a series of VBA statements based on a predefined number of times. During this session we will review the For...Next, Do…While and Do…Until loops.

Syntax:

1. For…Next
2. Do…While
3. Do…Until

During this session we will review a couple of practical examples of using the For…Next, Do…While and Do…Until loop statements.

An Exit statement is used to break out of the current code execution. It can be used to break out of Functions, Procedures, Loop statements, IF statements etc. During this session we will review the basic uses and capabilities of the Exit statement.

During this session we will review some practical examples of how to terminate code execution using the Exit statement.

The GoTo Statement is predominantly used to implement error handling routines to your program. During this session we will review the GoTo statement, which is used to move code execution to a defined location within your program.

During this session we will review a practical example of using the GoTo statement to move code execution within a program.

Enumeration data types provide the convenience way of controlling the values assigned to your variables. During this session we will review the enumeration data type and the syntax used.

During this session we will review a practical example of defining and using an enumeration data type to control the values assigned to your variables.

The DoEvents statement is used to control CPU processing, in order to free up computing resources to avoid the Excel application becoming unresponsive when executing resource intensive functions and procedures.

During this session we will review how to use the DoEvents statement to release computer resources to enhance the overall user experience and to avoid your Excel project file from becoming ‘Unresponsive’.

During this session we will review a practical example of adding basic error handling routines to your program.

During this session we will review a practical example of using the macro recorder to create a sub procedure.

An array is a group of variables of the same data type. During this session we will review the basic functionality of an array, including a review of the main differences between an one dimensional and multi-dimensional array.

During this session we will review a practical example of how to declare and work with an array.

During this session we will review a practical example of how to use the following key statements to process and interrogate array objects. Specifically we will review how to:

1. Use the LBound and UBound to determine the upper and lower limits of your defined array

During this session we will review a practical example of how to use the following key statements to process and interrogate array objects. Specifically we will review how to:

1. Use the LBound and UBound to determine the upper and lower limits of your defined arrays

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

During this session we will be reviewing a practical example of how to populate an array using the Split function including deleting the values currently stored within an array using the Erase statement.

Mastering the Excel objects, properties, methods and events provided within the Excel reference code library is critical to becoming an effective programmer. It also allows you to identify potential solutions to code problems based on your determination on how you can use that available functionality to your benefit and use.

During this session, we will be reviewing how to navigate the Object Browser to review the basic objects, properties, methods and events available to you.

Review a practical example of accessing general Excel objects contained within the Excel code library.

Classes are used to deploy custom Excel objects within your projects, which in turn allow you to leverage the basic principles of object orientated programming. Using classes allow you to define unique properties, methods and events for your objects and help simplify and your code.

During this training session we will review a practical example of how to build a custom Excel object, including properties and methods using Excel classes.

During this training session we will review a practical example of how to build a custom Excel object, including properties and methods using Excel classes.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

During this training session we will review a practical example of how to build a custom Excel object, including properties and methods using Excel classes.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

Excel provides a number of statements that can be combined to develop some advance string manipulation routines. During this session we will review the use of the following statements:

1. Trim – remove leading and trailing spaces
2. LTrim – remove the leading spaces
3. RTrim – remove the trailing spaces
4. Right – used to extract a substring from a string starting from the right
5. Left – used to extract a substring from a string start from the left
6. Instr – used to return the position of the first character match
7. InstrRev – used to return the last position of the character match

During this training session we will review an example of performing advance string manipulation routines using a combination of the Trim, LTrim, LTrim, Right, Left, Instr and IntrRev statements.

During this training session we will review an example of performing advance string manipulation routines using a combination of the Trim, LTrim, LTrim, Right, Left, Instr and IntrRev statements.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

During this training session we will review an example of performing advance string manipulation routines using a combination of the Trim, LTrim, LTrim, Right, Left, Instr and IntrRev statements.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

During this training session we will continue on our previous example of building complex and advance string manipulation statements.

During this training session we will continue on our previous example of building complex and advance string manipulation statements.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

UserForms can be used to control and validate user input. During this training session we will review a practical example of adding a UserForm to your project.

UserForms can be used to control and validate user input. During this training session we will review a practical example of adding a UserForm to your project.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

During this training session we will continue to build on the previous example, review a practical example of adding input validation to your custom user form.

During this training session we will be reviewing our progress to date and what you can expect from the last part of this training course. In summary, to date we have covered:

1. The basics of VBA programming
2. Lessons learned
3. Reviewed the general layout of capabilities of the VBA editor
4. Reviewed how to control VBA program flow using VBA statements

During the last part of this training course we will focus on reviewing additional examples to help drive the concepts home.

During this training session we will review an example of copying text within a worksheet based on pre-defined routines.

During this training session we will review an example of copying text within a worksheet based on pre-defined routines.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

During this session we will be reviewing an example of merging text using the Excel range objects.

During this session we will be reviewing an example of merging text using the Excel range objects.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

During this session we will be reviewing an example of merging text using the Excel range objects.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

During this training session we will be reviewing an example of creating a user defined function to remove text from a string based on user input.

During this training session we will be reviewing a practical example of how to access text files for input and output.

During this training session we will be reviewing a practical example of how to access text files for input and output.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

During this training session we will review a practical example of how to remove duplicate values from an array using an Excel dictionary object.

During this training session we will review a practical example of how to remove duplicate values from an array using an Excel dictionary object.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

During this training session we will be looking at a more advance example of using the concepts of object orientated programming using custom objects. We will also review how to use collections and assign objects to each other.

During this training session we will be looking at a more advance example of using the concepts of object orientated programming using custom objects. We will also review how to use collections and assign objects to each other.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

During this training session we will be looking at a more advance example of using the concepts of object orientated programming using custom objects. We will also review how to use collections and assign objects to each other.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

During this training session we will be reviewing what you have covered during this training course.