Crash Course Excel VBA | 15 Min Tutorial

Do you want to increase your productivity in Excel? Have heard about VBA, but don’t know where to start? Learn to program using Excel VBA in just 15 mins in this compact and feature packed course.

In this session, we will learn how to copy data from an external file and paste it into our template file.

Files are available to download from Github. 

No prior knowledge of VBA is required. However, you do need some working knowledge of Excel.

For a more comprehensive tutorial, check out my 2.5 hour Youtube video and blogpost.

Intro

VBA is a great tool to enhance productivity of any corporate analyst who spends a great portion of their time in Excel. It allows you to automate repetitive tasks, giving you options you might not have thought possible before.

This blogpost is designed to support the above Youtube video. The video contains detailed explanation and walk through. This webpage provides a summary of the basic concepts as well as a commentary on the entire code. You can also, find the full, uninterrupted code for all the macros shown in the video (at the bottom of this page – refer to the Menus).

Setup VB Editor

To access Standard toolbar, go to View in the Menu, then go to Toolbars and select Standard.

To access Project Explorer, go to View and select Project Explorer. Our code will be held inside a Module in this section. To create a Module, right click in the Project Explorer, select Insert and select Module.

Not required for this tutorial:

Properties Window, go to View and select Properties Window.

Immediate Window , go to View and select Immediate window.

First Macro

Right click in Project Explorer -> Insert -> Module

We will write our macro inside a sub procedure, which is like a container of code. The sub procedure or “sub” starts with the word Sub and ends with the words End Sub. In our first macro, we will display the a message in Excel using Msgbox command.

Sub HelloWorldMacro()
MsgBox "Hello World"
End Sub

To run the macro, keep the cursor in the sub and hit the play button.

Objects: Workbook, Worksheet and Range

Excel follows a hierarchy and each element within that hierarchy is called an object.

The most common hierarchy used in Excel VBA is:

Excel Application -> Workbook -> Worksheet -> Range

Where, Workbook is the Excel file, Worksheet is our sheets and Range is a cell or a group of cells.

To refer to a Range or Worksheet, we need to follow this hierarchy. Application need not be mentioned. Hence, we will ignore it here. Below examples show us how to refer to cell B3

When referring to an external workbook:

Workbooks("Monthly Analysis.xlsm").Sheets("Console").Range("B3").Value

When referring to the current workbook: (i.e. the file from which the macro is being called)

ThisWorkbook.Sheets("Data").Sheets("Console").Range("B3").Value

Properties and Methods

Let’s consider this B3 which is a Range object.

It has a text value, background color is Yellow, the row is of a certain height, the font is black etc. These are all its characteristics which are called properties of the object.

Let’s display the value in Cell B3.

Sub TestMacro1()
MsgBox ThisWorkbook.Sheets("Console").Range("B3").Value
End Sub

But, we can also, do something with it. We can copy its value or we can clear its contents. Both of these are methods. To put a definition to it, method is some action that we want to perform with the object.

Let’s clear contents in columns A to B in Console Sheet

Sub TestMacro2()
ThisWorkbook.Sheets("Console").Columns("A:B").ClearContents
End Sub

Variables

A variable allows us to store the value of an object or the object itself for use later.

There are 2 reasons why I use variables. One is to avoid repeating code. And second, and the most important reason is to improve readability.

Variable to store a value

A value can be e.g. the value inside a cell or the number of rows in a range. We would normally, do this to help read the code better. To use a value variable

  • Declare it using Dim statement.
  • Give name (no spaces)
  • Specify it a meaningful data type as String (Text) or Long (Number). There are many more types.
  • Assign it a values.

e.g. We are storing our folder path in cell B3. Lets assign a variable to refer to it.

Sub TestMacro3()

Dim mainFolder As String
mainFolder = ThisWorkbook.Sheets("Console").Range("B3").Value

MsgBox mainFolder

End Sub

Variable to store an object

We can also, assign variable to an object such as workbook, worksheet or range. We would normally, do this to avoid repeating code.

To use an object variable

  • Declare it using Dim statement.
  • Give it a meaningful name (no spaces)
  • Specify data type as the VBA Object it is going to be used to store.
  • Assign the object to it using Set statement.

