Какое действие реализует следующая инструкция dim mysheet as object

Алан Перлис

Постоянная одного человека — переменная другого

Эта статья содержит полное руководство по работе с переменными и использованию VBA Dim.

Первый раздел содержит краткое руководство по использованию оператора
Dim, включая примеры и формат оператора Dim.

Остальная часть поста содержит наиболее полное руководство, которое вы
найдете в VBA Dim Statement.

Если вы заинтересованы в объявлении параметров, вы можете прочитать о
них здесь.

Краткое руководство по использованию VBA Dim Statement

* Примечание. Для раннего связывания необходимо добавить
справочный файл с помощью меню «Инструменты» -> «Ссылки». Смотрите здесь,
как добавить ссылку на Dictonary.

Полезные ссылки 

• Объявление параметров в подпрограмме или функции
• Использование объектов в VBA
• Массивы VBA
• Коллекции VBA
• Словарь VBA
• VBA Workbook
• VBA Worksheet

Что такое VBA Dim Statement?

Ключевое слово Dim — это сокращение от Dimension. Он
используется для объявления переменных в VBA.

Объявление означает, что мы говорим VBA о переменной,
которую будем использовать позже.

Существует четыре типа Dim Statement. Все они очень похожи по синтаксису.

Вот они:

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

Ниже приводится краткое описание каждого типа

1. Basic variable — этот тип переменной содержит одно значение. Это такие типы, как Long, String, Date, Double, Currency.
2. Variant — VBA решает во время выполнения, какой тип будет использоваться. Вы должны избегать вариантов, где это возможно, но в некоторых случаях требуется их использование.
3. Object — это переменная, которая может иметь несколько методов (то есть подпрограмм / функций) и несколько свойств (то есть значений). Есть 3 вида:
   Объекты Excel, такие как объекты Workbook, Worksheet и Range.
   Пользовательские объекты, созданные с использованием модулей классов.
   Внешние библиотеки, такие как Словарь.
4. Array — это группа переменных или объектов.

В следующем разделе мы рассмотрим формат оператора VBA Dim с
некоторыми примерами каждого из них.

В последующих разделах мы рассмотрим каждый тип более
подробно.

Формат VBA Dim Statement

Формат выражения Dim показан ниже.

' 1. BASIC VARIABLE
' Объявление основной переменной
Dim [Имя переменной] As [тип]

' Объявление фиксированной строки
Dim [Имя переменной] As String * [размер]

' 2. VARIANT
Dim [Имя переменной] As Variant
Dim [Имя переменной]

' 3. OBJECT
' Объявление объекта
Dim [Имя переменной] As [тип]

' Объявление и создание объекта
Dim [Имя переменной] As New [тип]

' Объявление объекта с использованием позднего связывания
Dim [Имя переменной] As Object

' 4. ARRAY
' Объявление статического массива
Dim [Имя переменной](first To last) As [тип]

' Объявление динамического массива
Dim [Имя переменной]() As [тип]
Ниже приведены примеры использования различных форматов.
Sub Primeri()

    ' 1. BASIC VARIABLE
    ' Объявление основной переменной
    Dim name As String
    Dim count As Long
    Dim amount As Currency
    Dim eventdate As Date
    
    ' Объявление фиксированной строки
    Dim userid As String * 8
    
    ' 2. VARIANT
    Dim var As Variant
    Dim var
    
    ' 3. OBJECT
    ' Объявление объекта
    Dim sh As Worksheet
    Dim wk As Workbook
    Dim rg As Range
    
    ' Объявление и создание объекта
    Dim coll1 As New Collection
    Dim o1 As New Class1
    
    ' Объявление объекта - создайте объект ниже, используя Set
    Dim coll2 As Collection
    Dim o2 As Class1
    
    Set coll2 = New Collection
    Set o2 = New Class1
    
    ' 	Объявление и присвоение с использованием позднего связывания
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' 4. ARRAY
    ' Объявление статического массива
    Dim arrScores(1 To 5) As Long
    Dim arrCountries(0 To 9) As String
    
    ' Объявление динамического массива - установите размер ниже, используя ReDim
    Dim arrMarks() As Long
    Dim arrNames() As String
    
    ReDim arrMarks(1 To 10) As Long
    ReDim arrNames(1 To 10) As String

End Sub

Мы рассмотрим эти различные типы операторов Dim в следующих
разделах.

Как использовать Dim с несколькими переменными

Мы можем объявить несколько переменных в одном выражении Dim.

Dim name As String, age As Long, count As Long

Если мы опускаем тип, то VBA автоматически устанавливает тип как Variant. Мы увидим больше
о Variant позже.

' Сумма является вариантом
Dim amount As Variant

' Сумма является вариантом
Dim amount

' Адрес это вариант - имя это строка
Dim name As String, address

' имя - это вариант, адрес – строка
Dim name, address As String

Когда вы объявляете несколько переменных, вы должны указать тип каждой отдельно.

Dim wk As Workbook, marks As Count, name As String

Вы можете поместить столько переменных, сколько захотите, в
одном выражении Dim, но
для удобства чтения рекомендуется оставить его равным 3 или 4.

Где я должен поместить Dim Statement?

Оператор Dim может быть помещен в любое место кода. Тем не менее, он должен предшествовать любой строке, где используется переменная.

Если переменная используется перед оператором Dim, вы получите ошибку
«переменная не определена»

Когда дело доходит до позиционирования ваших Dim утверждений, вы можете сделать это двумя основными способами. Вы можете разместить все свои Dim заявления в верхней части процедуры.

Sub DimVverh()

    ' Размещение всех Dim statements наверху
    Dim count As Long, name As String, i As Long
    Dim wk As Workbook, sh As Worksheet, rg As Range
    
    Set wk = Workbooks.Open("C:ДокументыОтчет.xlsx")
    Set sh = wk.Worksheets(1)
    Set rg = sh.Range("A1:A10")
    
    For i = 1 To rg.Rows.count
        count = rg.Value
        Debug.Print count
    Next i
  
End Sub

ИЛИ вы можете объявить переменные непосредственно перед их
использованием:

Sub DimIsp()

    Dim wk As Workbook
    Set wk = Workbooks.Open("C:ДокументыОтчет.xlsx")
    
    Dim sh As Worksheet
    Set sh = wk.Worksheets(1)
    
    Dim rg As Range
    Set rg = sh.Range("A1:A10")
    
    Dim i As Long, count As Long, name As String
    For i = 1 To rg.Rows.count
        count = rg.Value
        name = rg.Offset(0, 1).Value
        Debug.Print name, count
    Next i
  
End Sub

Я лично предпочитаю последнее, так как оно делает код более
аккуратным, и его легче читать, обновлять и обнаруживать ошибки.

Использование Dim в циклах

Помещение оператора Dim в цикл не влияет на переменную.

Когда VBA запускает Sub (или Function), первым делом он
создает все переменные, которые были объявлены в выражениях Dim.

Следующие 2 фрагмента кода практически одинаковы. Во-первых,
переменная Count объявляется перед циклом. Во втором он объявлен в цикле.

Sub CountPeredCiklom()

    Dim count As Long

    Dim i As Long
    For i = 1 To 3
        count = count + 1
    Next i
    
    ' значение счета будет 3
    Debug.Print count

