Vba примеры: Макросы для Excel с исходными кодами и описанием функций

VBA Примеры — Лучшие 4 примера VBA Macro Excel для начинающих

• Excel VBA Примеры

Это очень хорошо, когда я начал работать и изучать макросы VBA, я не знал, с чего начать. Мы уже видели редактор VBA, в котором есть все определения параметров и вкладок окна редактора VBA. Как начинающий, мы всегда не знаем, как начать, пока не найдем. Эта статья — решение для тех, кто сталкивается с трудностями в использовании и изучении примеров VBA и создании макросов. Мы все столкнулись с тем временем, когда некоторые вещи в Excel могли быть автоматизированы с помощью Macro. Но тот, кто не знает, как использовать VBA, не сможет его создать. Это будет полезным руководством для тех, кто не знаком с VBA Macro-кодированием.

Примеры VBA в Excel для начинающих

Ниже приведены примеры VBA в Excel:

Вы можете скачать этот шаблон Excel VBA Примеры здесь — Шаблон VBA Примеры Excel

VBA Пример № 1

Давайте посмотрим на простой пример использования VBA Coding. Для этого выполните следующие шаги:

Шаг 1: Откройте модуль VBA, где мы будем писать наш код на вкладке меню «Вставка», как показано ниже.

Шаг 2: Теперь напишите подпроцедуру макроса VBA под любым именем.

Код:

 Sub VBA_Examples1 () End Sub 

Шаг 3: Определите переменную, используя DIM, где мы будем хранить числа как целые числа.

Код:

 Sub VBA_Examples1 () Dim A As Integer End Sub 

Шаг 4: Теперь присвойте любое число указанной выше переменной, как показано ниже.

Код:

 Sub VBA_Examples1 () Dim A As Integer A = 100 End Sub 

Шаг 5: Теперь мы будем использовать окно сообщения для печати значения, хранящегося в переменной A.

Код:

 Sub VBA_Examples1 () Dim A As Integer A = 100 MsgBox A End Sub 

Шаг 6: Запустите код, нажав клавишу F8 или нажав кнопку воспроизведения, расположенную под строкой меню. Мы увидим окно сообщения со значением 100 в нем.

Код:

 Sub VBA_Examples1 () Dim A As Integer A = 100 MsgBox A End Sub 

Шаг 7: Это также можно сделать с помощью функции Debug Print, значения которой отображаются в Immediate Window. ( Примечание: нажмите Ctrl + G, чтобы открыть окно)

Код:

 Sub VBA_Examples1 () Dim A As Integer A = 100 Debug. Печать A End Sub 

VBA Пример № 2

Теперь в этом примере мы увидим другой пример, где мы будем использовать цикл For-Next для печати имен листов. Для этого выполните следующие шаги:

Шаг 1: Откройте модуль и напишите подпроцедуру, как показано ниже. Мы можем использовать любое имя, чтобы определить это.

Код:

 Sub VBA_Examples2 () End Sub 

Шаг 2: Выберите переменную как целое число. Мы можем выбрать переменную имени, как мы это сделали в примере-1.

Код:

 Sub VBA_Examples2 () Dim A As Integer End Sub 

Шаг 3: Откройте цикл For-Next, как показано ниже.

Код:

 Sub VBA_Examples2 () Dim A As Integer For Next A End Sub 

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

Код:

 Sub VBA_Examples2 () Dim A As Integer For A = 1 To Sheets.Count Next A End Sub 

Шаг 5: Теперь мы назначаем переменную A с ячейкой Value, чтобы увидеть имя листа, как показано ниже.

Код:

 Sub VBA_Examples2 () Dim A As Integer For A = 1 To Sheets. Количество ячеек (A, 1). Значение = Sheets (A). Имя Next A End Sub 

Шаг 6: Теперь запустите код, нажав клавишу F8 или нажав кнопку Play.

Мы увидим, как у нас было 3 листа с именами по умолчанию, так что их имя было напечатано от ячейки А1 до ниже, до количества листов у нас есть.

VBA Пример № 3

В этом примере мы научимся печатать числа. Этот процесс совершенно такой же, как мы видели в примере 2. Для этого выполните следующие шаги:

Шаг 1: Откройте модуль и напишите подпроцедуру.

Код:

 Sub VBA_Examples3 () End Sub 

Шаг 2: Рассмотрим переменную, использующую DIM в качестве целого числа. Поскольку мы используем числа для целочисленного типа данных должны быть использованы.

Код:

 Sub VBA_Examples3 () Dim A As Integer End Sub 

Шаг 3: Снова откройте цикл For-Next, как показано ниже.

Код:

 Sub VBA_Examples3 () Dim A As Integer For Next A End Sub 

Шаг 4: Теперь выберите диапазон ячеек, от которого мы хотим видеть число до последнего в синтаксисе For.

Код:

 Sub VBA_Examples3 () Dim A As Integer для A = 1–10 Следующая A End Sub 

Шаг 5: Теперь исправьте положение ячейки с помощью переменной А. Здесь, вторая вершина положения в ячейке показывает номер столбца. Поскольку мы выбрали 1, мы ожидаем увидеть числа, начиная с ячейки A1.

Код:

 Sub VBA_Examples3 () Dim A As Integer для A = 1–10 ячеек (A, 1). Значение = A Next A End Sub 

Шаг 6: Теперь запустите код, нажав клавишу F8 или нажав кнопку Play. Мы увидим, что числа генерируются от ячейки A1 до A10, начиная с 1 до 10 .

Шаг 7: Что если мы попытаемся добавить эти числа во второй столбец? Измените вершину столбца с 1 на 2. Или добавьте еще одну строку кода, как показано ниже.

