Содержание рубрики VBA Excel на сайте «Время не ждёт». Систематизация статей по тематическим группам для ускорения поиска нужной информации по заданной теме.
Содержание
-
Знакомство с VBA Excel
- Начинаем программировать с нуля
- Первая кнопка (для начинающих)
- Первая форма (для начинающих)
- Первая функция (для начинающих)
- Правильные имена переменных и процедур
- Свойства ячейки (объекта Range)
- Свойство ActiveCell объекта Application
- Свойство Selection объекта Application
-
Методы 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
- Объект Collection (создание, методы, примеры)
- Объект Dictionary (свойства, методы, примеры)
- Объект FileSystemObject
- Объект PageSetup (параметры страницы)
- Объект TextStream (свойства и методы)
- Рабочая книга (открыть, создать новую, закрыть)
- Рабочий лист (обращение, переименование, скрытие)
- Рабочий лист (создание, копирование, удаление)
- Регулярные выражения (объекты, свойства, методы)
-
Операторы в VBA Excel
- Арифметические операторы
- Логические операторы
- Оператор Beep (одиночный звуковой сигнал)
- Оператор If…Then…Else и функция IIf
- Оператор On Error (обработка ошибок)
- Оператор Open (синтаксис, параметры)
- Оператор Select Case (синтаксис, примеры)
- Оператор SendKeys (имитация нажатия клавиш)
- Оператор With
- Операторы сравнения
- Операторы чтения и записи в файл
-
Переменные в VBA Excel
- Глобальная переменная
- Количество измерений массива
- Массивы (одномерные, многомерные, динамические)
- Пользовательские типы данных (оператор Type)
- Тип данных Decimal
- Типы данных
-
Примеры кода VBA Excel
- Автоматическая запись текущей даты и времени
- Автоматическое заполнение интервала дат (периода)
- Бегущая, ползущая и танцующая строки
- Буфер обмена (копирование, вставка, очистка)
- Выбор случайной ячейки из диапазона
- Генератор случайных чисел (Rnd и Randomize)
- Генерация документов (реестр, массив, бланк)
- Генерация документов и отчетов
- Добавление кнопки в контекстное меню
- Изменение свойств пользовательской формы
- Квадратные ячейки (тетрадные клетки)
- Копирование данных с одного листа на другой
- Копирование и перемещение файлов
- Номер последней заполненной строки
- Отбор неповторяющихся значений
- Отбор уникальных значений с помощью Collection
- Отбор уникальных значений с помощью Dictionary
- Открыта или закрыта книга (проверка состояния)
- Открытие файла другой программы из кода VBA Excel
- Ошибки в таблице – поиск и исправление
- Парсинг сайтов, html-страниц и файлов
- Перебор листов в книге
- Перемещение листа и его отмена
- Переход к ячейке по адресу из формулы
- Переход по ссылке к ячейке в другой книге
- Поиск значения в таблице
- Поиск и выделение дубликатов в столбце
- Пользовательская автоформа (создание)
- Проверка существования листа
- Программное создание графика (диаграммы)
- Программное создание модуля
- Программное создание формы
- Секундомер в ячейке рабочего листа
- Создание простого тестового задания
- Создание таблицы (умной, обычной)
- Создание файлов
- Создание, копирование, перемещение папок
- Сортировка массива
- Сортировка таблицы (диапазона)
- Сохранение книг и листов в PDF
- Сохранение массива в текстовый файл
- Список файлов в папке
- Сравнение прайс-листов
- Сумма прописью (код пользовательской функции)
- Удаление книги из собственного кода
- Удаление повторяющихся значений в диапазоне ячеек
- Удаление пустых строк
- Удаление файлов
- Учет расхода воды и других ресурсов
- Число Пи (значение)
- Экспорт и импорт пользовательской формы
-
Прочее в VBA Excel
- Время работы макроса
- Знаки подстановки для шаблонов
- Конвертация цвета из Long в RGB
- Объявление функций в 64-разрядных версиях
- Округление чисел (особенности)
- Открыть папку (каталог) в проводнике
- Параметры и аргументы
- Работа с фигурами (Shapes)
- Свойство Application.WindowState
- Смена кодировки UTF-8 на ANSI (Windows-1251)
- Смена раскладки клавиатуры
- Сохранение файла рабочей книги
- Стандартная палитра из 56 цветов
- Стандартный диалог выбора файлов Application.GetOpenFilename
-
Работа с Word из кода VBA Excel
- Bookmarks – закладки в документе Word
- Вставка таблицы Excel в документ Word
- Редактирование документов Word
- Создание и открытие документов Word
- Создание таблиц в документе Word
- Управление приложением Word
-
Редактор VBA Excel
- Вызов процедуры Sub из другой подпрограммы VBA
- Защита паролем и снятие защиты проекта VBA
- Личная книга макросов (создание, предназначение)
- Макросы (запись, запуск, пример)
- Модуль (импорт, экспорт, удаление)
- Модуль, процедура, форма
- Окно Immediate (отладка кода, вычисления)
- Перенос кода процедуры и текста на новую строку
-
События 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
- Цикл Do Until… Loop
- Цикл Do While… Loop
- Цикл For Each… Next
- Цикл For… Next
- Цикл While… Wend
- Циклы (краткое описание)
-
Элементы управления в 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 (древовидная структура)
Здравствуйте! У Вас нет темы по работе с фильтром. Можно проконсультироваться на этот счёт?
Здравствуйте, Сергей!
Задавайте вопрос, постараюсь ответить.
Спасибо, Евгений! Имеется в Excel столбец с включённым автофильтром. Можно ли программно запустить окно фильтра для выбора значений? Т.е. не руками нажимать на иконку фильтра, а заставить окно открываться с помощью макроса. Спасибо за ответ.
Да, это возможно:
Большущее спасибо!!! Я перерыл кучу сайтов, но нигде на находил ответ на свой вопрос. Можно ещё, Евгений, Вас потревожу?
Теперь как корректно отработать кнопки Ok и Отмена работы фильтра? Только на одном форуме нашёл способ — вставить в произвольной ячейке формулу, в которой присутствует поле из фильтрованного списка (например, Range(«B2»).FormulaR1C1 = «=RС[-1]»), а потом в событии листа Worksheet_Calculate() прописать команды, которые соответствуют нажатию кнопки OK. Всё хорошо, когда лист пустой. Но если на нём ещё есть формулы или нужно подправить значение в какой-то ячейке, то не знаю, как отследить, в какой момент запускался фильтр, а в какой — другие манипуляции.
Сергей, уточните вопрос: нужно программно нажать кнопки Ok и Отмена или отследить, какая из них была нажата?
Просто отследить, какая кнопка нажата.
По предыдущему вопросу. Я вставил sendkeys… в конец макроса, который запускается при нажатии кнопки на листе. Окно фильтра появляется и тут же закрывается. А как сделать, чтобы окно осталось, и пользователь мог выбрать данные?
Евгений, я разобрался, почему не выводилось окно с фильтром.
Пример отслеживания нажатий кнопки «OK» автофильтра. На нажатие кнопки «Отмена» код не реагирует.
Вставьте в стандартный модуль (в примере — Module1) объявление глобальной переменной и код процедуры для кнопки:
В модуль листа:
Range("A1").CurrentRegionможно заменить на имя таблицы.Евгений, а формулу в поле B2 оставлять или её можно удалить?
Можно удалить, если в таблице есть другие формулы. Я изменил код процедуры
Worksheet_Calculate()в предыдущем примере, чтобы она не реагировала на изменение формул, а только на нажатие кнопки «OK» автофильтра.Range("A1")— это ячейка с кнопкой фильтра.Евгений, спасибо за корректировку процедуры. Думаю, в ней и поле myString1 необязательно (и связанная проверка с ним), т.к. пользователь может выбрать все значения.
Но… после команды
SendKeys "%{DOWN}"событиеWorksheet_Calculate()отрабатывается только один раз :(.Сергей, событие
Worksheet_Calculate()не зависит от командыSendKeys "%{DOWN}". Ограничение стоит здесь:Код в процедуре
Worksheet_Calculate()срабатывает только когда активна ячейкаRange("A1"). Если заменить в этой строкеRange("A1")на диапазон строки заголовков таблицы, тогда код будет срабатывать при любой активной ячейке в заголовке.В переменную
myStringзаписывается состояние таблицы до применения фильтра, в переменнуюmyString1— после применения. Затем их содержимое сравнивается: если они содержат разные значения — значит, фильтр был применен.Здравствуйте! может я не туда пишу. вопрос
нужно посчитать кол-во строк в столбце «В» (от 100 до 5000) и вставить в формулу вместо 744
table = Range("B2:C744")спасибо.
Привет, Сергей!
В столбце «B» не должно быть пустых ячеек до последней строки таблицы.
Как определить в VBA есть узор в ячейке?
Привет, Сергей!
Большое спасибо
Спасибо за достаточно полную информацию.
Был бы очень вам признателен, если бы вы подсказали:
как изменить ширину блоков верхнего колонтитула.
«Введена слишком длинная строка. Уменьшите число знаков.»
При изменении шрифта с полужирного на обычный всё нормально
воспринимается,
Прошу прощения, если не по адресу.
Добрый день! Никак не могу решить проблему с заблокированным автофильтром на защищенном макросом (нашел на форумах) листе. Подскажите пожалуйста, куда и какую строчку нужно добавить в макрос, чтобы при открытии файла был активен автофильтр:
Добрый день, Владислав!
Замените строку
на
Добрый день, Евгений!
ОГРОМНОЕ СПАСИБО!!! :))
Здравствуйте, помогите, пожалуйста, решить задачу((
Разработать программу «Кредитный калькулятор» для расчета выплат по кредиту в условиях начисления процентов по аннуитетной схеме постнумерандо. Форма должна предусматривать ввод:
1) сумма кредита, допустимы 2 знака после запятой;
2) процентная ставка (годовая), допустимы 2 знака после запятой;
3) количество периодов (месяцев), целое положительное число.
Также форма должна содержать две кнопки:
1) «Ok» (выполнить расчет, заполнить лист Excel, освободить форму и закончить работу программы);
2) «Отмена» (освободить форму и закончить работу программы).
На лист Excel следует вывести 5 колонок:
1) номер периода;
2) остаток кредита;
3) сумма процентов за пользование кредитом, подлежащая к оплате;
4) сумма погашения основного долга (кредита);
5) общая сумма выплат за период.
Форма должна быть открыта из макроса «main» и там же освобождена по завершению своего использования. Форма должна содержать только код работы с формой и ввода данных. Логика верификации данных должна быть, по возможности, отделена от кода формы. Бизнес-логика обязательно должна быть отделена от кода формы.
Здравствуйте, Рафия!
С разработкой кредитного калькулятора вам помогут только за плату на бирже фриланса.
Добрый день, Евгений! Я только начинаю работать с VBA. Помогите, пожалуйста, прописать код для автоматического перемещения курсора на ячейку вправо после заполнения предыдущей.
Здравствуйте, Ева! Для перемещения фокуса в ячейку справа код VBA не нужен: используйте для этого клавишу «Tab» или настройте клавишу «Enter» для перехода вправо (Файл >> Параметры >> Дополнительно).
Спасибо за ответ! Да, но это надо делать вручную; речь идёт об автоматическом перемещении курсора.
Этот код перемещает фокус с активной ячейки на ячейку справа:
Спасибо большое! То, что надо.
Добрый день, Евгений .
Был бы очень вам признателен, если бы вы подсказали:
как изменить ширину блоков верхнего колонтитула.
«Введена слишком длинная строка. Уменьшите число знаков.»
При изменении шрифта с полужирного на обычный всё нормально
воспринимается,
Прошу прощения, если не по адресу.
Здравствуйте, Таер!
Изменить максимальное количество знаков в колонтитуле невозможно. Попробуйте создать рисунок с надписью и вставить его в колонтитул: Вставка — Текст — Колонтитулы — Рисунок.
спасибо, я так и подразумевал
Доброго времени суток. Помогите с написанием кода. Имеется диапазон с данными (A2:A4). В каждой из ячейки внесены данные. К каждой ячейке прикреплена фигура. Первоначально фигура имеет зеленый цвет. После выбора нужной ячейки фигура подкрашивается, а после переключения ячейки на др., фигура не возвращается в первоначальный цвет(Зеленый). Помогите написать код. Пример:
Привет! Для пар «ячейка A2 — Oval 1»; «ячейка A3 — Oval 2»; «ячейка A4 — Oval 3»:
Благодарю за ответ, Евгений. У меня таких овалов будет штук 30. Есть ли возможность сократить код?
Все зависит от условий задачи. Например, если номер овала соответствует номеру строки соответствующей ему ячейки и нет лишних фигур на листе, тогда можно использовать следующий код:
Доброго времени суток. Вопрос: Как сократить код если фигур на листе около 2000 и количество листов около 10 и на каждом объектов по 2000.Благодарю за ответ!
На листе есть еще и другие фигуры.
Помогите разобраться, что такое Target? И как его использовать…
Добрый день, Алексей!
Определение Target и его использование рассмотрены в статье Событие Worksheet.SelectionChange.
Благодарю за ответ!
Здравствуйте, Евгений!
Подскажите как решить задачу: нужно при запуске макроса открывать другой файл «.csv». В нём в столбце А «переменная», в столбце B время (17.01.2022 4:00:01). Нужно найти ячейку с «переменной» и временем на 5 минут меньше чем ТЕКУЩЕЕ в системе, скопировать её значение и вставить в 1 файл в ячейку А1. Я так понимаю нужна функция Find, но не понимаю как в ней сделать увязку с текущим временем минус 5 минут.
Добрый день, Алексей!
Используйте функцию DateAdd:
Добрый день.
Из рабочей книги Excel с помощью макроса создается новая книга, в которую копируются данные, и переименовывается.
Есть ли возможность, чтобы у этой новой книги задать в макросе альбомную ориентацию и вписать данные на одну страницу, а то каждый день приходится делать это в ручную.
Здравствуйте, Сергей!
Здравствуйте, Евгений.
Спасибо за вашу помощь. С альбомной ориентацией все получилось.
А что насчет вписать все эти данные на 1 лист, так как в режиме печати получается
на 12 страниц.
Попробуйте так:
У меня объектов будет 400. И при написании для всех 400 фигур (овал и прямоугольники), пишет, что объёмный текст. Спасибо за ответ.
Добрый день. Евгений, вы бываете тут?
Добрый день. Бываю, практически, каждый день, но отвечаю не на все комментарии.
Евгений, буду глубоко признателен, если Вы поможете мне с проблемой описанной в посте выше!
Попробуйте по группам фигур с последовательными номерами и одним присваиваемым цветом пройтись циклами:
Благодарю за ответ. Буду пробовать.
Добрый день.
Как с вами связаться? Нужно за денежку естественно написать небольшой код по скачиванию определенной страницы с интернет через макрос эксель.
Здравствуйте, Денис!
Я не оказываю платные услуги. Вы можете обратиться на биржу фриланса Кворк.
Здравствуйте Евгений.
Отличный курс. Спасибо.
Уточните пожалуйста. Имеются ли все статьи из рубрики VBA Excel в одном файле (word, pdf или другом), для изучения в оффлайн режиме.
Буду признателен за помощь.
Добрый день, Тамир!
Все статьи только на сайте.
Извините за длинное сообщение. Более проконсультироваться не у кого. А «диванные герои» ничего путевого не могут посоветовать.
Изучая большое количество информации выявил следующее, что повергло меня в некое замешательство, так как не компетентен в отношении 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) Если вы планируете работать с 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» (лист с примечаниями должен быть активным).