End Sub

Sub CountPosleCikla()

    Dim i As Long
    For i = 1 To 3
        Dim count As Long
        count = count + 1
    Next i
    
    ' значение счета будет 3
    Debug.Print count

End Sub

Код будет вести себя точно так же, потому что VBA создаст переменные при
входе в подпрограмму.

Могу ли я использовать Dim для присвоения значения?

В таких языках, как C ++, C # и Java, мы можем объявлять и назначать переменные в одной строке:

' C++
int i = 6
String name = "Иван"

Мы не можем сделать это в VBA. Мы можем использовать оператор двоеточия для размещения
объявлений и назначения строк в одной строке.

Dim count As Long: count = 6

Мы не объявляем и не присваиваем в одной строке VBA. Что мы
делаем, это помещаем эти две строки (ниже) в одну строку в редакторе. Что
касается VBA, это две отдельные строки, как здесь:

Dim count As Long
count = 6

Здесь мы помещаем 3 строки кода в одну строку редактора,
используя двоеточие:

count = 1: count = 2: Set wk = ThisWorkbook

Нет никаких преимуществ или недостатков в назначении и
объявлении в одной строке редактора. Все сводится к личным предпочтениям.

Dim действительно требуется?

Ответ в том, что это не обязательно. VBA не требует от вас
использовать Dim Statement.

Однако не использовать оператор Dim — плохая практика и
может привести к множеству проблем.

Вы можете использовать переменную без предварительного
использования оператора Dim. В этом случае переменная автоматически будет типом
варианта.

Это может привести к таким проблемам, как

1. Все переменные являются вариантами (проблемы с
   этим см. В разделе «Варианты»).
2. Некоторые переменные ошибки останутся
   незамеченными.

Из-за этих проблем рекомендуется сделать использование Dim
обязательным в нашем коде. Мы делаем это с помощью оператора Option Explicit.

Option Explicit

 Мы можем сделать Dim
обязательным в модуле, набрав «Option Explicit» в верхней части модуля.

Мы можем сделать это автоматически в каждом новом модуле,
выбрав Tools-> Options из меню и отметив флажок «Требовать декларацию
переменной». Затем, когда вы вставите новый модуль, «Option Explicit» будет
автоматически добавлен в начало.

Давайте посмотрим на некоторые ошибки, которые могут
остаться незамеченными, если мы не будем использовать Dim.

Ошибки Переменной

В приведенном ниже коде мы используем переменную Total без использования оператора Dim.

Sub BezDim()

    Total = 6
    
    Total = Total + 1
    
    Debug.Print Total

End Sub

Если мы случайно написали Total неправильно, VBA сочтет это
новой переменной.

В приведенном ниже коде мы неправильно написали переменную Total как Totall.

Sub BezDimOshibki()

    Total = 6
    
    ' Первый Total - это ошибка
    Totall = Total + 1
    
    ' напечатает 6 вместо 7
    Debug.Print Total

End Sub

VBA не обнаружит ошибок в коде, и будет напечатано неверное
значение.

Давайте добавим Option Explicit и попробуйте приведенный
выше код снова

Option Explicit 

Sub BezDimOshibki()

    Total = 6
    
    ' Первый Total - это ошибка
    Totall = Total + 1
    
    ' Напечатает 6 вместо 7
    Debug.Print Total

End Sub

Теперь, когда мы запустим код, мы получим ошибку «Переменная
не определена». Чтобы эта ошибка не появлялась, мы должны использовать Dim для каждой переменной,
которую мы хотим использовать.

Когда мы добавим оператор Dim для Total
и запустим код, мы получим ошибку, сообщающую, что опечатка Totall не была определена.

Это действительно полезно, так как помогает нам найти
ошибку, которая в противном случае осталась бы незамеченной.

Ошибка в ключевом слове

Вот второй пример, который более тонкий.

Когда следующий код выполняется, он должен изменить шрифт в
ячейке A1 на синий.

Однако, когда код запускается, ничего не происходит.

Sub ZadatCvet()

    Sheet1.Range("A1").Font.Color = rgblue

End Sub

Ошибка здесь в том, что rgblue должен быть rgbBlue. Если вы
добавите Option Explicit в модуль, появится ошибка «переменная не определена».
Это значительно облегчает решение проблемы.

Эти два примера очень просты. Если у вас много кода, то
подобные ошибки могут стать кошмаром для отслеживания.

Использование Dim с Basic Variables

VBA имеет те же основные типы переменных, которые
используются в электронной таблице Excel.

Вы можете увидеть список всех типов переменных VBA здесь.

Тем не менее, большую часть времени вы будете использовать следующие:

* Первоначально мы использовали бы тип Long вместо Integer,
потому что Integer был 16-разрядным, и поэтому диапазон был от -32 768 до 32
767, что довольно мало для многих случаев использования целых чисел.

Однако в 32-битной (или выше) системе целое число автоматически
преобразуется в длинное. Поскольку Windows была 32-битной начиная с Windows 95
NT, нет смысла использовать Integer.

В двух словах, всегда используйте Long для целочисленного
типа в VBA.

Фиксированный тип строки

В VBA есть один необычный тип базовой переменной, с которым
вы, возможно, не знакомы.

Это фиксированный тип строки. Когда мы создаем нормальную строку в VBA, мы можем добавить текст, и VBA автоматически изменит размер строки для нас.

Sub TipStroki()

    Dim s As String
    
    ' s is "Иван Петров"
    s = "John Smith"
    
    ' s is "Игорь"
    s = "Tom"

End Sub

Фиксированная строка никогда не изменяется. Эта строка
всегда будет иметь одинаковый размер независимо от того, что вы ей назначаете

вот несколько примеров:

Sub FiksStroka()
    
    Dim s As String * 4
    
    ' s is "Иван"
    s = "Иван Перов"
    
    ' s = "Игорь "
    s = "Игорь"

End Sub

Использование Dim с Variants

Когда мы объявляем переменную как вариант, VBA решает во время выполнения, какой
тип переменной должен быть.

Мы объявляем варианты следующим образом

' Оба варианта
Dim count
Dim count As Variant
Это звучит как отличная идея в теории. Больше не нужно беспокоиться о типе переменной
Sub IspVariants()
    
    Dim count As Variant
        
    count = 7
    
    count = "Иван"
    
    count = #12/1/2018#

End Sub

Однако использование вариантов является плохой практикой, и
вот почему:

1. VBA
   не будет замечать неправильных ошибок типа (т. Е. Несоответствие данных).
2. Вы не можете получить доступ к Intellisense.
3. VBA
   угадывает лучший тип, и это может быть не то, что вы хотите.

Тип ошибки

Ошибки твои друзья!

Они могут раздражать и расстраивать, когда они случаются, но
они предупреждают вас о будущих проблемах, которые не так легко найти.

Ошибка несоответствия типов предупреждает вас, когда используются неверные данные.

Например. Представьте, что у нас есть лист оценок учеников.
Если кто-то случайно (или намеренно) заменит метку на текст, данные будут
недействительными.

Если мы используем вариант для хранения меток, то ошибки не
возникнет

