Как объявить переменную? Объявление переменных в VBA
Имя, которое задается области компьютерной памяти и используется для хранения данных, называется переменной. Переменные обеспечивают возможность временно сохранять данные в компьютерной программе и этими данными манипулировать. Переменные в операторах VBA, это практически тоже самое, что и переменные в алгебраических уравнениях. В переменных могут содержаться числа, текстовые данные или другая информация, которая во время написания оператора не известна, но будет доступна при его выполнении. При появлении имени переменной в операторе, VBA подставляет в этот оператор фактическое значение, которое хранится в текущий момент в области памяти.
Требования к именам переменных
При выборе имен для переменных необходимо руководствоваться следующими правилами:
— имена переменных должны начинаться с буквы;
— после первой буквы можно использовать любые сочетания букв, цифр и символов подчеркивания (_);
— в именах переменных не могут содержаться пробелы, точки и символы, используемые VBA для обозначения математических действий и операций сравнения;
— имя переменной не может превышать 255 символов;
— имена переменных не должны повторять ключевые слова VBA;
— имя переменной (идентификатор) должно быть уникальным в пределах его области действия.
Кроме того, имена переменных не различают состояние регистра. Имена FirstRow и firstrow представляют для VBA одно и то же. При выборе имен переменных следует делать их максимально информативными.
Неявное объявление переменных (implicit variable declaration)
Использование переменной в операторе VBAП является простейшим способом создания этой переменной. Такое создание переменной называется неявным объявлением переменной. Использование переменной в операторе, неявно указывает (объявляет) VBA о создании этой переменной. Когда переменная впервые появляется в операторе, VBA создает переменную, при этом для нее резервируется область памяти. Переменные, созданные неявным объявлением, всегда имеют тип данных Variant. Не смотря на удобство, неявное объявление переменных имеет определенные недостатки. Напрмер, вы можете дважды неявно объявить одну и ту же переменную, думая, что объявляете новую и при этом потерять ранее сохраненное значение. При неявном объявлении переменных могут возникать и другие проблемы, которые впоследствии очень трудно обнаружить. Кроме того, при таком объявлении замедляется скорость выполнения программного кода.
Явное объявление переменных (explicit variable declaration)
Visual Basic for Application предоставляет возможность выполнять явное объявление переменных. Для того чтобы явно объявить переменную используется VBA-оператор Dim (сокращение от слова Dimension). Все переменные, которые создаются с этой формой ключевого слова Dim, являются переменными типа Variant. Для явного объявления переменной используется следующий синтаксис:
Dim MyVar
Этот оператор указывает VBA создать переменную с именем MyVar.
Переменная объявляется только один раз в отдельной процедуре или модуле. Хорошим правилом считается все явные объявления переменных производить в одном месте в начале процедуры. Явное объявление переменных имеет ряд преимуществ перед неявным:
— ускоряется выполнение программного кода;
— уменьшение количества ошибок в результате неправильного написания имен переменных;
— код становится более читабельным и понятным;
— нормализация использования переменных с заглавными буквами в именах.
Задание типа данных переменной
Независимо от того, объявляются ли переменные явно или неявно, все они являются переменными типа Variant, если только не задается другой тип переменной в объявляющем ее операторе. Объявление переменной, при котором задается тип переменной, называется типизированным объявлением переменной. Типизированное объявление переменной имеет следующий синтаксис:
Dim MyVar As Single
При типизированном объявлении переменной используется один из нескольких типов данных VBA.
При таком объявлении переменных имеет несколько преимуществ:
— ускоряется выполнение кода программ;
— повышается эффективность кода и сокращается объем памяти, требуемый для программы;
— легче читается и воспринимается программный код;
— легче обнаруживаются некоторые типы ошибок.
Использование специальных символов определения типа для объявления переменных
Существует еще один способ объявления переменных, при помощи специальных символов: !, @, #, $, %, &. Эти символы называют символами определения типа. В более ранних разновидностях Basic такой способ определения переменных был единственно возможным, сейчас же использование оператора Dim с ключевым словом As делает определение типа переменной проще и понятнее. Тем не менее, при неявном объявлении переменной можно задать ее тип, добавив специальный символ в конец имени переменной. При этом этот символ необходимо будет добавлять каждый раз, когда используется имя переменной, иначе будет возникать runtime-ошибка. В таблице приведено соответствие между символами определения и типами данных.
Таким образом, результат действия процедуры с явным объявлением переменной
Sub HelloExcel_1() Dim HelloMsg As String HelloMsg = "Hello, Excel!" MsgBox HelloMsg End Sub
будет таким-же как и результат действия с неявным объявлением переменной при помощи символа определения типа.
Sub HelloExcel_2() HelloMsg$ = "Hello, Excel!" MsgBox HelloMsg$ End Sub
Другие материалы по теме:
Что такое переменная и как правильно её объявить?
Переменная — это некий контейнер, в котором VBA хранит данные. Если подробнее, то это как коробочка, в которую Вы можете положить что-то на хранение, а затем по мере необходимости достать. Только в данном случае в переменной мы храним число, строку или иные данные, которые затем можем извлекать из неё и использовать в коде по мере необходимости.
Для чего нужна переменная? Чтобы хранить значение и применить его позже в любой момент. Например, в ячейке А1 записана сумма, а нажатием на кнопку запускается обновление отчета. После обновления отчета сумма в А1 изменится. Необходимо сверить сумму до обновления с суммой после и в зависимости от этого сделать какое-либо действие. Переменная как раз позволит запомнить значение ячейки до того, как она обновится и использовать именно это значение после обновления.
- Требования к переменным
- Типы данных, хранимых в переменных
- Как объявлять переменные
- Как правильно назвать переменную
- Пример использования переменных
- Константы
Требования к переменным:
В качестве имен переменных можно использовать символы букв и числа, но первой в имени переменной всегда должна быть буква. Не допускается использование точки, запятой, пробела и иных знаков препинания, кроме нижнего подчеркивания. Длина имени не должна превышать 254 символов. Так же нельзя использовать в качестве имен для переменных зарезервированные константы редактора VBA(например Sub, Msgbox, ubound, Date и т.п.). Так же для переменных неважен регистр букв.
Теперь рассмотрим основные декларированные в VBA типы данных, которые можно хранить в переменных:
| Тип данных | Занимает байт в памяти | Пределы значений |
|---|---|---|
| Byte | 1 | Целые числа от 0 до 255 |
| Boolean | 2 | True или False |
| Integer | 2 | Целые числа от (-32768) до 32767 |
| Long | 4 | Целые числа от (-2147483648) до 2147483647 |
| Single | 4 | От (–3.402823Е+38) до (–1.401298Е-45) и от 1.401298Е-45 до 3.402823Е+38 |
| Double | 8 | От ±1.79769313486232Е+308 до ±4.94065645841247Е-324 |
| Decimal | 12 | От ±79228162514264337593543950335 без десятичных знаков до ±7,9228162514264337593543950335 с 28-ю знаками после запятой |
| Currency | 8 | От (–922337203685477.5808) до 922337203685477.5807 |
| Date | 8 | От 01.01.100 до 31.12.9999(не надо путать с датами в Excel — 01.01.1900 до 31.12.9999) |
| String | 10(+длина строки) | От 0 до 65400 символов для фиксированных строк и чуть более 2 млрд. для строк переменной длины |
| Object | 4 | Любой объект |
| Array | Определяется кол-вом и размером элементов | — |
| Variant | от 16-ти | Любой из встроенных типов данных |
Как видно из таблицы больше всего памяти занимает Variant . Притом это если он хранит числовые данные. Если же такая переменная будет хранить данные строкового типа(текст), то размер занимаемой памяти будет измеряться уже начиная с 22 байт + длина строки, хранящейся в переменной. Чем больше памяти занимает переменная, тем дольше она инициализируется в памяти и тем медленнее код будет выполняться. Вот поэтому и важно явно задавать тип данных, хранимых в переменной — это называется объявить переменную.
Тип данных Decimal больше не используется, поэтому объявить переменную данного типа в VBA не получится — подобная попытка приведет к синтаксической ошибке. Для работы с данными типа Decimal переменную необходимо изначально объявить как Variant или вообще без типа (например Dim a), т.к. тип данных Variant используется в VBA по умолчанию и принимает любой тип данных.
Так же переменным можно назначать и другие типы данных, которых нет в таблице выше — это типы, которые поддерживаются объектной моделью приложений, к которым «подключен» VBA. Например, если зайти в VBA из Excel, то библиотека типов объектной модели Excel подключена по умолчанию и для переменных становится доступно множество типов этой объектной модели. Многие из них знакомы всем: Workbook, Worksheet, Range, Cells, Chart и т.д. Т.е. это книги, листы, ячейки, диаграммы. Типов много, почти на каждый объект и коллекцию. Рассматривать здесь все бессмысленно. Могу лишь привести пару строк кода:
Dim rRange as Range 'назначили переменной тип ячейка/диапазон Set rRange = Range("A1") 'присвоили ссылку на ячейку A1 текущего листа
Dim rRange as Range ‘назначили переменной тип ячейка/диапазон Set rRange = Range(«A1») ‘присвоили ссылку на ячейку A1 текущего листа
Про объявление переменных подробно написано чуть ниже.
А более подробно про обращение к диапазонам из VBA можно почитать в этой статье: Как обратиться к диапазону из VBA
как объявлять переменные
На самом деле все очень просто. Это делается при помощи операторов области действия: Dim , Public , Static и оператора присвоения типа As . Самый распространенный оператор — Dim . Его и возьмем в качестве примера. Синтаксис объявления:
[оператор области действия] Имя_переменной As [тип данных]
[оператор области действия] Имя_переменной As [тип данных]
Очень частая ошибка при объявлении переменных, совершаемая начинающими изучать VBA:
Dim MyVar1, MyVar2, MyVar3 As Integer
Dim MyVar1, MyVar2, MyVar3 As Integer
Вроде бы исходя из логики всем переменным присвоен тип данных Integer . Но это ошибочное суждение. Тип Integer присвоен только последней переменной, к которой он «привязан» оператором As — MyVar3. Все остальные переменные имеют тип данных Variant . Т.е. если Вы не задаете каждой переменной свой тип хранимых данных явно(т.е. не указываете для неё тип данных через As), то VBA сам присваивает для такой переменной тип данных Variant , т.к. он может хранить любой тип данных. А вот так выглядит правильное присвоение типа данных:
Dim MyVar1 As Integer, MyVar2 As Integer, MyVar3 As Integer
Dim MyVar1 As Integer, MyVar2 As Integer, MyVar3 As Integer
Это и есть объявление переменных. Т.е. сначала идет оператор области действия ( Dim , Public , Static ), сразу за ним имя переменной, затем оператор As и тип.
Но это не все. Некоторые типы переменным можно присваивать еще короче — даже без оператора As :
Dim MyVar1%, MyVar2%, MyVar3%
Dim MyVar1%, MyVar2%, MyVar3%
Всего шесть типов, которые можно объявить подобным методом:
! — Single
# — Double
$ — String
% — Integer
& — Long
@ — Currency
На что стоит обратить внимание, при объявлении переменных подобным образом: между именем переменной и знаком типа не должно быть пробелов.
Я лично в большинстве статей предпочитаю использовать первый метод, т.е. полное указание типа. Это читабельнее и понятнее. В каких-то проектах могу использовать краткое указание, в общих(разработка в команде) — полное. В своих кодах Вы вправе использовать удобный Вам метод — ошибки не будет.
Теперь разберемся с операторами области действия( Dim , Public и Static ):
- Dim — данный оператор используется для объявления переменной, значение которой будет храниться только в той процедуре, внутри которой данная переменная объявлена. Во время запуска процедуры такая переменная инициализируется в памяти и использовать её значение можно внутри только этой процедуры, а по завершению процедуры переменная выгружается из памяти(обнуляется) и данные по ней теряются. Переменную, объявленную подобным образом еще называют локальной переменной. Однако с помощью данного оператора можно объявить переменную, которая будет доступна в любой процедуре модуля. Необходимо объявить переменную вне процедуры — в области объявлений(читать как первой строкой в модуле, после строк объявлений типа — Option Explicit ). Тогда значение переменной будет доступно в любой процедуре лишь того модуля, в котором данная переменная была объявлена. Такие переменные называются переменными уровня модуля. Также для использования переменных во всех процедурах и функциях одного конкретного модуля можно использовать оператор Private . Но он в данном случае ничем не отличается от Dim , а пишется длиннее 🙂 Плюс, Private нельзя использовать внутри процедуры или функции(только в области объявлений), что еще больше сужает её применимость. По сути чаще этот оператор применяется к функциям и процедурам(об этом см.ниже)
- Static — данный оператор используется для объявления переменной, значение которой предполагается использовать внутри конкретной процедуры, но не теряя значения данной переменной по завершении процедуры. Переменные данного типа обычно используют в качестве накопительных счетчиков. Такая переменная инициализируется в памяти при первом запуске процедуры, в которой она объявлена. По завершении процедуры данные по переменной не выгружаются из памяти, но однако они не доступны в других процедурах. Как только Вы запустите процедуру с этой переменной еще раз — данные по такой переменной будут доступны в том виде, в котором были до завершения процедуры. Выгружается из памяти такая переменная только после закрытия проекта(книги с кодом).
- Public — данный оператор используется для объявления переменной, значение которой будет доступно в любой процедуре проекта(в обычных модулях, модулях класса, модулях форм, модулях листов и книг). Переменная, объявленная подобным образом, должна быть объявлена вне процедуры — в области объявлений. Такая переменная загружается в память во время загрузки проекта(при открытии книги) и хранит значение до выгрузки проекта(закрытия книги). Использовать её можно в любом модуле и любой процедуре проекта. Важно: объявлять подобным образом переменную необходимо строго в стандартном модуле. Такие переменные называются переменными уровня проекта. В простонародье такие переменные еще называют глобальными(возможно из-за того, что раньше подобные переменные объявлялись при помощи оператора Global, который в настоящее время устарел и не используется).
Для большего понимания того, где и как объявлять переменные уровня проекта два небольших примера.
Неправильное объявление
Option Explicit Sub main() Public MyVariable As String MyVariable = "Глобальная переменная" 'показываем текущее значение переменной MsgBox MyVariable 'пробуем изменить значение переменной Call sub_main 'показываем измененное значение переменной MsgBox MyVariable End Sub 'доп.процедура изменения значения переменной Sub ChangeMyVariable() MyVariable = "Изменили её значение" End Sub
переменные не будут видны во всех модулях всех процедур и функций проекта, потому что:
1. Оператор Public недопустим внутри процедуры(между Sub и End Sub), поэтому VBA при попытке выполнения такой процедуры обязательно выдаст ошибку — Invalid Attribut in Sub or Function.
2. Даже если Public заменить на Dim — это уже будет переменная уровня процедуры и для других процедур будет недоступна.
3. Т.к. объявление неверное — вторая процедура( ChangeMyVariable ) ничего не знает о переменной MyVariable и естественно, не сможет изменить именно её.
Правильное объявление
'выше глобальных переменных и констант могут быть только декларации: Option Explicit 'принудительное объявление переменных Option Base 1 'нижняя граница объявляемых массивов начинается с 1 Option Compare Text 'сравнение текста без учета регистра 'глобальная переменная - первой строкой, выше всех процедур Public MyVariable As String 'далее процедуры и функции Sub main() MyVariable = "Глобальная переменная" 'показываем текущее значение переменной MsgBox MyVariable, vbInformation, "www.excel-vba.ru" 'пробуем изменить значение переменной Call ChangeMyVariable 'показываем измененное значение переменной MsgBox MyVariable, vbInformation, "www.excel-vba.ru" End Sub 'доп.процедура изменения значения переменной Sub ChangeMyVariable() MyVariable = "Изменили её значение" End Sub
'процедура записана в Module1 'эта процедура будет доступна для вызова исключительно из процедур в этом же модуле 'но не будет доступна при вызове из других модулей Private Sub PrivateMain() MsgBox "Процедура может быть вызвана только из модуля, в котором записана", vbInformation, "www.excel-vba.ru" End Sub 'другая процедура, записанная в этом же модуле Sub CallPrivate() Call PrivateMain End Sub
‘процедура записана в Module1 ‘эта процедура будет доступна для вызова исключительно из процедур в этом же модуле ‘но не будет доступна при вызове из других модулей Private Sub PrivateMain() MsgBox «Процедура может быть вызвана только из модуля, в котором записана», vbInformation, «www.excel-vba.ru» End Sub ‘другая процедура, записанная в этом же модуле Sub CallPrivate() Call PrivateMain End Sub
'эта процедура записана в другом модуле - Module2 'при попытке вызова этой процедурой получим ошибку ' Sub or Function not defined ' потому что процедура PrivateMain объявлена только для Module1 Sub CallPrivate_FromModule1() Call PrivateMain End Sub
‘эта процедура записана в другом модуле — Module2 ‘при попытке вызова этой процедурой получим ошибку ‘ Sub or Function not defined ‘ потому что процедура PrivateMain объявлена только для Module1 Sub CallPrivate_FromModule1() Call PrivateMain End Sub
Как правильно назвать переменную:
«Что самое сложное в работе программиста? — выдумывать имена переменным.» 🙂 А ведь придумать имя переменной тоже не так-то просто. Можно, конечно, давать им имена типа: a, d, f, x, y и т.д.(я сам иногда так делаю, но либо в простых кодах, либо для специального запутывания кода). Но стоит задуматься: а как Вы с ними будете управляться в большом коде? Код строк на 10 еще потерпит такие имена, а вот более крупные проекты — не советовал бы я в них оперировать такими переменными. Вы сами запутаетесь какая переменная как объявлена и какой тип данных может хранить и что за значение ей присвоено. Поэтому лучше всего давать переменным осмысленные имена и следовать соглашению об именовании переменных. Что за соглашение? Все очень просто: перед основным названием переменной ставится префикс, указывающий на тип данных, который мы предполагаем хранить в данной переменной. Про имеющиеся типы данных я уже рассказал выше. А ниже приведена примерная таблица соответствий префиксов типам данных:
| Префикс | Тип хранимых данных |
|---|---|
| b | Boolean |
| bt | Byte |
| i | Integer |
| l | Long |
| s | Single |
| d | Double |
| c | Currency |
| dt | Date |
| str | String |
| obj | Object |
| v | Variant |
Лично я немного для себя её переделал, т.к. некоторые обозначения мне кажутся скудными. Например Double я обозначаю как dbl, а Single как sgl. Это мне кажется более наглядным.
В чем еще плюс явного указания префикса данных. В VBA есть такие операторы как Def, при помощи которых можно указать тип данных по умолчанию для переменных, первая буква имени которых попадает в заданный в операторе диапазон. Например:
DefBool B Sub test() Dim bCheck End Sub
DefBool B Sub test() Dim bCheck End Sub
Автоматически переменной bCheck будет присвоен тип Boolean, т.к. она начинается с буквы b — регистр здесь не имеет значения(впрочем как в VBA в целом). Оператор Def задается в области объявления. Можно задать не одну букву, а целый диапазон букв:
DefBool B-C Sub test() Dim bCheck, cCheck End Sub
DefBool B-C Sub test() Dim bCheck, cCheck End Sub
Но я считаю, что одной буквы для одного типа вполне достаточно. Можно задать сразу несколько операторов Def.
DefBool B DefStr S Sub test() Dim bCheck, sCheck End Sub
DefBool B DefStr S Sub test() Dim bCheck, sCheck End Sub
Ниже приведен полный перечень операторов типов и данные, которые задает каждый из них:
DefBool — Задает тип Boolean
DefByte — Задает тип Byte
DefCur — Задает тип Currency
DefDate — Задает тип Date
DefDbl — Задает тип Double
DefInt — Задает тип Integer
DefLng — Задает тип Long
DefObj — Задает тип Object
DefSng — Задает тип Single
DefStr — Задает тип String
DefVar — Задает тип Variant
По умолчанию в VBA применена инструкция DefVar для всех букв(иначе говоря для всех переменных, которым не назначен тип данных явно через оператор As).
Ну и немаловажный момент это непосредственно осмысленное имя переменной. Имя переменной должно примерно отражать то, что в ней будет храниться. Например, Вы создаете отчет и Вам надо объявить две переменные: одна имя листа, другая имя книги. Можно было сделать так: str1, str2. Коротко, здорово. Но если подумать — и как можно понять, какая из этих переменных что хранит? Никак. Надо просматривать код и вспоминать, какой переменной какое значение было присвоено. Не совсем удобно, правда? А если бы Вы задали имена так: strBookName, strSheetName, то вроде как более понятно, что мы в них будем хранить. Это удобно не только вам самим при работе с кодом, но и другим людям, которые, возможно в будущем будут пользоваться Вашим кодом. Им будет удобнее читать код, если он будет оформлен грамотно, а переменные названы осмысленно. И не стоит экономить на длине имени — имя должно быть понятным. Без фанатизма, конечно :-). Хоть VBA и позволяет нам создавать переменные длиной до 254 символов, но читать такую переменную так же неудобно, как и с одним символом. Но здесь уже все зависит от Ваших предпочтений и фантазии.
Небольшое дополнение: лучше привыкать давать названия переменным на латинице(т.е. английский алфавит), т.к. для VBA английский язык «родной» и лучше использовать его.
Небольшой пример использования переменных в кодах:
Sub main() 'объявляем переменные с назначением конкретных типов 'As String - текст 'As Long - целое число Dim sAddress As String, sNewAddress As String, sShName As String Dim lRow As Long Dim rRange as Range 'назначили переменной тип ячейка/диапазон 'присвоили переменной rRange ссылку на текущую выделенную ячейку Set rRange = Selection 'меняем выделение - выделяем ячейку D9 Range("D9").Select 'назначаем переменной адрес выделенных ячеек sAddress = Selection.Address 'назначаем переменной lRow значение первой строки выделенной области lRow = Selection.Row 'показываем сообщение MsgBox "Адрес выделенной области: " & sAddress, vbInformation, "www.excel-vba.ru" MsgBox "Номер первой строки: " & lRow, vbInformation, "www.excel-vba.ru" 'назначаем другой переменной значение адреса ячейки A1 sNewAddress = "A1" 'выделяем ячейку, заданную переменной sNewAddres Range(sNewAddress).Select MsgBox "Адрес выделенной области: " & sNewAddress, vbInformation, "www.excel-vba.ru" 'выделяем изначально выделенную ячейку, используя переменную rRange rRange.Select MsgBox "Адрес выделенной области: " & rRange.Address, vbInformation, "www.excel-vba.ru" 'задаем значение переменной sShName = "excel-vba" 'переименовываем активный лист на имя, заданное переменной ActiveSheet.Name = sShName End Sub
Sub main() ‘объявляем переменные с назначением конкретных типов ‘As String — текст ‘As Long — целое число Dim sAddress As String, sNewAddress As String, sShName As String Dim lRow As Long Dim rRange as Range ‘назначили переменной тип ячейка/диапазон ‘присвоили переменной rRange ссылку на текущую выделенную ячейку Set rRange = Selection ‘меняем выделение — выделяем ячейку D9 Range(«D9»).Select ‘назначаем переменной адрес выделенных ячеек sAddress = Selection.Address ‘назначаем переменной lRow значение первой строки выделенной области lRow = Selection.Row ‘показываем сообщение MsgBox «Адрес выделенной области: » & sAddress, vbInformation, «www.excel-vba.ru» MsgBox «Номер первой строки: » & lRow, vbInformation, «www.excel-vba.ru» ‘назначаем другой переменной значение адреса ячейки A1 sNewAddress = «A1» ‘выделяем ячейку, заданную переменной sNewAddres Range(sNewAddress).Select MsgBox «Адрес выделенной области: » & sNewAddress, vbInformation, «www.excel-vba.ru» ‘выделяем изначально выделенную ячейку, используя переменную rRange rRange.Select MsgBox «Адрес выделенной области: » & rRange.Address, vbInformation, «www.excel-vba.ru» ‘задаем значение переменной sShName = «excel-vba» ‘переименовываем активный лист на имя, заданное переменной ActiveSheet.Name = sShName End Sub
Просмотреть пошагово выполнение данного кода поможет статья: Отлов ошибок и отладка кода VBA
Важно! Назначение значений переменным задается при помощи знака равно(=). Однако, есть небольшой нюанс: для переменных типа Object (а так же других объектных типов(Workbook, Worksheet, Range, Cells, Chart и т.п.)) присвоение идет при помощи ключевого оператора Set :
'присвоили переменной rRange ссылку на текущую выделенную ячейку Set rRange = Selection
‘присвоили переменной rRange ссылку на текущую выделенную ячейку Set rRange = Selection
это так же называется присвоением ссылки на объект. Почему именно ссылки? Все просто: при помещении в переменную непосредственно ячейки или диапазона( Set var = Range(«A1») или Set rRange = Selection ) нет никакого запоминания самой ячейки. В переменную помещается лишь ссылка на эту ячейку(можете считать, что это как ссылка в формулах) со всеми вытекающими: такое назначение не запоминает свойства ячейки до или после — в переменной хранится ссылка на конкретную ячейку и доступ есть исключительно к свойствам ячейки на текущий момент. Чтобы запомнить для этой ячейки значение, цвет или даже адрес (а так же и другие свойства) до её изменения и применить запомненное даже после изменения/перемещения самой ячейки — необходимо запоминать в переменные именно свойства ячейки:
Sub main() Dim val, l_InteriorColor As Long, l_FontColor As Long Dim rRange As Range 'назначили переменной тип ячейка/диапазон 'присвоили переменной rRange ссылку на активную ячейку Set rRange = ActiveCell 'запоминаем свойства ячейки val = rRange.Value 'значение l_InteriorColor = rRange.Interior.Color 'цвет заливки l_FontColor = rRange.Font.Color 'цвет шрифта 'копируем другую ячейку и вставляем на место активной ActiveSheet.Range("D1").Copy rRange 'проверяем, что rRange теперь имеет совершенно другие свойста - как у D1 MsgBox "Значение rRange: " & rRange.Value & vbNewLine & _ "Цвет заливки rRange: " & rRange.Interior.Color & vbNewLine & _ "Цвет шрифта rRange: " & rRange.Font.Color & vbNewLine, vbInformation, "www.excel-vba.ru" 'назначаем свойства из сохраненных в переменных rRange.Value = val 'значение rRange.Interior.Color = l_InteriorColor 'цвет заливки rRange.Font.Color = l_FontColor 'цвет шрифта 'проверяем, что rRange возвращены параметры до копирования MsgBox "Значение rRange: " & rRange.Value & vbNewLine & _ "Цвет заливки rRange: " & rRange.Interior.Color & vbNewLine & _ "Цвет шрифта rRange: " & rRange.Font.Color & vbNewLine, vbInformation, "www.excel-vba.ru" End Sub
Sub main() Dim val, l_InteriorColor As Long, l_FontColor As Long Dim rRange As Range ‘назначили переменной тип ячейка/диапазон ‘присвоили переменной rRange ссылку на активную ячейку Set rRange = ActiveCell ‘запоминаем свойства ячейки val = rRange.Value ‘значение l_InteriorColor = rRange.Interior.Color ‘цвет заливки l_FontColor = rRange.Font.Color ‘цвет шрифта ‘копируем другую ячейку и вставляем на место активной ActiveSheet.Range(«D1»).Copy rRange ‘проверяем, что rRange теперь имеет совершенно другие свойста — как у D1 MsgBox «Значение rRange: » & rRange.Value & vbNewLine & _ «Цвет заливки rRange: » & rRange.Interior.Color & vbNewLine & _ «Цвет шрифта rRange: » & rRange.Font.Color & vbNewLine, vbInformation, «www.excel-vba.ru» ‘назначаем свойства из сохраненных в переменных rRange.Value = val ‘значение rRange.Interior.Color = l_InteriorColor ‘цвет заливки rRange.Font.Color = l_FontColor ‘цвет шрифта ‘проверяем, что rRange возвращены параметры до копирования MsgBox «Значение rRange: » & rRange.Value & vbNewLine & _ «Цвет заливки rRange: » & rRange.Interior.Color & vbNewLine & _ «Цвет шрифта rRange: » & rRange.Font.Color & vbNewLine, vbInformation, «www.excel-vba.ru» End Sub
Это так же распространяется на все другие объекты. Т.е. те переменные, значения которым назначаются через оператор Set.
Для других же типов Set не нужен и в переменную значение заносится без этих нюансов.
Константы
Так же есть и иной вид «переменных» — константы. Это такая же переменная, только(как следует из её названия) — она не может быть изменена во время выполнения кода, т.к. является величиной постоянной и значение её назначается только один раз — перед выполнением кода.
Const sMyConst As String = "Имя моей программы"
Const sMyConst As String = «Имя моей программы»
Константам могут быть назначены данные тех же типов, что и для переменных, за исключением типа Object, т.к. Object это всегда ссылка на объект, который как правило обладает «динамическими»(т.е. обновляющимися) свойствами. А изменение для констант недопустимо.
Для дополнительной области видимости/жизни констант используется только Public . Если область видимости не указана, то константа будет доступна только из того модуля, в котором объявлена. Здесь обращаю внимание на то, что Dim уже не используется, т.к. Dim это идентификатор только для переменных. Пару важных отличий объявления констант от объявления переменных:
- при объявлении константы необходимо обязательно указывать явно, что это константа ключевым словом Const
- сразу в момент объявления необходимо назначить константе значение: = «Имя моей программы»
Во всем остальном объявление и применение констант идентично объявлению переменных. Коротко приведу пару примеров.
Если константа объявлена внутри процедуры:
Sub TestConst() Const sMyConst As String = "Имя моей программы" MsgBox sMyConst 'показываем сообщение с именем программы End Sub
Sub TestConst() Const sMyConst As String = «Имя моей программы» MsgBox sMyConst ‘показываем сообщение с именем программы End Sub
то она не может быть использована в другой процедуре:
Sub TestConst() Const sMyConst As String = "Имя моей программы" MsgBox sMyConst 'показываем сообщение с именем программы End Sub Sub TestConst2() MsgBox sMyConst 'вызовет ошибку Variable not defined End Sub
Sub TestConst() Const sMyConst As String = «Имя моей программы» MsgBox sMyConst ‘показываем сообщение с именем программы End Sub Sub TestConst2() MsgBox sMyConst ‘вызовет ошибку Variable not defined End Sub
Чтобы использовать одну константу во всех процедурах модуля(того, в котором она объявлена), необходимо объявить её в области объявлений:
Const sMyConst As String = "Имя моей программы" Sub TestConst() MsgBox sMyConst 'показываем сообщение с именем программы End Sub Sub TestConst2() MsgBox sMyConst 'уже не вызовет ошибку Variable not defined End Sub
Const sMyConst As String = «Имя моей программы» Sub TestConst() MsgBox sMyConst ‘показываем сообщение с именем программы End Sub Sub TestConst2() MsgBox sMyConst ‘уже не вызовет ошибку Variable not defined End Sub
Чтобы использовать одну константу во всех процедурах проекта(книги), необходимо объявить её как Public:
Public Const sMyConst As String = "Имя моей программы" Sub TestConst() MsgBox sMyConst 'показываем сообщение с именем программы End Sub Sub TestConst2() MsgBox sMyConst 'не вызовет ошибку Variable not defined, даже если процедура в другом модуле End Sub
Public Const sMyConst As String = «Имя моей программы» Sub TestConst() MsgBox sMyConst ‘показываем сообщение с именем программы End Sub Sub TestConst2() MsgBox sMyConst ‘не вызовет ошибку Variable not defined, даже если процедура в другом модуле End Sub
Подробнее можно прочитать выше — как я уже писал для констант применяются те же правила, что и для переменных.
Статья помогла? Поделись ссылкой с друзьями!
Работа с переменными
Эта статья содержит полное руководство по работе с переменными и использованию VBA Dim.
Первый раздел содержит краткое руководство по использованию оператора Dim, включая примеры и формат оператора Dim.
Остальная часть поста содержит наиболее полное руководство, которое вы найдете в VBA Dim Statement.
Если вы заинтересованы в объявлении параметров, вы можете прочитать о них здесь.
Краткое руководство по использованию VBA Dim Statement
| Описание | Формат | Пример |
| Базовая переменная |
Dim [имя переменной] As [Тип] |
Dim count As Long Dim amount As Currency Dim name As String Dim visible As Boolean |
| Фиксированная строка |
Dim [имя переменной] As String *[размер] |
Dim s As String * 4 Dim t As String * 10 |
| Вариант | Dim [имя переменной] As Variant Dim [имя переменной] |
Dim var As Variant Dim var |
| Объект использует Dim и New |
Dim [имя переменной] As New [тип объекта] |
Dim coll As New Collection Dim coll As New Class1 |
| Объект использует Dim и New |
Dim [имя переменной] As [тип объекта] Set [имя переменной] = New [тип объекта] |
Dim coll As Collection Set coll = New Collection Dim coll As Class1 Set coll = New Class1 |
| Статический массив |
Dim [имя переменной] ([первый] To [последний] ) As[Тип] |
Dim arr(1 To 6) As Long |
| Динамический массив |
Dim [имя переменной]() As [Тип] ReDim [имя переменной] ([первый] To [последний]) |
Dim arr() As Long ReDim arr(1 To 6) |
| Внешняя библиотека (Раннее связывание) * |
Dim [имя переменной] As New [пункт] |
Dim dict As New Dictionary |
| Внешняя библиотека (Раннее связывание с использованием Set) * |
Dim [имя переменной] As [пункт] Set [имя переменной] = New [пункт] |
Dim dict As Dictionary Set dict = New Dictonary |
| Внешняя библиотека (Позднее связывание) |
Dim [имя переменной] As Object Set [имя переменной] = CreateObject («[библиотека]») |
Dim dict As Object Set dict = CreateObject(«Scripting. Dictionary») |
* Примечание. Для раннего связывания необходимо добавить справочный файл с помощью меню «Инструменты» -> «Ссылки». Смотрите здесь, как добавить ссылку на Dictonary.
Полезные ссылки
- Объявление параметров в подпрограмме или функции
- Использование объектов в VBA
- Массивы VBA
- Коллекции VBA
- Словарь VBA
- VBA Workbook
- VBA Worksheet
Что такое VBA Dim Statement?
Ключевое слово Dim — это сокращение от Dimension. Он используется для объявления переменных в VBA.
Объявление означает, что мы говорим VBA о переменной, которую будем использовать позже.
Существует четыре типа Dim Statement. Все они очень похожи по синтаксису.
Ниже приводится краткое описание каждого типа
- Basic variable — этот тип переменной содержит одно значение. Это такие типы, как Long, String, Date, Double, Currency.
- Variant — VBA решает во время выполнения, какой тип будет использоваться. Вы должны избегать вариантов, где это возможно, но в некоторых случаях требуется их использование.
- Object — это переменная, которая может иметь несколько методов (то есть подпрограмм / функций) и несколько свойств (то есть значений). Есть 3 вида:
Объекты Excel, такие как объекты Workbook, Worksheet и Range.
Пользовательские объекты, созданные с использованием модулей классов.
Внешние библиотеки, такие как Словарь. - 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. В этом случае переменная автоматически будет типом варианта.
Это может привести к таким проблемам, как
- Все переменные являются вариантами (проблемы с этим см. В разделе «Варианты»).
- Некоторые переменные ошибки останутся незамеченными.
Из-за этих проблем рекомендуется сделать использование Dim обязательным в нашем коде. Мы делаем это с помощью оператора 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 здесь.
Тем не менее, большую часть времени вы будете использовать следующие:
| Тип | Хранение | Диапазон | Описание |
| Boolean | 2 байта | ИСТИНА или ЛОЖЬ | Эта переменная может быть ИСТИНА или ЛОЖЬ. |
| Long | 4 байта | от -2,147,483,648 до 2,147,483,647 |
Long — это сокращение от Long Integer. Используйте это вместо типа Integer * |
| Currency | 8 байт | от -1,79769313486231E308 до -4,94065645841247E-324 для отрицательных значений; от 4.94065645841247E-324 до 1.79769313486232E308 для положительных значений |
Аналогично Double, но имеет только 4 знака после запятой |
| Double | 8 байт | от -922,337,203,685,477.5808 до 922,337,203,685,477.5807 |
|
| Date | 8 байт | С 1 января 100 по 31 декабря 9999 |
|
| String | меняется | От 0 до примерно 2 миллиардов |
Содержит текст |
* Первоначально мы использовали бы тип 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
Однако использование вариантов является плохой практикой, и вот почему:
- VBA не будет замечать неправильных ошибок типа (т. Е. Несоответствие данных).
- Вы не можете получить доступ к Intellisense.
- 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
Dim wk As Workbook
Когда вы используете переменную wk с десятичной точкой, VBA автоматически отобразит доступные опции для переменной.
Вы можете увидеть Intellisense на скриншоте ниже
Если вы используете Variant как тип, то Intellisense будет недоступен
Dim wk As Variant
Это потому, что VBA не будет знать тип переменной до времени выполнения.
Использование Dim с Objects
Если вы не знаете, что такое Objects, вы можете прочитать мою статью об VBA Objects здесь.
Есть 3 типа объектов:
- Объекты Excel
- Объекты модуля класса
- Внешние объекты библиотеки
Примечание. Объект 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) и многих других задач.
Мы можем создать эти объекты двумя способами:
- Раннее связывание
- Позднее связывание
Давайте посмотрим на это по очереди.
Раннее связывание
Раннее связывание означает, что мы добавляем справочный файл. Как только этот файл добавлен, мы можем рассматривать объект как объект модуля класса.
Мы добавляем ссылку, используя 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 есть два типа массивов:
- Статический — размер массива задается в операторе Dim и не может изменяться.
- Динамический — размер массива не указан в выражении 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 для объяснения различных типов ошибок.
| Ошибка | Тип | Причина |
| Массив уже рассчитан |
Компиляция | Использование Redim для статического массива. |
| Ожидаемый: идентификатор |
Синтаксис | Использование зарезервированного слова в качестве имени переменной. |
| Ожидаемый: новый тип имени |
Синтаксис | Тип отсутствует в выражении Dim. |
| Переменная объекта или переменная блока не установлена |
Время выполнения | New не был использован для создания объекта. |
| Переменная объекта или переменная блока не установлена |
Время выполнения | Set не использовался для назначения переменной объекта. |
| Пользовательский тип не определен |
Компиляция | Тип не распознан. Это может произойти, если ссылочный файл не добавлен в меню «Инструменты-> Ссылка» или имя модуля класса написано неправильно. |
| Недопустимый оператор вне блока Type |
Компиляция | Имя переменной отсутствует в выражении Dim |
| Переменная не определена |
Компиляция | Переменная используется перед Dim-строкой. |
Локальные и глобальные переменные
Когда мы используем 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 типов:
- Dim — используется для объявления локальных переменных, т. Е. В процедурах.
- Private — используется для объявления глобальных переменных и процедур. Эти переменные доступны только для текущего модуля.
- Public — используется для объявления глобальных переменных и процедур. Эти переменные доступны во всех модулях.
- Global — старая и устаревшая версия Public. Может использоваться только в стандартных модулях. Он существует только для обратной совместимости.
Заключение
На этом мы заканчиваем статью о VBA Dim Statement. Если у вас есть какие-либо вопросы или мысли, пожалуйста, дайте мне знать в комментариях ниже.
Переменные в VBA
Переменная – это поименованная, либо адресуемая любым другим способом область компьютерной памяти, в которой хранятся данные указанного типа. Эти данные, в ходе выполнения программы, могут изменяться неограниченное количество раз.
Можно сказать, что переменная – это контейнер (ящик) для хранения изменяемых данных. Сюда можно положить какое-то значение (например, дату, число, строку и т.д.). Данные, которые хранит в себе переменная, называют значением переменной. И в ходе выполнения программы, эти данные можно получить и изменить.
Представьте, что вы повесили на ящик ярлык (дали ему имя) «Игрушки» и положили в него плюшевого мишку. Получается, что вы создали переменную – Игрушки. Значение переменной Игрушки будет – плюшевый мишка. В любой момент вы может достать этого мишку из ящика, обратившись к нему по имени «Игрушки» и положить в ящик уже не мишку, а например, зайчика. И тогда у вас будет переменная Игрушки со значением – зайчик.
Как вы уже поняли, прежде чем начинать работу с переменной, её нужно объявить.
Объявления переменных в VBA
Правила присвоения имени переменных в VBA:
- Имя переменной должно быть уникальным (две разные переменные не могут иметь одно и то же имя).
- Максимальная длина имени переменной не может превышать 255 символов.
- Первым символом в имени переменной должна быть буква, после которой допускается использование цифр, или знака подчеркивания «_».
- Нельзя использовать пробел, точку, восклицательный знак, а также символы @, #, $, %, &.
- Нельзя использовать зарезервированные (ключевые) слова (слова, которые подсвечиваются синим цветом в окне редактора кода).
При присвоении имени переменной можно использовать как строчные, так и прописные буквы в любом сочетании. Например, MySum и mysum компилятор VBA воспримет как одно и то же имя. Если вы первый раз написали имя переменной MySum (первое вхождение), а второй раз написали mysum (второе вхождение), то редактор VBA автоматически исправит mysum на MySum.
Прежде чем использовать переменную, её рекомендуется объявить. То есть нужно указать компилятору, что требуется создать переменную с данным именем, в которой будут храниться данные определённого типа.
Если создать переменную без её предварительного объявления, то она будет иметь тип Variant. В VBA этот тип переменной присваивается по умолчанию.
Если переменную создают (используют в операторе) без предварительного объявления, то в таком случае говорят, что переменная объявлена не явно.
Чтобы явно объявить переменную используют оператор Dim. Например, объявить переменную в VBA можно так:
Dim MyAge As IntegerВ этой строке, MyAge – это имя переменной, а Integer – это присвоенный ей тип данных (Целое число).
В одной строке можно объявить и несколько переменных. Делается это через запятую вот так:
Dim MyAge1 As Integer, MyAge2 As Integer, MyName As StringОбратите внимание, что при объявлении переменных в одной строке, оператор Dim указывается один раз, а тип данных указывается для каждой переменной отдельно.
Кроме того, в одном операторе Dim разрешается смешивать объявление переменных различных типов: Intager, String, Date, Boolean и т.д.
Область видимости переменных в VBA
Переменную можно неоднократно использовать только в той части программы, где она доступна. В VBA предусмотрено 4 ключевых слова для определения области видимости переменных:
- Dim – используется наиболее часто при объявлении переменных в VBA. Если переменная объявлена как Dim в области объявлений модуля, то она будет доступна для всех процедур в этом модуле, если в процедуре – то такая переменная будет доступна только на время работы этой процедуры.
- Private – при объявлении переменных в стандартных модулях VBA работает точно так же, как и Dim.
- Public – такая переменная будет доступна всем процедурам во всех модулях данного проекта, если вы объявили её в области объявлений модуля. Если вы объявили ее внутри процедуры, она будет вести себя как Dim.
- Static – такие переменные можно использовать только внутри процедуры. Они видны только внутри процедуры, в которой объявлены. Переменные, объявленные с оператором Static, сохраняют свои значения до тех пор, пока выполняется код.
Если нет никаких особых требований, то при объявлении переменных в VBA лучше всего выбирать область видимости Dim.
Как присваивать имена объектам в VBA правильно?
При создании VBA программ, присваивать имена объектам рекомендуется по следующим правилам:
- Имя переменной должно начинаться с префикса, записанного строчными буквами. Префикс указывает, что именно будет храниться в этой переменной:
- str (или s) – String, символьное значение;
- fn (или f) – функция;
- sub – процедура;
- c (или все буквы имени заглавные) – константа;
- b – Boolean, логическое значение (True или False);
- d – дата;
- obj (или o) – ссылка на объект;
- n – числовое значение;
- Имена функций, методов и каждое слово в составном слове должно начинаться с заглавной буквы (Например, MsgBox ActiveDocument.Name).
- Константы принято записывать заглавными буквами, а между словами ставить символ подчеркивания, например COMPANY_NAME.