e.g. Let’s assign a variable to store our Data worksheet object.

Now, to select a cell A1 in the Console worksheet we can directly use this variable.

Sub TestMacro4()
Dim wsCons As Worksheet
Set wsCons = ThisWorkbook.Sheets("Console")
wsCons.Range("A1").Select
End Sub

Macro Build

Check out the full uninterrupted code at the bottom.

Step 0: Initial Setup

We will be using Console and Data worksheet. Declare object variables for them.

'Declare the Object variables
Dim wsCons As Worksheet
Set wsCons = ThisWorkbook.Sheets("Console")
Dim wsData As Worksheet
Set wsData = ThisWorkbook.Sheets("Data")

Our folder path and file path are in Console sheet in cells B3 and B4. Lets declare value variables for them.

'Declare the value variables
Dim mainFolder As String
mainFolder = wsCons.Range("B3").Value
Dim importFile As String
importFile = wsCons.Range("B4").Value

We will join the folder path and the file name to create the full file path.

'Join folder path and file name to create full file path
Dim fullSourceFileName As String
fullSourceFileName = mainFolder & "\" & importFile

Step 1: Clear any previous data in columns A to E in the Data sheet.

'Step 1: Clear the data from Data sheet in our template file
wsData.Columns("A:E").ClearContents

Step 2: Open the source file using Workbooks.Open method. Since we need to interact with the file once its open (i.e. copy the data and close the workbook), we will assign an Object variable for the file.

'Step 2:  Open the source file.
Dim wb As Workbook
Set wb = Workbooks.Open(fullSourceFileName)

Step 3: Select the data range. We don’t need to select a data range in order to copy it. Hence, this step is not required.

Step 4: Copy data.

Create a range object to hold the data range which can be identified using the CurrentRegion property of the Range object.

Then, we can copy the data using the Copy method. And we will provide the starting cell of the range that we want to paste the data into.

'Step 4: Copy the Data. Ctrl C
Dim rngToCopy As Range
Set rngToCopy = wb.Sheets("Sheet1").Range("A1").CurrentRegion
rngToCopy.Copy wsData.Range("A1")

The following 3 steps are now redundant since we were able to achieve them within step 4 itself.

Step 5: Select our Monthly Analysis file
Step 6: Select cell A1.
Step 7: Ctrl V and paste data.

Step 8: AutoFit the Columns

'Step 8: AutoFit the Columns
wsData.Columns("A:E").AutoFit

Step 9: Close the source file.

We will use the Close method of the workbook object and tell Excel to not save changes as we close the file.

'Step 9: Close the source file
wb.Close SaveChanges:=False

Finally, display a message saying the procedure is complete.

MsgBox "Import Complete"

Full Macro Code

Sub ImportData()

'Step 0: Initial Setup

'Declare the Object variables
Dim wsCons As Worksheet
Set wsCons = ThisWorkbook.Sheets("Console")
Dim wsData As Worksheet
Set wsData = ThisWorkbook.Sheets("Data")

'Declare the value variables
Dim mainFolder As String
mainFolder = wsCons.Range("B3").Value
Dim importFile As String
importFile = wsCons.Range("B4").Value

'Join folder path and file name to create full file path
Dim fullSourceFileName As String
fullSourceFileName = mainFolder & "\" & importFile

'Step 1: Clear the data from Data sheet in our template file
wsData.Columns("A:E").ClearContents

'Step 2:  Open the source file.
Dim wb As Workbook
Set wb = Workbooks.Open(fullSourceFileName)

'Step 3: Select the data range

'Step 4: Copy the Data. Ctrl C
Dim rngToCopy As Range
Set rngToCopy = wb.Sheets("Sheet1").Range("A1").CurrentRegion
rngToCopy.Copy wsData.Range("A1")

'Step 5: Select our Monthly Analysis file
'Step 6: Select cell A1.
'Step 7: Ctrl V and paste data.

'Step 8: AutoFit the Columns
wsData.Columns("A:E").AutoFit

'Step 9: Close the source file
wb.Close SaveChanges:=False

MsgBox "Import Complete"

End Sub

Related Posts