Top-office11.ru

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

Excel vba wait

VBA Application.Wait to Pause for Specific Time

The VBA Tutorials Blog

Introduction — VBA Application.Wait

Use the VBA Application.Wait method to pause your macro for a specific amount of time. Application.Wait is a simple way to delay your macro by a fixed number of seconds.

This isn’t the first tutorial I’ve posted about how to pause your macro. A few months ago I told you how to use VBA Sleep to add a time delay to your macro.

While VBA Sleep is still my favorite method for pausing a macro, it’s a bit more complicated than Application.Wait. When you know you’ll be pausing for more than 1 second, the Application.Wait method is a great way to do it because it’s easy to remember!

Stick with me and I’ll show you how it works.

Example — VBA Application.Wait

Pausing your macro for a certain number of seconds

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 full of pre-built macros so you can master file I/O, arrays, strings and more — grab a copy below.

Tutorial — VBA Application.Wait

Big Picture

Do you remember when I said Application.Wait was easy to remember? Well, I wasn’t kidding. There’s really not much left for me to explain!

The VBA Application.Wait method only accepts one argument: time. To wait 5 seconds, like I did in the demo above, you feed Application.Wait the current time with the Now function. Then, you use the TimeValue function to add 5 seconds to the current time. Once the 5 seconds elapses, your macro will continue. In the example demo, that means you’ll get a MsgBox telling you it’s been 5 seconds.

TimeValue argument

The syntax of the TimeValue function is the most difficult piece to remember. The TimeValue function converts a string to a time. Well, technically it returns the serial number equivalent of the time, but that’s beside the point. All you need to know is that, for the purposes of delaying your macro, your string should be entered in a format like hh:mm:ss, where hh represents hours, mm represents minutes, and ss represents seconds.

Because the TimeValue function accepts arguments in the “hh:mm:ss” format, you’re not able to use it to pause your macro for less than 1 second. You would need to use the VBA Sleep function if you want pauses for fractions of a second.

The important thing to remember is that, if you want to delay your macro by a specific amount of time, you must add the results of the TimeValue function to the current time calculated using the Now function, like I did in the example macro. Otherwise, your macro could be paused until the middle of the night!

Don’t worry, if you accidentally use the wrong syntax, you can stop your macro with the Esc key or the Break key.

Application Ideas — VBA Application.Wait

To inspire you, here are a few examples where I used the VBA Application.Wait method on the VBA Tutorials Blog:

These are actually some of my most popular posts. Are they the most popular because I used Application.Wait? Probably not, but who knows! Maybe when you use Application.Wait, you’ll be popular, too! 😉

How do you plan on using the VBA Application.Wait method? It really can be quite helpful!

When you’re done, please share this article with your friends on Facebook and Twitter. Sharing articles with others is how wellsr.com will continue to grow, so I genuinely appreciate it.

When you’re ready to free up your time in the office, subscribe using the form below.

Oh, and if you have a question, post it in our VBA Q&A community.

The best free VBA training on the web
I see people struggling with Excel every day and I want to help. That’s why I’m giving away my 90-days to Master VBA eCourse and my entire personal macro library for free.

Over 2 million people use our VBA tutorials each year to help automate their work. Are you ready to reclaim your time, too? Grab our VBA Cheat Sheets and you’ll be writing macros like a professional. With over 180 tips and 135 macro examples, they include everything you need to know to become a great VBA programmer.

VBA Wait and Sleep Functions – Explained

Wait and Sleep functionality in programs is required to hold or pause the program execution for a specified time. These methods suspend all the activities of Microsoft Excel and sometimes may also prevent you from performing other operations on it until the pause is in effect.

Читать еще:  Vba excel цвет текста

However your computer can simultaneously process other background tasks like printing or thread ordering.

Significance of Wait and Sleep Functions in VBA:

These functions can be quite useful when you need to hold the program to wait for some other process or task (not directly connected or communicable to the VBA engine) that is yet to be completed.

In such cases you can find out the maximum time required for completing such a task and then in your code you hold the execution for that amount of time.

For example: We have a VBA code that can run some other executable (.exe) and after running that executable you need to wait for that executable to complete and then continue the code. So, in such a case we know that VBA cannot directly communicate with the executable file.

Hence we can code in such a way that first we start that executable then let the code to wait for 10 seconds (max time required for this executable to run) and then continue the execution again.

VBA Wait Function in Excel:

Wait is a VBA function only available in Excel. Its syntax is as follows:

Here ‘Time’ specifies the time at which you want the macro to resume again. ‘Time’ should always be in Microsoft excel time format.

Examples of Wait Function in VBA:

Example 1: Pausing a code till 2.00 PM today.

Example 2: Pausing an application for 10 seconds.

Example 3: Using VBA Wait function to create a program that tells time after every minute (till 10 loops).

Now let’s move to Sleep Function.

Sleep Function:

