VBA Internship Tutorial 1: The Basics

The Internship VBA Tutorial Series is an initiative by NUS Business Analytics Society. We are determined to help University Students learn fundamental VBA Skills at their own pace with a comprehensive and streamlined learning resource.

This guide is the first of a series that will benefit individuals who are learning VBA for internships, covering common use cases that we ourselves have encountered based on our own experiences. At the end of the series, individuals should have a acquired a comprehensive base of VBA skills that they can use and further refine in their chosen field of work.

Slide7

Click to download the VBA Internship Tutorial – Working File

Slide8

Landing Page
This will be the first screen that you will see upon opening the Working File. Don’t forget to like our Facebook page if you haven’t!

Capture

Using the Workbook
Click the various buttons on the “Mod” tabs to view the answers for each exercise e.g. Mod1Ex1
Click the “Shortcuts” button to navigate between exercises in the same sheet

Guiding Instructions for Modules
Guiding instructions on each Sheet will share with you each Module’s objective and instructions.
Capture 3
Capture 2

Alternatively, you may also refer below for a visual guide on what is covered inside the Training Workbook as well as how to conduct first time setup of Excel to enable VBA Macros (Part 1 of the Visual Guide), but you are highly advised to also try it out for yourself.

Areas covered in this tutorial:

  1. Introduction to VBA
  2. Sub Routine Testing
  3. Variables
  4. Assigning Values to Cells
  5. Concatenation
  6. If Statements
  7. Loops

Visual Guide

1. Introduction to VBA

VBA stands for Visual Basic for Applications, a variation of its more comprehensive and powerful sibling, Visual Basic or VB. With VBA, one can automate routines tasks in Microsoft Office Applications such as Excel, PowerPoint and Word. For the purposes of this tutorial series, we will be focusing on its applications in Excel.

1.1 Initial Setup

Unless you have already been using VBA previously, you will need to enable the developer tab to be able to program in VBA.

For Excel on Windows:  File Tab > Options > Customize Ribbon > “Check” Developer (The gif below provides you with a video guide on the process)

Tutorial1 01 Setup.gif

1.2 Recording Macros

Recording macros allow you to record your actions and review how VBA records them in its own language. They are an intermediate solution to automate processes for those who may not know how to automate the tasks that they are seeking to automate. The 2 gifs below provide you with a visual guide on how this is done, and

Tutorial1 09 Record Macro

Tutorial1 10 Run Macro.gif

1.3 Developer Interface

Now that we know how to record our first VBA Macros, we need to understand how to manoeuvre our way inside the VBA Developer Interface to create and modify existing/recorded VBA Macros for our needs.

Developer Tab > Code Category > Visual Basic Button

Developer Tab

This will open a new Developer Window as seen below:

Developer Window

A workbook is represented by “VBAProject” in the project explorer. Each project will have both Microsoft Excel Objects and Modules.

  • Microsoft Excel Objects: Created automatically whenever you create new worksheets
  • Modules: Right click on project explorer (any space) > insert > module

A code can be written in both Microsoft Excel Objects & Modules. Code written in Sheets will disappear when Sheets are deleted and can only be used on that sheet. For this reason, it is usually preferable to create codes in Modules because they are not sheet specific, meaning that they will not disappear with the deletion of any sheets and can be used anywhere within the workbook.

Double Click on an object or module in the project explorer to open the relevant code window, for example double clicking on Module 2 will bring up its code window as shown below:

Module 2 Window

FYI: These are the codes used by the buttons that are used by our training file.

2. Sub Routine Testing

While VBA is useful in helping to automate tasks, it also poses a danger as executing VBA Macros removes the option to “Undo” changes made to the Workbook. This means that mistakes could prove costly and time consuming to rectify if data is accidentally deleted or overwritten. Do make up a backup copy of the Data File before playing around with VBA. If you have written a VBA code, you need to save your Excel file in the *.xlsm format.

Press Ctrl + G (or View>Immediate Window) to open the Immediate Window. The Immediate Window saves you from switching between the Code Editor & the workbook. You can output values to the immediate window to check the code, using the Debug.Print function. Try the following out!

Sub Mod2Ex2()
     Debug.Print "Hello World"
End Sub

This will output the phrase “Hello World” into an Immediate Window as seen below:

Immediate Window

Codes inside the VBA window can be executed completely, or by line. You can run through the entire code by placing your cursor in the code and pressing the play button (shortcut: F5)

Tutorial1 12 F5.jpg

Alternatively, you may also wish to run the code line by line. This is often useful in debugging large complex pieces of code, especially if they involve multiple references, loops, and conditional statements.

Tutorial1 13 F8.jpg

3. Variables

Variables allow us to store a variety of values within our VBA code depending on their type, which can range from a phrase, whole numbers and even decimal numbers. Some of the common variables are Integers, Strings, Double, Float, and Boolean.

