← Редактор VBA и безопасность…Условия и циклы →

Переменные и типы

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

Переменная — именованная коробочка в памяти, куда макрос кладёт значение (число, текст, дату или целый диапазон ячеек), чтобы потом им пользоваться. Тип — договорённость о том, что именно в этой коробочке лежит.

Зачем переменные, если есть ячейки

Логичный вопрос человека, который всю жизнь работал в Excel: зачем какая-то «память», если данные и так лежат на листе? Ответ — три причины, и все три вы почувствуете уже на первой рабочей задаче.

Первая — читаемость. Сравните два кода. Вот премия сотрудникам, посчитанная «в лоб»:

Sub PremiyaBezPeremennykh()
    Range("C2").Value = Range("B2").Value * 0.15
    Range("C3").Value = Range("B3").Value * 0.15
    Range("C4").Value = Range("B4").Value * 0.15
End Sub

Через месяц никто не вспомнит, что такое 0.15 и почему оно повторяется трижды. А если ставка премии изменится на 12 %, править придётся в трёх местах — и обязательно где-нибудь забудут. Вот тот же смысл, но с переменными:

Sub PremiyaSPeremennymi()
    Dim stavkaPremii As Double
    Dim oklad As Double
    Dim i As Long

    stavkaPremii = 0.15

    For i = 2 To 4
        oklad = Cells(i, 2).Value
        Cells(i, 3).Value = oklad * stavkaPremii
    Next i
End Sub

Ставка задана один раз и названа человеческим именем. Строки перебирает цикл — а значит, кода не станет больше ни при 4 сотрудниках, ни при 4000.

Вторая — скорость. Каждое обращение к ячейке (Range("B2").Value) — это разговор VBA с Excel через довольно медленный «переводчик». Если одно и то же значение нужно три раза, прочитайте его в переменную один раз и дальше работайте с ней. На больших таблицах разница — секунды против минут.

Третья — надёжность. Переменная с типом не даст записать текст туда, где ожидается число, и подскажет об ошибке сразу, а не через полгода в квартальном отчёте.

Dim: как объявить переменную

Объявление выглядит так: Dim имя As Тип. Слово Dim — сокращение от dimension («выделить место»). Имя может состоять из латиницы, цифр и подчёркиваний, начинаться с буквы и не совпадать со служебными словами VBA.

ТипЧто хранитКогда брать
Longцелое число примерно до ±2,1 млрдномера строк и столбцов, счётчики, количество — тип по умолчанию для всех целых
Doubleдробное числосуммы, проценты, средние, коэффициенты
StringтекстФИО, названия, статусы, имена листов
BooleanTrue или Falseфлаги: «нашли», «оплачено», «нужно пропустить»
Dateдату и/или времясроки, даты отгрузки
Range, Worksheet, Workbookобъекты Excelссылка на диапазон, лист, книгу
Variantчто угодно, включая «пусто» и «ошибка»когда заранее неизвестно, что в ячейке; массивы данных с листа

Обратите внимание: Integer в таблице нет — и это не забывчивость. Он умеет считать только до 32 767, а строк на листе Excel больше миллиона. Счётчик Integer в цикле по строкам рано или поздно упадёт с ошибкой Overflow (ошибка 6). Пишите Long всегда — он не медленнее.

Объектные переменные и волшебное слово Set

Обычной переменной значение присваивают знаком равенства. Но если переменная хранит объект (лист, диапазон, книгу), перед присваиванием обязательно ставится Set. Это не каприз языка: Set означает «запомни ссылку на этот объект», а простое = означало бы «возьми у него значение».

Sub RabotaSListom()
    Dim ws As Worksheet
    Dim itogo As Range

    Set ws = ThisWorkbook.Worksheets("Продажи")
    Set itogo = ws.Range("D100")

    itogo.Value = WorksheetFunction.Sum(ws.Range("D2:D99"))
    itogo.Font.Bold = True
End Sub

Теперь ws — это конкретный лист «Продажи», и все обращения ws.Range(...), ws.Cells(...) гарантированно идут именно к нему, а не к тому листу, который случайно оказался активным в момент запуска. Забудете Set — получите ошибку 91 «Object variable or With block variable not set» или загадочную 424 «Object required».

Option Explicit — одна строка, которая экономит часы

По умолчанию VBA разрешает использовать переменную, не объявляя её: встретил новое имя — сам создал. Звучит удобно, а на деле это самая дорогая «фича» языка. Смотрите:

Sub SchitatSkidku()
    tsena = 1000
    skidka = 0.2
    itogo = tsena * (1 - skdka)   ' опечатка: skdka вместо skidka
    MsgBox itogo
End Sub

Результат:

1000

Макрос не упал, ничего не подчеркнул красным — просто показал неверную сумму. VBA увидел незнакомое имя skdka, создал новую пустую переменную, посчитал 1 - 0 и честно вернул полную цену. Найдите такую опечатку в отчёте на 300 строк.

Лекарство — первая строка модуля:

Option Explicit

С ней VBA требует объявлять каждую переменную и на попытку использовать skdka сразу ругается: Variable not defined. Ошибка ловится до запуска, а не после сдачи отчёта.

Чтобы редактор дописывал эту строку в новые модули сам, включите галочку один раз: в окне VBA Tools → Options → Editor → Require Variable Declaration (в русской версии — Сервис → Параметры → Редактор → Требовать объявления переменных). В уже созданные модули строку придётся дописать руками.

