VBA Terms – Option Explicit , Option Base, Set , New

Commonly used terms in VBA Excel Code

This post aims to provide insights about the terms which we come across in most of the VBA codes, while looking out for a solution of a problem. Having the basic understanding of these terms can be very handy while writing or debugging code.

Option Explicit

It is the statement defined at the top of the code. It makes it mandatory to declare all the variables, which are used in the code. 

Option Base {0 | 1 }

Option Base is the statement which modifies the subscript of an array. By default, it is 0, while using the Option Base 1 statement the initial subscript value for an array will become 1.

Option Base 0

Option Base 0
Sub Demo()
Dim CountryArray
Dim CountryValue
CountryArray = Array("NZ", "UK", "US", "AU")
CountryValue_1 = CountryArray(0)    ' MyDay contains "NZ".
CountryValue_2 = CountryArray(1)    ' MyDay contains "UK".
CountryValue_3 = CountryArray(2)    ' MyDay contains "US".
End Sub

 

Option Base 1

Option Base 1
Sub Demo()
Dim CountryArray
Dim CountryValue
CountryArray = Array("NZ", "UK", "US", "AU")
‘CountryValue_1 = CountryArray(0)    ' Will throw an error if uncommented
CountryValue_2 = CountryArray(1)    ' MyDay contains "NZ".
CountryValue_3 = CountryArray(2)    ' MyDay contains "UK".
End Sub

 

Here the array lower bound subscript will start at 1 , not at zero. It means CountryArray(0) will not exist.

Nullifying the impact of Option Base

It is to be noted that even if Option Base statement 1 is placed in the code, we can keep or change the initial value of subscript in an array. If we explicitly declare the subscript value of an array, then in that case subscript value will not be affected by Option Base 1. You can see below, that we have set the initial subscript value of array as 2 and set the Option Base 1 in the code, but still code will not throw error.

Option Base 1
Sub Demo()
Dim CountryArray(2 To 5)
CountryArray(2) = "UK"
CountryArray(3) = "USA"
CountryArray(4) = "NZ"
CountryArray(5) = "AUS"
'CountryArray(6) = "AUS" It will throw error if uncommented
End Sub

 

New

New keyword creates a new instance of an object, for example Recordset is a object of ADODB library. Here New key word is creating an instance of recordset object. While variable  MyRecSet has reference to newly created recordset object.

Dim MyRecSet as New ADODB.Recordset
Set MyRecSet  = New  ADODB.Recordset

Object Variables

In VBA Excel object variables are those variables which refer to an object.In simple terms the object variable refers an object, like worksheet , shape etc. While the regular variable stores the values, which are of intrinsic data type [ integer, Boolean, float].

Set Statement

Set Statement creates a reference to an object. As you can see in the code below, object variable MyNewSheet belongs to worksheet class. So here object MyNewSheet expression, holds a reference to the worksheet which is added by the statement ThisWorkbook.Worksheets.Add.

 

Sub MyWorkSheet()
Dim MyNewSheet As Worksheet
Set MyNewSheet = ThisWorkbook.Worksheets.Add
MyNewSheet.Name = "TestWorksheet"
Sheets("TestWorksheet").Select
End Sub

 

Multiple Object Variables

Multiple object variables can be created for same object, here we are creating two object variables MyWsObj1 and MyWsObj2 which refer to same worksheet, which is sheet1. Both the object variables are modifying the value of same cell, which implies that more than one object variable can modify the property of a same Range object, which is Range(“A1”).value here. Eventually the message box will show the recently updated value in cell A1.

 

Sub MultipleObjectVariables()

Dim MyWsObj1 As Worksheet
Dim MyWsObj2 As Worksheet

Set MyWsObj1 = Worksheets("Sheet1")
Set MyWsObj2 = Worksheets("Sheet1")

MyWsObj1.Range("A1").Value = 10
MyWsObj2.Range("A1").Value = 20
MsgBox Range("A1").Value

End Sub

 

 

Variant Data Type

 A variable with the variant as data type can contain value or object of any type, be it integer, string or shape. It only has limitation with fixed length string type.

Code 1 is equivalent to Code 2

Code 1

Sub Demo()
Dim MyFirstVar, MySecondVar As String , MyThirdVar As Integer
End Sub

 

Code 2

Sub Demo()
Dim MyFirstVar As Variant
Dim MySecondVar As String
Dim MySecondVar As Integer
End Sub

 

Range of the data type in variant keeps on changing based upon the value assigned to variable. Change of data type can be observed in watch window.

 

If we look in the image below we can see that when the variable MyVar was declared as variant, which means that it can contain any type of variable.So, when we define the value of MyVar as 1, then its data type can be seen as integer in local window. Shown below.

 

vba variant data type

 

At the same time when we store the value 2000000 in MyVar, its data type is changes to double, which can be seen in the image below.It means that the type of variant variable can change based upon the value stored in it.

vba variant data type

Reference

https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/option-explicit-statement
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/option-base-statement
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/set-statement

Post you may like

Add a table on slide from embedded excel file in PowerPoint VBA

1 comment on “VBA Terms – Option Explicit , Option Base, Set , New

Comments are closed.