Макрос требует больше времени для выполнения при активации через кнопку внутри листа в Excel 2019

Question

У меня есть макрос в Excel 2019, который запускается менее чем за 1 секунду непосредственно через VBE (нажатием клавиши F5) или когда я настраиваю кнопку для макроса на ленте (через параметры> настроить ленту).

Однако, когда я создаю кнопку (FormControlButton) внутри области листа и связываю этот макрос с этой кнопкой, запуск макроса занимает не менее 7 секунд.

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

Если эта информация помогает, мой макрос создает зубчатый массив с данными (~4000 записей), находящимися на другом листе, а затем сортирует массив по пузырьковой / быстрой сортировке (проверял оба, чтобы проверить, могла ли проблема быть здесь, и не было), затем фильтрует его и возвращает данные на новом листе.

Макросы были разработаны в Excel 2010, и я заметил проблему сразу после того, как наша компания обновила Microsoft Office с 2010 по 2019 год. (Наши окна были обновлены в тот же день с 2007 по 10, но я думаю, что проблема в Excel, как я тестировал это опять же на некоторых наших ПК, на которых еще был офис 2010, и макросы работали так же быстро, как если бы они работали через VBE) Создание и редактирование макросов не запрещено администраторами.

У кого-нибудь есть представление о том, что может происходить?

Добавление дополнительной информации по запросу:

Я не добавил код, потому что это не проблема конкретного макроса, но я заметил, что те, которые больше всего тормозят, это те, которые взаимодействуют с массивами. Кроме того, поскольку этого не произошло, когда я использовал кнопки внутри листа в офисе 2010, возможно, это ошибка в офисе 2019 года.

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

Sub SubName()

Call DeactivateSystemFunctions

'Rest of the code

Call ReactivateSystemFunctions

End Sub

где

Sub DeactivateSystemFunctions()

Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.ActiveSheet.DisplayPageBreaks = False
Application.EnableEvents = False

End Sub


Sub ReactivateSystemFunctions()

Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationAutomatic
Application.ActiveSheet.DisplayPageBreaks = True
Application.EnableEvents = True

End Sub

Я не использую.activate или.select ни в одном из моих макросов, и во время форматирования я всегда стараюсь поместить максимум в With/End With

заранее спасибо

Answer 1

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

Мой макрос работал нормально через VBE, но занимал слишком много времени при активации через FormControlButton внутри моего листа. Как предложил @RonRosenfeld, мне пришлось установить таймер для каждой конкретной части моего кода, чтобы найти, где была проблема. Я установил таймер в начале своего кода, и мне пришлось переместить команду, чтобы остановить таймер, в каждую его часть, пока я не обнаружил, где он замедляется.

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

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

NewSheet.Cells(NewSheetRow, Column1) = SortedArray(RecordNumber)(DesiredInfo1 - 1)
NewSheet.Cells(NewSheetRow, Column2) = SortedArray(RecordNumber)(DesiredInfo7 - 1)
NewSheet.Cells(NewSheetRow, Column3) = SortedArray(RecordNumber)(DesiredInfo14 - 1)

'As my jagged array is built with data from a Source Worksheet:
'RecordNumber is the (Row - 1) in the source worksheet
'DesiredInfoX is the Column in the source worksheet

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

Я пошел к исходному листу и заметил некоторые проблемы:

• Поскольку файл перешел с excel 2000 на 2010 на 2019 год, и данные не были перенесены, а просто сохранены из.xls в.xlsm, когда я перешел к концу исходного листа, я заметил, что в нем было только 65536 строк (а не 1048576, как ожидалось), но имел 16384 столбца (последний =XFD). Это происходило только с исходным листом, в котором у нас есть больше данных. Другие листы в той же книге имели ожидаемые 1048576 строк и 16384 столбца.