Sleep is a windows function and not a VBA Function, but you can still use this function in VBA code by calling the windows Sleep API. Actually sleep is a function present inside Windows DLL files. So, before using them you have to declare the name of API above the code in your module.

The syntax of Sleep statement is as follows:

Here, ‘delay’ specifies the time in milliseconds till which you have to pause the execution.

Examples of Sleep Function in VBA:

Example 1: Pausing an application for 10 seconds

Example 2: Halting the code for a user defined delay by using an InputBox function.

Difference between VBA Wait and Sleep Function:

The job of both of these functions is same but sleep function is not as accurate as Wait. Sleep statement depends on processor’s ticks to calculate the time delays which many vary slightly on different machines. But this is not the case with Wait Function.

The advantage of Sleep statement over Wait is that it is quite flexible as you can give the time delays in milliseconds. While in Wait function you can only delay the application by whole seconds.

So, this was all about VBA Wait and Sleep Functions. Do let us know in case you have any queries related to the topic.

Ankit is the founder of Excel Trick. He is tech Geek who loves to sit in front of his square headed girlfriend (his PC) all day long. :D. Ankit has a strong passion for learning Microsoft Excel. His only aim is to turn you guys into ‘Excel Geeks’.

Comments

Very nice tip although it needs at least one clarification. Your statement “Wait function is VBA system defined function” is technically incorrect. The Excel.Application.Wait function is available in the Excel application alone. It is not a function within the VBA application and thereby not available in Access, Outlook, etc. unless the Excel.Application is instantiated in one of the other Office applications.

Yes Bob, you are right. Have corrected it now.
Thanks for pointing it out.

this only works on 32 bit systems.

on 64 bit systems, VBA generates the following Compile error:

The code in the project must be updated for use on 64-bit systems. Please review and update Declare statements and then mark them wit the PtrSafe attribute.

Another advantage of sleep over wait is that sleep does not require CPU cycles while Wait does.

Читать еще:  Как сделать формулу в excel сумма

Hi Bobf,
A big thanks for sharing your precious suggestion.
I have updated the post now.

VBA Wait

VBA Wait Function

Similar to sleep function Wait function in VBA makes a code to wait or pause for a few seconds. How such functions are used that we need to make some specific code to be on pause or hold to let some other program work first and then resume the other program. It is also called as Application.Wait method in VBA.

As the name suggests VBA Wait function in VBA is used to hold a program to a specific time so that other code can work during that time. The background applications keep running and only the current module waits.

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

Syntax for Wait Function in Excel VBA

The syntax to use Application.Wait function in VBA is as follows:

This function returns a Boolean value.

So if we need a code to wait till 2:00 PM today the syntax for the code will be as follows:

Application.Wait (“14:00:00”)

The above code will make the code to stop until 2:00 PM and the code will resume only after 2:00 PM. This is not optimal to make a code wait for 2 PM, in general, we want to wait for a certain code for another 10 seconds and then run. If I need to make code to wait for 10 seconds the code will be as follows:

Application.Wait (Now + TimeValue (“0:00:10”))

Now is the function which will take the current time and we are adding 10 seconds to the current time through time value function so that the code can wait or pause for 10 seconds.

How to Use Excel VBA Wait Function?

We will learn how to use a VBA Wait Excel function with few examples.

VBA Wait Function – Example #1

Currently, the time in my laptop is 3:50 pm and I want the code to resume at 4:00 PM. We will use simple addition to some variables but display the result after 4:00 PM. We want the code to wait for that period.

Step 1: Go to the Developer tab and click on Visual Basic to open VB Editor.

Step 2: Click on Insert Tab and then click on Module.

Step 3: Once the code window opens up declare a sub-function to start writing the code.

Code:

Step 4: Declare three variables A B and C as an integer.

Code:

Step 5: Assign random values to A and B variable.

Code:

Step 6: Use an application.wait function to make the function wait until 4:00 PM.

Code:

Step 7: Use the addition of A and B and store the output in C.

Code:

Step 8: Use a Msgbox function to display the value of C.

Code:

Step 9: Run the above code. Once we run the code we see that code does not display a message until 4:00 pm but after 4:00 pm I get the following output.

I had to wait until 4:00 pm to see this result. The code of the Msgbox function had to wait actually.

VBA Wait Function – Example #2

Let us see the code with some personalized messages that the code will wait for 10 seconds and after 10 seconds have passed it will display that wait time has expired and displayed the final result.

Step 1: Go to the Developer tab and click on Visual Basic to open VB Editor.

Step 2: Declare a sub-function to start writing the code.

Code:

Step 3: Declare three variables A B and C as an integer.

Code:

Step 4: Assign Random values to A and B variables.

Code:

Step 5: Use a Msgbox function to display that wait time is about to start.

Code:

Step 6: Use Wait function to wait for the code for 10 Seconds.

Code:

Step 7: In Variable C it stores the value of B/A and displays a personalized message that wait period is over and then display the value of C.

Code:

