Excel VBA Courses

Excel VBA Course (6): Excel VBA Language Basics - Variables

Published September 11, 2022 · Updated October 11, 2022

In this article, we look at the contents of variables among the basics of the Excel VBA language.

This is a continuation of the previous article.

Excel VBA Course (5): Excel File Extension, VBE, Font Settings

<<Table of Contents>>

3. Excel VBA language basics

3.1. variable

Examine variable declaration and binding methods.

3.1.1. variable declaration

Declare a variable with the following syntax:

'변수 선언 문법
Dim <변수명> [As Type]

In Visual Basic, you can use a variable without declaring it, and even if you declare it, you may not specify the type.

Dim t, x

As above, there is no problem if you do not specify the type while declaring the t and x variables.

However, in order to reduce the possibility of errors or bugs and to make the code easier to read, it is recommended to observe the following.

Variables must be declared and used. (Option Explicit recommended)
The type must also be specified.
Hungarian notation, which uses the abbreviation of the type as a prefix, is applied to the variable name so that the type can be easily recognized.

'문자열 Title
Dim sTitle As String

There is no problem at all if you use Korean characters for names such as variable names, procedure names, and function names. If it is difficult to decide on a variable name or there is a need to improve readability, use Korean names actively.

The mainly used types and prefixes are as follows.

Prefix	Type	Length	Reference
s	String	10 + string length	About 2 billion characters can be expressed
i	Integer	2 bytes	Thresholds: -32,768 to 32,767
l	Long	4 bytes	Limits: -2,147,483,648 to 2,147,483,647
b	Boolean	2 bytes	True, False
o	Object		Objects such as Workbook, Worksheet, Range, and Collection

Visual Basic main variables type, prefix

An example of declaring a variable using the above prefix is as follows.

Dim s단어 As String
Dim b단어발견 As Boolean
Dim oTargetBook As Workbook
Dim oTargetSht As Worksheet

3.1.2. Variables Early binding, Late binding

The method in which the variable type is determined when it is declared is called early binding, and the method in which it is determined when it is executed is called late binding. The concepts, examples, recommendations, advantages and disadvantages of the two methods are summarized in the following table.

division	early binding	Late binding
concept	How to specify a type when declaring a variable If it is not a built-in type, add a reference to that type and specify the type	How Types Are Specified at Execution Time
example	Dim sword As String Dim oRecordSet As ADO.RecordSet	Dim sword Dim oRecordSet As Object
Recommendations	Recommended in most cases	Use only when absolutely necessary (limited to object type only)
Advantages	Automatic completion when writing code, dynamic help support (convenient) Since the type is determined at compile time, it is advantageous for performance.	If there is no reference library in the running computer, only the late bound code part is not executed.
disadvantage	If there is no reference library in the running computer, the whole will not run	Auto-completion is not supported when writing code (inconvenient) Performance is relatively poor because the type is determined at execution time and dynamically bound.

Variable Early binding, Late binding comparison

* Reference: https://docs.microsoft.com/en-us/previous-versions/office/troubleshoot/office-developer/binding-type-available-to-automation-clients

Let's look at example code for each method. This is a simple code that creates the recipient, subject, and content of an email with Outlook and displays it on the screen.

Early binding vs. Late binding example code

The example code of the early binding method is as follows.

Sub SendOLMail_EarlyBound()
    ' declare variables for the Application and Mailitem objects
    Dim oAPP                       As Outlook.Application
    Dim oItem                      As Outlook.MailItem
     
    ' instantiate the Application
    Set oAPP = New Outlook.Application
     
    ' create a new email
    Set oItem = oAPP.CreateItem(olMailItem)
     
    ' set basic properties and display the email
    With oItem
        .To = "foo@bar.com"
        .Subject = "this is a test"
        .Body = "nothing to see here"
        .Display
    End With
End Sub

The variables oApp and oItem were designated as Outlook.Application type and Outlook.MailItem type respectively when declaring them. In this case, you need to add a reference to the Outlook library as follows.

필요한 type library 참조 추가 — Add required type library references

Open “VBE > Tools > References” menu, check “Microsoft Outlook 16.0 Object Library” in “Available References” and click the OK button.

When you open the reference menu again, it should look like this:

type library 참조 추가 확인 — Check adding type library reference

If you copy the macro file (.xlsm or .xlsb) in which this VBA code is saved to a PC that does not have Outlook 16 installed and run it, an error saying that the type cannot be found occurs.

An example code for the late binding method is as follows.

Sub SendOLMail_LateBound()
    Dim oAPP                       As Object
    Dim oItem                      As Object
    ' need to declare this constant as it has no meaning without
    ' the reference set to the Outlook library
    Const olMailItem               As Long = 0

    ' instantiate the Application - cannot use New without a reference
    ' so we must use CreateObject
    Set oAPP = CreateObject("Outlook.Application")

    '이후 코드는 동일
    ' create a new email
    Set oItem = oAPP.CreateItem(olMailItem)

    ' set basic properties and display the email
    With oItem
        .To = "foo@bar.com"
        .Subject = "this is a test"
        .Body = "nothing to see here"
        .Display
    End With
End Sub

The oApp and oItem variables are designated as Object type at the time of declaration, and the object creation method is different.

Create an object with the following code on line 10.

Set oAPP = CreateObject(“Outlook.Application”)

For detailed description of the CreateObject function, refer to the following URL.

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/createobject-function

Some excerpts from the URL above are left behind.

Syntax

CreateObject(class, [ servername ])

The CreateObject function syntax has these parts:

Part	Description
class	Required; Variant (String). The application name and class of the object to create.
servername	Optional; Variant (String). The name of the network server where the object will be created. If servername is an empty string (“”), the local machine is used.

The class argument uses the syntax appname.objecttype and has these parts:

Part	Description
appname	Required; Variant (String). The name of the application providing the object.
objecttype	Required; Variant (String). The type or class of object to create.

If you copy the macro file (.xlsm or .xlsb) in which this VBA code is saved to a PC where Outlook 16 is not installed and run it, an error saying that the type cannot be found occurs as in Early binding. The difference is that in the Early binding method, all codes are not executed from the beginning if there is no reference library, and in the Late binding method, other codes operate and only the codes (procedures, functions) including CreateObject are not executed.

So far, we have looked at variable declaration and binding methods. Next, I will explain the syntax of the VBA language.

<< Related article list >>