• После того, как мы начали использовать excel 2019, некоторые данные, которые должны были быть String(Text), были отформатированы как GENERAL/NUMBER. Я не могу утверждать, что это была не человеческая ошибка, но наш исходный лист заполнен макросом, а не человеком, и макрос заставляет форматировать все данные.

Что я сделал для решения проблемы: я перенес все данные со всех листов в новую книгу, используя VBA, а не копирование / вставку. После передачи значений в новый исходный лист я принудительно отформатировал каждый столбец. Также пришлось перенести все макросы.

После этого FormControlButton внутри листа работает так же быстро, как активация макроса напрямую через VBE нажатием F5.

Если кому нужно:

'###Timer code
'Got it from https://www.thespreadsheetguru.com/the-code-vault/2015/1/28/vba-calculate-macro-run-time
'Put this part in the beggining of your code
Dim StartTime As Double
Dim SecondsElapsed As Double

'Remember time when macro starts
  StartTime = Timer



'Put this part where you want the timer to stop
'Determine how many seconds code took to run
  SecondsElapsed = Round(Timer - StartTime, 2)

'Notify user in seconds
  MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation


'###Migration macro:
Sub Migrate()

Call DeactivateSystemFunctions

'Source File
Dim XLApp As Object
Dim WbSource As Object
Dim WsSource As Object

Set XLApp = CreateObject("Excel.Application")
XLApp.Visible = False    

Set WbSource = XLApp.Workbooks.Open("C:\FolderFoo\FolderBar\Desktop\SourceFileName.Extension")
Set WsSource = WbSource.Worksheets("SourceWorksheetName")


'Destination File. May be set as source file or if using this workbook by simply:
Dim WsDest As Worksheet    
Set WsDest = ThisWorkbook.Worksheets("DestinationSheetName")


Dim BDR As Long
Dim BDC As Long

Dim UltR As Long
Dim UltC As Long

UltR = WsSource.Cells(Rows.Count, 1).End(xlUp).Row
UltC = WsSource.Cells(1, Columns.Count).End(xlToLeft).Column

For BDR = 1 To UltR

    For BDC = 1 To UltC

        If WsSource.Cells(BDR, BDC) <> vbEmpty Then

            WsDest.Cells(BDR, BDC) = WsSource.Cells(BDR, BDC)

        End If

    Next BDC

Next BDR

'Format your columns as needed    
With WsDest
.Columns(Column1Number).NumberFormat = "0"
.Columns(Column2Number).NumberFormat = "dd/mm/yyyy"
.Columns(Column3Number).NumberFormat = "@"
.Columns(Column4Number).NumberFormat = "@"
.Columns(Column5Number).NumberFormat = "0.000"
End With


WbSource.Close SaveChanges:=False

Call ReactivateSystemFunctions

End Sub

Answer 2

Я собираюсь поделиться с остальными простыми фрагментами кода, чтобы гарантировать более быструю скорость выполнения кода. Есть два шага. Во-первых, вы должны скопировать две подпрограммы ниже и вставить их в верхнюю часть модуля в вашем проекте VBA.

Первая часть кода VBA

Public CalcState As Long
Public EventState As Boolean
Public PageBreakState As Boolean

Sub OptimizeCode_Begin()

Application.ScreenUpdating = False

EventState = Application.EnableEvents
Application.EnableEvents = False

CalcState = Application.Calculation
Application.Calculation = xlCalculationManual

PageBreakState = ActiveSheet.DisplayPageBreaks
ActiveSheet.DisplayPageBreaks = False

End Sub

Вторая часть кода VBA

Sub OptimizeCode_End()

ActiveSheet.DisplayPageBreaks = PageBreakState
Application.Calculation = CalcState
Application.EnableEvents = EventState
Application.ScreenUpdating = True

End Sub

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

Sub MyAwesomeMacro()

Dim x As Long
Dim y As Long
Dim cell As Range

'Optimize Code
  Call OptimizeCode_Begin