Ловушка «Dim a, b As Long»

Тут VBA ведёт себя не так, как большинство языков, и на этом спотыкаются почти все:

Dim i, j As Long      ' i — Variant (!), j — Long
Dim i As Long, j As Long   ' вот так правильно: обе Long

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

Область видимости: кто откуда виден

Область видимости — это ответ на вопрос «в каком куске кода имя переменной вообще существует». В VBA три этажа.

  • Внутри процедуры. Dim внутри Sub — переменная живёт от Sub до End Sub и исчезает после выхода. 95 % ваших переменных должны быть такими.
  • Внутри модуля. Private в самом верху модуля, до первой процедуры — видна всем процедурам этого модуля.
  • Во всём проекте. Public в верху стандартного модуля — видна из любого модуля книги. Мощно и опасно: такую переменную может испортить любой макрос, и потом ищи виноватого.
Option Explicit

Public Const STAVKA_NDS As Double = 0.2   ' константа, видна всей книге
Private gPoslednyayaOshibka As String     ' видна только этому модулю

Sub RaschetSNDS()
    Dim bezNDS As Double      ' видна только внутри RaschetSNDS
    Dim sNDS As Double

    bezNDS = Range("B2").Value
    sNDS = bezNDS * (1 + STAVKA_NDS)

    Range("C2").Value = sNDS
    gPoslednyayaOshibka = ""
End Sub

Const — это переменная, которую нельзя изменить после объявления. Все «магические числа» (ставка НДС, имя листа, номер колонки) выносите в константы: имя объясняет смысл, а правка делается в одном месте.

Отдельно стоит Static: такая переменная живёт внутри процедуры, но не обнуляется между вызовами.

Sub SchetchikZapuskov()
    Static zapuskov As Long

    zapuskov = zapuskov + 1
    MsgBox "Макрос запущен раз: " & zapuskov
End Sub

Первый запуск покажет 1, второй — 2, и так до закрытия книги.

Как это работает

Когда вы пишете Dim summa As Double, VBA ещё до запуска знает: под summa нужно 8 байт, и в них будет дробное число. Такой код компилируется в прямые обращения к памяти — быстро и с проверкой на этапе компиляции. Если же тип не указан, переменная становится Variant: 16 байт плюс внутренняя пометка «а что тут сейчас лежит?», и на каждом действии VBA обязан эту пометку читать. На тысяче операций разницы не заметите, на миллионе — заметите отчётливо.

Важная деталь про ячейки: Range("B2").Value всегда возвращает Variant. Внутри может оказаться число, текст, дата, «пусто» (Empty) — или значение ошибки, если в ячейке #Н/Д. Поэтому строчка summa = Range("B2").Value, где summa объявлена как Double, спокойно работает на нормальных данных и падает с Type mismatch (ошибка 13) на первой же ячейке с текстом «нет данных». Защита — проверка перед присваиванием:

Dim summa As Double
Dim yacheyka As Range

Set yacheyka = Range("B2")

If IsNumeric(yacheyka.Value) And Not IsEmpty(yacheyka.Value) Then
    summa = yacheyka.Value
Else
    summa = 0
End If

Частые ошибки

  • Нет Option Explicit. Самая дорогая ошибка новичка: опечатка в имени не ломает макрос, а тихо портит цифры.
  • Dim i, j As Long. Первая переменная останется Variant. Пишите тип после каждого имени.
  • Integer для номеров строк. На 32 768-й строке — Overflow. Всегда Long.
  • Забытый Set. ws = Worksheets("Лист1") вместо Set ws = ... — ошибка 91 или 424.
  • Присваивание значения из «грязной» ячейки в Double. Пустая ячейка, текст или #ЗНАЧ! роняют макрос. Проверяйте IsNumeric/IsEmpty.
  • Имена вроде a, x1, temp2. Через две недели вы сами не поймёте свой код. lastRow, oklad, wsOtchet — читаются без комментариев.
  • Всё Public. Глобальные переменные превращают проект в поле, где любой макрос может испортить чужие данные. Передавайте значения параметрами (об этом — в уроке про процедуры).

Итоги

  • Dim имя As Тип — объявление переменной; тип пишем после каждого имени в строке.
  • Рабочий набор типов: Long (целые), Double (дробные), String (текст), Boolean (да/нет), Date (даты), Range/Worksheet (объекты), Variant (когда неизвестно, что придёт с листа).
  • Объектам значение присваивают через Set, обычным переменным — через =.
  • Option Explicit в первой строке каждого модуля — не рекомендация, а обязательное условие спокойной жизни.
  • Переменные держите как можно «локальнее»: Dim внутри процедуры — норма, Public — исключение.
  • Магические числа и имена листов выносите в Const.
Проверьте себя
1. Что объявит строка Dim i, j As Long?
AОбе переменные будут типа Long
Bi станет Variant, а Long будет только j
CVBA выдаст ошибку компиляции: так писать нельзя
DОбе переменные станут Variant
2. Зачем в начале модуля пишут Option Explicit?
AЧтобы макрос работал быстрее на больших таблицах
BЧтобы VBA автоматически подбирал тип каждой переменной
CЧтобы VBA требовал объявлять переменные и ловил опечатки в именах ещё до запуска
DЧтобы разрешить использовать объектные переменные вроде Range и Worksheet