How to Use Excel VBA to Wait Until Refresh Complete

Excel VBA programming is editable programming executed when a macro is recorded in Excel. Nowadays people are more into automation and VBA simplifies the repetitive work in real life. Sometimes we need some time between two operations to refresh the worksheet. In this article, we will learn how to execute Excel VBA wait until refresh is complete.

Here is the overview of this article, which shows a practical example to execute this process. You will know better once you go through the total article.

Excel VBA to Wait Until Refresh Complete: Step-by-Step Procedures

The dataset below is the P&L Sheet of Electronics Companies. This P&L sheet contains Company Name, Revenue, Cost, and Profit/Loss. Here, we will get the revenue and cost using the VBA code. And after refreshing the dataset, we will get the Profit/Loss through this code. For more information follow the below steps shown below.

Step-1: Creating Dataset

Initially, we will create a dataset manually, as below to get the revenue, cost, and (Profit/Loss) using VBA code.

Step-2: Applying VBA Code

Once you create the dataset, you need to write VBA code in Excel. After that, apply the following code to get the required result.

• Following the mentioned article, we enter into the VBA module section.
• After that, write down the below on the module.

Follow the below code and breakdown of the code for a better understanding.

Code:

Sub GenerateDataAndCalculateProfit()
' Set the range for the revenue and cost data
Dim revenueRange As Range
Set revenueRange = Range("C5:C15")
Dim costRange As Range
Set costRange = Range("D5:D15")
' Generate random revenue and cost data
Dim i As Long
For i = 1 To revenueRange.Cells.Count
revenueRange.Cells(i).Value = Application.WorksheetFunction. _
RandBetween(1000, 10000)
costRange.Cells(i).Value = Application.WorksheetFunction._
RandBetween(500, 5000)
Next i
' Refresh the workbook and wait for the refresh to complete
MsgBox "Refreshing workbook..."
ActiveWorkbook.RefreshAll
Do While Application.CalculationState <> xlDone
DoEvents
Loop
MsgBox "Refresh complete."
' Calculate the profit
Dim profitRange As Range
Set profitRange = Range("E5:E15")
profitRange.Formula = "=C5-D5"
End Sub

Sub GenerateDataAndCalculateProfit()

Dim revenueRange As Range
Set revenueRange = Range("C5:C15")

Dim costRange As Range
Set costRange = Range("D5:D15"))

Dim i As Long

For i = 1 To revenueRange.Cells.Count
revenueRange.Cells(i).Value = Application.WorksheetFunction. _
RandBetween(1000, 10000)
costRange.Cells(i).Value = Application.WorksheetFunction. _
RandBetween(500, 5000)
Next i

MsgBox "Refreshing workbook..."
ActiveWorkbook.RefreshAll

Do While Application.CalculationState <> xlDone
        DoEvents
Loop
MsgBox "Refresh complete."

Dim profitRange As Range
Set profitRange = Range("E5:E15")
profitRange.Formula = "=C5-D5"
End Sub

Step-3: Creating Button

Now, we will create a button to easily use the code in Excel with a simple click. Follow the below steps to create this button.

• Initially, select Developer >> Insert >> Button (Icon) from the Toolbar.

• Afterward, place the button on the desired location of your dataset and right-click on the mouse to get the Context Menu.
• Select Assign macro from the option bar.

• The Assign Macro dialog box will pop up.
• Select the macro from the dialog box and click OK to assign the macro.

• Now, again right click on the mouse to get to the option bar.
• Select Edit Text from the Context Menu and write down the button name.

After editing the button name you can also change the format for visual satisfaction.

• Right-click on the button and select Format Control from the Context Menu.

• After selecting Format Control a format control dialog box will pop up and edit the format according to your preferences.
• Lastly, click OK to complete this process.

Once you complete, the full process, the VBA code is ready to be executed. Once you check out the below image and see what the button looks like after applying customization.

Step-4: Use Button to See Final Output

• Now, click on the Excel button to run the VBA and see what is going on.

You get the Revenue and Cost from a random number generator after running the code. Then the macro will wait until refresh the operation before calculating the profit.

Excel VBA to Wait for 5 Seconds Before Another Process Starts

Sometimes, you need to refresh and wait for another operation to start for a certain period. Here, we will show you how to apply Excel VBA that waits for 5 seconds before another process starts. You can also customize this code if you want to wait for more or less than 5 seconds.

📌Steps:

