Logo
×

VBA Dim

What is Dim?

DIM or Dimension or Declare in Memory is a keyword that is used in VBA to declare a variable with the different data types (Integer, String, variable, Boolean, Double, etc.) and allocate the storage space as per the specified data type. With the help of Dim, we can also declare either the inbuilt class structure or can declare one created by ourselves. The variable declared with Dim can be used anywhere in the VBA code and all Dim statements are used at the beginning of each Sub or Function procedure.

In any programming language, declaring a variable means specifying the application regarding the variable that we want to use later. For instance, if we're going to declare any variable with the integer data type, it signifies we can only store integer values in it that particular variable else for any other value apart from int it would throw a type mismatch error.

In VBA, Dim statements are of four types which are as follows: 

  1. Basic variable
  2. Variant
  3. Object
  4. Array

Basic variable – The Basic variable holds value at a time. It uses the commonly used data types in every programming language such as Integer, String, Long, Boolean, Double, Currency, Data, etc.,

Variant – The variant is used when the data type of the variable is not known prior and is decided by the VBA application at runtime. It is usually avoided as it takes the maximum storage space as compared to the basic variable. But still is used in many cases it is a requirement to use them.

Object – In VBA, the object variable can contain data and is associated with multiple methods and properties. The Object variable can also contain other objects. With Dim keyword, you want to use three types of objects which are as follows:

  • Excel objects- Workbook, Worksheet, Sheet, and Range objects.
  • User-Defined objects- Class Modules.
  • External library objects- Dictionary.

Array – Array is known as a group of variables or objects and can hold more than one piece of data. With Dim keyword, you can declare the array as:

  • Static Array
  • Dynamic Array

Is Dim required in VBA?

In VBA, it is not mandatory to use a Dim statement. If the programmer does not use Dim, the compiler will not throw any error. You can even use the variable without declaring the Dim, and in that case, it will be automatically considered as a variant type. However, it is always it is advisable to make it a necessary practice as VBA code without Dim statements are considered as poor code and can generate many problems such as:

  1. All variables are by default considered as variants. A variant is set to 16 bytes, which is the most significant variable type. Thus, taking the maximum storage and increasing the compile time.
  2. Some variable errors will go undetected. VBA will not detect the runtime errors (i.e., Data Mismatch).
  3. VBA also cannot identify the compile-time errors.
  4. It disables the intelliSense feature (this feature will automatically display the available options for the variable where you type only the first few letters and VBA displays the list).

Syntax of Dim Variable

  1. BASIC VARIABLE

Syntax

Dim [variable name] As [type]

Parameters used

Variable name (required)- It represents the name of the variable and it should be as per the standard variable naming conventions.

Type (optional)- It represents the data type for the variable. The default value is Variant.

Code

Sub Dim_BasicVariable()
 'declaring four basic variables with different data types
 Dim num As Long
 Dim profit As Currency
 Dim name As String
 Dim logical_val As Boolean
 name = "Reema"
 num = 67 
 profit = 789
 logical_val = True
 MsgBox (name & " with ID no " & num & "has won a profit of" & profit & ":" & logival_val)
 End Sub
VBA Dim

Output

VBA Dim
  • FIXED STRING

Syntax

Dim [variable name] As String * [size]

Parameter Used

Variable name-

Type (optional)- It represents the data type for the variable. The default value is Variant.

 Size (optional)- This parameter denoted the string length.

Code

Sub Dim_FixedString()
 Dim firstName As String * 8
 Dim lastName As String * 10
 firstName = "Harshita"
 'as we have fixed the string it will only take first 10 characters
 lastName = "Saini ehfggrvfhbjdkhb"
 MsgBox ("Name =" & firstName & " " & lastName)
 End Sub
VBA Dim

Output

VBA Dim
  • VARIANT

Syntax

Dim [variable name] As Variant
 Dim [variable name]

Parameter Used

Variable name (required)- It represents the name of the variable and it should be as per the standard variable naming conventions.

Code

Sub Dim_Variant()
 'decalring the variable with variant
 Dim val1 As Variant
 'If you don't declare any data type by default it is variant
 Dim val2
 'specifying integer values
 val1 = 12 
 val2 = 56
 MsgBox (val1 + val2)
 End Sub
VBA Dim

Output

VBA Dim
  • OBJECT

Syntax

Dim [variable name] As Object

Parameter Used

Variable name (required)- It represents the name of the variable and it should be as per the standard variable naming conventions.

Type (optional)- It represents the data type for the variable. The default value is Variant.

Code

