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

Содержание

Макросы. Чем меньше-тем лучше.

Макросы VBA. Чем их меньше — тем лучше.

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

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

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

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

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

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

Если макросы не универсальные, то они надежно работают пока ничего не трогаешь. Но когда ты начинаешь удалять или переименовывать листы, диапазоны или ячейки, то можешь изменить те, к которым обращается макрос. В результате программа пытается обратиться к несуществующему листу, диапазону или ячейке и останавливается, либо отрабатывает некорректно.
Даже простая перестановка колонок местами, если макрос написан неграмотно, может привести к проблемам. Ведь в отличие от формул, которые автоматически изменяются в случае изменения названий листов и диапазонов, изменения адреса ячеек, ставки/удаления строк и столбцов и т.д., в макросе адреса ячеек и имена диапазонов нужно менять вручную.

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

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

По вышеописанным причинам использование макросов в решениях ExcelSoft минимизировано настолько, насколько это возможно. И все равно это около 3 тысяч строк кода. Но без них уже никак. К хорошему быстро привыкаешь.

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

Курс по макросам на VBA для Excel в Санкт-Петербурге – Бруноям

Алиса Пушина

Опыт работы

Закончила факультет социологии, работала маркетологом-аналитиком. Общий стаж в аналитике данных 10 лет.

О себе

Я не люблю работать «руками». От монотонной работы у меня слипаются глаза. Поэтому я стараюсь максимально автоматизировать свою деятельность, чтобы заниматься интересными делами, а не полевым сбором данных. Если в этих утверждениях вы нашли себя — давайте вместе двигаться в направлении развития! Excel — это не просто инструмент, это интересно.

Я хотела восстановить знания по Excel после длительного перерыва. И на курсе я их не просто восстановила, а узнала много новых тонкостей. Прекрасная, теплая, дружелюбная атмосфера в группе. Спасибо огромное Алисе за профессиональные и структурированные знания, которыми она с нами делилась

— Мария, Группа PE189-1551

Опыт работы

Закончила факультет социологии, работала маркетологом-аналитиком. Общий стаж в аналитике данных 10 лет.

О себе

— Мария, Группа PE189-1551

Александр Травин

Опыт работы

Более 5 лет веду научно-исследовательскую и преподавательскую деятельность в области экономики и менеджмента. Одно из основных направлений моей деятельности – прикладное использование пакета Microsoft Office в научных исследованиях и бизнес-процессах.

О себе

«Microsoft Office» давно и плотно вошел в наш повседневный обиход. Поэтому многим кажется, что ничего интересного полезного для себя там не найти. Но для меня MS Office – целая вселенная возможностей для повышения продуктивности и эффективности своей работы, удивительных открытий, приятных сюрпризов для коллег и близких («смотри как я могу!») или просто для сокращения повседневной рутины. И эта вселенная – живая, в последние годы она очень динамично расширяется, там всегда есть что изучать! Поэтому я всегда рад, когда мои студенты узнают новые полезные приемы, развивают навыки работы. Ваш успех – моя лучшая награда!

Хотел получить углубленные знания в Excel, в частности получить знания в функциях ВПР, ГПР, ИНДЕКС,ПОИСКПОЗ… Удовлетворен полностью, т.к. курс не только теоретический, но и максимально практический. Спасибо большое преподавателю Александру

— Александр Малыгин, PE304-2074

Опыт работы

О себе

— Александр Малыгин, PE304-2074

Редактировать макросы в Excel | CustomGuide

Если вы записываете макрос, а затем хотите внести изменения без необходимости его повторной записи, его можно отредактировать в окне Visual Basic. Этот урок знакомит вас с языком программирования Visual Basic (также называемым VB или VBA) — кодом, который Excel использует для записи макросов. Используя язык Visual Basic и редактор Visual Basic, вы можете вносить небольшие изменения в свои макросы после их записи.

Лучший способ узнать о Visual Basic — просмотреть существующий код. В этом уроке мы рассмотрим, как просматривать и редактировать код существующего макроса.

Показать личную книгу

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

Перейдите на вкладку Просмотр .
Нажмите кнопку Показать окно .
Убедитесь, что выбрано ЛИЧНОЕ, и нажмите OK .

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

После того, как вы закончите редактирование личной книги, вы захотите снова ее скрыть.

Редактировать макрос