Step 8: We run the code and see the first message like the following.

Step 9: Press OK to see the next result after 10 seconds have passed.

Step 10: When we press ok we get the final result.

In the above example, we made the code to wait for 10 seconds and then display the result of the value of C.

Читать еще:  Как установить дату в excel

VBA Wait Function – Example #3

Now let us rename worksheets sheet 1 and sheet 2 as Anand and Aran Respectively but we want a gap of 5 seconds between both sheet being renamed.

Step 1: Go to the Developer tab and click on Visual Basic to open VB Editor.

Step 2: Declare a sub-function to start writing the code.

Code:

Step 3: Activate the first worksheet.

Code:

Step 4: Rename the first worksheet as Anand.

Code:

Step 5: Display a message that sheet 1 has been renamed and the code will pause for five seconds.

Code:

Step 6: Use Wait function to make the code wait for five seconds.

Code:

Step 7: Now rename the second sheet 2 as Aran.

Code:

Step 9: Display a message that sheet 2 has been renamed and the wait has been passed.

Code:

Step 10: Now run the code to see the result.

We can see that sheet 1 has been renamed as Anand and we get the message to wait for five seconds. Click on OK and wait for five seconds for the second message.

From the above examples, it is clear on how to use the Wait Function in VBA.

Things to Remember

There are a few things we need to remember about Excel VBA Wait Function:

  • It is similar to Sleep function.
  • The wait function is inbuilt in VBA unlike sleep function which is a windows function.
  • Wait function takes Time as an argument.

Recommended Articles

This has been a guide to VBA Wait Function. Here we discussed how to use Excel VBA Wait Function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

All in One Software Development Bundle (600+ Courses, 50+ projects)

Excel vba wait

На этом шаге мы рассмотрим основные методы этого объекта .

Перечислим наиболее часто используемые методы объекта Application .

Таблица 1. Методы объекта Application

МетодыВыполняемые действия
CalculateВызывает принудительное вычисление во всех открытых рабочих книгах. Например:

RunЗапускает на выполнение подпрограмму или макрос. Синтаксис:

  • Macro — строка с именем макроса;
  • Arg1, Arg2, . — аргументы передаваемые макросу.

Например: Application.Run Macro:= «Расчет» — запускает макрос Расчет

VolatileВызывает перевычисление функции пользователя при изменении значений параметров. Например, функция Квадрат будет автоматически пересчитывать результат на рабочем листе при изменении значения аргумента:
WaitВременно приостанавливает работу приложения без остановки работы других программ. Синтаксис:
OnKeyУстанавливает выполнение специфицированной процедуры при нажатии заданной комбинации клавиш. Синтаксис:

  • Procedure — имя выполняемой подпрограммы при нажатии клавиш;
  • Key — строка, определяющая комбинацию клавиш, которая должна быть нажата. В этой строке можно также указывать специальные клавиши, используя следующие коды:
    • Backspace — или ;
    • Caps Lock — ;
    • Delete или Del — или ;
    • клавиша стрелка вниз — ;
    • End — ;
    • Enter (цифровая клавиатура) — ;
    • ESC — или ;
    • Home — ;
    • Ins или Insert — ;
    • клавиша стрелка влево — ;
    • Num Lock — ;
    • Page Down — ;
    • Page Up — ;
    • Return — ;
    • клавиша стрелка вправо — ;
    • Scroll Lock — ;
    • Tab — ;
    • клавиша стрелка вверх — ;
    • от F1 до F15 — от до .

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

  • Shift — +;
  • Ctrl — ^;
  • Alt — %.

В примере процедуре Амортизация назначена комбинация клавиш «Ctrl»+»+» , а процедуре ПроцентнаяСтавка — Shift+Ctrl+» стрелка вправо » :

OnRepeat и OnUndoОпределяет процедуру, выполняемую при выборе команды Правка | Повторить (Edit | Repeat) и Правка | Отменить (Edit | Undo) соответственно. Синтаксис:

  • Text — строка, задающая текст команды Правка | Повторить (Edit | Repeat) ;
  • Procedure — имя подпрограммы, выполняемой при выборе команды Правка | Повторить (Edit | Repeat) .
OnTimeНазначает выполнение процедуры на определенное время. Синтаксис:

  • EarliestTime — момент запуска процедуры;
  • Procedure — имя процедуры;
  • LatestTime — если на момент запуска процедуры, Excel не может ее запустить в силу того, что выполняется другое действие, то параметр LatestTime определяет последнее время ее запуска. Если этот аргумент опущен, то Excel будет ждать до тех пор, пока не сможет выполнить эту процедуру;
  • Schedule — допустимые значения: True (выполнение процедуры откладывается на сутки) и False (во всех остальных случаях).

В следующем примере демонстрируется, как запустить процедуру Очистка на выполнение через 15 секунд от текущего времени:

QuitЗакрывает приложение. Например:

На следующем шаге мы рассмотрим события этого объекта .

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