How to wait until the CPU usage drops below 60% in VBA?

Question

The following code is using for measuring CPU % usage.

Public Sub Macro1()

Dim strComputer As String
Dim objWMIService As Object
Dim colItems As Object
Dim objItem As Object

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_PerfFormattedData_PerfOS_Processor", , 48)

For Each objItem In colItems
    Debug.Print objItem.PercentProcessorTime
Next
    
End Sub

I have got the above code from this link: https://analystcave.com/excel-measuring-cpu-usage-in-vba-and-other-performance-metrics/#comment-184350.

How to wait until the CPU usage drops below 60% in VBA?

The code in the post queries the WMI data only once. As far as I know, it's not possible to request streamed data from WMI. The author of the article runs the macro with a 2-second timed cron job (presumably not cron, but rather a setting within the macro). To determine when CPU usage exceeds 60%, you need to query the CPU every 1-2 seconds until the result reaches 60% or higher. — rozsazoltan
– rozsazoltan, Commented Mar 10 at 7:49

Joe Bourne · Accepted Answer · 2025-03-12 22:02:03Z

1

you could try something like this:

(I would probably put a Sleep() in there too to help reduce it spinning to fast)

Sub Test()

    Debug.Print "Starting"
    Call PauseWhileBusy
    Debug.Print "Done"

End Sub

Sub PauseWhileBusy(Optional ByVal lMaxPercent As Long = 60, Optional ByVal strComputer As String = ".")
    
    Dim colItems As Object
    Dim objItem As Object
    Dim bTooBusy As Boolean
    
    With GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
        Do
            Set colItems = .ExecQuery("Select * from Win32_Processor")
            For Each objItem In colItems
                Debug.Print "Current Load:", objItem.LoadPercentage
                bTooBusy = objItem.LoadPercentage > lMaxPercent
            Next
            DoEvents
        Loop While bTooBusy
        
    End With
End Sub

edited Mar 12 at 22:02

answered Mar 12 at 21:25

Joe Bourne

1,22912 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kram Kramer Mar 13 at 1:40

Hi Joe Bourne Your code is perfect! Thank you very much. I dont know if Sleep() is necessary or not. If you have free time, I need other codes regarding Memory Usage and Disk Usage. Thanks in advance.

Eliav Zukerman · Accepted Answer · 2025-03-10 09:00:36Z

-1

You can achieve this by running a loop that continuously checks the CPU usage and only exits when it drops below 60%. To prevent excessive CPU usage while waiting, you should use Sleep to introduce a small delay between checks and DoEvents to keep the system responsive.

answered Mar 10 at 9:00

Eliav Zukerman

9

1 Comment

Chris Mar 23 at 19:17

Welcome to Stack Overflow. Please read How to Answer. This answer vaguely describes a possible solution, which isn't very clear. An example would really help.

Collectives™ on Stack Overflow

How to wait until the CPU usage drops below 60% in VBA?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related