Перейдите на вкладку Разработчик .
Нажмите кнопку Макросы .
Появится диалоговое окно макроса. Здесь вы можете увидеть макросы, которые вы записали.
Выберите макрос для редактирования.
Нажмите кнопку Редактировать .
Появится программа Microsoft Visual Basic для приложений. То, что может выглядеть как беспорядочный текст, на самом деле является кодом Visual Basic — языком, который использовался Excel для записи созданного вами макроса.
Вам не нужно изучать Visual Basic, чтобы владеть Excel, однако знание основ может быть полезным, если вы когда-нибудь захотите изменить существующий макрос. Если вы внимательно посмотрите на код своего макроса, некоторые процедуры должны быть вам понятны. Например, если ваш макрос выбирает некоторые ячейки в книге, вы можете увидеть текст «Выбрать» или «Выбор».
Вы можете удалить участки кода, чтобы удалить определенные действия из макроса, или отредактировать код, чтобы изменить действия макроса.
Отредактируйте код макроса по желанию.
Нажмите кнопку Сохранить .
Закройте окно программы Visual Basic для приложений.

Окно редактора Visual Basic закрывается, и вы возвращаетесь в главное окно Excel.

Удалить макрос

Перейдите на вкладку Разработчик .
Щелкните макросы 9кнопка 0014.
Появится диалоговое окно макроса. Здесь вы можете увидеть макросы, которые вы записали.
Выберите макрос.
Нажмите кнопку Удалить .

Макрос удален.

Проверка отредактированного макроса

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

Выделив лист, на котором вы хотите запустить редактируемый макрос, нажмите кнопку Кнопка «Макросы » на вкладке «Разработчик».
Выберите редактируемый макрос.
Нажмите кнопку Выполнить .

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

БЕСПЛАТНЫЙ краткий справочник

Нажмите, чтобы загрузить

Бесплатно для распространения с нашими комплиментами; мы надеемся, что вы рассмотрите наше платное обучение.

Скорость и эффективность Excel VBA

Кевин Роупер

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

Первые пять правил обычно оказывают наибольшее влияние на производительность макросов. Правила с 6 по 11 имеют незначительное влияние. Обратите внимание, что приведенные ниже оценки экономии времени могут значительно отличаться для вашего конкретного приложения. Для анализа использовалась программа Excel 2007.

Правило №1. Отключить автоматический расчет электронных таблиц

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

Application.Calculation = xlCalculationManual

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

Вычислить
Рабочие листы («лист1»). Вычислить
Диапазон («A1:C5»). Вычислить

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

Application.Calculation = xlCalculationAutomatic.

Правило №2. Отключить обновления экрана

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

Application.ScreenUpdating = FALSE

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

Application.ScreenUpdating = TRUE

Правило №3. Сведите к минимуму трафик между VBA и рабочим листом

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

В этом примере макросу потребуется многократно извлекать именованный диапазон «issue_age» из рабочего листа. Это распространенная ошибка. VBA работает намного быстрее, когда ему не нужно останавливаться и взаимодействовать с рабочим листом.

Для Duration = от 1 до 100
Attained_Age = Range(«Issue_Age») + Duration
Next Duration

В следующем коде переменная Issue_Age считывается с рабочего листа только один раз, и трафик между VBA и Excel сведен к минимуму. Код ниже более чем в 100 раз быстрее, чем код выше!

Issue_Age = Range(«Возраст_выпуска»)
For Duration = от 1 до 100
Attained_Age = Issue_Age + Duration
Next Duration

Также эффективнее выполнять все числовые вычисления в VBA. Часто возникает соблазн оставить формулы в электронной таблице и вызвать их из макроса. Но, если важна скорость, занесите все формулы в макрос. Это сводит к минимуму трафик и не требует пересчета электронной таблицы.

Как правило, используйте команды WorkSheets, Range, Cells и Application как можно эффективнее вне циклов.

Правило №4. Чтение и запись блоков данных за одну операцию

Это правило является продолжением правила №3. Это еще один способ минимизировать трафик между VBA и Excel. По возможности читайте и записывайте данные порциями. Есть несколько способов сделать это. Вот пример чтения большого блока данных (2600 ячеек) в массив. Этот пример примерно в 50 раз быстрее, чем чтение в каждой ячейке по отдельности в цикле.

Dim myArray() As Variant ´ обратите внимание, что это должен быть вариант
myArray= Worksheets(«Sheet1»).Range(«A1:Z100»).value рабочий лист. Все это примерно в 40 раз быстрее, чем запись каждой из 2600 ячеек по отдельности в цикле.
Метод №1
Рабочие листы(«Лист1»).Range(«A1:Z100»).value = myArray
Метод №2
С рабочими листами(«Лист1»)
.Range(«A1:Z100») .Значение = мой массив
End With
Метод №3
Уменьшить диапазон как диапазон
Установить theRange = Range(«A1:Z100»)
theRange.value = myArray
Правило №5. Избегайте использования определенных функций листа Excel
Это правило меня удивило. Я наивно полагал, что общие функции рабочего листа будут эффективно обрабатываться VBA. Это явно не так. Например, большинство пользователей VBA, вероятно, знают, что в VBA нет функций Max() или Min(). В Excel есть эти функции. Обычно используется следующий код, использующий версию Max() для Excel:
variable1 = Application.Max(Value1, Value2)
Я нашел в Интернете версию функции VBA Max() с открытым исходным кодом. Это было в 10 раз быстрее, чем аналог на основе Excel выше. Однако приведенный ниже код более чем в 80 раз быстрее! Я допускаю, что приведенная ниже функция работает только с двумя аргументами и не поддерживает массивы, но улучшение скорости существенное.
Функция Max2 (Value1, Value2)
Если Value1 > Value2 Then
Max2 = Value1
Else
Max2 = Value2
End If
End Function
Я советую соблюдать осторожность при использовании функций рабочего листа в больших, трудоемких макросах. Вы должны оценить влияние переписывания функции. Обратите внимание, что любая команда, начинающаяся с «Приложение». или «Функция рабочего листа». относится к функции Excel. Не могу сказать, что все «Приложения». функции медленные. Но я написал или скачал версии Min(), Max(), Average(), Match(), NormSInv() и StDev(), которые намного быстрее, чем версии Excel.
Правило №6. Избегайте использования Variant в формулах
Не объявляйте числовую переменную как Variant без необходимости. Обратите внимание, что если вы решите не использовать «Option Explicit» в начале макроса, любая неопределенная переменная будет Variant. Варианты очень гибкие, потому что они могут быть числовыми или текстовыми, но они медленно обрабатываются в формуле. Влияние на эффективность невелико, но каждая мелочь помогает. Обратите внимание, что это правило также применяется к любым функциям, которые вы пишете. Основываясь на моих тестах, типы переменных от самого быстрого до самого медленного в математических уравнениях: константа, одиночная, двойная, длинная, целое число, вариант.
Правило №7. Избегайте оценки строк
Строки (текст) обрабатываются медленно. Избегайте оценки строк в таком коде:
Выбор пола случая
Случай «Мужской»
(вставьте сюда код)…
Вариант «Женский»
(вставьте сюда код)…
Случай «Унисекс»
(вставьте код здесь)…
End Select
Перечисление присваивает переменной постоянное числовое значение. VBA может быстро обрабатывать перечисляемые значения, сохраняя при этом удобочитаемый код. Перечисление может назначать числовые значения по умолчанию или можно назначать конкретные значения.
Public Enum enumGender
Male = 0
Female = 1
Unisex = 2
End Enum
Dim Gender as enumGender
Select Case Gender
Case Male
(вставьте код здесь)… код ert здесь) …
Case Unisex
(вставьте сюда код)…
End Select
Логические операторы — это просто переключатели TRUE или FALSE, которые обрабатываются очень быстро. В приведенном ниже примере bMale, bFemale и bUnisex являются булевыми переменными. Логический код примерно в 10 раз быстрее, чем использование строк.
Если bМужчина Тогда
(вставьте сюда код)…
Иначе Если bЖенщина Тогда
(вставьте сюда код)…
Иначе Если bУнисекс Тогда
(вставьте сюда код)…
End If
Правило №8. Не выбирайте определенные листы без необходимости
Как правило, вам не нужно использовать команду «Выбрать» для чтения или записи на лист. Не выбирать рабочий лист примерно в 30 раз быстрее.
Избегайте этого:
Worksheets(«sheet1»). Выберите
Amount1 = Cells(1, 1)
Вместо этого сделайте следующее:
Сумма1 = Рабочие листы(«лист1»).Ячейки(1,1)
Правило №9. Избегайте чрезмерного использования обновлений StatusBar
VBA может обрабатывать математические данные быстрее, чем отображать StatusBar. Запись в StatusBar — еще один пример трафика между VBA и Excel. В следующем примере один из каждых 100 сценариев записывается в строку состояния. Это примерно в 90 раз быстрее, чем запись каждого сценария в StatusBar.
Для сценария = 1 На 10000
(вставьте сюда код)…
Если мод сценария 100 = 0, то Application.StatusBar = сценарий
Следующий сценарий
Правило №10. Избегайте ненужной математики
Как актуарии, мы любим макросы, полные формул. Часто формулы не так эффективны, как должны быть. (1/12) 9(1/12)
Для i = от 1 до 600
fund(i) = fund(i-1) * Interest_factor
Next i

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

Правило №11. Не копируйте и вставляйте

Функции копирования и вставки (или PasteSpecial) работают медленно. Примерно в 25 раз быстрее использовать следующее для копирования и вставки значений.

Диапазон(«A1:Z100»).значение = Диапазон(«A101:Z200»).значение

Заключительные мысли

Я счел полезным написать небольшой макрос для оценки экономии времени, связанной с различными методами. Макрос просто выполняет метод около миллиона раз и записывает время, затраченное на выполнение этого метода. Простой макрос ниже сравнивает функцию Excel Max() с функцией Max2, показанной в правиле № 5.

‘**Вычислить первую функцию
Start_time = Now
For i = 1 To 1000000
value1 = Application.