• In the beginning, open the module using the process already shown before.
• Now, write down the code on the module and run the code by pressing F5 button.

• Look at the following video to see what is happening after running the code.

Code:

Sub generateRevenueAndCost()
Dim revenueRange As Range
Set revenueRange = Range("C5:C15")
Dim costRange As Range
Set costRange = Range("D5:D15")
Dim cell As Range
For Each cell In revenueRange
Randomize
cell.Value = Int((5000 - 1000 + 1) * Rnd + 1000)
Next cell
For Each cell In costRange
Randomize
cell.Value = Int((5000 - 1000 + 1) * Rnd + 1000)
Next cell
MsgBox "Waiting for 5 seconds..."
Application.Wait (Now + TimeValue("0:00:05"))
Dim profitRange As Range
Set profitRange = Range("E5:E15")
Dim i As Integer
For i = 1 To revenueRange.Count
profitRange.Cells(i).Value = revenueRange.Cells(i).Value - costRange.Cells(i).Value
Next i
End Sub

Sub generateRevenueAndCost()

Dim revenueRange As Range
Set revenueRange = Range("C5:C15")

Dim costRange As Range
Set costRange = Range("D5:D15")

Dim cell As Range
For Each cell In revenueRange
Randomize
cell.Value = Int((5000 - 1000 + 1) * Rnd + 1000)
Next cell

For Each cell In costRange
Randomize
cell.Value = Int((5000 - 1000 + 1) * Rnd + 1000)
Next cell

MsgBox "Waiting for 5 seconds..."
Application.Wait (Now + TimeValue("0:00:05"))

Dim profitRange As Range
Set profitRange = Range("E5:E15")
Dim i As Integer
For i = 1 To revenueRange.Count
profitRange.Cells(i).Value = revenueRange.Cells(i).Value - costRange.Cells(i).Value
Next i

Excel VBA to Pause and Resume Macro While Macro Is Working

While working on a dataset sometimes you need to pause the operation. In this case, we will learn how to pause between two different operations. Here you can set a time limit to start the macro. Follow the below steps for details.

📌 Steps:

• Once you open the Visual Basic window and enter into Module section as the previously shown process.
• Now, insert the VBA code.

• Run the code by pressing the F5 button and follow the video to know what is happening here.

Code:

Sub generateRevenueAndCost()
Dim revenueRange As Range
Set revenueRange = Range("C5:C15")
Dim costRange As Range
Set costRange = Range("D5:D15")
Dim cell As Range
For Each cell In revenueRange
Randomize
cell.Value = Int((5000 - 1000 + 1) * Rnd + 1000)
Next cell
For Each cell In costRange
Randomize
cell.Value = Int((5000 - 1000 + 1) * Rnd + 1000)
Next cell
Dim pause As Boolean
pause = False
Do Until pause = True
pause = MsgBox("Do you want to pause the operation?", vbYesNo) = vbYes
Loop
Dim delay As Integer
delay = InputBox("Enter a delay time in seconds:")
MsgBox "Waiting for " & delay & " seconds..."
Application.Wait (Now + TimeValue("0:00:" & delay))
Dim profitRange As Range
Set profitRange = Range("E5:E15")
Dim i As Integer
For i = 1 To revenueRange.Count
profitRange.Cells(i).Value = revenueRange.Cells(i).Value - costRange.Cells(i).Value
Next i
End Sub

Dim pause As Boolean
pause = False
Do Until pause = True
pause = MsgBox("Do you want to pause the operation?", vbYesNo) = vbYes
Loop

Dim delay As Integer
delay = InputBox("Enter a delay time in seconds:")

After adding these two loops process the code with Application.Wait (Now+Time value) and get the delay time.

Things to Remember

• Adding a button is optional, you can execute VBA macros just by using the Run button.
• Adding comments is also optional while writing down the code. Comments don’t affect the code. You can write comments for better understanding.

Download Practice Workbook

You can find the practice workbook here.

Conclusion

This article taught us how to execute Excel VBA to wait until refresh is complete. This process will help if you are working on a dataset where you need to add refresh common and wait before another operation starts.

Hopefully, you can execute the examples shown in this article. Please let us know in the comment section if there is any query or suggestions.

Related Articles

• Excel VBA Wait Milliseconds
• How to Use Excel VBA DoEvents Function
• Excel VBA DoEvents and Wait Properties
• Excel VBA DoEvents Alternative
• Excel VBA: Wait Until a Process Complete