Sub Dim_Object()
 ' Declaring an object
 Dim rang As Range
 Dim wrkbok As Workbook
 Dim wrksheet As Worksheet
 'assinging range
 Set rang = "A1:A5"  
 ' assigning wrkbok to a new workbook 
 Set wrkbok = Workbooks.Add
 End Sub
VBA Dim
  • OBJECT USING NEW

Syntax

Dim [variable name] As New [type]

Parameter Used

Variable name (required)- It represents the name of the variable and it should be as per the standard variable naming conventions.

Type (optional)- It represents the data type for the variable. The default value is Variant.

Code

In the below code we want to read through a range of data. It will only create an object if the range value is greater than 40. At last, we will use Set to create the ClassModule object. 

Sub Dim_Object_Set()
 ' Declaring a ClassModule object variable
 Dim obj As ClassModule
 ' Read a range
 Dim i As Long
 For i = 1 To 9
     If Sheet1.Range("A" & i).Value > 40 Then
         ' Create object if condition met 
         Set obj = New ClassModule
     End If
 Next i
 End Sub
VBA Dim
  • OBJECT USIGN SET AND NEW

Syntax

Dim [variable name] As [object type]
 Set [variable name] = New [object type]

Parameter Used

Variable name (required)- It represents the name of the variable and it should be as per the standard variable naming conventions.

Type (optional)- It represents the data type for the variable. The default value is Variant.

Code

Sub Dim_Object ()
 ‘declaring and setting pre-defined object
 Dim obj As Collection
 Set obj = New Collection
 
 ‘creating and setting new class object 
 Dim ob1j As ClassModule
 Set obj1 = New ClassModule
 End Sub
  • STATIC ARRAY

Syntax

Dim [variable name] (first To last) As [type]

Parameter Used

Variable name (required)- It represents the name of the variable and it should be as per the standard variable naming conventions.

First (optional) – It represents the upper bound of the Array variable. The default value is 1.

Last (optional)- It represents the lower bound of the array variable. The default value is 1.

Type (optional)- It represents the data type for the variable. The default value is Variant.

Code

Sub Dim_StaticArray()
  'declaring the static array
  Dim arr(9)
  'inserting values in the array
  arr(0) = "Thomas"
  arr(1) = "Raj"
  arr(2) = "Rahul"
  arr(3) = "Frank" 
  'running a loop to print the array
  For i = 0 To 3
      Cells(i + 2, 1).Value = arr(i)
  Next
  End Sub
VBA Dim

Output

VBA Dim
  • DYNAMIC ARRAY

Syntax

Dim [variable name]() As [Type]
 ReDim [variable name]([first] To [last])

Parameter Used

Variable name (required)- It represents the name of the variable and it should be as per the standard variable naming conventions.

First (optional) – It represents the upper bound of the Array variable. The default value is 1.

Last (optional)- It represents the lower bound of the array variable. The default value is 1.

Type (optional)- It represents the data type for the variable. The default value is Variant.

Code

Sub Dim_DynamicArray()
      'declare the array variable
     Dim arr() As Variant
     i = 0
     'set the size of the array
     ReDim arr(6) 
     arr(0) = "Hello VBA"
     arr(1) = 14
      'resize the last array dimension with ReDim
     ReDim Preserve arr(8)
     For i = 4 To 8
     arr(i) = i
     Next 
     'to Fetch the output
     For i = 0 To UBound(arr)
        Cells(i + 1, 1).Value = arr(i)
     Next
  End Sub
VBA Dim

Output

VBA Dim
  • EXTERNAL LIBRARY

Syntax

Dim [variable name] As New [item]

Parameter Used

Variable name (required)- It represents the name of the variable and it should be as per the standard variable naming conventions.

Item – This parameter represents the inbuilt classes or user-designed classes.

Code:

Sub Dim_ExternalLibrary ()
 ‘Declaring the external library
 Dim dictnary As New Dictionary
 End Sub
  1. EXTERNAL LIBRARY USING SET

Syntax

Dim [variable name] As [item]
 Set [variable name] = New [item]

Parameter Used

Variable name (required)- It represents the name of the variable and it should be as per the standard variable naming conventions.

Item - This parameter represents the inbuilt classes or user-designed classes.

Code

Sub Dim_ExternalLibrary ()
 ‘Declaring and setting the external library
 Dim dictnary As Dictionary
 Set dictnary = New Dictonary 
 End Sub

Using Dim with Multiple Variables

We can declare multiple variables in a single Dim statement. It will help you to make your code shorter. In big program, it saves your compile time.

Syntax

Dim [variable name] As [type], [variable name] As [type],..

Code

 Sub Multiple_Dim_Example()
     ' Placing mutiple Dim statement
     Dim marks As Long, name As String, rollNo As Long
     name = "Reema"
     rollNo = 1523027
     marks = 89
     MsgBox ("Name: " & name & " Rollno: " & "Marks:" & marks)
 End Sub