Код:

 Sub VBA_Examples3 () Dim A As Integer для A = 1–10 ячеек (A, 1). Значение = A ячеек (A, 2). Значение = A Следующее A End Sub 

Шаг 8: Снова запустите полный код, нажав клавишу F8 или нажав кнопку Play. Мы увидим то же число, начиная с 1 по 10, которое будет напечатано в столбце B, а также из ячейки B1 до B10.

VBA Пример № 4

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

Шаг 1: Откройте модуль, напишите подпроцедуру, как показано ниже.

Код:

 Sub VBA_Example4 () End Sub 

Шаг 2: Определите переменную, используя DIM в качестве диапазона, так как мы выбираем выходной диапазон номеров.

Код:

 Sub VBA_Example4 () Dim A As End End Sub 

Шаг 3: Теперь установите эту переменную как Выбор

Код:

 Sub VBA_Example4 () Dim A As Set Range A = Выбор End Sub 

Шаг 4: Теперь выберите диапазон чисел, используя определенную переменную A, и выберите xlCellTypeBlanks, чтобы выбрать только пустые ячейки.

Код:

 Sub VBA_Example4 () Dim A As Set Range A = Выбор A.Cells.SpecialCells (xlCellTypeBlanks). End Sub 

Шаг 5: Теперь, чтобы изменить цвет интерьера пустых ячеек, используйте Interior.Color и выберите цвет, которым мы хотим выделить пустые ячейки. Используйте тип vbBlue или vbGreen, чтобы определить цвет. Использование VB является обязательным.

Код:

 Sub VBA_Example4 () Dim A As Set Range A = Выбор A.Cells.SpecialCells (xlCellTypeBlanks) .Interior.Color = vbBlue End Sub 

Шаг 6: Теперь выберите диапазон ячеек и затем запустите код, нажав клавишу F8 или нажав кнопку Play. Мы увидим, что ячейки A4 и A7, которые были пустыми, теперь выделены синим цветом, как показано ниже.

Плюсы и минусы Excel VBA Примеры

• VBA повышает эффективность работы.
• Таким образом, мы хотим уменьшить количество повторяющихся задач за один раз.
• Мы можем создать любой тип макроса, который мы хотим.
• Мы также можем записать макрос, если делать кодирование не так просто.
• Людям, у которых нет знаний или идей в области кодирования, может быть сложно использовать VBA Macro.

То, что нужно запомнить

• Начинающим в VBA следует помнить о необходимости сохранения кода в формате Excel с поддержкой макросов. Это позволит нам сохранить код в том же файле.
• Всегда компилируйте полный код, даже если код состоит из одной строки. Это сократит наше время на отладку, если вы столкнетесь с какой-либо ошибкой после запуска кода.
• Всегда начинайте процесс обучения VBA или любого другого языка программирования с небольшого и простого кода. Это даст лучшее понимание и уверенность.

Рекомендуемые статьи

Это руководство по VBA Примеры в Excel. Здесь мы обсудим несколько полезных примеров кода макроса VBA в Excel вместе с загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи —

1. Коллекция VBA (Примеры)
2. VBA IF Заявления | Шаблоны Excel
3. Как использовать функцию сортировки Excel VBA?
4. VBA While Loop (Примеры с шаблоном Excel)
5. VBA Environ

Категория:

VBA

000 Все примеры Microsoft Access

Примеры программирования на языке VBA

В этой главе

Изучение примеров на языке VBA
Как сделать код VBA максимально быстрым
Философия обучения написанию макросов Excel уделяет большое внимание примерам. Я считаю, что хорошо продуманный пример часто передает концепцию гораздо лучше, чем длинное описание лежащей в ее основе теории. Поскольку вы читаете эту тему, вы, вероятно, согласны со мной. В этой главе представлено несколько примеров, демонстрирующих распространенные приемы VBA.

Я организую эти примеры по следующим категориям:

Работа с диапазонами Изменение настроек Excel Работа с диаграммами Ускорение кода VBA
Хотя вы можете использовать некоторые примеры напрямую, в большинстве случаев вам придется адаптировать их к своим потребностям.

Работа с диапазонами