Sub MarksVariant()
    
    Dim marks As Variant
    
    Dim i As Long
    For i = 1 To 10
        
        ' Прочитайте отметку
        mark = Sheet1.Range("A" & i).Value
        
    Next

End Sub

Это не хорошо, потому что в ваших данных есть ошибка, а вы
не знаете об этом.

Если вы зададите переменную Long, VBA сообщит вам об ошибке
«Несоответствие типов», если значения являются текстовыми.

Sub MarksLong()
    
    Dim mark As Long
    
    Dim i As Long
    For i = 1 To 10
        
        ' Прочитайте отметку
        mark = Sheet1.Range("A" & i).Value
        
    Next

End Sub

Доступ к Intellisense

Intellisense — удивительная особенность VBA. Он дает вам
доступные параметры в зависимости от типа, который вы создали.

Представьте, что вы объявляете переменную листа, используя
Dim

Когда вы используете переменную wk с десятичной точкой, VBA
автоматически отобразит доступные опции для переменной.

Вы можете увидеть Intellisense на скриншоте ниже

Если вы используете Variant как тип, то Intellisense будет
недоступен

Это потому, что VBA не будет знать тип переменной до времени
выполнения.

Использование Dim с Objects

Если вы не знаете, что такое Objects, вы можете прочитать
мою статью об VBA Objects здесь.

Есть 3 типа объектов:

1. Объекты Excel
2. Объекты модуля класса
3. Внешние объекты библиотеки

Примечание. Объект VBA Collection используется аналогично тому, как мы используем объект Class Module. Мы используем новое, чтобы создать его.

Давайте посмотрим на каждый из них по очереди.

Объекты Excel

Объекты Excel, такие как Рабочая книга, Рабочий лист,
Диапазон и т. Д., Не используют Новый, поскольку они автоматически создаются
Excel. Смотрите, «когда New не требуется».

При создании или открытии книги Excel автоматически создает
связанный объект.

Например, в приведенном ниже коде мы открываем рабочую
книгу. VBA создаст объект, а функция Open вернет книгу, которую мы можем
сохранить в переменной

Sub OtkrWorkbook()
    
    Dim wk As Workbook
    Set wk = Workbooks.Open("C:ДокументыОтчет.xlsx")

End Sub

Если мы создадим новый лист, произойдет похожая вещь. VBA
автоматически создаст его и предоставит доступ к объекту.

Sub DobavSheet()
    
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets.Add

End Sub

Нам не нужно использовать ключевое слово New для этих объектов Excel.

Мы просто присваиваем переменную функции, которая либо
создает новый объект, либо дает нам доступ к существующему.

Вот несколько примеров назначения переменных Workbook, Worksheet и range

Sub DimWorkbook()
    
    Dim wk As Workbook
    
    ' назначить wk новой книге
    Set wk = Workbooks.Add
    
    ' назначить wk первой открытой книге
    Set wk = Workbooks(1)
    
    ' назначить wk рабочей книге Отчет.xlsx
    Set wk = Workbooks("Отчет.xlsx")
    
    ' назначить wk активной книге
    Set wk = ActiveWorkbook
    
End Sub

Sub DimWorksheet()
    
    Dim sh As Worksheet
    
    ' Назначить sh на новый лист
    Set sh = ThisWorkbook.Worksheets.Add
    
    ' Назначьте sh на крайний левый лист
    Set sh = ThisWorkbook.Worksheets(1)
    
    ' Назначьте sh на лист под названием «Клиенты»
    Set sh = ThisWorkbook.Worksheets("Клиенты")
    
    ' Присвойте sh активному листу
    Set sh = ActiveSheet

End Sub

Sub DimRange()

    ' Получить рабочий лист клиента
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets("Клиенты")
    
    ' Объявите переменную диапазона
    Dim rg As Range
    
    ' Присвойте rg диапазону A1
    Set rg = sh.Range("A1")
    
    ' Назначьте rg в диапазоне от B4 до F10
    Set rg = sh.Range("B4:F10")
    
    ' Присвойте rg диапазону E1
    Set rg = sh.Cells(1, 5)
    
End Sub

Если вы хотите узнать больше об этих объектах, вы можете ознакомиться со следующими статьями: Workbook VBA, Worksheet VBA и Cell и Range VBA.

Использование Dim с Class Module Objects

В VBA мы используем Class Modules для создания наших собственных пользовательских объектов. Вы можете прочитать все о Class Modules здесь.

Если мы
создаем объект, нам нужно использовать ключевое слово New.

Мы можем сделать это в операторе Dim или в операторе Set.

Следующий код создает объект, используя ключевое слово New в выражении Dim:

' Объявить и создать
Dim o As New class1
Dim coll As New Collection

Использование New в выражении Dim означает, что каждый раз
при запуске нашего кода будет создаваться ровно один объект.

Использование Set дает нам больше гибкости. Мы можем создать
много объектов из одной переменной. Мы также можем создать объект на основе
условия.

Этот следующий код показывает, как мы создаем объект Class Module, используя Set. (Чтобы создать модуль класса, перейдите в окно проекта, щелкните правой кнопкой мыши соответствующую книгу и выберите «Вставить модуль класса». Подробнее см. «Создание Simple Class Module».)

' Объявить только
Dim o As Class1

' Создать с помощью Set
Set o = New Class1

Давайте посмотрим на пример использования Set. В приведенном ниже коде мы хотим
прочитать диапазон данных. Мы создаем объект только в том случае, если значение
больше 50.

Мы используем Set для создания объекта Class1. Это потому, что количество нужных нам объектов зависит от
количества значений более 50.

Sub IspSet()
    
    ' Объявите переменную объекта Class1
    Dim o As Class1
    
    ' Читать диапазон
    Dim i As Long
    For i = 1 To 10
        If Sheet1.Range("A" & i).Value > 50 Then

            ' Создать объект, если условие выполнено
            Set o = New Class1
            
        End If
    Next i

End Sub

Я сохранил этот пример простым для ясности. В реальной версии этого кода мы бы заполнили объект Class Module данными и добавили его в структуру данных, такую как Collection или Dictionary.

Вот пример реальной версии, основанной на данных ниже:

' Class Module - clsStudent
Public Name As String
Public Subject As String

' Стандартный модуль
Sub ChitatBalli()

    ' Создать коллекцию для хранения объектов
    Dim coll As New Collection
    
    ' Current Region получает соседние данные
    Dim rg As Range
    Set rg = Sheet1.Range("A1").CurrentRegion
    
    Dim i As Long, oStudent As clsStudent
    For i = 2 To rg.Rows.Count
        
        ' Проверьте значение
        If rg.Cells(i, 1).Value > 50 Then
            ' Создать новый объект
            Set oStudent = New clsStudent
            
            ' Читать данные на объект студента
            oStudent.Name = rg.Cells(i, 2).Value
            oStudent.Subject = rg.Cells(i, 3).Value
            
            ' добавить объект в коллекцию
            coll.Add oStudent
            
        End If
        
    Next i
    
    ' Распечатайте данные в Immediate Window, чтобы проверить их
    Dim oData As clsStudent
    For Each oData In coll
        Debug.Print oData.Name & " studies " & oData.Subject
    Next oData

End Sub

Чтобы узнать больше о Set вы можете заглянуть сюда.

Объекты из внешней библиотеки