They allow us to to store multiple values which we can then use later on in our code for purposes ranging from calculations to conditional statements. For this part, we will only be focusing on Integers and Strings. Integers store whole numbers, while Strings hold text (A-Z, a-z, 0-9)

To create a variable, we first need to define it, as seen below:

Sub Mod2Ex3()

Dim a as String
a = "Hello World"
Debug.Print a

End Sub

By building upon the previous section on Sub Routine Testing, we can now define our string as the phrase “Hello World”. In doing so, we can now “print” the string simply by calling out its variable “a”.

Note that for Integers, it is not necessary to input the values with the quotes (“). This is only required for text (also known as Strings).

4. Assigning Values to Cells

Having tested our outputs and assigned values to variables, we shall now look at how to assign values to cells in our Workbook.

Sub Mod2Ex4()

Dim a As String
a = "Hello World"
Cells(4,2).Value = a

End Sub

In the code above, we defined a variable “a” to have a string of text, and then we assigned a cell to have that value using the Cells function in VBA. The Cells function follows a specific format,  namely:

Cells (row number, column number)

This means that we are referring to Cells B4 in this case, because B4 corresponds to the intersection of row 4, column 2.

Another use of cell referencing is to define the values of variables as the value of a specific cell, for example:

Sub Mod2Ex4()

Dim a As String
a = Cells(3,2).Value
Cells(4,2).Value = a

End Sub

5. Concatenation

Concatenation refers to the joining of values, between 2 or more separate variables or cell values into a single variable or value. To do this, we need to use “&” in our VBA code to tell the program that we wish to combine the initial stated value with another. An example is shown below:

Concatenate

As we can see from our example, we combined our 2 variables “a”, which refers to a cell with the value “Hello World”, with b using the “&” function. The Presence of ” “, or blank space in our concatenation of the 2 variables specifies that we want a spacing between the 2 strings of text. By default, there is no spacing given when combining variables. Using the Debug.Print line, we have now produced an output in our immediate window as shown below:

Concatenate Debug Print

A similar value is of course, exported to the actual excel sheet:

Concatenate Sheet

6. If Statements

If statements are conditional statements that specify when certain sections of VBA code are to be executed. In simple terms, if the condition is true, then execute the corresponding action. The format for an if statement in VBA is as follows:

If <condition to be met is true> Then
     <Code to be executed>
Else
     <Alternative code to be executed if condition is not met>
End If

An example can be seen below:

IF

Here, we specify that if the value of B8 matches our variable, we will input the string “Welcome back Merlyn” into cell B4. If it does not match, then we will input the string “Hello Merlyn” instead.

Note that the “Else” line is optional. In its absence, the program will simply not execute any code, and the cell will remain blank if the condition is not met.

Based on our sheet as shown below, we have entered the string “Welcome back Merlyn” into B4, because as we can see, the value in B8 is “Merlyn”, and therefore, the condition is met.

IF Excel

7. Fixed Range Loops

Loops are a function in VBA that allow us to cycle a section of code multiple times through either a fixed or dynamic range of cells in out Excel Sheets. They enable us to save time and file space when we have to run the same code on multiple rows or columns within a sheet.

In this section, we will be focusing on fixed range loops, or loops that are to be executed on a predefined area within our sheets. Fixed Range Loops are useful in situations where you know the number of cells that the code has to be executed on will not change from one iteration of the Workbook to another. Fixed Range Loops generally have the following format:

Dim <Integer Name> as Integer

For <Integer Name> = <Starting Number> to <Max Number> Then
     <Code to be Executed>
Next <Integer Name>

In our example below, we define 2 integers, with “i” referencing the row of our output cell and “a” referencing the row of out conditional cell. On its own, “For a = 8 To 10” has no real meaning, but by combining it with cell referencing, it allows us to specify that the If Statement is to be executed on cells B8 to B10.

Our next step is to check whether our condition is met. “<>” refers to not equal to. Here, if the conditional cell does not contain the word “Merlyn”, then cell B12 will concatenate the string “Hello World” with the value in the initial conditional cell. Otherwise, we use the string “Welcome Back”.

At the end of our if statement, we increase the integer “i” value by 1 using “i = i + 1” to move the input cell down 1 row to B13, and we use “Next a” to allow the program to execute the If Statement on the next cell, which is B9.

For Loop VBA

This process is repeated until the program has been executed on cell B10, upon which it will cease as that marks the end of the Fixed Range Loop. This gives us the values as shown below based on the values in the corresponding cells:

For Loop Sheet

These features are explored in greater detail in the working file, which you can use to gain a greater understanding of the topics covered in this section. In the next part of our series, we will be looking at how to import and export data between workbooks, generate and refresh PivotTables, and create Dynamic Range Loops.

This tutorial was put together by Goh Jun Hui and Ganesh Muthupalani.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s