Большая часть вашего программирования VBA, вероятно, связана с диапазонами рабочих листов. (Курс повышения квалификации по объектам Range см. в главе 8. При работе с объектами Range помните следующее:
Вашему VBA не нужно выбирать диапазон для работы с ним.
Если ваш код выбирает диапазон, его рабочий лист должен быть активен.
Средство записи макросов не всегда генерирует наиболее эффективный код. Часто вы можете создать свой макрос с помощью устройства записи, а затем отредактировать код, чтобы сделать его более эффективным.
Рекомендуется использовать именованные диапазоны в коде VBA. Например, использование Range(Total) лучше, чем Range(D45). В последнем случае, если вы добавите строку над строкой 45, вам нужно изменить макрос, чтобы он использовал правильный адрес диапазона (D46).
При запуске макроса, работающего с текущим выбором диапазона, пользователь может выбрать целые столбцы или строки. В большинстве случаев вам не нужно перебирать каждую ячейку в выделении (это может занять много времени). Ваш макрос должен создать подмножество выделения, состоящее только из непустых ячеек.
Excel допускает множественный выбор. Например, вы можете выбрать диапазон, нажать Ctrl и выбрать другой диапазон. (Разумеется, выделите диапазон с помощью мыши.) Вы можете проверить это в своем макросе и предпринять соответствующие действия.
Примеры в этом разделе, которые доступны на веб-сайте этой темы, демонстрируют эти моменты.
Если вы предпочитаете вводить эти примеры самостоятельно, нажмите Alt+F11, чтобы активировать VBE. Затем вставьте модуль VBA и введите код. Убедитесь, что рабочая книга настроена правильно. Например, если в примере используются два листа с именами Лист1 и Лист2, убедитесь, что в книге есть листы с такими именами.

Копирование диапазона

Копирование диапазона считается одним из самых любимых действий Excel всех времен. Когда вы включаете средство записи макросов и копируете диапазон от A1:A5 до B1:B5, вы получаете этот макрос VBA:

Обратите внимание на последнее утверждение. Этот оператор был сгенерирован нажатием клавиши Esc, которая отменяет отображение марширующих муравьев, которое появляется на листе при копировании диапазона.
Этот макрос работает нормально, но вы можете копировать диапазон более эффективно, чем этот. Тот же результат можно получить с помощью следующего однострочного макроса, который не выбирает ячейки:

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

Копирование диапазона переменных размеров

Во многих случаях вам нужно скопировать диапазон ячеек, но неизвестны точные размеры строки и столбца. Например, у вас может быть рабочая книга, в которой отслеживаются еженедельные продажи. Количество строк меняется по мере добавления новых данных.
На рис. 14-1 показан диапазон на рабочем листе. Этот диапазон состоит из нескольких строк, и количество строк может меняться изо дня в день. Поскольку вы не знаете точный адрес диапазона в любой момент времени, написание макроса для копирования диапазона может оказаться сложной задачей. Вы готовы принять вызов?

Рисунок 14-1:
Этот диапазон может состоять из любого количества строк.
Следующий макрос демонстрирует, как скопировать этот диапазон с Листа1 на Лист2 (начиная с ячейки A1). Он использует свойство Current Region, которое возвращает объект Range, соответствующий блоку ячеек вокруг конкретной ячейки. В данном случае это А1.

Использование свойства «Текущий регион» эквивалентно выбору команды «Редактировать переход», нажатию специальной кнопки и выбору параметра «Текущий регион». Чтобы увидеть, как это работает, запишите свои действия при вводе этой команды. Как правило, Текущая область состоит из прямоугольного блока ячеек, окруженного одной или несколькими пустыми строками или столбцами.
Вы можете сделать этот макрос еще более эффективным, не выбирая место назначения. Следующий макрос использует тот факт, что метод копирования может использовать аргумент для целевого диапазона:

Выделение до конца строки или столбца ячейку в конец строки или столбца. Неудивительно, что вы можете писать макросы, которые выполняют эти типы выбора.
Вы можете использовать свойство Текущая область, чтобы выбрать весь блок ячеек. Но что, если вы хотите выбрать, скажем, один столбец из блока ячеек? К счастью, VBA поддерживает этот тип действий. Следующая процедура VBA выбирает диапазон, начинающийся с активной ячейки и заканчивающийся ячейкой чуть выше первой пустой ячейки в столбце. Выделив диапазон, вы можете делать с ним все, что хотите — копировать, перемещать, форматировать и так далее.

В этом примере используется метод End объекта Active Cell, который возвращает объект Range. Метод End принимает один аргумент, которым может быть любая из следующих констант:
xlUp
xlDown
xlToLeft xlToRight
Имейте в виду, что нет необходимости выбирать диапазон, прежде чем что-то с ним делать. Следующий макрос применяет полужирное форматирование к диапазону переменного размера без выбора диапазона:

Выбор строки или столбца

Следующая процедура демонстрирует, как выбрать столбец, содержащий активную ячейку. Он использует свойство Entire Column, которое возвращает объект Range, состоящий из полного столбца:

Как и следовало ожидать, VBA также предлагает свойство Entire Row, которое возвращает объект Range, состоящий из целой строки.

Перемещение диапазона

Вы перемещаете диапазон, вырезая его в буфер обмена, а затем вставляя в другое место. Если вы записываете свои действия во время выполнения операции перемещения, средство записи макросов генерирует следующий код:

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

Этот макрос использует тот факт, что метод Cut может использовать аргумент, указывающий место назначения. Обратите также внимание на то, что диапазон не был выбран. Указатель ячейки остается в исходном положении.

Эффективный цикл по диапазону

Многие макросы выполняют операцию над каждой ячейкой в ​​диапазоне или могут выполнять выбранные действия на основе содержимого каждой ячейки. Эти макросы обычно включают цикл For-Next, который обрабатывает каждую ячейку в диапазоне.
В следующем примере показано, как перебрать диапазон ячеек. В этом случае диапазон является текущим выбором. Переменная Cell относится к обрабатываемой ячейке. В цикле For-Next единственный оператор оценивает ячейку и меняет ее внутренний цвет, если ячейка содержит положительное значение.

Этот пример работает, но что, если выделение состоит из целого столбца или строки? Это не редкость, поскольку Excel позволяет выполнять операции над целыми столбцами или строками. В таком случае макрос кажется бесконечным, потому что он перебирает каждую ячейку в выделении — даже пустые ячейки. Чтобы сделать макрос более эффективным, вам нужно средство для обработки только непустых ячеек.
Следующая подпрограмма делает именно это, используя метод специальных ячеек. (Подробнее об аргументах см. в справочной системе VBA.) Эта подпрограмма использует ключевое слово Set для создания двух новых объектов: подмножества выборки, состоящего из ячеек с константами, и подмножества выборки, состоящего из ячеек с формулами. Подпрограмма обрабатывает каждое из этих подмножеств, в результате чего все пустые ячейки пропускаются. Довольно ловко, а?

Процедура Skip Blanks работает одинаково быстро, независимо от того, что вы выберете. Например, вы можете выбрать диапазон, все столбцы в диапазоне, все строки в диапазоне или даже весь рабочий лист. Это значительное улучшение по сравнению с процедурой ProcessCells, представленной ранее в этом разделе.

Обратите внимание, что в этом коде я использую следующий оператор:

Этот оператор указывает Excel игнорировать любые возникающие ошибки и просто обрабатывать следующий оператор (обсуждение обработки ошибок см. в главе 12). Этот оператор необходим, потому что метод специальных ячеек выдает ошибку, если ни одна ячейка не подходит.
Использование метода «Специальные ячейки» эквивалентно выбору команды «Редактировать перейти», нажатию специальной кнопки и выбору параметра «Константы» или параметра «Формулы». Чтобы понять, как это работает, запишите свои действия во время выполнения этой команды и выберите различные параметры.

Запрос значения ячейки

Как показано на рис. 14-2, вы можете использовать функцию VBA InputBox для получения значения от пользователя. Затем вы можете вставить это значение в ячейку. Следующая процедура демонстрирует, как запросить у пользователя значение и поместить значение в ячейку A1 активного рабочего листа, используя только одну инструкцию:

Рисунок 14-2:
Используйте функцию VBA InputBox для получения значения от пользователя.
Если вы попробуете этот пример, вы обнаружите, что нажатие кнопки «Отмена» в поле ввода стирает текущее значение в ячейке A1. Следующий макрос демонстрирует лучший подход: использование переменной (x) для хранения значения, введенного пользователем. Если значение не пустое (то есть пользователь не нажал кнопку «Отмена»), значение x помещается в ячейку A1. В противном случае ничего не происходит.

Переменная x определена как вариант, поскольку она может быть числом или пустой строкой (если пользователь нажмет кнопку «Отмена»).

Определение типа выбора

Если вы разрабатываете свой макрос для работы с выбором диапазона, макрос должен уметь определять, действительно ли выбран диапазон. Если выбрано что-то, кроме диапазона (например, диаграмма или фигура), макрос, вероятно, сработает. В следующей процедуре функция имени типа VBA используется для определения типа выбранного в данный момент объекта:

Если выбран объект Range, MsgBox отображает Range. Если ваш макрос работает только с диапазонами, вы можете использовать оператор If, чтобы убедиться, что диапазон выбран. В этом примере отображается сообщение и завершается процедура, если текущий выбор не является объектом Range:

Идентификация множественного выбора

Как вы знаете, Excel допускает множественный выбор, нажимая Ctrl при выборе объектов или диапазонов. Это может вызвать проблемы с некоторыми макросами. Например, вы не можете скопировать множественный выбор, состоящий из несмежных диапазонов. (Попробуйте, если не верите мне.)
Следующий макрос демонстрирует, как определить, сделал ли пользователь множественный выбор, чтобы ваш макрос мог выполнить соответствующее действие:

В этом примере используется метод Areas, который возвращает коллекцию всех объектов в выборе. Свойство Count возвращает количество объектов в коллекции.

Изменение настроек Excel

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

Изменение логических параметров

Подобно выключателю света, логический параметр либо включен, либо выключен. Например, вы можете создать макрос, который включает и выключает заголовки строк и столбцов рабочего листа. Если заголовки включены, Excel генерирует следующий код, если вы записываете свои действия при доступе к диалоговому окну «Параметры»:

С другой стороны, если заголовки отключены при записи макроса, Excel генерирует следующий код:

Это может навести вас на мысль, что вам нужны два макроса: один для включения заголовков и один для их отключения. Не правда. В следующей процедуре используется оператор Not для эффективного переключения отображения заголовка с True на False и с False на True:

Первый оператор гарантирует, что активный лист является рабочим листом. (Листы диаграммы не имеют заголовков.) Если рабочий лист не активен, процедура завершается. Вы можете использовать этот метод с любыми настройками, которые имеют логические значения (True или False).

Изменение небулевых параметров

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

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

Работа с диаграммами

Диаграммы заполнены различными объектами, поэтому управление диаграммами с помощью VBA может быть довольно запутанным. Чтобы почувствовать это, включите средство записи макросов, создайте диаграмму и выполните некоторые рутинные задачи по редактированию диаграммы. Вы можете быть удивлены количеством кода, который генерирует Excel. Однако после того, как вы разберетесь с объектами на диаграмме, вы сможете создать несколько полезных макросов.
Чтобы писать макросы, управляющие диаграммами, вы должны понимать некоторую терминологию. Встроенная диаграмма на листе — это объект Chart Object. Вы можете активировать объект диаграммы так же, как вы активируете лист. Следующий оператор активирует объект диаграммы с именем Chart 1:

После активации диаграммы вы можете ссылаться на нее в своем коде VBA как на Active Chart. Если диаграмма находится на отдельном листе диаграммы, она становится активной диаграммой, как только вы активируете этот лист диаграммы.
Когда вы щелкаете встроенную диаграмму, Excel фактически выбирает объект внутри объекта Chart Object. Вы можете выбрать сам объект диаграммы, нажав Ctrl и щелкнув встроенную диаграмму. Выберите объект диаграммы, если вы хотите изменить имя встроенной диаграммы. После выбора объекта диаграммы используйте поле «Имя» (элемент управления слева от строки формул), чтобы изменить имя.

Изменение типа диаграммы

Вот вам сбивающее с толку утверждение: объект Chart Object действует как контейнер для объекта Chart.
Чтобы изменить диаграмму с помощью VBA, вам не нужно активировать диаграмму. Вместо этого метод Chart может возвращать диаграмму, содержащуюся в объекте Chart. Вы еще окончательно не запутались? Следующие две процедуры имеют одинаковый эффект — они превращают диаграмму с именем Chart 1 в диаграмму с областями. Первая процедура сначала активирует график; второй нет. Встроенная константа xlArea представляет собой диаграмму с областями.

Перебор коллекции Chart Objects

В этом примере изменяется тип диаграммы для каждой встроенной диаграммы на активном листе. Процедура использует цикл For-Next для циклического просмотра каждого объекта в коллекции объектов диаграммы, доступа к объекту диаграммы в каждом из них и изменения его свойства Type.

Следующий макрос выполняет ту же функцию, но работает на всех листах диаграмм в активной книге:

Изменение свойств

В следующем примере изменяется шрифт легенды для всех диаграмм на активном листе. Он использует цикл For-Next для обработки всех объектов Chart Object:

Обратите внимание, что объект Font содержится в объекте Legend, который содержится в объекте Chart, который содержится в коллекции Chart Objects. Теперь вы понимаете, почему это называется иерархией объектов?

Применение форматирования диаграммы

В этом примере к активной диаграмме применяется несколько различных типов форматирования:

Вы должны активировать график перед выполнением этого макроса. Активируйте встроенную диаграмму, щелкнув ее. Чтобы активировать диаграмму на листе диаграмм, активируйте лист диаграмм.
Чтобы убедиться, что диаграмма выбрана, вы можете добавить код обработки ошибок. (Подробности об обработке ошибок см. в главе 12.) Вот модифицированный макрос, который отображает сообщение, если диаграмма не выбрана:

Я создал этот макрос, записывая свои действия при форматировании диаграммы. Затем я подчистил записанный код, удалив ненужные строки.

Советы по скорости VBA

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

Отключение обновления экрана

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

Если вы хотите, чтобы пользователь видел, что происходит в любой момент макроса, используйте следующий оператор, чтобы снова включить обновление экрана:

Чтобы продемонстрировать разницу в скорости, выполните этот простой макрос, который заполняет диапазон цифрами: клетки. Теперь вставьте следующий оператор в начало процедуры и выполните его снова:

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

Отключение автоматического расчета

Если у вас есть рабочий лист со множеством сложных формул, вы можете обнаружить, что можете значительно ускорить процесс, установив ручной режим расчета во время выполнения макроса. Когда макрос завершится, снова установите режим расчета на автоматический.
Следующая инструкция устанавливает ручной режим вычислений в Excel:

Выполните следующую инструкцию, чтобы установить автоматический режим вычислений:

Устранение надоедливых предупреждающих сообщений

Как вы знаете, макрос может автоматически выполнять ряд действий. Во многих случаях вы можете запустить макрос, а затем отправиться к кулеру с водой, пока Excel делает свое дело. Однако некоторые операции, выполняемые в Excel, отображают сообщения, требующие вмешательства человека. Например, если ваш макрос удаляет лист, Excel отображает сообщение, показанное на рис. 14-3. Эти типы сообщений означают, что вы не можете оставить Excel без присмотра, пока он выполняет ваш макрос.

Рисунок 14-3:
Вы можете указать Excel не отображать эти типы предупреждений во время выполнения макроса.
Чтобы избежать этих предупреждающих сообщений, вставьте в макрос следующий оператор VBA:

По завершении процедуры Excel автоматически сбрасывает свойство «Отображать предупреждения» в значение «Истина» (его нормальное состояние).

Упрощение ссылок на объекты

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

Если ваш макрос часто использует этот диапазон, вы можете создать объектную переменную с помощью команды Set. Например, следующий оператор присваивает этот объект Range объектной переменной с именем Rate:

После определения этой объектной переменной вы можете использовать переменную Rate вместо длинной ссылки. Например, вы можете изменить значение ячейки с именем «Процентная ставка»:

Это гораздо проще напечатать (и понять), чем следующее утверждение:

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

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

Обычно вам не нужно беспокоиться о типе данных, которые вы присваиваете переменной. Excel обрабатывает все детали для вас за кулисами. Например, если у вас есть переменная с именем MyVar, вы можете присвоить ей число любого типа. Вы даже можете присвоить ему текстовую строку позже в процедуре.
Но если вы хотите, чтобы ваши процедуры выполнялись как можно быстрее (и избегали некоторых потенциально неприятных проблем), сообщите Excel, какой тип данных будет присвоен каждой из ваших переменных. Это известно как объявление типа переменной. (Подробности см. в главе 7.) Выработайте привычку объявлять все переменные, которые вы используете.
Как правило, следует использовать тип данных, который требует наименьшего количества байтов, но при этом может обрабатывать все назначенные ему данные. Когда VBA работает с данными, скорость выполнения зависит от количества байтов, которыми располагает VBA. Другими словами, чем меньше байтов использует данные, тем быстрее VBA может получить доступ к данным и управлять ими.
Если вы используете переменную объекта (как описано в предыдущем разделе), вы можете объявить переменную как объект определенного типа. Вот пример:

Использование структуры With-End With

Вам нужно задать ряд свойств для объекта? Ваш код работает быстрее, если вы используете структуру With-End With. Дополнительным преимуществом является то, что ваш код может быть легче читать.

Следующий код не использует With-End With:

Вот тот же код, переписанный для использования With-End With.

Если эта структура кажется вам знакомой, это, вероятно, потому, что программа записи макросов использует With-End With всякий раз, когда это возможно.

18 готовых к использованию кодов VBA, которые спасут вас

В этой статье я покажу вам некоторые из самых удивительных кодов VBA Excel, которые вы можете использовать для оптимизации своей работы. VBA — это язык программирования, который можно использовать для расширения возможностей MS Excel и других приложений MS Office. Это чрезвычайно полезно для пользователей MS Excel, поскольку с его помощью можно автоматизировать работу и значительно повысить эффективность. Эта статья познакомит вас с VBA и покажет некоторые из наиболее полезных, готовых к использованию кодов VBA. Вы можете использовать эти примеры макросов для создания собственных скриптов, соответствующих вашим потребностям.

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

Я подготовил для вас ряд готовых к использованию примеров VBA Excel Macro с большой функциональностью, которые вы можете использовать для оптимизации своей работы. Чтобы использовать их, вам нужно «установить» их в свой файл Excel. Следующий абзац касается установки макроса Excel. Пропустите эту часть, если вы уже знакомы с этим.

Содержание

Как установить макрос

В Excel нажмите комбинацию клавиш alt+F11. Это приведет вас к редактору VBA в MS Excel. Затем щелкните правой кнопкой мыши папку «Объекты Microsoft Excel» слева и выберите «Вставить» => «Модуль». Это место, где хранятся макросы. Чтобы использовать макрос, вам нужно сохранить документ Excel с поддержкой макросов. На вкладке «Файл» => «Сохранить как» выберите «Сохранить как книгу с поддержкой макросов» (расширение .xlsm). Теперь пришло время написать свой первый макрос!

1.

Скопировать данные из одного файла в другой.

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

 Sub CopyFiletoAnotherWorkbook()
    'Скопируйте данные
        Листы("Пример 1").Диапазон("B4:C15").Копировать
    'Создать новую книгу
        Книги.Добавить
    'Вставить данные
        ActiveSheet.Paste
    'Отключить оповещения приложений
        Application.DisplayAlerts = Ложь
    'Сохраните новый файл. Измените имя каталога.
        Имя файла ActiveWorkbook.SaveAs:="C:\Temp\MyNewBook.xlsx"
    'Снова включить оповещения приложений
        Application.DisplayAlerts = Истина
Конец суб 

2. Показать скрытые строки

Иногда большие файлы Excel содержат скрытые строки для большей ясности. Вот макрос, который покажет все строки на активном листе:

 Sub ShowHiddenRows()
    Столбцы.EntireColumn.Hidden = Ложь
    Строки. EntireRow.Hidden = Ложь
End Sub 

3. Удалить пустые строки и столбцы

Пустые строки в Excel являются проблемой при обработке данных. Вот как от них избавиться:

 Sub DeleteEmptyRowsAndColumns()
    'Объявите свои переменные.
        Затемнить MyRange как диапазон
        Dim iCounter до тех пор, пока
    'Определить целевой диапазон.
        Установить MyRange = ActiveSheet.UsedRange
        'Начать обратный цикл по диапазону Rows.
        Для iCounter = MyRange.Rows.Count To 1 Step -1
    'Если вся строка пуста, удалите ее.
           Если Application.CountA(Строки(iCounter).EntireRow) = 0 Тогда
               Строки(iCounter).Удалить
               'Удалить комментарий, чтобы посмотреть, какие строки пустые
               'MsgBox "строка" & iCounter & "пуста"
           Конец, если
    'Увеличить счетчик вниз
        Следующий iCounter
    'Шаг 6: Начните обратный цикл по диапазону Columns.
        Для iCounter = MyRange.Columns.Count To 1 Step -1
    'Шаг 7: Если весь столбец пуст, удалите его. 
               Если Application.CountA(Столбцы(iCounter).EntireColumn) = 0 Тогда
                Столбцы(iCounter).Удалить
               Конец, если
    'Шаг 8: Увеличьте счетчик вниз
        Следующий iCounter
Конец суб 

4. Найти пустую ячейку

 Sub FindEmptyCell()
    ActiveCell.Смещение(1, 0).Выбрать
       Делать, пока не пусто (ActiveCell)
          ActiveCell.Смещение(1, 0).Выбрать
       Петля
End Sub 

5. Замените пустые ячейки значением.

Как упоминалось ранее, пустые ячейки мешают обработке данных и созданию сводных таблиц. Вот код, который заменяет все пустые ячейки на 0. Этот макрос имеет очень большое применение, потому что вы можете использовать его для поиска и замены результатов N/A, а также других символов, таких как точки, запятые или повторяющиеся значения:

 Sub FindAndReplace()
    'Объявите свои переменные
        Затемнить MyRange как диапазон
        Dim MyCell как диапазон
    «Сохранить рабочую книгу перед изменением ячеек?
        Выберите Case MsgBox("Невозможно отменить это действие. " & _
                            «Сначала сохранить книгу?», vbYesNoCancel)
            Дело = vbДа
            ThisWorkbook.Сохранить
            Случай = vbCancel
            Выйти из подпрограммы
        Конец выбора
    'Определить целевой диапазон.
        Установите MyRange = Выбор
    'Начните перебирать диапазон.
        Для каждой ячейки MyCell в MyRange
    'Проверить нулевую длину, затем добавить 0.
            Если Len(MyCell.Value) = 0 Тогда
                Моя ячейка = 0
            Конец, если
    'Получить следующую ячейку в диапазоне
        Следующая MyCell
Конец суб 

6. Сортировка чисел

Следующий макрос сортирует в порядке возрастания все числа из столбца активной ячейки. Просто дважды щелкните любую ячейку в столбце, который вы хотите отсортировать.
NB: вам нужно поместить код на лист 1, а не в модуль для работы:

 Private Sub Worksheet_BeforeDoubleClick (ByVal Target as Range, Cancel As Boolean)
    'Объявите свои переменные
        Dim LastRow As Long
    'Найти последнюю строку
        LastRow = Cells (Rows. Count, 1) .End (xlUp) .Row
    'Сортировка по возрастанию по двойному щелчку столбца
        Строки ("6:" и LastRow). Сортировка _
        Key1: = Ячейки (6, ActiveCell.Column), _
        Порядок1: = кслПо возрастанию
Конец суб 

7. Удаление пустых пространств

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

 Sub TrimTheSpaces()
    'Объявите свои переменные
        Затемнить MyRange как диапазон
        Dim MyCell как диапазон
    'Сохраните книгу перед изменением ячеек
        Выберите Case MsgBox("Невозможно отменить это действие." & _
                            «Сначала сохранить книгу?», vbYesNoCancel)
            Дело = vbДа
            ThisWorkbook.Сохранить
            Случай = vbCancel
            Выйти из подпрограммы
        Конец выбора
    'Определить целевой диапазон.
        Установите MyRange = Выбор
    'Начните перебирать диапазон. 
        Для каждой ячейки MyCell в MyRange
    «Обрезать пробелы.
            Если Не Является Пустым (Моя Ячейка), Тогда
                Моя ячейка = Обрезать (Моя ячейка)
            Конец, если
    'Получить следующую ячейку в диапазоне
        Следующая MyCell
Конец суб 

8. Выделите повторяющиеся значения

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

 Sub HighlightDuplicates()
    'Объявите свои переменные
        Затемнить MyRange как диапазон
        Dim MyCell как диапазон
    'Определить целевой диапазон.
        Установите MyRange = Выбор
    'Начните перебирать диапазон.
        Для каждой ячейки MyCell в MyRange
    'Убедитесь, что ячейка имеет текстовое форматирование.
            Если WorksheetFunction.CountIf(MyRange, MyCell.Value) > 1, то
                MyCell.Interior.ColorIndex = 36
            Конец, если
    'Получить следующую ячейку в диапазоне
        Следующая MyCell
Конец суб 

9.

Выделить первую десятку значений

Этот код выделит первые десять значений из выбранных ячеек:

 Sub TopTen ()
    Selection.FormatConditions.AddTop10
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        С Selection.FormatConditions(1)
            .TopBottom = xlTop10Top
            'Измените ранг здесь, чтобы выделить другое количество значений
            .Ранг = 10
            .Процент = Ложь
        Конец с
        С Selection.FormatConditions(1).Font
            .Цвет = -16752384
            .TintAndShade = 0
        Конец с
        С Selection.FormatConditions(1).Interior
            .PatternColorIndex = кслаутоматик
            .Цвет = 13561798
            .TintAndShade = 0
        Конец с
    Selection.FormatConditions(1).StopIfTrue = False
End Sub 

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

10. Выделите значения больше

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

 ПодсветкаGreaterThanValues()
    Dim i как целое число
    i = InputBox("Введите значение больше чем", "Введите значение")
    Selection.FormatConditions.Delete
    'Измените оператор на xlLower, чтобы выделить значения ниже
    Selection.FormatConditions.Add Тип:=xlCellValue, Оператор:=xlGreater, Formula1:=i
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        С Selection.FormatConditions(1)
            .Шрифт.Цвет = RGB(0, 0, 0)
            .Внутренний.Цвет = RGB(31, 218, 154)
        Конец с
Конец суб 

Вы можете изменить этот код, чтобы выделить более низкие значения.

Простой макрос, выделяющий все ячейки, содержащие комментарии:

 Sub HighlightCommentCells()
    Selection.SpecialCells(xlCellTypeComments).Select
    Selection.Style= "Примечание"
End Sub 

12. Выделение ячеек со словами с ошибками

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

 Sub ColorMispelledCells()
    Для каждого cl в ActiveSheet.UsedRange
        Если не Application.CheckSpelling(Word:=cl.Text) Then _
        кл.Interior.ColorIndex = 28
    Следующий кл
End Sub 

13. Создайте сводную таблицу

Вот как создать сводную таблицу в MS Excel (версия 2007 г.). Особенно полезно, когда вы делаете пользовательский отчет каждый день. Вы можете оптимизировать создание сводной таблицы следующим образом:

 Sub PivotTableForExcel2007()
    Затемнить исходный диапазон как диапазон
    Установить SourceRange = Sheets("Лист1").Range("A3:N86")
    ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=SourceRange, _
    Версия:=xlPivotTableVersion12).CreatePivotTable _
    Назначение таблицы:="", _
    ИмяТаблицы:="", _
    Дефолтверсион: = кслпивоттаблеверсион12
