How to Use Excel VBA to Wait Until Refresh Is Complete

Dataset Overview

Consider the Profit and Loss (P&L) sheet for electronics companies. This sheet contains essential information such as Company Name, Revenue, Cost, and Profit/Loss. Our goal is to extract revenue and cost data using VBA code.

After refreshing the dataset, we’ll calculate the Profit/Loss using the following steps:

Step 1 – Creating Dataset

• Manually create a dataset with revenue, cost, and Profit/Loss columns.
• We’ll use this dataset as the basis for our calculations.

Step 2 – Applying VBA Code

• Write VBA code in Excel to perform the necessary calculations.
• Specifically, we’ll focus on revenue, cost, and Profit/Loss.
• Access the VBA module section.
• Enter the following code:

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

To make it easier to execute the VBA code in Excel, we’ll create a button that users can click. Follow these steps to create the button:

• Go to the Developer tab (if you don’t see it, enable it by going to File, select Option, click on the Customize Ribbon and check Developer).
• Click Insert and select Button (Icon) from the toolbar.

• Place the button on your worksheet at the desired location.
• Right-click on the button and select Assign Macro from the Context Menu.

• In the Assign Macro dialog box, choose the macro you’ve written (e.g., GenerateDataAndCalculateProfit) and click OK to assign it to the button.

• Right-click on the button again and select Edit Text from the Context Menu.
• Enter a descriptive name for the button (e.g., Calculate Profit).

• To improve visualization, you can format the button:
  • Right-click on the button and select Format Control.

• • Adjust the formatting options according to your preferences (e.g., font, color, size).
  • Click OK to apply the changes.

Once you’ve completed these steps, your button is ready to execute the VBA code. The image below shows what the customized button looks like:

Step 4 – Use Button to See Final Output

• Click the Excel button you’ve created.
• The VBA code will run:
  • It generates random Revenue and Cost values using a number generator.
  • It then waits for the refresh operation to complete.
  • It calculates the Profit based on the formula: Profit = Revenue – Cost.

Excel VBA to Wait for 5 Seconds Before Another Process Starts

Sometimes, you need to introduce a delay before starting another process. Here’s how to apply Excel VBA that waits for 5 seconds before proceeding. You can customize this code if you want to wait for more or less than 5 seconds.

Steps:

• Open the VBA module (as shown previously).
• Enter the following code in the module.
• Press the F5 button to run 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

When working with datasets, there are times when you need to pause an operation and then resume it. In this case, we’ll learn how to introduce a delay between two different operations using Excel VBA. You can set a time limit before the macro starts. Follow the steps below for details:

Steps:

• Open the Visual Basic Window:
  • Access the Visual Basic for Applications (VBA) editor by clicking on the Developer tab (if not visible, enable it by going to the File tab, selecting Options, clicking on Customize Ribbon and checking Developer).
  • Navigate to the Module section within the VBA editor.

• Insert the VBA Code:
  • Enter the following VBA code in the module.

• • Press the F5 button to run 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
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) to get the delay time.

Things to Remember

• Adding a button is optional; you can execute VBA macros using the Run button.
• Comments are optional but can improve code understanding.

Download Practice Workbook

You can download the practice workbook from here:

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