Действительно полезная часть VBA — это то, как у нас есть
доступ к внешним библиотекам. Это открывает целый новый мир тому, что мы можем
сделать.

Примерами являются библиотеки Access, Outlook и Word,
которые позволяют нам взаимодействовать с этими приложениями.

Мы можем использовать библиотеки для различных типов
структур данных, таких как Словарь, Массив, Стек и Очередь.

Существуют библиотеки для очистки веб-сайта (библиотека
объектов Microsoft HTML), использования регулярных выражений (регулярные
выражения Microsoft VBScript) и многих других задач.

Мы можем создать эти объекты двумя способами:

1. Раннее связывание
2. Позднее связывание

Давайте посмотрим на это по очереди.

Раннее связывание

Раннее связывание означает, что мы добавляем справочный
файл. Как только этот файл добавлен, мы можем рассматривать объект как объект
модуля класса.

Мы добавляем ссылку, используя Tools-> Reference, а затем
проверяем соответствующий файл в списке.

Например, чтобы использовать словарь, мы ставим флажок
«Microsoft Scripting Runtime»

Как только мы добавим ссылку, мы можем использовать словарь
как объект модуля класса

Sub RanSvyaz()

    ' Используйте только Dim
    Dim dict1 As New Dictionary
    
    ' Используйте Dim и Set
    Dim dict2 As Dictionary
    Set dict2 = New Dictionary

End Sub

Преимущество раннего связывания заключается в том, что у нас
есть доступ к Intellisense. Недостатком является то, что это может вызвать
конфликтные проблемы на других компьютерах.

Лучше всего использовать раннюю привязку при написании кода,
а затем использовать позднюю привязку при распространении кода другим
пользователям.

Позднее связывание

Позднее связывание означает, что мы создаем объект во время
выполнения.

Мы объявляем переменную как тип «Объект». Затем мы
используем CreateObject для создания объекта.

Sub PozdSvyaz()

    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
End Sub

Использование Dim с Arrays

В VBA есть два типа массивов:

1. Статический — размер массива задается в
   операторе Dim и не может изменяться.
2. Динамический — размер массива не указан в
   выражении Dim. Это устанавливается позже с помощью оператора ReDim

' Статический массив

' Магазины 7 длинных - от 0 до 6
Dim arrLong(0 To 6) As Long

' Магазины 7 длинных - от 0 до 6
Dim arrLong(6) As String

Динамический массив дает нам гораздо больше гибкости. Мы
можем установить размер во время выполнения кода.

Мы объявляем динамический массив, используя инструкцию Dim,
и устанавливаем размер позже, используя ReDim.

' Динамический массив

' Объявите переменную
Dim arrLong() As Long

' Установить размер
ReDim arrLong(0 To 6) As Long

Использование ReDim

Большая разница между Dim и ReDim
заключается в том, что мы можем использовать переменную в выражении ReDim. В операторе Dim размер должен быть
постоянным значением.

Sub IspSet()

    ' Объявите переменную
    Dim arrLong() As Long
    
    ' Спросите пользователя о размере
    Dim size As Long
    size = InputBox("Пожалуйста, введите размер массива.", Default:=1)
    
    ' Установите размер на основе пользовательского ввода
    ReDim arrLong(0 To size) As Long

End Sub

На самом деле мы можем использовать оператор Redim без
предварительного использования оператора Dim.

В первом примере вы можете видеть, что мы используем Dim:

Sub IspDimReDim()

    ' Использование Dim
    Dim arr() As String

    ReDim arr(1 To 5) As String
    
    arr(1) = "Яблоко"
    arr(5) = "Апельсин"
    
End Sub

Во втором примере мы не используем Dim:

Sub IspTolkoReDim ()

    ' Использование только ReDim
    ReDim arr(1 To 5) As String
    
    arr(1) = "Яблоко"
    arr(5) = "Апельсин"
    
End Sub

Преимущество состоит в том, что вам не нужно тусклое
утверждение. Недостатком является то, что это может сбить с толку тех, кто
читает ваш код. В любом случае это не имеет большого значения.

Вы можете использовать ключевое слово Preserve с ReDim для сохранения существующих данных при изменении размера массива. Вы можете прочитать больше об этом здесь.

Вы можете найти все, что вам нужно знать о массивах в VBA здесь.

Устранение неполадок ошибок  Dim

В таблице
ниже приведены ошибки, с которыми вы можете столкнуться при использовании Dim. См. Ошибки VBA для
объяснения различных типов ошибок.

Локальные и глобальные переменные

Когда мы используем Dim в процедуре (то есть подпрограмме или функции), она считается
локальной. Это означает, что это доступно только с этой процедурой.

Глобальные переменные объявляются вне процедур. В
зависимости от типа, они могут быть доступны для всех процедур в одном и том же
модуле или для всех процедур во всех модулях в текущей рабочей книге.

В приведенном ниже коде мы объявили count как глобальную переменную:

' Глобальная
Dim count As Long
Sub UseCount1()
    count = 6
End Sub
Sub UseCount2()
    count = 4
End Sub

Что произойдет, если у нас будет глобальная переменная и
локальная переменная с одинаковым именем?

На самом деле это не вызывает ошибку. VBA дает приоритет локальной декларации.

' Глобальная
Dim count As Long

Sub UseCount()
    ' Локальная
    Dim count As Long
    
    ' Относится к локальному счету
    count = 6
    
End Sub

Подобная ситуация может привести только к проблемам, так как
трудно отследить, какой счет используется.

Вообще глобальных переменных следует избегать, где это
возможно. Они делают код очень трудным для чтения, потому что их значения могут
быть изменены в любом месте кода. Это затрудняет обнаружение и устранение ошибок.

Важно знать и понимать глобальные переменные как вы,
поскольку вы можете встретить их в существующем коде.

Dim против Private

В VBA есть ключевое слово Private.

Если мы используем ключевое слово Private с переменной или
подфункцией / функцией, то этот элемент доступен только в текущем модуле.

Использование Dim и Private для переменной дает тот же
результат

' Доступно во всем этом модуле
Private priCount As Long
Dim dimCount As Long

Sub UseCount()

    ' Доступно только в этом разделе
    Private priName As String
    Dim dimName As String
    
End Sub

В VBA принято использовать Private для глобальных переменных
и Dim для локальных

' Доступно во всем этом модуле
Private priCount As Long

Sub UseCount()
    ' Только локальный
    Dim dimName As String
    
End Sub

Local OnlyThere
в VBA есть 2 других
типа объявлений, которые называются Public и Global.

Ниже приводится краткое изложение всех 4 типов:

1. Dim
   — используется для объявления локальных переменных, т. Е. В процедурах.
2. Private
   — используется для объявления глобальных переменных и процедур. Эти переменные
   доступны только для текущего модуля.
3. Public
   — используется для объявления глобальных переменных и процедур. Эти переменные
   доступны во всех модулях.
4. Global
   — старая и устаревшая версия Public.
   Может использоваться только в стандартных модулях. Он существует только для обратной
   совместимости.

Заключение

На этом мы заканчиваем статью о VBA Dim Statement. Если у вас есть
какие-либо вопросы или мысли, пожалуйста, дайте мне знать в комментариях ниже.