Конец суб 

14. Прикрепите активную книгу к электронному письму

Мой любимый код VBA. Он позволяет вам прикреплять и отправлять файл, над которым вы работаете, с предопределенным адресом электронной почты, заголовком сообщения и телом сообщения! Сначала вам нужно установить ссылку на Microsoft Outlook (в редакторе VBA щелкните инструменты => ссылки и выберите Microsoft Outlook).

 Sub SendFIleAsAttachment()
    'Объявите свои переменные
    'Установить ссылку на библиотеку объектов Microsoft Outlook
        Dim OLApp как Outlook.Application
        Dim OLMail как объект
    'Открыть Outlook, начать новый почтовый элемент
        Установите OLApp = Новый Outlook.Application
        Установите OLMail = OLApp.CreateItem(0)
        OLApp.Session.Logon
    'Создайте свой почтовый ящик и отправьте
        С OLMail
        .To = "[email protected]; [email protected]"
        .СС = ""
        .BCC = ""
        .Subject = "Это строка темы"
        .Body = "Привет"
        .Attachments.Add ActiveWorkbook.FullName
        .Display 'Изменить на .Send для отправки без проверки
        Конец с
    'Очистка памяти
        Установите OLMail = Ничего
        Установите OLApp = Ничего
Конец суб 

15. Отправить все диаграммы Excel в презентацию PowerPoint

Очень удобный макрос, позволяющий добавить все диаграммы Excel в презентацию Powerpoint одним щелчком мыши:

 Sub SendExcelFiguresToPowerPoint()
    'Установить ссылку на библиотеку объектов Microsoft Powerpoint
    'Объявите свои переменные
        Dim PP As PowerPoint. Application
        Dim PPPres As PowerPoint.Presentation
        Затемнение PPSlide как PowerPoint.Slide
        Dim i как целое число
    'Проверить диаграммы; выйти, если диаграмм нет
        Листы("Данные слайда").Выбрать
            Если ActiveSheet.ChartObjects.Count < 1 Тогда
                MsgBox "Нет диаграмм на активном листе"
                Выйти из подпрограммы
            Конец, если
    'Откройте PowerPoint и создайте новую презентацию
        Установите PP = New PowerPoint.Application
        Установите PPPres = PP.Presentations.Add
        PP.Видимый = Истина
    'Запустить цикл на основе количества графиков
            Для i = 1 в ActiveSheet.ChartObjects.Count
            'Скопируйте диаграмму как картинку
                ActiveSheet.ChartObjects(i).Chart.CopyPicture _
                Размер:=xlScreen, Формат:=xlPicture
                Application.Wait(Сейчас + TimeValue("0:00:1"))
            'Подсчет слайдов и добавление нового слайда в качестве следующего доступного номера слайда
                ppSlideCount = PPPres. Slides.Count
                Установите PPSlide = PPPres.Slides.Add (SlideCount + 1, ppLayoutBlank)
                PPSlide.Select
            'Вставьте изображение и отрегулируйте его положение; Перейти к следующему графику
                PPSlide.Shapes.Paste.Select
                PP.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
                PP.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
            Далее я
    'Очистка памяти
        Установите PPSlide = Ничего
        Установите PPPres = Ничего
        Установите PP = Ничего
