VBA Excel. Содержание рубрики

    Содержание рубрики VBA Excel на сайте «Время не ждёт». Систематизация статей по тематическим группам для ускорения поиска нужной информации по заданной теме.

    Знакомство с VBA Excel

    Методы VBA Excel

    Метод Application.Goto
    Метод Application.InputBox (синтаксис, параметры)
    Метод Application.Intersect (пересечение диапазонов)
    Метод Application.OnTime
    Метод Application.Union (объединение диапазонов)
    Метод Controls.Add пользовательской формы
    Метод CreateTextFile (синтаксис, параметры)
    Метод Find объекта Range
    Метод FindNext объекта Range
    Метод Hyperlinks.Add (создание гиперссылки)
    Метод OpenTextFile (синтаксис, параметры)
    Метод Range.AutoFill (автозаполнение ячеек)
    Метод Range.Insert (вставка со сдвигом ячеек)
    Метод Range.Justify (переупорядочивание текста)
    Метод Range.PasteSpecial (специальная вставка)
    Метод Range.Replace (замена текста в ячейках)
    Метод Range.Show
    Метод WorksheetFunction.Match (поиск позиции)
    Метод WorksheetFunction.Sum – сумма аргументов
    Метод WorksheetFunction.SumIf
    Метод WorksheetFunction.SumIfs
    Метод WorksheetFunction.Transpose
    Метод WorksheetFunction.VLookup
    Методы Count, CountA и CountBlank
    Методы CountIf и CountIfs
    Методы очистки ячеек (Range.Clear и другие)
    Открытие сайта методом FollowHyperlink
    Удаление ячеек со сдвигом (Range.Delete)

    Объект Range в VBA Excel

    Автоподбор высоты объединенной ячейки
    Автоподбор ширины объединенной ячейки
    Вставка формулы в ячейку
    Выделенный диапазон ячеек (адрес, выбор, строки)
    Выравнивание текста в ячейке
    Вырезание, копирование и вставка ячеек (диапазонов)
    Диапазон ячеек и массив (обмен значениями)
    Объединение ячеек и его отмена
    Переменная диапазона ячеек (As Range)
    Программное создание границ ячеек
    Размер ячейки (высота строки, ширина столбца)
    Свойства Column и Columns объекта Range
    Свойства Row и Rows объекта Range
    Свойство Areas объекта Range
    Свойство Cells объекта Range
    Свойство End объекта Range
    Свойство Range.Characters
    Свойство Range.CurrentRegion
    Свойство Range.Hidden
    Свойство Range.Offset
    Свойство Range.Resize (синтаксис, примеры)
    Свойство Range.Text
    Свойство Range.WrapText (перенос текста)
    Цвет текста (шрифта) в ячейке
    Цвет ячейки (заливка, фон)
    Узор (рисунок) в ячейке
    Форматирование текста в ячейке (объект Font)
    Ячейки (обращение, запись, чтение, очистка)

    Объекты VBA Excel

    Операторы в VBA Excel

    Переменные в VBA Excel

    Примеры кода VBA Excel

    Автоматическая запись текущей даты и времени
    Автоматическое заполнение интервала дат (периода)
    Бегущая, ползущая и танцующая строки
    Буфер обмена (копирование, вставка, очистка)
    Выбор случайной ячейки из диапазона
    Генератор случайных чисел (Rnd и Randomize)
    Генерация документов (реестр, массив, бланк)
    Генерация документов и отчетов
    Добавление кнопки в контекстное меню
    Изменение свойств пользовательской формы
    Квадратные ячейки (тетрадные клетки)
    Копирование данных с одного листа на другой
    Копирование и перемещение файлов
    Номер последней заполненной строки
    Отбор неповторяющихся значений
    Отбор уникальных значений с помощью Collection
    Отбор уникальных значений с помощью Dictionary
    Открыта или закрыта книга (проверка состояния)
    Открытие файла другой программы из кода VBA Excel
    Ошибки в таблице – поиск и исправление
    Парсинг сайтов, html-страниц и файлов
    Перебор листов в книге
    Перемещение листа и его отмена
    Переход к ячейке по адресу из формулы
    Переход по ссылке к ячейке в другой книге
    Поиск значения в таблице
    Поиск и выделение дубликатов в столбце
    Пользовательская автоформа (создание)
    Проверка существования листа
    Программное создание графика (диаграммы)
    Программное создание модуля
    Программное создание формы
    Секундомер в ячейке рабочего листа
    Создание простого тестового задания
    Создание таблицы (умной, обычной)
    Создание файлов
    Создание, копирование, перемещение папок
    Сортировка массива
    Сортировка таблицы (диапазона)
    Сохранение книг и листов в PDF
    Сохранение массива в текстовый файл
    Список файлов в папке
    Сравнение прайс-листов
    Сумма прописью (код пользовательской функции)
    Удаление книги из собственного кода
    Удаление повторяющихся значений в диапазоне ячеек
    Удаление пустых строк
    Удаление файлов
    Учет расхода воды и других ресурсов
    Число Пи (значение)
    Экспорт и импорт пользовательской формы

    Прочее в VBA Excel

    Работа с Word из кода VBA Excel

    Редактор VBA Excel

    События VBA Excel

    Функции в VBA Excel

    Изменение значений других ячеек из функции
    Пользовательская функция (синтаксис, компоненты)
    Работа с текстом (функции)
    Удаление лишних пробелов (LTrim, RTrim, Trim)
    Функции Left, Mid, Right (вырезать часть строки)
    Функции Space, String и StrReverse
    Функция Beep API (звуковой сигнал, мелодия)
    Функция Choose (синтаксис, компоненты, примеры)
    Функция FileDateTime
    Функция Filter (фильтрация массива)
    Функция Format (синтаксис, параметры, примеры)
    Функция FreeFile
    Функция Hex
    Функция InputBox (синтаксис, параметры, значения)
    Функция InStr (синтаксис, параметры, примеры)
    Функция InStrRev (синтаксис, параметры, примеры)
    Функции Int и Fix
    Функция Join (синтаксис, параметры, значения)
    Функция MicroTimer
    Функция MsgBox (синтаксис, параметры, значения)
    Функция Replace (замена подстроки)
    Функция Shell
    Функция Split (синтаксис, параметры, значения)
    Функция StrComp (сравнение строк)
    Функция StrConv (смена регистра букв)
    Функция Switch (синтаксис, примеры)
    Функция Timer (примеры)
    Функция Val (примеры)
    Функция для вычисления факториала
    Функции для работы с датой
    Функция преобразования HTML-цвета в число
    Функции преобразования типов данных

    Циклы в VBA Excel

    Элементы управления в VBA Excel

    ComboBox – заполнение поля со списком
    ListBox – заполнение списка данными
    Заполнение списка ComboBox по условию
    Маска ввода в TextBox
    Привязка события к элементу управления
    Размеры и расположение элементов управления
    Свойства SelStart, SelLength, SelText (TextBox)
    Свойство Picture элементов управления
    Сочетания клавиш для кнопок
    Удаление элементов ActiveX с рабочего листа
    Удаление элементов управления формы с листа
    Элемент управления CheckBox (флажок)
    Элемент управления ComboBox (поле со списком)
    Элемент управления CommandButton (кнопка)
    Элемент управления DTPicker
    Элемент управления Frame (рамка)
    Элемент управления Image
    Элемент управления Label (метка, надпись)
    Элемент управления ListBox (список)
    Элемент управления MultiPage
    Элемент управления OptionButton (переключатель)
    Элемент управления RefEdit (редактор ссылок)
    Элемент управления ScrollBar (полоса прокрутки)
    Элемент управления SpinButton (счетчик)
    Элемент управления TabStrip
    Элемент управления TextBox (текстовое поле)
    Элемент управления ToggleButton (выключатель)
    Элемент управления TreeView (древовидная структура)

    59 комментариев для “VBA Excel. Содержание рубрики”

    1. Здравствуйте! У Вас нет темы по работе с фильтром. Можно проконсультироваться на этот счёт?

    2. Спасибо, Евгений! Имеется в Excel столбец с включённым автофильтром. Можно ли программно запустить окно фильтра для выбора значений? Т.е. не руками нажимать на иконку фильтра, а заставить окно открываться с помощью макроса. Спасибо за ответ.

        1. Большущее спасибо!!! Я перерыл кучу сайтов, но нигде на находил ответ на свой вопрос. Можно ещё, Евгений, Вас потревожу?
          Теперь как корректно отработать кнопки Ok и Отмена работы фильтра? Только на одном форуме нашёл способ — вставить в произвольной ячейке формулу, в которой присутствует поле из фильтрованного списка (например, Range(«B2»).FormulaR1C1 = «=RС[-1]»), а потом в событии листа Worksheet_Calculate() прописать команды, которые соответствуют нажатию кнопки OK. Всё хорошо, когда лист пустой. Но если на нём ещё есть формулы или нужно подправить значение в какой-то ячейке, то не знаю, как отследить, в какой момент запускался фильтр, а в какой — другие манипуляции.

          1. Евгений

            Сергей, уточните вопрос: нужно программно нажать кнопки Ok и Отмена или отследить, какая из них была нажата?

            1. Просто отследить, какая кнопка нажата.
              По предыдущему вопросу. Я вставил sendkeys… в конец макроса, который запускается при нажатии кнопки на листе. Окно фильтра появляется и тут же закрывается. А как сделать, чтобы окно осталось, и пользователь мог выбрать данные?

          2. Евгений

            Пример отслеживания нажатий кнопки «OK» автофильтра. На нажатие кнопки «Отмена» код не реагирует.

            Вставьте в стандартный модуль (в примере — Module1) объявление глобальной переменной и код процедуры для кнопки:

            В модуль листа:

            Range("A1").CurrentRegion можно заменить на имя таблицы.

              1. Евгений

                Можно удалить, если в таблице есть другие формулы. Я изменил код процедуры Worksheet_Calculate() в предыдущем примере, чтобы она не реагировала на изменение формул, а только на нажатие кнопки «OK» автофильтра. Range("A1") — это ячейка с кнопкой фильтра.

    3. Евгений, спасибо за корректировку процедуры. Думаю, в ней и поле myString1 необязательно (и связанная проверка с ним), т.к. пользователь может выбрать все значения.
      Но… после команды SendKeys "%{DOWN}" событие Worksheet_Calculate() отрабатывается только один раз :(.

      1. Евгений

        Сергей, событие Worksheet_Calculate() не зависит от команды SendKeys "%{DOWN}". Ограничение стоит здесь:

        Код в процедуре Worksheet_Calculate() срабатывает только когда активна ячейка Range("A1"). Если заменить в этой строке Range("A1") на диапазон строки заголовков таблицы, тогда код будет срабатывать при любой активной ячейке в заголовке.

        В переменную myString записывается состояние таблицы до применения фильтра, в переменную myString1 — после применения. Затем их содержимое сравнивается: если они содержат разные значения — значит, фильтр был применен.

    4. Здравствуйте! может я не туда пишу. вопрос
      нужно посчитать кол-во строк в столбце «В» (от 100 до 5000) и вставить в формулу вместо 744
      table = Range("B2:C744")
      спасибо.

      1. Евгений

        Привет, Сергей!

        В столбце «B» не должно быть пустых ячеек до последней строки таблицы.

      1. Евгений

        Привет, Сергей!

    5. Спасибо за достаточно полную информацию.
      Был бы очень вам признателен, если бы вы подсказали:
      как изменить ширину блоков верхнего колонтитула.
      «Введена слишком длинная строка. Уменьшите число знаков.»
      При изменении шрифта с полужирного на обычный всё нормально
      воспринимается,
      Прошу прощения, если не по адресу.

    6. Владислав

      Добрый день! Никак не могу решить проблему с заблокированным автофильтром на защищенном макросом (нашел на форумах) листе. Подскажите пожалуйста, куда и какую строчку нужно добавить в макрос, чтобы при открытии файла был активен автофильтр:

      1. Евгений

        Добрый день, Владислав!
        Замените строку

        на

    7. Здравствуйте, помогите, пожалуйста, решить задачу((

      Разработать программу «Кредитный калькулятор» для расчета выплат по кредиту в условиях начисления процентов по аннуитетной схеме постнумерандо. Форма должна предусматривать ввод:
      1) сумма кредита, допустимы 2 знака после запятой;
      2) процентная ставка (годовая), допустимы 2 знака после запятой;
      3) количество периодов (месяцев), целое положительное число.
      Также форма должна содержать две кнопки:
      1) «Ok» (выполнить расчет, заполнить лист Excel, освободить форму и закончить работу программы);
      2) «Отмена» (освободить форму и закончить работу программы).
      На лист Excel следует вывести 5 колонок:
      1) номер периода;
      2) остаток кредита;
      3) сумма процентов за пользование кредитом, подлежащая к оплате;
      4) сумма погашения основного долга (кредита);
      5) общая сумма выплат за период.
      Форма должна быть открыта из макроса «main» и там же освобождена по завершению своего использования. Форма должна содержать только код работы с формой и ввода данных. Логика верификации данных должна быть, по возможности, отделена от кода формы. Бизнес-логика обязательно должна быть отделена от кода формы.

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

      1. Евгений

        Здравствуйте, Ева! Для перемещения фокуса в ячейку справа код VBA не нужен: используйте для этого клавишу «Tab» или настройте клавишу «Enter» для перехода вправо (Файл >> Параметры >> Дополнительно).

        1. Спасибо за ответ! Да, но это надо делать вручную; речь идёт об автоматическом перемещении курсора.

          1. Евгений

            Этот код перемещает фокус с активной ячейки на ячейку справа:

    9. Добрый день, Евгений .
      Был бы очень вам признателен, если бы вы подсказали:
      как изменить ширину блоков верхнего колонтитула.
      «Введена слишком длинная строка. Уменьшите число знаков.»
      При изменении шрифта с полужирного на обычный всё нормально
      воспринимается,
      Прошу прощения, если не по адресу.

      1. Евгений

        Здравствуйте, Таер!
        Изменить максимальное количество знаков в колонтитуле невозможно. Попробуйте создать рисунок с надписью и вставить его в колонтитул: Вставка — Текст — Колонтитулы — Рисунок.

    10. Доброго времени суток. Помогите с написанием кода. Имеется диапазон с данными (A2:A4). В каждой из ячейки внесены данные. К каждой ячейке прикреплена фигура. Первоначально фигура имеет зеленый цвет. После выбора нужной ячейки фигура подкрашивается, а после переключения ячейки на др., фигура не возвращается в первоначальный цвет(Зеленый). Помогите написать код. Пример:

      1. Евгений

        Привет! Для пар «ячейка A2 — Oval 1»; «ячейка A3 — Oval 2»; «ячейка A4 — Oval 3»:

        1. Благодарю за ответ, Евгений. У меня таких овалов будет штук 30. Есть ли возможность сократить код?

          1. Евгений

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

            1. Доброго времени суток. Вопрос: Как сократить код если фигур на листе около 2000 и количество листов около 10 и на каждом объектов по 2000.Благодарю за ответ!

    11. Алексей

      Здравствуйте, Евгений!
      Подскажите как решить задачу: нужно при запуске макроса открывать другой файл «.csv». В нём в столбце А «переменная», в столбце B время (17.01.2022 4:00:01). Нужно найти ячейку с «переменной» и временем на 5 минут меньше чем ТЕКУЩЕЕ в системе, скопировать её значение и вставить в 1 файл в ячейку А1. Я так понимаю нужна функция Find, но не понимаю как в ней сделать увязку с текущим временем минус 5 минут.

      1. Евгений

        Добрый день, Алексей!
        Используйте функцию DateAdd:

    12. Добрый день.
      Из рабочей книги Excel с помощью макроса создается новая книга, в которую копируются данные, и переименовывается.
      Есть ли возможность, чтобы у этой новой книги задать в макросе альбомную ориентацию и вписать данные на одну страницу, а то каждый день приходится делать это в ручную.

    13. Здравствуйте, Евгений.
      Спасибо за вашу помощь. С альбомной ориентацией все получилось.
      А что насчет вписать все эти данные на 1 лист, так как в режиме печати получается
      на 12 страниц.

      1. Евгений

        Попробуйте так:

    14. У меня объектов будет 400. И при написании для всех 400 фигур (овал и прямоугольники), пишет, что объёмный текст. Спасибо за ответ.

      1. Евгений

        Добрый день. Бываю, практически, каждый день, но отвечаю не на все комментарии.

        1. Евгений, буду глубоко признателен, если Вы поможете мне с проблемой описанной в посте выше!

          1. Евгений

            Попробуйте по группам фигур с последовательными номерами и одним присваиваемым цветом пройтись циклами:

    15. Добрый день.

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

    16. Здравствуйте Евгений.
      Отличный курс. Спасибо.
      Уточните пожалуйста. Имеются ли все статьи из рубрики VBA Excel в одном файле (word, pdf или другом), для изучения в оффлайн режиме.
      Буду признателен за помощь.

        1. Извините за длинное сообщение. Более проконсультироваться не у кого. А «диванные герои» ничего путевого не могут посоветовать.

          Изучая большое количество информации выявил следующее, что повергло меня в некое замешательство, так как не компетентен в отношении VBA:
          1) Многие статьи рекомендуют в параметрах программы Excel установить настройку безопасности макроса «Отключить все макросы с уведомлением» и активировать галочку «Доверять доступ к объектной модели проектов VBA».
          2) Так же некоторые статьи советуют редакторе Visual Basic в пункте Tools-Options на вкладке Editor установить галочку на параметр «Require Variable Declaration» и изменить зхначение «Tab Width» на 2.
          Уточните пожалуйста, нужно ли проводить данные настройки, либо использовать какие-либо другие манипуляции по настройке табличного редактора Excel и редактора Visual Basic?

          Далее о проблеме, по которой и нашел Ваш сайт.
          Имеется таблица Excel, созданная в версии 2003 и редактируемая, на сегодняшний день, в версии 2016. Таблица имеет несколько тысяч строк с десятками примечаний в каждой строке. Спустя много лет использования файла, большая часть примечания при открытии для редактирования оказывается на 2-5 тысяч строк ниже материнской ячейки. В ручную это все перетаскивать ну очень не удобно, вследствие чего решил написать макрос, который восстановит стандартное положение примечаний.

          Задача. Создать отдельную книгу Excel с макросом, которую можно будет переместить на другой компьютер и с помощью которой можно восcтановить расположение примечаний в проблемном файле.

          Здесь возникло огромное количество вопросов. Опишу процесс поэтапно и прошу поправить, если где-то что-то делаю некорректно.
          1) Создаю модуль insert-Module
          2) Двойной клик на Module1, в пункте Insert-Procedure выбираю Sub + Public, и тут не знаю, нужно ли устанавливать галочку «All local variables as Static»
          3) Далее в правом окне пишу код. Здесь в смятении, что писать, так как нашел много разных вариантов восстановления расположения примечаний.
          У меня получилось вот такие варианты кода, в которых сомневаюсь.
          Первый:

          Второй (без выполнения п.2):

          4) Закрываю редактор VBA.
          5) Закрываю файл Excel. здесь подтверждаю сохранение и соглашаюсь с политикой конфиденциальности.
          6) Переношу файл с макросом на другой компьютер, запускаю его.
          7) Запускаю проблемную книгу, перехожу на лист со сместившимися примечаниями, запускаю макрос.
          Правильно ли выбран порядок действий и как правильнее оформить код, на Ваш взгляд?

          Прошу помощи по всему вышенаписанному.
          Заранее благодарю.

          1. Евгений

            Здравствуйте, Тамир!

            1) Если вы планируете работать с VBA Excel, установите настройку безопасности «Включить все макросы (не рекомендуется, возможен запуск опасной программы)» и галочку «Доверять доступ к объектной модели проектов VBA». Незнакомые файлы Excel, например, полученные из Интернета, перед открытием проверяйте антивирусом.
            2) Параметр «Tab Width» задает количество пробелов, добавляемых при нажатии клавиши «Tab». Установите столько, сколько вам нравится. Я использую — 4.
            3) Ключевая фраза «Option Explicit» означает, что все используемые переменные должны быть заранее объявлены, — очень полезная вещь. Можно сделать так, чтобы запись «Option Explicit» в каждом новом модуле появлялась автоматически: Tools >> Options >> установить галочку у пункта «Recuire Variable Declaration».

            С файлом 2003 года попробуйте поступить так:
            1) Сделайте копию файла 2003 года и сохраните его как файл 2016 года с поддержкой макросов (.xlsm).
            2) Создайте модуль, а новую процедуру не создавайте. Просто скопируйте любую из двух процедур из вашего комментария и вставьте ее в модуль под ключевой фразой «Option Explicit».
            3) Запустите процедуру, нажав клавишу «F5» или кнопку «Run» (лист с примечаниями должен быть активным).

    Добавить комментарий

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