Doevents vba excel как использовать
С помощью События VBA, мы можем заставить код работать в фоновом режиме и одновременно позволять нам работать с Excel и другим прикладным программным обеспечением. DoEvents не только позволяет нам работать с другим программным обеспечением, мы также можем прерывать выполнение кода.
Функция DoEvents передает управление операционной системе компьютера, на котором мы работаем.
Как использовать функцию DoEvents?
Большое количество Код VBA Код VBA Код VBA относится к набору инструкций, написанных пользователем на языке программирования приложений Visual Basic в редакторе Visual Basic (VBE) для выполнения определенной задачи. читать далее требуется, когда потребность огромна. В этих случаях Excel зависает и останавливается на некоторое время, а иногда даже перестает отвечать на запросы.
Например, посмотрите на приведенный ниже код.
Код:
Более того, мы не можем получить доступ к рабочему листу, над которым работаем. Это разочаровывает, но как сделать так, чтобы рабочий лист Excel был доступен для работы, пока код выполняется за экраном.
Это может быть достигнуто путем добавления VBA Функция DoEvents.
Код:
В тот момент, когда мы добавляем функцию DoEvents в код, мы можем получить доступ к рабочему листу Excel.
Из приведенного выше видно, что код работает, но мы можем получить доступ к рабочему листу.
Прервать выполнение кода
Когда код выполняется за экраном, мы можем добавлять строки, столбцы и удалять их, мы можем переименовывать лист, а также делать много других вещей. В тот момент, когда мы добавляем DoEvents, это ускоряет работу кода VBA и позволяет нам понять, что упомянутая задача выполняется сама по себе.
- Одна из опасностей функции DoEvents заключается в том, что когда мы переключаем рабочие листы или рабочие книги, она перезаписывает активные значения листа.
- Другая опасность заключается в том, что если мы введем какое-либо значение в ячейку, выполнение кода остановится, и он даже не уведомит нас.
Note: The function, method, object, or property described in this topic is disabled if the Microsoft Jet Expression Service is running in sandbox mode, which prevents the evaluation of potentially unsafe expressions. For more information on sandbox mode, search for "sandbox mode" in Help.
Yields execution so that the operating system can process other events.
DoEvents ( )
The DoEvents function returns an Integer representing the number of open forms in stand-alone versions of Microsoft Visual Basic, such as Visual Basic, Professional Edition. DoEvents returns zero in all other applications.
DoEvents passes control to the operating system. Control is returned after the operating system has finished processing the events in its queue and all keys in the SendKeys queue have been sent.
DoEvents is most useful for simple things like allowing a user to cancel a process after it has started, for example a search for a file. For long-running processes, yielding to the processor is better accomplished by using a Timer or delegating the task to an ActiveX EXE component. In the latter case, the task can continue completely independent of your application, and the operating system takes care of multitasking and time slicing.
Any time you temporarily yield to the processor within an event procedure, make sure the procedure is not executed again from a different part of your code before the first call returns; this could cause unpredictable results. In addition, do not use DoEvents if other applications could possibly interact with your procedure in unforeseen ways during the time you have yielded control.
Example
Note: Examples that follow demonstrate the use of this function in a Visual Basic for Applications (VBA) module. For more information about working with VBA, select Developer Reference in the drop-down list next to Search and enter one or more terms in the search box.
This example uses the DoEvents function to cause execution to yield to the operating system once every 1000 iterations of the loop. DoEvents returns the number of open Visual Basic forms, but only when the host application is Visual Basic.
Примечание: Функция, метод, объект или свойство, описанные в данном разделе, отключаются, если служба обработки выражений Microsoft Jet выполняется в режиме песочницы, который не позволяет рассчитывать потенциально небезопасные выражения. Для получения дополнительных сведений выполните в справке поиск по словам "режим песочницы".
Функция передает управление операционной системе для обработки других событий.
DoEvents ( )
Функция DoEvents возвращает целое число, представляющее количество открытых форм в автономных версиях Microsoft Visual Basic, таких как Visual Basic Professional Edition. Функция DoEvents возвращает нуль для всех других приложений.
DoEvents передает управление операционной системе. Управление возвращается по завершении обработки очереди событий операционной системой и отправки всех значений из очереди SendKeys.
Функция DoEvents наиболее удобна при выполнении простых задач, таких как предоставление пользователю возможности прервать запущенный процесс (например, поиск файла). При обработке продолжительных процессов рекомендуется для передачи управления использовать событие "Таймер" или делегирование задачи компоненту ActiveX EXE. В последнем случае выполнение задачи может продолжаться совершенно независимо от приложения, а многозадачность и управление временем процессора осуществляется операционной системой.
Каждый раз, когда вы временно возвращаетесь к процессору в процедуре обработки события, убедитесь, процедура не выполняется повторно из другой части кода до первого звонка; это может привести к непредсказуемым результатам. Кроме того, не используйте doEvents, если другие приложения могли взаимодействовать с вашей процедурой непредвиденным образом в период, когда вы укатиле управление.
Пример
Примечание: В примерах ниже показано, как использовать эту функцию в модуле Visual Basic для приложений (VBA). Чтобы получить дополнительные сведения о работе с VBA, выберите Справочник разработчика в раскрывающемся списке рядом с полем Поиск и введите одно или несколько слов в поле поиска.
В этом примере функция DoEvents используется для передачи управления операционной системе через каждую тысячу итераций цикла. Функция DoEvents возвращает количество открытых форм Visual Basic при условии, что ведущим приложением является Visual Basic.
Функция DoEvents. Анализ ее влияния на быстродействие.
Организация прерывания цикла.
Цикл ожидания действий пользователя.
Для того, чтобы твое приложение, имеющее длинные циклы, не мешало пользователю на время их выполнения заняться другой работой, временно передвинув или даже свернув твою красивую форму, в нашем могучем арсенале имеется невостребованная пока функция DoEvents, синтаксис которой приятно ласкает взгляд:
DoEvents()
и никаких тебе аргументов.
Эта функция, согласно мануалу, возвращает значение типа Integer, представляющее собой количество открытых форм, если фокус находится на форме Visual Basic и ноль, если фокус находится на окнах других приложений. Ну это кому что интересно. Главное это то, что эта функция передает управление операционной системе, то бишь Windows, для обработки других событий. И после обработки Windows'ом всех событий из очереди и передачи всех нажатий клавиш из очереди функции SendKeys возвращает управление обратно в Visual Basic.
Хотя в использовании этой функции нет особенных трудностей, учитывая специфику сайта, лучше один раз пощупать функцию в маленькой процедурке, чем искать ошибку в большом проекте.
Создадим Exe-проектик, положим на форму кнопку Command1 и текстбокс Text1и напишем маленькую процедурку:
Private Sub Command1_Click()
Dim x As Long
For x = 1 To 300000
Text1 = x
Next x
End Sub
Private Sub Command1_Click()
Dim x As Long
For x = 1 To 300000
Text1 = x
DoEvents
Next x
End Sub
Конечно мигание цифр не радует глаз. Кроме того, при передаче управления Windows выполнение программы Visual Basic (в нашем случае цикла) приостанавливается, но зато все наши проблемы мгновенно решились: и форму можно двигать и форма обновляется.
В связи с тем, что повторный вызов функции DoEvents в момент, когда функция еще не передала управление из операционной системы назад в Visual Basic может вызвать непредвиденные последствия, нежелательно вызывать процедуру DoEvents из других процедур программы. Видимо эти же соображения имеют место, когда предполагается, что нежелательно использовать функцию DoEvents в процедурах обработки событий Click. Но мне не удалось добиться никаких особенных фатальных результатов, кроме перезапуска цикла, даже беспрестанно кликая по кнопке Command1.
Основным недостатком этой функции является очень значительное уменьшение быстродействия в длинных по времени циклах (в коротких ее вообще нет смысла использовать). Самое простое решение этого вопроса - запуск DoEvents не при каждой итерации, а например, при каждом сотом (или тысячном) проходе цикла, в зависимости от его параметров. Решить это можно, например, вводя дополнительную переменную-счетчик (в нашем примере y). С каждым проходом цикла счетчик увеличивается на единицу, а при достижении им значения, например 1000, вызывает функцию DoEvents и обнуляется, чтобы при дальнейшей работе цикла снова увеличится до 1000. Тогда наша процедура будет выглядеть следующим образом:
Private Sub Command1_Click()
Dim x As Long
Dim y As Long
For x = 1 To 300000
Text1 = x
y = y + 1
If y = 1000 Then 'проверяем счетчик итераций
'и если он совпадает с заданым числом, запускаем DoEvents и обнуляем счетчик
DoEvents
y = 0
End If
Next x
End Sub
Если тебя уже раздражает такое "ламерское" (но на мой взгляд удобное и наглядное решение), можно отследить тысячный проход цикла по-другому, например деля значения счетчика цикла x на эту тысячу с остатком (Mod) при каждой итерации, тогда при остатке ноль можно считать, что значение х кратно нашей тысяче. Тогда наш код будет выглядеть так:
Private Sub Command1_Click()
Dim x As Long
Dim y As Long
For x = 1 To 300000
Text1 = x
y = y + 1
If x Mod 1000 = 0 Then DoEvents 'получаем остаток от деления
'и если он равен нулю, запускаем DoEvents
Next x
End Sub
А может быть снижение быстродействия не так уж и значительно и им можно пренебречь? Чтобы нам не принимать все на веру, мы сами проведем соответствующее сравнительное исследование зависимости быстродействия от частоты срабатывания функции DoEvents. Для этого нам на форме дополнительно понадобятся Timer1, и еще два текстбокса Text2 и Text3. В уже имеющийся Text1 мы будем вводить интервал цикла (по умолчанию он у нас будет 300000), в Text2 - на какой итерации (1-ой, т.е. каждой, 100-ой, 10000-й и т.д ) будет включаться функция DoEvents. В Text3 выведем результат измерений в миллисекундах.
Объявим три переменных:
Option Explicit
Dim Iter As Long 'итерация срабатывания DoEvents
Dim RunTime As Integer 'время работы цикла
Dim RunLong As Long 'длина цикла
В процедуре Form_Load присвоим начальные значения цикла и итераций:
Private Sub Form_Load()
Text2 = 1
Text1 = 1000000
End Sub
Код процедуры Command1_Click Изменим следующим образом:
Private Sub Command1_Click()
Dim x As Long
Dim y As Long
Iter = Val(Text2) 'Присваиваем переменным значения для цикла
RunLong = Val(Text1)
RunTime = 0
Text3 = "Цикл в работе"
Timer1.Interval = 1 'устанавливаем интервал таймера в 1 мск
Timer1.Enabled = True 'запускаем таймер
For x = 1 To RunLong
'обратите внимание на то, что вывод значений переменной цикла X не выводится в Text1,
'так как это непомерные затраты времени и исказят наши значения.
'Text1 = x
y = y + 1
If y = Iter Then
DoEvents
y = 0
End If
Next x
Timer1.Enabled = False 'выключаем таймер
Text3 = RunTime 'выводим значение счетчика таймера в текстбокс
End Sub
'В процедуре таймера мы просто имеем счетчик, который увеличивается
' на единицу при каждом включении, т. е. каждую миллисекунду.
Используя эту функцию мы можем позволить пользователю прервать продолжительный процесс. Для этого достаточно ввести булеву переменную для флага, например Flag , которая и будет сигнализировать о том, что процесс необходимо прервать.
Добавим на форму кнопку Command2, которой и будем прерывать выполнение цикла. В секцию (General) добавим объявление флага
Dim Flag As Boolean
В процедуре Command1_Click, прямо в самом начале установим начальное для цикла значения флага:
Flag = False
а в самом цикле поставим условие на выход
If Flag = True Then Exit Sub
Теперь, для выхода из цикла надо просто изменить значение флага False на True. Это мы и сделаем в процедуре Command2_Click:
Private Sub Command2_Click()
Flag = True
Text3 = "Цикл прерван"
End Sub
С помощью функции DoEvents можно также организовать цикл ожидания, прерываемый пользователем. В качестве примера сделаем программку-шутку, которая иммитирует предупреждение пользователю о начале форматирования жесткого диска. Для этого в новом Exe-проекте на форме нам понадобится Label1, Command1 и Timer1.
Объявим пару переменных:
Option Explicit
Dim RunTime As Integer 'время в секундах
Dim Flag As Boolean 'флаг, сигнализирующей о прерывании цикла
Для процедуры, собственно, цикла ожидания я выбрал событие формы _Activate, так как оно возникает, когда форма уже загружена и инициализирована и элементы на ней видимы.
Private Sub Form_Activate()
RunTime = 10 'устанавливаем начальные значения секунд
Timer1.Interval = 1000 'интервал срабатывания таймера 1 сек
Timer1.Enabled = True ' включаем таймер
'организуем цикл, работающий до тех пор, пока переменная RunTime
'не стане равной нулю (изменяется таймером)
Do Until RunTime = 0
DoEvents 'постоянно передаем управление Windows
If Flag Then 'проверяется, нажата ли кнопка Command1 для
'прерывания цикла (Flag=True) или нет (Flag=False)
Label1 = "Молодец, успел, но все равно форматирование начинается!"
Exit Sub 'выход из процедуры, если кнопка Command1 нажималась
End If 'конец условия
Loop 'конец цикла
Timer1.Enabled = False 'выключение таймера
Label1 = "Не успел, не успел, надеюсь на винчестере ничего ценного нет?"
End Sub
Процедура Command1_Click() управляет флагом и выключает таймер
VBA DoEvents yields execution of your macro, so your computer processor will be able to simultaneously run other tasks and recognize other events. The VBA DoEvents function also enables interruption of code execution so it’s easier to stop a running macro.
Only few things in life are more frustrating than trying the halt the execution of a program and not being able to because Excel has become unresponsive. I’m sure you’ve been there. We’ve all made a mistake in a code which causes an infinite loop or something Your program will, in theory, never terminate and Excel will remain unresponsive until you shut down your Excel application! So frustrating.
In the first part of this tutorial, we’ll show you how to circumvent this and other similar problems by using the VBA DoEvents function. However, as the saying goes, all good things come with a price. The DoEvents function in VBA is no exception since it will cause the execution time of your program to increase.
This is logical, since, by yielding execution to other events and processes, Excel temporarily halts the code execution while the operating system handles these other events or processes.
The second part of this introduction is dedicated to exploring how the DoEvents function affects performance. We’ll present some performance test results with important lessons attached to them and then we’ll show you how to balance the yielding of code execution with performance considerations.
Finally, we’ll discuss the generic applications of the DoEvents function in Excel and when not to use it in your VBA macros.
With this in mind, let’s present a very basic and nifty example of VBA DoEvents usage!
VBA DoEvents Example
Insert the code below into a standard code module:
Make powerful macros with our free VBA Developer Kit
It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.
In this simple procedure we use VBA to repeatedly update the value of the Application.Statusbar in the lower left corner of the Excel window inside a loop. In practical situations, if a program containing a loop takes a long time to complete it’s really helpful to track its progress that way.
The procedure starts by storing the current value of the .StatusBar in a Variant type variable. This is because you update the .StatusBar with a string, but its default value is the boolean value False . This may sound strange, but otherwise it would be impossible update the the .StatusBar with the string “False” without setting it to its default value, “Ready”!
We also turn the screen updating off by setting .ScreenUpdating to False , just like we did in our Application.ScreenUpdating VBA tutorial. As will become evident in the next section, this is crucial in terms of performance!
Next, the procedure loops from 1 to 5000 and updates the .StatusBar at every iteration. The DoEvents function in the loop enables you to interrupt the code execution. To do this, simply press Esc or Ctrl + Break. Note, however, that not all computer keyboards come with a Break button!
We’ve actually written a good tutorial explaining how to stop an Excel macro. By including VBA DoEvents, it’s easier for the system to not got bogged down so it should be better able to recognize when you’re trying to press the Esc or Ctrl + Break to end your running macro.
When the loop completes, the .StatusBar is set to its original value stored in the AppStatus variable and we also set the .ScreenUpdating property back to True .
Okay, we’ve shown you how to include the DoEvents VBA function in your macro and how it can make it easier for you to cancel your macro. Next, let’s examine the price you pay for this increased user friendliness and control stemming from the use of the DoEvents function.
VBA DoEvents Performance Impact
To test the influence of the DoEvents function on macro performance, we modified the ShowStatusWithDoEvents procedure to loop from 1 to 10 and we wrote a small timing procedure which calls it 10,000 times. Next, we timed the execution of the four different variants of ShowStatusWithDoEvents procedure shown in table 1 below.
Table 1. DoEvents test results ranked by average execution time.
Rank | Iterations | Total execution time(seconds) | Average execution time(seconds) | |
---|---|---|---|---|
Without DoEvents,without screen updating | 1 | 10,000 | 25.34 | 0.002534 |
With DoEvents,without screen updating | 2 | 10,000 | 91.16 | 0.009116 |
With DoEvents,with screen updating | 3 | 10,000 | 1,663.66 | 0.166366 |
Without DoEvents,with screen updating | 4 | 10,000 | 2,658.63 | 0.265864 |
It should come as no surprise that the fastest execution time (25.34s) is obtained by removing DoEvents and setting .ScreenUpdating to False in the ShowStatusWithDoEvents procedure.
The second row in the table shows that calling the DoEvents function at every iteration increases the execution time by a factor of 3.6 (91.16s / 25.34s). In other words, the performance overhead incurred by using DoEvents that way is quite substantial!
Even more conspicuous is the steep increase in execution time we observe from row 2 to row 3 (1,663.66s). This is caused by not setting the .ScreenUpdating property to False in the procedure. This result suggests that forgetting to do so may increase the execution time of your program by a factor of more than 18! The main takeaway from this is that, unless you have specific reasons not to do so, you should always set the .ScreenUpdating to False while executing your programs.
The bottom feeder in our tests is row 4 (2,658.62s), where the .ScreenUpdating property was set to True and DoEvents was removed. It is quite surprising that including DoEvents at every iteration in the loop, cf. row 3, decreases the execution time of the procedure compared to when DoEvents is removed from the procedure!
The Microsoft Developer Network documentation on the Application.ScreenUpdating property states: “Turn screen updating off to speed up your macro code. You won’t be able to see what the macro is doing, but it will run faster. “ However, it’s not entirely accurate to say that you can’t always see what the macro is doing while it’s being executed when the screen updating is turned off. When you update the status bar, you can in fact see what your macro is doing, even if you haven’t included the DoEvents function in your code. That is, unless your screen freezes up. This suggests that .ScreenUpdating only updates the worksheet area (i.e. the cells), rather than the entire Excel window, including the status bar. This may in turn explain the surprising result in row 4 of the table. If the repeated requests from the loop to update the screen and the status bar are conflicting with one another, this might indeed cause the procedure to run slower without the DoEvents function!
Of course this is all just a theory. I’d love to hear if you all get similar results in your tests!
The obvious solution to decreased performance caused by the DoEvents function is to only call it intermittently in the loop. This can easily be achieved by using the VBA Mod operator like this:
The line of code above tells Excel only to call DoEvents at every tenth iteration. This decreases the total execution time of the macro tested in row 2 of the table from 91.16s to only 29.67s and you can still interrupt the code execution. A no-brainer!
Finally, let’s discuss the generic applications of the VBA DoEvents function and, just as importantly, when not to use it!
Application ideas using VBA DoEvents
Broadly speaking, there are two main reasons for using the DoEvents function:
- Debugging purposes.
- Let say you’re developing a procedure which loops through thousands of rows while performing expensive operations. Unless you include DoEvents in your loop, it means you simply must wait until all the operations specified in your code have been carried out, even if your code isn’t working as expected. This is particularly annoying if you mistakenly wrote an infinite loop because you forgot to specify a termination clause.
- The users of your Excel VBA programs may find it annoying to watch a blank screen while your procedure is executing. For psychological reasons, you may want use something like a status bar or VBA progress bar to track the progress of your procedure while it’s running. Visual tools like these are nice distractions for an end user.
The DoEvents function should not always be used in your procedures, though. For most “lightweight” procedures with short execution time, this function is entirely unnecessary. Moreover, if performance is key and your code has been thoroughly tested, you may want to leave DoEvents out of your code, even if the execution time is substantial. In that case, the benefits described above may be outweighed by the price of using it.
The choice is yours!
Comments
If you haven’t already done so, please subscribe to my free wellsrPRO VBA Training Program. What are you waiting for? You’ll love the great VBA content I send your way!
Share this article on Twitter and Facebook, then leave a comment below and let’s have a discussion.
Ready to do more with VBA?
We put together a giant PDF with over 300 pre-built macros and we want you to have it for free. Enter your email address below and we'll send you a copy along with our VBA Developer Kit, loaded with VBA tips, tricks and shortcuts.Before we go, I want to let you know we designed a suite of VBA Cheat Sheets to make it easier for you to write better macros. We included over 200 tips and 140 macro examples so they have everything you need to know to become a better VBA programmer.
This article was written by Michael H. Sorensen, a contributing writer for The VBA Tutorials Blog.
Читайте также: