Remkomplekty.ru

IT Новости из мира ПК
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Ускорение работы макроса

How to dou

Как ускорить работу Excel или макроса

Table of Contents:

Поскольку ваши макросы Excel становятся все более надежными и сложными, вы можете обнаружить, что они теряют производительность. При обсуждении макросов слово производительность обычно является синонимом speed . Скорость — это то, как быстро ваши процедуры VBA выполняют свои намеченные задачи. Ниже приведены десять способов помочь сохранить макросы Excel с оптимальным уровнем производительности.

Расчет листового листа

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

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

Поместите Excel в ручной режим расчета, запустите свой код и затем вернитесь в автоматический режим расчета.

Установка режима вычисления обратно в xlCalculationAutomatic автоматически приведет к пересчету рабочего листа, поэтому нет необходимости нажимать клавишу F9 после запуска макроса.

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

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

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

После того, как свойство ScreenUpdating вернётся к True, Excel автоматически вызовет перерисовку экрана.

Отключение обновлений строки состояния

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

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

Указание Excel на игнорирование событий

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

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

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

Перед запуском макроса установите для свойства EnableEvents значение False. После того, как ваш макрокоманда закончен, вы можете вернуть свойство EnableEvents значение True.

Скрытие разрывов страниц

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

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

Установите для свойства листа DisplayPageBreaks значение False, чтобы скрыть разрывы страниц. Если вы хотите продолжать показывать разрывы страниц после запуска макроса, установите для свойства листа DisplayPageBreaks значение True.

Приостановка обновлений сводной таблицы

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

Вы можете улучшить производительность своего макроса, приостановив перерасчет сводной таблицы до тех пор, пока не будут сделаны все изменения поля поворота. Просто установите PivotTable. Свойство ManualUpdate для True, чтобы отложить пересчет, запустите свой макрокоманд и затем установите сводную таблицу. Свойство ManualUpdate вернется к False, чтобы вызвать пересчет.

Очистка от копирования и вставки

Важно помнить, что хотя Macro Recorder экономит время, написав код VBA для вас, он не всегда записывает наиболее эффективный код. Ярким примером является то, как Macro Recorder захватывает любое действие копирования и вставки, которое вы выполняете во время записи.

Вы можете немного увеличить свои макросы, вырезав посредника и выполнив прямую копию из одной ячейки в ячейку назначения. Этот альтернативный код использует аргумент Destination для обхода буфера обмена и копирования содержимого ячейки A1 непосредственно в ячейку B1.

Если вам нужно скопировать только значения (не форматирование или формулы), вы можете повысить производительность еще больше, избегая при этом метода копирования. Просто установите значение ячейки назначения на то же значение, которое находится в исходной ячейке. Этот метод примерно в 25 раз быстрее, чем при использовании метода Copy:

Если вам нужно скопировать только формулы из одной ячейки в другую (а не значения или форматирование), вы можете установить формулу ячейки назначения той же формуле, содержащейся в исходной ячейке:

Читать еще:  Проги для ускорения игр на пк

Использование оператора With

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

Оператор With, используемый в следующем примере, сообщает Excel применять все изменения форматирования за один раз:

Привычка к чередованию действий в операторы With не только ускорит выполнение макросов, но и упростит чтение вашего макрокода.

Избегание метода Select

Macro Recorder предпочитает использовать метод Select для явного выбора объектов, прежде чем предпринимать действия над ними. Как правило, нет необходимости выбирать объекты перед их работой. Фактически, вы можете значительно улучшить производительность макросов, не используя метод Select.

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

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

Ограничение поездок на рабочий лист

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

Например, следующий простой код заставляет VBA непрерывно возвращаться к листам («Лист1»). Range («A1»), чтобы получить номер, необходимый для сравнения, выполняемого в инструкции If:

Более эффективным способом является сохранение значения в листах («Лист1»). Range («A1») к переменной MyMonth. Таким образом, код ссылается на переменную MyMonth вместо рабочего листа:

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