“One man’s constant is another man’s variable.” – Alan Perlis

This post provides a complete guide to using the VBA Dim statement.

The first section provides a quick guide to using the Dim statement including examples and the format of the Dim statement.

The rest of the post provides the most complete guide you will find on the VBA Dim Statement.

If you are interested in declaring parameters then you can read about them here.

A Quick Guide to using the VBA Dim Statement

*Note: Early binding requires that you add the reference file using Tools->References from the menu. See here for how to add the Dictonary reference.

Useful Links

Declaring parameters in a sub or function
Using Objects in VBA
VBA Arrays
VBA Collection
VBA Dictionary
VBA Workbook
VBA Worksheet

What is the VBA Dim Statement

The Dim keyword is short for Dimension. It is used to declare variables in VBA.

Declare means we are telling VBA about a variable we will use later.

There are four types of Dim statements. They are all pretty similar in terms of syntax.

They are:

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

The following is a brief description of each type:

1. 1. Basic variable – this variable type holds one value. These are the types such as Long, String, Date, Double, Currency.

1. 1. Variant – VBA decides at runtime which type will be used. You should avoid variants where possible but in certain cases it is a requirement to use them.

1. 1. Object – This is a variable that can have multiple methods(i.e. subs/functions) and multiple properties(i.e. values). There are 3 kinds:
      1. Excel objects such as the Workbook, Worksheet and Range objects.
      2. User objects created using Class Modules.
      3. External libraries such as the Dictionary.

1. Array – this is a group of variables or objects.

In the next section, we will look at the format of the VBA Dim statement with some examples of each.

In later sections we will look at each type in more detail.

Format of the VBA Dim Statement

The format of the Dim statement is shown below

' 1. BASIC VARIABLE
' Declaring a basic variable
Dim [variable name] As [type]

' Declaring a fixed string
Dim [variable name] As String * [size]

' 2. VARIANT
Dim [variable name] As Variant
Dim [variable name]

' 3. OBJECT
' Declaring an object
Dim [variable name] As [type]

' Declaring and creating an object
Dim [variable name] As New [type]

' Declaring an object using late binding
Dim [variable name] As Object

' 4. ARRAY
' Declaring a static array
Dim [variable name](first To last) As [type]

' Declaring a dynamic array
Dim [variable name]() As [type]

Below are examples of using the different formats

' https://excelmacromastery.com/
Sub Examples()

    ' 1. BASIC VARIABLE
    ' Declaring a basic variable
    Dim name As String
    Dim count As Long
    Dim amount As Currency
    Dim eventdate As Date
    
    ' Declaring a fixed string
    Dim userid As String * 8
    
    ' 2. VARIANT
    Dim var As Variant
    Dim var
    
    ' 3. OBJECT
    ' Declaring an object
    Dim sh As Worksheet
    Dim wk As Workbook
    Dim rg As Range
    
    ' Declaring and creating an object
    Dim coll1 As New Collection
    Dim o1 As New Class1
    
    ' Declaring an object - create object below using Set
    Dim coll2 As Collection
    Dim o2 As Class1
    
    Set coll2 = New Collection
    Set o2 = New Class1
    
    ' Declaring and assigning using late binding
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' 4. ARRAY
    ' Declaring a static array
    Dim arrScores(1 To 5) As Long
    Dim arrCountries(0 To 9) As String
    
    ' Declaring a dynamic array - set size below using ReDim
    Dim arrMarks() As Long
    Dim arrNames() As String
    
    ReDim arrMarks(1 To 10) As Long
    ReDim arrNames(1 To 10) As String

End Sub

We will examine these different types of Dim statements in the later sections.

How to Use Dim with Multiple Variables

We can declare multiple variables in a single Dim statement

Dim name As String, age As Long, count As Long

If we leave out the type then VBA automatically sets the type to be a Variant. We will see more about Variant later.

' Amount is a variant
Dim amount As Variant

' Amount is a variant
Dim amount

' Address is a variant - name is a string
Dim name As String, address

' name is a variant, address is a string
Dim name, address As String

When you declare multiple variables you should specify the type of each one individually

Dim wk As Workbook, marks As Long, name As String

You can place as many variables as you like in one Dim statement but it is good to keep it to 3 or 4 for readability.

Where Should I Put the Dim Statement?

The Dim statement can be placed anywhere in a procedure. However, it must come before any line where the variable is used.

If the variable is used before the Dim statement then you will get a “variable not defined” error:

When it comes to the positioning your Dim statements you can do it in two main ways. You can place all your Dim statements at the top of the procedure:

' https://excelmacromastery.com/
Sub DimTop()

    ' Placing all the Dim statements at the top
    Dim count As Long, name As String, i As Long
    Dim wk As Workbook, sh As Worksheet, rg As Range
    
    Set wk = Workbooks.Open("C:Docsdata.xlsx")
    Set sh = wk.Worksheets(1)
    Set rg = sh.Range("A1:A10")
    
    For i = 1 To rg.Rows.count
        count = rg.Value
        Debug.Print count
    Next i
  
End Sub

OR you can declare the variables immediately before you use them:

' https://excelmacromastery.com/
Sub DimAsUsed()

    Dim wk As Workbook
    Set wk = Workbooks.Open("C:Docsdata.xlsx")
    
    Dim sh As Worksheet
    Set sh = wk.Worksheets(1)
    
    Dim rg As Range
    Set rg = sh.Range("A1:A10")
    
    Dim i As Long, count As Long, name As String
    For i = 1 To rg.Rows.count
        count = rg.Value
        name = rg.Offset(0, 1).Value
        Debug.Print name, count
    Next i
  
End Sub

I personally prefer the latter as it makes the code neater and it is easier to read, update and spot errors.

Using Dim in Loops

Placing a Dim statement in a Loop has no effect on the variable.

When VBA starts a Sub (or Function), the first thing it does is to create all the variables that have been declared in the Dim statements.

The following 2 pieces of code are almost the same. In the first, the variable Count is declared before the loop. In the second it is declared within the loop.

' https://excelmacromastery.com/
Sub CountOutsideLoop()

    Dim count As Long

    Dim i As Long
    For i = 1 To 3
        count = count + 1
    Next i
    
    ' count value will be 3
    Debug.Print count

End Sub

' https://excelmacromastery.com/
Sub CountInsideLoop()

    Dim i As Long
    For i = 1 To 3
        Dim count As Long
        count = count + 1
    Next i
    
    ' count value will be 3
    Debug.Print count

End Sub

The code will behave exactly the same because VBA will create the variables when it enters the sub.

Can I use Dim to Assign a Value?

In languages like C++, C# and Java, we can declare and assign variables on the same line

' C++
int i = 6
String name = "John"

We cannot do this in VBA. We can use the colon operator to place the declare and assign lines on the same line.

Dim count As Long: count = 6

We are not declaring and assigning in the same VBA line. What we are doing is placing these two lines(below) on one line in the editor. As far as VBA is concerned they are two separate lines as here:

Dim count As Long
count = 6

Here we put 3 lines of code on one editor line using the colon:

count = 1: count = 2: Set wk = ThisWorkbook

There is really no advantage or disadvantage to assigning and declaring on one editor line. It comes down to a personal preference.

Is Dim Actually Required?