Конец суб 

16. Отправить таблицу Excel в MS Word

Таблицы Excel обычно вставляются в текстовые документы. Вот автоматизированный способ экспорта таблицы Excel в MS Word:

 Sub ExcelTableInWord()
    'Установить ссылку на библиотеку объектов Microsoft Word
    'Объявите свои переменные
        Затемнить MyRange как Excel.Range
        Dim wd As Word.Application
        Dim wdDoc как Word.Document
        Dim WdRange As Word. Range
    'Скопировать указанный диапазон
       Sheets("Таблица доходов").Range("B4:F10").Cop
    'Открыть целевой документ Word
        Установите wd = Новое Word.Application
        Установите wdDoc = wd.Documents.Open _
        (ThisWorkbook.Path & "\" & "PasteTable.docx")
        wd.Visible = Истина
    'Установить фокус на целевой закладке
        Установите WdRange = wdDoc.Bookmarks("DataTableHere").Rangе
    'Удалить старую таблицу и вставить новую
        При ошибке Возобновить Далее
        WdRange.Tables(1).Удалить
        WdRange.Paste 'вставить в таблицу
    'Настроить ширину столбцов
        WdRange.Tables(1).Columns.SetWidth _
        (MyRange.Width/MyRange.Columns.Count), вдададжустсамевидс
    'Повторно вставьте закладку
        wdDoc.Bookmarks.Add "DataTableHere", WdRange
    'Очистка памяти
        Установите wd = Ничего
        Установите wdDoc = Ничего
        Установите WdRange = ничего
Конец суб 

Мы можем использовать формулы, если хотим извлечь определенное количество символов. Но что, если мы хотим извлечь только второе слово из предложения или диапазона слов в ячейке? Для этого мы можем создать пользовательскую функцию Excel с помощью VBA. Это одна из самых важных функций VBA, поскольку она позволяет создавать собственные функции, которых нет в MS Excel. Давайте продолжим и создадим две функции: findword() и findwordrev(). Вот код vba для этого:

 Функция FindWord (источник как строка, позиция как целое число) как строка
     При ошибке Возобновить Далее
     FindWord = Разделить (WorksheetFunction.Trim (Источник), " ") (Позиция - 1)
     При ошибке Перейти к 0
Конечная функция
Функция FindWordRev (источник в виде строки, позиция в виде целого числа) в виде строки
     Dim Arr() как строка
     Arr = VBA.Split(WorksheetFunction.Trim(Source), "")
     При ошибке Возобновить Далее
     FindWordRev = Arr(UBound(Arr) - Позиция + 1)
     При ошибке Перейти к 0
Завершить функцию 

Очень хорошо, мы создали две функции cstom Excel. Теперь попробуйте использовать их в Excel. Функция = FindWordRev(A1,1) берет последнее слово из ячейки A1. Функция = FindWord (A1,3) берет третье слово из ячейки A1 и т. д.

18. Защитите свою рабочую книгу

Иногда мы хотим защитить данные в нашем файле, чтобы только мы могли их изменить. Вот как это сделать с помощью VBA:

 Sub ProtectSheets()
    'Объявите свои переменные
        Dim ws As рабочий лист
    'Начать цикл по всем листам
        Для каждого ws в ActiveWorkbook.Worksheets
    'Защитить и перейти к следующему рабочему листу
        пароль ws.Protect: = "1234"
        Следующий мс
Конец суб 

Поздравляем! Поскольку вы все еще читаете это, вы действительно заинтересованы в изучении VBA. Как вы уже сами убедились, язык программирования VBA чрезвычайно полезен и может сэкономить нам много времени. Я надеюсь, что вы нашли эту информацию полезной и используете ее, чтобы стать мастером в MS Excel, VBA и компьютерном программном обеспечении в целом.

© 2017 Atanas Yonkov

Литература:

1.