Как ускорить и оптимизировать код VBA

  1. Если в коде есть много всяких Activate и Select , тем более в циклах — следует немедленно от них избавиться. Как это сделать я писал в статье: Select и Activate — зачем нужны и нужны ли?
  2. Обязательно на время выполнения кода отключить:
    • автоматический пересчет формул . Чтобы формулы не пересчитывались при каждой манипуляции на листе во время выполнения кода — это может дико тормозить код, если формул много:

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

Главное, что следует помнить — все эти свойства необходимо включить обратно после работы кода . Иначе могут быть проблемы с работой внутри Excel. Например, если забыть включить автопересчет формул — большинство формул будут пересчитывать исключительно принудительным методом — Shift+F9. А если забыть отключить обновление экрана — то есть шанс заблокировать себе возможность работы на листах и книгах. Хотя по умолчанию свойство ScreenUpdating и должно возвращаться в True, если было отключено внутри процедуры — лучше не надеяться на это и привыкать возвращать все свойства на свои места принудительно. По сути все это сведется к нескольким строкам:

‘Возвращаем обновление экрана Application.ScreenUpdating = True ‘Возвращаем автопересчет формул Application.Calculation = xlCalculationAutomatic ‘Включаем отслеживание событий Application.EnableEvents = True

Как такой код выглядит на практике. Предположим, надо записать в цикле в 10 000 строк значения:

Sub TestOptimize() ‘отключаем обновление экрана Application.ScreenUpdating = False ‘Отключаем автопересчет формул Application.Calculation = xlCalculationManual ‘Отключаем отслеживание событий Application.EnableEvents = False ‘Отключаем разбиение на печатные страницы ActiveWorkbook.ActiveSheet.DisplayPageBreaks = False ‘Непосредственно код заполнения ячеек Dim lr As Long For lr = 1 To 10000 Cells(lr, 1).Value = lr ‘для примера просто пронумеруем строки Next ‘Возвращаем обновление экрана Application.ScreenUpdating = True ‘Возвращаем автопересчет формул Application.Calculation = xlCalculationAutomatic ‘Включаем отслеживание событий Application.EnableEvents = True End Sub

Разрывы печатных страниц можно не возвращать — они тормозят работу в любом случае.
Следует избегать циклов, вроде Do While для поиска последней ячейки . Часто такую ошибку совершают начинающие. Куда эффективнее и быстрее вычислять последнюю ячейку на всем листе или в конкретном столбце без этого тормозного цикла Do While. Я обычно использую

другие варианты определения последней ячейки я детально описывал в статье: Как определить последнюю ячейку на листе через VBA?

Для более опытных пользователей VBA я приведу несколько решений по оптимизации кодов в различных ситуациях:

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

Sub TestOptimize_Array() ‘Непосредственно код заполнения ячеек Dim arr, lr As Long ‘запоминаем в массив одним махом все значения 10000 строк первого столбца arr = Cells(1, 1).Resize(10000).Value ‘если нужно заполнение для двух и более столбцов ‘arr = Cells(1, 1).Resize(10000, 2).Value ‘или ‘arr = Range(Cells(1, 1),Cells(10000, 2)).Value ‘или автоматически вычисляем последнюю ячейку и заносим в массив данные, начиная с ячейки А3 ‘llastr = Cells(Rows.Count, 1).End(xlUp).Row ‘последняя ячейка столбца А ‘arr = Range(Cells(3, 1),Cells(llastr, 2)).Value For lr = 1 To 10000 arr(lr,1) = lr ‘заполняем массив порядковыми номерами Next ‘Выгружаем обработанный массив обратно на лист в те же ячейки Cells(1, 1).Resize(10000).Value = arr End Sub

