Общие сведения о типах данных Excel VBA (переменные и константы)

В Excel VBA часто требуется использовать переменные и константы.

При работе с VBA переменная - это место в памяти вашего компьютера, где вы можете хранить данные. Тип данных, которые вы можете хранить в переменной, будет зависеть от типа данных переменной.

Например, если вы хотите хранить целые числа в переменной, вашим типом данных будет «Целое число», а если вы хотите сохранить текст, тогда вашим типом данных будет «Строка».

Подробнее о типах данных далее в этом руководстве.

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

Зачем использовать переменные в VBA?

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

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

Например, ниже приведен код, который складывает первые 10 положительных чисел и затем отображает результат в окне сообщения:

Sub AddFirstTenNumbers () Dim Var As Integer Dim i As Integer Dim k as Integer For i = 1 To 10 k = k + i Next i MsgBox k End Sub

В приведенном выше коде есть три переменные - Вар, я, и k.

В приведенном выше коде используется цикл For Next, в котором все три переменные изменяются по мере завершения циклов.

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

Ниже приведены некоторые правила, которые следует учитывать при именовании переменных в VBA:

1. Вы можете использовать буквы, цифры и знаки препинания, но первое число должно быть алфавитом.
2. В имени переменной нельзя использовать пробел или точку. Однако вы можете использовать символ подчеркивания, чтобы сделать имена переменных более читабельными (например, Interest_Rate).
3. Вы не можете использовать специальные символы (#, $,%, & или!) В именах переменных.
4. VBA не различает регистр в имени переменной. Таким образом, «процентная ставка» и «процентная ставка» одинаковы для VBA. Вы можете использовать смешанный регистр, чтобы переменные были более удобочитаемыми.
5. VBA имеет несколько зарезервированных имен, которые вы можете использовать для имени переменной. Например, вы не можете использовать слово «Next» в качестве имени переменной, поскольку это зарезервированное имя для цикла For Next.
6. Имя вашей переменной может содержать до 254 символов.

Тип данных переменных

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

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

Ниже приведена таблица, в которой показаны все доступные типы данных, которые вы можете использовать в Excel VBA:

Тип данных	Используемые байты	Диапазон значений
Байт	1 байт	От 0 до 255
Логический	2 байта	Правда или ложь
Целое число	2 байта	От -32 768 до 32 767
Длинное (длинное целое)	4 байта	От -2 147 483 648 до 2 147 483 647
Одинокий	4 байта	От -3.402823E38 до -1.401298E-45 для отрицательных значений; 1.401298E-45 до 3.402823E38 для положительных значений
Двойной	8 байт	От -1,79769313486231E308 до -4,94065645841247E-324 для отрицательных значений; От 4.94065645841247E-324 до 1.79769313486232E308 для положительных значений
Валюта	8 байт	От -922,337,203,685,477,5808 до 922,337,203,685,477,5807
Десятичная дробь	14 байт	+/- 79,228,162,514,264,337,593,543,950,335 без десятичной точки; +/- 7,9228162514264337593543950335 с 28 знаками справа от десятичной точки
Дата	8 байт	1 января 100 г. по 31 декабря 9999 г.
Объект	4 байта	Ссылка на любой объект
Строка (переменной длины)	10 байт + длина строки	От 0 до примерно 2 миллиардов
Строка (фиксированной длины)	Длина строки	1 приблизительно до 65 400
Вариант (с цифрами)	16 байт	Любое числовое значение в диапазоне до Double
Вариант (с символами)	22 байта + длина строки	Тот же диапазон, что и для строки переменной длины
Определяемые пользователем	Варьируется	Диапазон каждого элемента совпадает с диапазоном его типа данных.

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

Например, если вам нужно использовать переменную, которая предназначена для хранения номера месяца, вы можете использовать тип данных BYTE (который может принимать значения от 0 до 255). Поскольку номер месяца не будет больше 12, это будет работать нормально, а также зарезервировать меньше памяти для этой переменной.

Напротив, если вам нужна переменная для хранения номеров строк в Excel, вам необходимо использовать тип данных, который может содержать число до 1048756. Поэтому лучше всего использовать тип данных Long.

Объявление типов данных переменных

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

Ниже приведен пример, в котором я объявил разные типы данных для разных переменных:

Sub DeclaringVariables () Dim X как целое число Dim Email как строка Dim FirstName As String Dim RowCount As Long Dim TodayDate As Date End Sub

Чтобы объявить переменный тип данных, вам необходимо использовать оператор DIM (сокращенно от Dimension).

В 'Dim X как целое число‘, Я объявил переменную X как целочисленный тип данных.

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

Если я попытаюсь присвоить ему значение, не являющееся целым числом, я получу сообщение об ошибке (как показано ниже):

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

Сделать объявление переменной обязательным (опция явная)

Хотя вы можете писать код без объявления переменных, это хорошая практика.

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

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

Вариант Явный

Когда вы добавляете «Option Explicit», вам нужно будет объявить все переменные перед запуском кода. Если есть переменная, которая не была объявлена, VBA выдаст ошибку.

Использование Option Explicit дает огромное преимущество.

Иногда вы можете допустить опечатку и ввести неверное имя переменной.

Как правило, VBA не может узнать, является ли это ошибкой или преднамеренной. Однако, когда вы используете «Option Explicit», VBA увидит неправильно написанное имя переменной как новую переменную, которая не была объявлена, и покажет вам ошибку. Это поможет вам идентифицировать эти имена переменных с ошибками, которые может быть довольно сложно обнаружить в длинном коде.

Ниже приведен пример, в котором использование «Option Explicit» идентифицирует ошибку (которая не могла бы быть перехвачена, если бы я не использовал «Option Explicit»).

Sub CommissionCalc () Dim CommissionRate As Double If Range ("A1"). Value> 10000 Then CommissionRate = 0.1 Else CommissionRtae = 0.05 End If MsgBox "Total Commission:" & Range ("A1"). Value * CommissionRate End Sub

Обратите внимание, что я однажды ошибся в написании слова «CommissionRate» в этом коде.

Если я не использую Option Explicit, этот код запустится и даст мне неправильное значение общей комиссии (в случае, если значение в ячейке A1 меньше 10000).

Но если я использую Option Explicit в верхней части модуля, он не позволит мне запустить этот код, пока я не исправлю слово с ошибкой или не объявлю его как другую переменную. Он покажет ошибку, как показано ниже:

Хотя вы можете вставлять строку «Option Explicit» каждый раз, когда кодируете, вот шаги, чтобы она отображалась по умолчанию:

1. На панели инструментов редактора VB нажмите Инструменты.
2. Щелкните Параметры.
3. В диалоговом окне "Параметры" перейдите на вкладку "Редактор".
4. Отметьте опцию - «Требовать объявление переменной».
5. Щелкните ОК.

После того, как вы включите эту опцию, всякий раз, когда вы открываете новый модуль, VBA автоматически добавляет к нему строку «Option Explicit».

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

Объем переменных

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

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

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

Есть три способа определить область переменной в Excel VBA:

1. Внутри одной подпрограммы (локальные переменные)
2. Внутри модуля (переменные уровня модуля)
3. Во всех модулях (публичные переменные)

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

Внутри одной подпрограммы (локальные переменные)

Когда вы объявляете переменную в подпрограмме / процедуре, эта переменная доступна только для этой подпрограммы.

Вы не можете использовать его в других подпрограммах модуля.

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

В приведенном ниже примере переменные объявлены внутри подпрограммы и будут удалены, когда эта подпрограмма завершится.

Внутри модуля (переменные уровня модуля)

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

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

В приведенном выше примере переменная «i» объявлена ​​в верхней части модуля и доступна для использования всеми модулями.

Обратите внимание, что при завершении подпрограммы переменные уровня модуля не удаляются (они сохраняют свое значение).

Ниже приведен пример, где у меня есть два кода. Когда я запускаю первую процедуру, а затем вторую, значение «i» становится 30 (поскольку оно несет значение 10 из первой процедуры).

Во всех модулях (общедоступные переменные)

Если вы хотите, чтобы переменная была доступна во всех процедурах в книге, вам необходимо объявить ее с помощью ключевого слова Public (вместо DIM).

Приведенная ниже строка кода в верхней части модуля сделает переменную «CommissionRate» доступной во всех модулях в книге.

 Государственная комиссия Ставка в два раза больше

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

Статические переменные (сохраняющие значение)

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

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

Позвольте мне сначала показать вам, что происходит в нормальном случае.

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

Подпроцедура1 () Dim i As Integer i = i + 10 MsgBox i End Sub

Теперь, если я использую ключевое слово Static вместо DIM и запускаю процедуру несколько раз, она будет продолжать показывать значения с шагом 10. Это происходит, когда переменная «i» сохраняет свое значение и использует его в вычислениях.

Подпроцедура1 () Статический i как целое число i = i + 10 MsgBox i End Sub

Объявление констант в Excel VBA

Хотя переменные могут изменяться во время выполнения кода, если вы хотите иметь фиксированные значения, вы можете использовать константы.

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

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

Например, если вы рассчитываете комиссию и ставка комиссии составляет 10%, вы можете создать константу (CommissionRate) и присвоить ей значение 0,1.

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

Ниже приведен пример кода, в котором я присвоил значение константе:

Sub CalculateCommission () Dim CommissionValue As Double Const CommissionRate As Double = 0,1 CommissionValue = Range ("A1") * CommissionRate MsgBox CommissionValue End Sub

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

Постоянная ставка комиссии как двойная = 0,1

При объявлении констант нужно начинать с ключевого слова ‘Const‘, За которым следует имя константы.

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

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

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

1. В рамках одной подпрограммы (локальные константы): Они доступны в подпрограмме / процедуре, в которой они объявлены. По окончании процедуры эти константы удаляются из системной памяти.
2. Внутри модуля (константы уровня модуля): Они объявляются в верхней части модуля (перед любой процедурой). Они доступны для всех процедур в модуле.
3. Во всех модулях (публичные константы): Они объявляются с использованием ключевого слова Public в верхней части любого модуля (перед любой процедурой). Они доступны для всех процедур во всех модулях.

Вам также могут понравиться следующие руководства по VBA:

• Как записать макрос в Excel
• Работа с ячейками и диапазонами в Excel VBA
• Работа с листами с использованием Excel VBA
• Работа с книгами в Excel VBA
• События VBA
• Циклы Excel VBA
• Как запустить макрос в Excel
• Если, то другое выражение в Excel VBA.