VBA Dim

Output

VBA Dim

Related Topics

Excel VBA CVErr Function

VBA CVErr Function: The CVErr function in VBA returns an Error data type, involving with a user-specified error code. Syntax CVErr (Expression) Parameter Expression (required)- This parameter represents the required error code. Return This function returns an...

1 minute read.

VBA Pivot Table Grouping

VBA- Pivot Table Grouping For an instance, if in our pivot table, we have 11 different age groups from 20 to 30 -  but there might be a possibility that we...

2 minutes read.

Excel VBA CSng Function

VBA CSng Function: The CSng function in VBA converts the supplied expression into a single-precision floating-point number (single data type). Syntax CSng (Expression) Parameter Expression (required) – This parameter represents the expression that you want...

1 minute read.

Excel VBA CCur Function

The CCur function in VBA is used to convert an expression into a Currency data type. It can take a maximum of 15 digits to the left of the decimal place and...

1 minute read.

Excel VBA Time Function

Excel VBA Time Function: The Time function in VBA returns the current time. Syntax Time () Parameter NA Return This function returns the current time.  Example 1 Sub TimeFunction_Example1() ' returns the current time Dim time_val...

1 minute read.

Excel VBA : With End-with Constructs

With End-with Constructs The With-End With construct enables the user to perform multiple operations on a single object. If you are going to perform several different actions on the same object and typing the same...

4 minutes read.

VBA Charts Basic Operations

VBA Charts- Basic Operations The Chart Object in Excel VBA represents the collection of all the charts sheet present in a workbook. A chart can be either an embedded chart or a separate chart sheet. The...

6 minutes read.

VBA Find Function

VBA Find Function The Excel VBA FIND function finds any information in your Excel. It can be used on a Range object on the worksheet. It works the same, unlike the Excel Find &...

6 minutes read.

Excel VBA DateSerial Function

The DateSerial function in VBA returns a Date from a supplied year, month, and day number. Syntax DateSerial (Year, Month, Day) Parameter Year (required) – This parameter represents an integer signifying the year. Month (required) – This parameter...

2 minutes read.

Excel VBA Month Function

The Mont function in VBA returns the month number for the specified date. Syntax Month (Date) Parameter Time (required) – This parameter represents the date. Return This function returns the month number for the specified date. Example 1 Sub MonthFunction_Example1() ...

1 minute read.

Excel VBA: IF THEN Statement

VBA Excel: IF THEN StatementThis conditional statement enables you to check one condition and on the basis of that then run one or multiple statements if the condition holds. If...

1 minute read.

Excel VBA LCase Function

VBA LCase Function: The LCase function in VBA converts the given String into lower case text. Syntax LCase (String) Parameter String (required)- This parameter represents the text string that you want to convert to...

1 minute read.

Excel VBA CByte Function

VBA CByte Function: The CByte function in VBA converts an expression into a Byte data type. Syntax CByte (Expression) Parameter Expression (required) - This parameter represents the expression that that you want to convert to...

1 minute read.

Basics of Userform

A User Form is a built-in customized dialog box that fetches data from the user through a user-friendly dialog box or window that makes up part of an application's user interface. It...

9 minutes read.

Excel VBA GoTo Statement

GoTo Statement he GoTo statement branches unconditionally to a specified line in a procedure. It is used to transfer the program control to a new statement, which is headed by a label. It sends...

2 minutes read.

Excel VBA Choose Function

VBA Choose Function: The Choose function in VBA chooses function selects the corresponding value from a list of arguments depending as per the specified index. Syntax Choose (Index, [Choice-1], [Choice-2], ...) Parameter Index (required) – This...

2 minutes read.

VBA Option Explicit

VBA Option Explicit The Option Explicit in VBA is used to declare the variables at the top of your macro code. It is the most secure and easy option to maintain your variables....

5 minutes read.

Excel VBA Array Function

VBA Array Function: The Array function in VBA generates an array containing the given set of values. Syntax Array (Arglist) Parameter Arglist (required) – This parameter the list of values that you want to make...

2 minutes read.

Excel VBA Left Function

VBA Left Function: The Left function in VBA returns a substring from the start of the specified string. Syntax Left (Str, Length) Parameter Str (required) – This parameter represents the string that you want to extract...

1 minute read.

VBA TimeSerial Function

The TimeSerial function in VBA returns a Time for the specified hour, minute, and second. Syntax TimeSerial (Hour, Minute, Second) Parameter Hour (required) – This parameter represents an integer (0 to 23), signifying the hour of the time. Minute...

2 minutes read.