Excel VBA Course (6): Excel VBA Language Basics - Variables
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
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 |
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. |
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.
![](https://prodskill.com/wp-content/uploads/2022/09/image-87.png)
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.
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:
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.
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 >>
- Start the Excel VBA course. (Lecture notice, feat. Why we recommend Excel VBA)
- Excel VBA Course(1): Overview of Excel VBA
- Excel VBA Course (2): Excel VBA Basics
- Excel VBA Course (3): Excel Object Model
- Excel VBA Course (4): Working with Excel Object Model
- Excel VBA Course (5): Excel File Extension, VBE, Font Settings
- Excel VBA Course (6): Excel VBA Language Basics - Variables
- Excel VBA Course (7): Excel VBA Language Basics - Syntax
- Excel VBA Course (8): Excel VBA Language Basics - Data Types, Data Structures
- Excel VBA Courses (9): Excel VBA How-To
- Excel VBA Course (10): Tools developed and used with Excel VBA
- Full Table of Contents for Excel VBA Courses