The answer is that it is not required. VBA does not require you to use the Dim Statement.

However, not using the Dim statement is a poor practice and can lead to lots of problems.

You can use a variable without first using the Dim statement. In this case the variable will automatically be a variant type.

This can lead to problems such as

1. All variables are variants (see the Variant section for issues with this).
2. Some variable errors will go undetected.

Because of these problems it is good practice to make using Dim mandatory in our code. We do this by using the Option Explicit statement.

Option Explicit

We can make Dim mandatory in a module by typing “Option Explicit” at the top of a module.

We can make this happen automatically in each new module by selecting Tools->Options from the menu and checking the box beside “Require Variable Declaration”. Then when you insert a new module, “Option Explicit” will be automatically added to the top.

Let’s look at some of the errors that may go undetected if we don’t use Dim.

Variable Errors

In the code below we use the Total variable without using a Dim statement

' https://excelmacromastery.com/
Sub NoDim()

    Total = 6
    
    Total = Total + 1
    
    Debug.Print Total

End Sub

If we accidentally spell Total incorrectly then VBA will consider it a new variable.

In the code below we have misspelt the variable Total as Totall:

' https://excelmacromastery.com/
Sub NoDimError()

    Total = 6
    
    ' The first Total is misspelt
    Totall = Total + 1
    
    ' This will print 6 instead of 7
    Debug.Print Total

End Sub

VBA will not detect any error in the code and an incorrect value will be printed.

Let’s add Option Explicit and try the above code again

' https://excelmacromastery.com/
Option Explicit 

Sub NoDimError()

    Total = 6
    
    ' The first Total is misspelt
    Totall = Total + 1
    
    ' This will print 6 instead of 7
    Debug.Print Total

End Sub

Now when we run the code we will get the “Variable not defined” error. To stop this error appearing we must use Dim for each variable we want to use.

When we add the Dim statement for Total and run the code we will now get an error telling us that the misspelt Totall was not defined.

This is really useful as it helps us find an error that would have otherwise gone undetected.

Keyword Misspelt Error

Here is a second example which is more subtle.

When the following code runs it should change the font in cell A1 to blue.

However, when the code runs nothing happens.

' https://excelmacromastery.com/
Sub SetColor()

    Sheet1.Range("A1").Font.Color = rgblue

End Sub

The error here is that rgblue should be rgbBlue. If you add Option Explicit to the module, the error “variable not defined” will appear. This makes solving the problem much easier.

These two examples are very simple. If you have a lot of code then errors like this can be a nightmare to track down.

Using Dim with Basic Variables

VBA has the same basic variable types that are used in the Excel Spreadsheet.

You can see a list of all the VBA variable types here.

However, most of the time you will use the following ones

*Originally we would use the Long type instead of Integer because the Integer was 16-bit and so the range was -32,768 to 32,767 which is quite small for a lot of the uses of integer.

However on a 32 bit(or higher) system the Integer is automatically converted to a Long. As Windows has been 32 bit since Windows 95NT there is no point in using an Integer.

In a nutshell, always use Long for an integer type in VBA.

Fixed String Type

There is one unusual basic variable type in VBA that you may not be familiar with.

This is the fixed string type. When we create a normal string in VBA we can add text and VBA will automatically resize the string for us

' https://excelmacromastery.com/
Sub StringType()

    Dim s As String
    
    ' s is "John Smith"
    s = "John Smith"
    
    ' s is "Tom"
    s = "Tom"

End Sub

A fixed string is never resized. This string will always be the same size no matter what you assign to it

Here are some examples

' https://excelmacromastery.com/
Sub FixedString()
    
    Dim s As String * 4
    
    ' s is "John"
    s = "John Smith"
    
    ' s = "Tom "
    s = "Tom"

End Sub

Using Dim with Variants

When we declare a variable to be a variant, VBA will decide at runtime which variable type it should be.

We declare variants as follows

' Both are variants
Dim count
Dim count As Variant

This sounds like a great idea in theory. No more worrying about the variable type

' https://excelmacromastery.com/
Sub UsingVariants()
    
    Dim count As Variant
        
    count = 7
    
    count = "John"
    
    count = #12/1/2018#

End Sub

However, using variants is poor practice and this is why:

1. Runtime Errors – VBA will not notice incorrect type errors(i.e. Data Mismatch).
2. Compile Errors – VBA cannot detect compile errors.
3. Intellisense is not available.
4. Size – A variant is set to 16 bytes which is the largest variable type

Runtime Errors

Errors are your friend!

They may be annoying and frustrating when they happen but they are alerting you to future problems which may not be so easy to find.

The Type Mismatch error alerts you when incorrect data is used.

For example. Imagine we have a sheet of student marks. If someone accidentally(or deliberately) replaces a mark with text then the data is invalid.

If we use a variant to store marks then no error will occur:

' https://excelmacromastery.com/
Sub MarksVariant()
    
    Dim mark As Variant
    
    Dim i As Long
    For i = 1 To 10
        
        ' Read the mark
        mark = Sheet1.Range("A" & i).Value
        
    Next

End Sub

This is not good because there is an error with your data and you are not aware of it.

If you make the variable Long then VBA will alert you with a “Type Mismatch” error if the values are text.

' https://excelmacromastery.com/
Sub MarksLong()
    
    Dim mark As Long
    
    Dim i As Long
    For i = 1 To 10
        
        ' Read the mark
        mark = Sheet1.Range("A" & i).Value
        
    Next

End Sub

Compile Errors

Using the compiler to check for errors is very efficient. It will check all of your code for problems before you run it. You use the compiler by selecting Debug->Compile VBAProject from the menu.

In the following code, there is an error. The Square function expects a long integer but we are passing a string(i.e. the name variable):

' https://excelmacromastery.com/
Sub CompileError()

    Dim name As String

    Debug.Print Square(name)

End Sub

Function Square(value As Long) As Long
    Square = value * value
End Function

If we use Debug->Compile on this code, VBA will show us an error:

This is good news as we can fix this error right away. However, if we declare the value parameter as a variant:

Function Square(value As Variant) As Long
    Square = value * value
End Function

then Debug.Compile will not treat this as an error. The error is still there but it is undetected.

Accessing the Intellisense

The Intellisense is an amazing feature of VBA. It gives you the available options based on the type you have created.

Imagine you declare a worksheet variable using Dim

Dim wk As Workbook

When you use the variable wk with a decimal point, VBA will automatically display the available options for the variable.

You can see the Intellisense in the screenshot below

If you use Variant as a type then the Intellisense will not be available

Dim wk As Variant

This is because VBA will not know the variable type until runtime.

Variant Size

The size of a variant is 16 bytes. If the variable is going to be a long then it would only take up 4 bytes. You can see that this is not very efficient.

However, unlike the 1990’s where this would be an issue, we now have computers with lots of memory and it is unlikely you will notice an inefficiency unless you are using a huge amount of variables.

Using Dim with Objects

If you don’t know what Objects are then you can read my article about VBA Objects here.

There are 3 types of objects:

1. Excel objects
2. Class Module objects
3. External library objects

Note: The VBA Collection object is used in a similar way to how we use Class Module object. We use new to create it.

Let’s look at each of these in turn.

Excel objects