'Insert your macro code here...
  x = Range("A1")
  y = Range("A3")

  For Each cell In Range("C2:C2000)")
    cell.Value = (x - cell.Row) / y
  Next cell

'Optimize Code
  Call OptimizeCode_End

End Sub

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

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

1. Обновление экрана Первым макросом, который я когда-либо запускал, был сотрудник, которому потребовалось 30 минут, чтобы закончить выполнение (он сделал много вещей и был очень плохо написан). Я всегда говорил, что она пыталась промыть мне мозги с помощью макроса, потому что в течение 30 минут непрерывно мерцало экран! Мало ли я знал, что одна строка кода спасла бы мои глаза (и разум) от ухудшения и в то же время значительно ускорила бы выполнение кода. В 99,9% случаев вы ВСЕГДА захотите отключить обновление экрана. Единственный сценарий, который я могу придумать, где вы, возможно, захотите, это если ваш код отображает какую-то анимацию на экране.

2. Отображение разрывов страниц У меня есть личная ненависть к визуализации разрывов страниц, но я полагаю, что некоторые из вас могут им понравиться. Одна плохая вещь в разрывах страниц, когда у вас работает код VBA, - они хотят пересчитывать "разрывы" всякий раз, когда вносятся изменения в электронную таблицу. Можно изобразить потребление времени, которое может иметь место, если вы запускаете код VBA, который удаляет или добавляет тысячи строк в электронную таблицу. Поскольку разрывы страниц необходимо постоянно пересчитывать, хорошо соблюдать осторожность и отключать их во время выполнения кода

Теперь разрывы страниц отличаются от других настроек, которые я рассматриваю в этой статье. Разрыв страницы - это настройка уровня рабочего листа, а не настройка уровня приложения. Это означает, что на каждом рабочем листе вашей книги есть "выключатель света". Чтобы сохранить код этой статьи в чистоте, я предположил, что макрос будет влиять только на ActiveSheet. Если ваш код будет изменять несколько листов, вы можете отключить отображение разрывов страниц для всех затронутых листов. Если вы хотите быть сверхэквивалентом, вы можете добавить несколько VBA, которые отключают каждый разрыв страницы, существующий в данный момент во время выполнения вашего кода. Вы можете найти этот код в разделе Code Vault моего сайта.

3. Режимы расчета Если ваш код влияет на ячейки, которые подают формулы ячеек, Excel попытается пересчитать значения в режиме реального времени. Значительная эффективность времени выполнения может быть достигнута, если вы выключите автоматические вычисления во время выполнения кода VBA. Но убедитесь, что вы вернули настройку расчета обратно в автоматический или полуавтоматический режим, иначе вы будете сильно путаться с самим собой или своим пользователем.

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

Заключение Итак, теперь у вас есть очень простой способ заставить ваш код работать намного эффективнее, и вы (надеюсь) понимаете, почему вам нужно временно отключить эти настройки! Дайте мне знать, сколько времени этот метод сокращал время выполнения вашего кода в разделе комментариев ниже. Я хотел бы услышать вашу историю! (Это так же безумно, как мой 30-минутный опыт макросов?)

Answer 3

TL; DR: у меня была такая же проблема, и я думаю, что это ошибка указателя мыши.

Мое решение:

Dim Cursor As XlMousePointer
Cursor = Application.Cursor
Application.Cursor = xlWait

-- YOUR CODE HERE --

Application.Cursor = Cursor

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

Вы можете заметить, что указатель мыши постоянно меняется, когда вы запускаете его с помощью FormButton или ActiveX Button, но когда вы запускаете макрос из VBE, он этого не делает.

В моем случае макрос даже начинал работать быстрее, если я убирал фокус с окна Excel - такая простая вещь, как наведение мыши на кнопку меню "Пуск" Windows или на приложение на панели задач Windows, улучшила производительность макроса.

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

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

С тех пор у меня не было такой проблемы.