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.
Download Practice Workbook
You can find the practice workbook here.
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, follow this How to Write VBA Code in Excel article to enter it into the Visual Module. 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
Code Breakdown
Sub GenerateDataAndCalculateProfit()
This code starts with the Subroutine called GenerateDataAndCalculateProfit.
Dim revenueRange As Range
Set revenueRange = Range("C5:C15")
Here Revenue is declared as range and set the value as range C5:C15.
Dim costRange As Range
Set costRange = Range("D5:D15"))
Cost is also declared as range type variable and sets the value as range D5:D15.
Dim i As Long
Declare i as a long type variable.
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
In the For loop, i start with 1 and end with the total count of the cell in the revenueRange. Now, within this loop revenue and cost will be determined by Randbetween. This determines the revenue value between 1000 to 10000 and the cost value between 500 to 5000.
MsgBox "Refreshing workbook..."
ActiveWorkbook.RefreshAll
This will show “Refreshing workbook…” and will refresh the active workbook.
Do While Application.CalculationState <> xlDone
DoEvents
Loop
MsgBox "Refresh complete."
Now, after getting the revenue and cost this set will refresh the code using the Doevents temporarily pauses the running macro and it can resume with a mouse click or anything else. Once the refresh is complete it will show Refresh complete in Msgbox.
Dim profitRange As Range
Set profitRange = Range("E5:E15")
profitRange.Formula = "=C5-D5"
End Sub
Lastly, calculate the profit using Formula, where profit is declared as Range and the range of the final output, is E5:E15.
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
Code Breakdown
Sub generateRevenueAndCost()
This code starts with the Subroutine which is generateRevenueAndCost.
Dim revenueRange As Range
Set revenueRange = Range("C5:C15")
Revenue is declared as Range type variable and set the value as range C5:C15.
Dim costRange As Range
Set costRange = Range("D5:D15")
Cost is also declared as Range type with range D5:D15.
Dim cell As Range
For Each cell In revenueRange
Randomize
cell.Value = Int((5000 - 1000 + 1) * Rnd + 1000)
Next cell
The For loop starts with the value of revenueRange and generates the random integer value between 5000 to 1000.
For Each cell In costRange
Randomize
cell.Value = Int((5000 - 1000 + 1) * Rnd + 1000)
Next cell
This For loop also generates values between 5000 to 1000 which is the value of Cost.
MsgBox "Waiting for 5 seconds..."
Application.Wait (Now + TimeValue("0:00:05"))
Add time using Wait (Now+Time value) and add the required delay time here.
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
profitRange is declared as Range type and set the value as range E5:E15. Here, the For loop starts with 1 and ends with Count. Lastly, the profit is calculated in the usual way to execute this process.
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
Note:
This code is quite similar to the previous one but here you need to add an extra loop of Pause and Delay in the code.
Code Breakdown
Dim pause As Boolean
pause = False
Do Until pause = True
pause = MsgBox("Do you want to pause the operation?", vbYesNo) = vbYes
Loop
Add Pause loop so that if the data is true the code pauses the operation.
Dim delay As Integer
delay = InputBox("Enter a delay time in seconds:")
Add Delay to get the delay time as required.
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.
Conclusion
This article taught us how to execute Excel VBA to wait until refresh 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 or you can also visit Exceldemy to explore more.