Excel objects such as the Workbook, Worksheet, Range, etc. do not use New because they are automatically created by Excel. See When New is not required.

When a workbook is created or opened then Excel automatically creates the associated object.

For example, in the code below we open a workbook. VBA will create the object and the Open function will return a workbook which we can store in a variable

' https://excelmacromastery.com/
Sub OpenWorkbook()
    
    Dim wk As Workbook
    Set wk = Workbooks.Open("C:Docsdata.xlsx")

End Sub

If we create a new worksheet, a similar thing happens. VBA will automatically create it and provide use access to the object.

' https://excelmacromastery.com/
Sub AddSheet()
    
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets.Add

End Sub

We don’t need to use the New keyword for these Excel objects.

We just assign the variable to the function that either creates a new object or that gives us access to an existing one.

Here are some examples of assigning the Workbook, Worksheet and range variables:

' https://excelmacromastery.com/
Sub DimWorkbook()
    
    Dim wk As Workbook
    
    ' assign wk to a new workbook
    Set wk = Workbooks.Add
    
    ' assign wk to the first workbook opened
    Set wk = Workbooks(1)
    
    ' assign wk to The workbook Data.xlsx
    Set wk = Workbooks("Data.xlsx")
    
    ' assign wk to the active workbook
    Set wk = ActiveWorkbook
    
End Sub

' https://excelmacromastery.com/
Sub DimWorksheet()
    
    Dim sh As Worksheet
    
    ' Assign sh to a new worksheet
    Set sh = ThisWorkbook.Worksheets.Add
    
    ' Assign sh to the leftmost worksheet
    Set sh = ThisWorkbook.Worksheets(1)
    
    ' Assign sh to a worksheet called Customers
    Set sh = ThisWorkbook.Worksheets("Customers")
    
    ' Assign sh to the active worksheet
    Set sh = ActiveSheet

End Sub

' https://excelmacromastery.com/
Sub DimRange()

    ' Get the customer worksheet
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets("Customers")
    
    ' Declare the range variable
    Dim rg As Range
    
    ' Assign rg to range A1
    Set rg = sh.Range("A1")
    
    ' Assign rg to range B4 to F10
    Set rg = sh.Range("B4:F10")
    
    ' Assign rg to range E1
    Set rg = sh.Cells(1, 5)
    
End Sub

If you want to know more about these objects you can check out these articles: VBA Workbook, VBA Worksheet and VBA Ranges and Cells.

Using Dim with Class Module Objects

In VBA we use Class Modules to create our own custom objects. You can read all about Class Modules here.

If we are creating an object then we need to use the New keyword.

We can do this in the Dim statement or in the Set statement.

The following code creates an object using the New keyword in the Dim statement:

' Declare and create
Dim o As New class1
Dim coll As New Collection

Using New in a Dim statement means that exactly one object will be created each time our code runs.

Using Set gives us more flexibility. We can create many objects from one variable. We can also create an object based on a condition.

This following code shows how we create a Class Module object using Set.

(To create a Class Module, go to the project window, right-click on the appropiate workbook and select “Insert Class Module”. See Creating a Simple Class Module for more details.)

' Declare only
Dim o As Class1

' Create using Set
Set o = New Class1

Let’s look at an example of using Set. In the code below we want to read through a range of data. We only create an object if the value is greater than 50.

We use Set to create the Class1 object. This is because the number of objects we need depends on the number of values over 50.

' https://excelmacromastery.com/
Sub UsingSet()
    
    ' Declare a Class1 object variable
    Dim o As Class1
    
    ' Read a range
    Dim i As Long
    For i = 1 To 10
        If Sheet1.Range("A" & i).Value > 50 Then

            ' Create object if condition met
            Set o = New Class1
            
        End If
    Next i

End Sub

I’ve kept this example simple for clarity. In a real-world version of this code we would fill the Class Module object with data and add it to a data structure like a Collection or Dictionary.

Here is an example of a real-world version based on the data below:

' Class Module - clsStudent
Public Name As String
Public Subject As String

' Standard Module
' https://excelmacromastery.com/
Sub ReadMarks()

    ' Create a collection to store the objects
    Dim coll As New Collection
    
    ' Current Region gets the adjacent data
    Dim rg As Range
    Set rg = Sheet1.Range("A1").CurrentRegion
    
    Dim i As Long, oStudent As clsStudent
    For i = 2 To rg.Rows.Count
        
        ' Check value
        If rg.Cells(i, 1).Value > 50 Then
            ' Create the new object
            Set oStudent = New clsStudent
            
            ' Read data to the student object
            oStudent.Name = rg.Cells(i, 2).Value
            oStudent.Subject = rg.Cells(i, 3).Value
            
            ' add the object to the collection
            coll.Add oStudent
            
        End If
        
    Next i
    
    ' Print the data to the Immediate Window to test it
    Dim oData As clsStudent
    For Each oData In coll
        Debug.Print oData.Name & " studies " & oData.Subject
    Next oData

End Sub

To learn more about Set you can check out here.

Objects from an External Library

A really useful thing we can do with VBA is to access external libraries. This opens up a whole new world to what we can do.

Examples are the Access, Outlook and Word libraries that allow us to communicate with these applications.

We can use libraries for different types of data structures such as the Dictionary, the Arraylist, Stack and Queue.

There are libraries for scraping a website (Microsoft HTML Object Library), using Regular Expressions (Microsoft VBScript Regular Expressions) and many other tasks.

We can create these objects in two ways:

1. Early Binding
2. Late Binding

Let’s look at these in turn.

Early Binding

Early binding means that we add a reference file. Once this file is added we can treat the object like a class module object.

We add a reference using Tools->Reference and then we check the appropriate file in the list.

For example, to use the Dictionary we place a check on “Microsoft Scripting Runtime”:

Once we have the reference added we can use the Dictionary like a class module object

' https://excelmacromastery.com/
Sub EarlyBinding()

    ' Use Dim only
    Dim dict1 As New Dictionary
    
    ' Use Dim and Set
    Dim dict2 As Dictionary
    Set dict2 = New Dictionary

End Sub

The advantage of early binding is that we have access to the Intellisense. The disadvantage is that it may cause conflict issues on other computers.

The best thing to do is to use early binding when writing the code and then use late binding if distributing your code to other users.

Late Binding

Late binding means that we create the object at runtime.

We declare the variable as an “Object” type. Then we use CreateObject to create the object.

' https://excelmacromastery.com/
Sub LateBinding()

    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
End Sub

Using Dim with Arrays

There are two types of arrays in VBA. They are:

1. Static – the array size is set in the Dim statement and it cannot change.
2. Dynamic – the array size is not set in the Dim statement. It is set later using the ReDim statement.

' STATIC ARRAY

' Stores 7 Longs - 0 to 6
Dim arrLong(0 To 6) As Long

' Stores 7 Strings - 0 to 6
Dim arrLong(6) As String

A dynamic array gives us much more flexibility. We can set the size while the code is running.

We declare a dynamic array using the Dim statement and we set the size later using ReDim.

' DYNAMIC ARRAY

' Declare the variable
Dim arrLong() As Long

' Set the size
ReDim arrLong(0 To 6) As Long

Using ReDim

The big difference between Dim and ReDim is that we can use a variable in the ReDim statement. In the Dim statement, the size must be a constant value.