Но здесь следует учитывать и тот момент, что большие массивы могут просто вызвать переполнение памяти. Наиболее актуально это для 32-битных систем, где на VBA и Excel выделяется памяти меньше, чем в 64-битных системах

  • Если используете быстрый ЕСЛИ — IIF , то замените его на IF . Then . Else
  • Так же лучше вместо Switch() и Choose() применить тот же IF . Then . Else
  • В большинстве случаев проверять строку на «не пусто» лучше через Len() , чем прямое сравнение с пустотой: Len(s)=0 вместо s = «» . Связано с тем, что работа со строками значительно медленнее, чем с числовыми данными и Len по сути не подсчитывает длину переменной, а берет это число непосредственно уже готовое из памяти. При сравнении же текста с пустой строкой(«»), VBA сначала создает в памяти переменную нулевой длинны, а уже потом сравнивает с ней наш текст. Поэтому в некоторых случаях так же ускоряет сравнение и в таком виде: s = vbNullString
  • Не применять объединение строк без необходимости. Например, s = «АВ» , будет быстрее, чем: s =»А» & «В»
  • Не применять сравнение текстовых величин напрямую. Лучше применить встроенную функцию StrComp:
    If s <> s1 Then будет медленнее, чем
    If StrComp(s, s1, vbBinaryCompare) = 0
    и тем более, если при сравнении необходимо не учитывать регистр:
    If LCase(s) <> LCase(s1) Then будет медленнее, чем
    If StrComp(s, s1, vbTextCompare) = 0
  • Циклы For … Next в большинстве случаев работает быстрее, чем цикл Do . Lоор
  • Избегать присвоения переменным типа Variant . Хоть соблазн и велик — этот тип забирает много памяти и в дальнейшем замедляет работу кода. Так же для объектных переменных следует избегать по возможности безликого глобального типа Object и применять конкретный тип:

    Ускоряем работу VBA в Excel

    Предисловие

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

    Ускоряем работу макроса

    Итак, к сути… Для того что бы реально ускорить работу VBA в Ecxel нужно понимать, что обращение к ячейке на листе — занимает значительно время. Если Вы хотите записать в ячейку одно значение, то это не займет значительного времени, но если Вам потребуется записать(прочитать, обратиться) к тысячам ячеек, то это потребует гораздо большего времени. Что же делать в таких случаях? На помощь приходят массивы. Массивы хранятся в памяти, а операции в памяти VBA выполняет в сотни, а то и в тысячи раз быстрее. Поэтому, если у Вас в данных тысячи, сотни тысяч значений, то время выполнения макроса может занимать от нескольких минут до нескольких часов, а если эти данные перенести в массив, то выполнение макроса может сократиться до нескольких секунд (минут).

    Я наведу пример кода и в комментариях объясню что к чему, так будет яснее. К тому же, могут пригодиться некоторые строки кода, не относящееся прямо к процессу ускорения.

    Пример

    Предположим, что у нас есть данные на “Лист1” (“Sheet1”). Данные содержаться в 50 колонках (колонки содержат названия) и 10 000 строк. К примеру, нам нужно в последнюю колонку внести значение, которое равно значению во второй колонке, деленное на значение в третьей колонке (начиная со 2-й строки, так как первая содержит заглавие). Потом мы возьмем первые 10 колонок и скопируем их на “Лист2” (“Sheet2”), для дальнейшей обработки (для других потребностей). Пусть пример и банальный, но, как мне кажется, он может отобразить всю суть данного поста.

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

    Заключение

    Большинство операций над данными можно выполнять в массиве, при этом, отображать на лист только результат. Иногда целесообразным бывает показать результат на лист, потом выполнить некоторые действия (например, сортировку) и снова загрузить данные в массив.

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

    Ускорение работы макроса

    Originally posted by pashulka
    [b]Ускорить работу макроса можно если :

    1- «Отключить» обновление экрана
    2- «Установить» ручной пересчёт вычислений
    3- Описывать все переменные (не Variant)
    4- Использовать инструкцию With, Set
    5- Не использовать методы .Select .Activate и т.д.

    P.S. Скорость выполнения программы может зависить и от других факторов, даже не связанных напрямую с конкретным приложением.

    Для ответа на все остальные вопросы IMHO нужно читать литературу или help

    Спасибо. Только можно поподробнее:
    1. «Отключить» обновление экрана
    2. «Установить» ручной пересчет вычислений

    извините за безграмотность.

    Если два совета необходимо об’единить в один, то например так :

    1.1 C точки зрения оптимизации кода, лучше не дёргать свойство .Count каждый раз, а об’явить переменную и использовать её, но только учитывая мощность современных машин, а также количество рабочих листов (10-15) разницы Вы всё равно не заметите.

    Эффект пожалуй можно заметить разве что на 386/486, да и то дело ограничится секундами.

    1.2 Код в том виде, что был опубликован, не будет работать в принципе, вот если бы Вы использовали функцию Array или инструкцию ReDim тогда другое дело, но в любом случае это больше похоже на извращение.

    Примечание : Функция Array, как правило, используется для перебора несмежных листов.

    Совет : Все рабочие листы входят в семейство WorkSheets, а значит мы может перебирать все рабочие листы, без лишних «телодвижений»

    2. Если речь идёт именно о копирование то это буфер обмена, если о переменных то Вы можете почитать о них в help. Эта часть описана довольно подробно.

    3. Как Вы думаете, есть ли разница между стрижкой одного барана или целого стада …

    4. В обоих случаях выполняется по 120 итераций, но вообще-то такие вопросы лучше рассматривать более предметно … или проверить свои сомнения с таймером и сравнить полученные результаты.

    5. «Процедура» — фрагмент VBA кода от ключевого слова Sub до ключевого слова End Sub (цитата)

    6. Я вообще зачастую не об’являл переменные, но это не мешало мне писать макросы, которые работали гораздо быстрее, чем их аналоги созданные людьми, которые называют себя программистами и делают «всё правильно», но при этом не могут обойтись без применения циклов, даже там, где они совершенно не нужны, не знают многочисленных свойств, например об’екта Range, которые могут весьма облегчить жизнь и т.д.

    На всякий случай :
    Byte — любое целое число в дипазоне от 0 до 255
    Long — любое число со знаком в диапазоне от -2 147 483 648 до 2 147 483 647

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

    8. В том виде, что Вы написали нет, по всей видимости речь идёт о создании об’ектных переменных с использованием инструкции Set (см. мой первый пост)

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

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

    Ускорение работы макроса

    Номер ответа: 2
    Автор ответа:
    Pavel

    Если честно, то не очень понятно. При чём тут макросы, если у вас Excel подвисает, когда вы открываете книгу со связамис другими файлами. Удалите все связи и книга будет быстро открываться.

    Если ли же вы запускаете макрос и он долго обрабатывает информацию (долго работает), то
    1) по возможности откажитесь от метода Select и Activate в вашем макросе
    2) используйте эту конструкцию в ваших макросах:

    Sub MyMacro()
    ‘выключаем некорые параметры для увеличения скорости обработки файла
    With Application
    .ScreenUpdating = False ‘отключение обновление экрана
    .Calculation = xlCalculationManual ‘отключение пересчёт формул вручную
    .EnableEvents = False ‘отключение событий
    .DisplayAlerts = False ‘отключение предупреждающих сообщений
    .ErrorCheckingOptions.BackgroundChecking = False ‘отключение фотоновой проверки ошибок
    End With

    With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
    .DisplayAlerts = True
    .ErrorCheckingOptions.BackgroundChecking = True
    End With
    End Sub

    Номер ответа: 3
    Автор ответа:
    mc-black

    Проверка формул на ошибки у меня и так отключена, а о каких предупреждающих сообщениях идёт речь, что-то непонятно. Можно пояснить. Я бы не стал отключать эти сообщения в случае, если есть риск возникновения циклических ссылок.

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

    Номер ответа: 4
    Автор ответа:
    Pavel

    ну, это я по максимому привёл пример. Я тоже использую указанные тобой 2 строки.
    А отключение предупрежд. сообщений я использую только так. Как пример

    EnableEvents отключаю, чтобы отключить какой-нибудь свой же обработчик, типа

  • Ссылка на основную публикацию
    Adblock
    detector