' https://excelmacromastery.com/
Sub UserSet()

    ' Declare the variable
    Dim arrLong() As Long
    
    ' Ask the user for the size
    Dim size As Long
    size = InputBox("Please enter the size of the array.", Default:=1)
    
    ' Set the size based on the user input
    ReDim arrLong(0 To size) As Long

End Sub

We can actually use the Redim Statement without having first used the Dim statement.

In the first example you can see that we use Dim:

' https://excelmacromastery.com/
Sub UsingDimReDim()

    ' Using Dim
    Dim arr() As String

    ReDim arr(1 To 5) As String
    
    arr(1) = "Apple"
    arr(5) = "Orange"
    
End Sub

In the second example we don’t use Dim:

' https://excelmacromastery.com/
Sub UsingReDimOnly()

    ' Using  ReDim only
    ReDim arr(1 To 5) As String
    
    arr(1) = "Apple"
    arr(5) = "Orange"
    
End Sub

The advantage is that you don’t need the Dim statement. The disadvantage is that it may confuse someone reading your code. Either way it doesn’t make much difference.

You can use the Preserve keyword with ReDim to keep existing data while you resize an array. You can read more about this here here.

You can find everything you need to know about arrays in VBA here.

Troubleshooting Dim Errors

The table below shows the errors that you may encounter when using Dim. See VBA Errors for an explanation of the different error types.

Local versus Module versus Global Variables

When we use Dim in a procedure (i.e. a Sub or Function), it is considered to be local. This means it is only available in the procedure where it is used.

The following are the different types of variables found in VBA:

• Local variables are variables that are available to the procedure only. Local variables are declared using the Dim keyword.
• Module variables are variables that are only available in the current module only. Module variables are declared using the Private keyword.
• Global variables are variables that are available to the entire project. Global variables are declared using the Public keyword.

In the code below we have declared count as a global variable:

 ' Global
 ' https://excelmacromastery.com/
 Public count As Long

 Sub UseCount1()

    count = 6
    
 End Sub

 Sub UseCount2()

    count = 4
    
 End Sub

What happens if we have a global variable and a local variable with the same name?

It doesn’t actually cause an error. VBA gives the local declaration precedence:

 ' https://excelmacromastery.com/

 ' Global
 Public count As Long 

 Sub UseCount()
    ' Local
    Dim count As Long
    
    ' Refers to the local count
    count = 6
    
 End Sub

Having a situation like this can only lead to trouble as it is difficult to track which count is being used.

In general global variables should be avoided. They make the code very difficult to read because their values can be changed anywhere in the code. This makes errors difficult to spot and resolve.

The one use of a global variable is retaining a value between code runs. This can be useful for certain applications where you want to ‘remember’ a value after the code has stopped running.

In the code below we have a sub called Update. Each time we run the Update sub(using Run->Run Sub from the menu or F5) it will add 5 to the amount variable and print the result to the Immediate Window(Ctrl + G to view this window):

 Public amount As Long

 Sub Update()
    amount = amount + 5
    Debug.Print "Update: " & amount
 End Sub

The results are:

First run: amount = 5

Second run: amount = 10

Third run: amount = 15

and so on.

If we want to clear all global variable values then we can use the End keyword. Note that the End keyword will stop the code and reset all variables so you should only use it as the last line in your code.

Here is an example of using the End keyword:

Sub ResetAllGlobals()
    End ' Resets all variables and ends the current code run
End Sub

Dim versus Private versus Public

We can declare variables using Public, Private and Dim. In some cases, they can be used interchangeably.

However, the following is the convention we use for each of the declaration keywords:

1. Dim – used to declare local variables i.e. available only in the current procedure.
2. Private – used to declare module variables and procedures. These are available within the current module only.
3. Public – used to declare global variables and procedures. These are available throughout the project.
4. Global(obsolete) – an older and obsolete version of Public. Can only be used in standard modules. It only exists for backward compatibility.

Note the Public and Private keywords are also used within Class Modules.

Conclusion

This concludes the article on the VBA Dim Statement. If you have any questions or thoughts then please let me know in the comments below.

What’s Next?

Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.

Related Training: Get full access to the Excel VBA training webinars and all the tutorials.

(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)

To delete a sheet using VBA, you need to use the VBA Delete method. You need to specify the sheet that you want to delete and then use this method. Let’s say if you want to delete the “Sheet1”, then you need to mention sheet1 and then type a dot (.) and in the end, type “Delete”.

In this tutorial, we will see different ways that you can use to delete a sheet using a VBA code. Make sure to have the developer tab on the ribbon from here you can get into the visual basic editor.

Delete a Sheet using its Name

Each sheet has a name, and you can use write a code to delete a sheet using the name. So, let’s say you want to delete the worksheet “Data”, the code would be:

Sub vba_delete_sheet()
Sheets("Data").Delete
End Sub

Delete a Sheet without Warning (Prompt)

When you delete a worksheet, Excel shows a message to confirm if you want to remove it or wish to cancel. And when you use a VBA code, in that case, Excel will also do that.

To deal with this, you can turn OFF the screen updating to delete a sheet and then turn it ON.

Application.DisplayAlerts = False
Sheets("Data").Delete
Application.DisplayAlerts = True

Name of the Sheet from a Cell

Now let’s say you want to use a cell value to use the name of the worksheet. In that case, you need to use the VBA range object to do that.

Delete the Sheet using the Sheet Number

That’s right. You can use the sheet’s number to delete it. Here’s the code.

Sub vba_delete_sheet()
Sheets(1).Delete
End Sub

Delete the ActiveSheet

To delete the active sheet, you can use the “ActiveSheet” object instead of using the sheet name to specify the sheet.

ActiveSheet.Delete

As I said, it deletes the active sheet, and you can activate it before removing it. But necessarily, you don’t need to do that as you can refer to a sheet and delete it as we have seen at the start of this tutorial.

Check if the Sheet Exists before Deleting

You can also write code in a way that it can check if the sheet exists or not and then deletes it.

Sub check_sheet_delete()
Dim ws As Worksheet
Dim mySheet As Variant
mySheet = InputBox("enter sheet name")
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
    If mySheet = ws.Name Then
      ws.Delete
    End If
Next ws
Application.DisplayAlerts = True
End Sub

In this code, you have FOR EACH to loop through all the worksheets. And then, an IF STATEMENT to delete the sheet if its name is equal to the name you have typed in the input box.

Delete All the Worksheets in Workbook

I’m sure you have this question in your mind, but I’m afraid it’s impossible to delete all the worksheets that you have in the workbook. You must have at least one worksheet left.

But I have found a solution to this problem. You can insert a new sheet that’s a blank one and then delete all which are already there.

Here’s the code: This code adds a new sheet and deletes all the other sheets.

Sub vba_delete_all_worksheets()
Dim ws As Worksheet
Dim mySheet As String
mySheet = "BlankSheet-" & Format(Now, "SS")
Sheets.Add.Name = mySheet
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> mySheet Then
      ws.Delete
    End If
Next ws
Application.DisplayAlerts = True
End Sub

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 

Output

• 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 

Output

• 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 

Output

• 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 

• 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 

• 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 

Output

• 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 

Output

• 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 

Output