VBA easily allows you to get your hand on your PCs performance metrics which often can prove useful when wanting to do some performance statistics. Having been playing with the VBA Multithreading Tool I wanted a way to see how well all my processor cores are utilized – especially when wanting to optimize the amount of threads needed to be spawned.

Windows Management Instrumentation – the single source of your performance metrics

Windows Management Instrumentation (WMI) is the infrastructure for management data and operations on Windows-based operating systems. It is useful when wanting to extract performance data, when automating administrative tasks both on local and remote workstation. Read more here.

WMI can be used in all Windows-based applications, and can be easily accessed using VBA, VBscript, C# or VB.NET.

Extracting performance data in Excel VBA

Walking you through this process I will want to extract CPU usage measured as percentage processor time.

Fortunately, Microsoft has issued a fantastic tool for generating code needed to extract any needed performance metrics using WMI.

Download the WMI Code Creator and select your metrics

Although you don’t need to it is much easier to utilize the Microsoft WMI Code Creator to generate the necessary performance metrics code e.g. CPU usage.

Use the link below to download from Microsoft:
Link to Microsoft WMI Code Generator
Open the attached WMICodeCreator.exe file to open the WMI Code Creator window. You should see something like this:

To configure it properly:

• Select Visual Basic Script from the Code Language drop down menu.
• Leave the namespace as rootcimv2 if you want general workstation performance metrics e.g. CPU usage, memory usage etc. Feel free to read more on the WMI Reference.
• Select an appropriate class for your desired metrics. E.g for CPU usage I will select Win32_PerfFormattedData_PerfOS_Processor.

The code should generate automatically. You can execute it to see if it works by clicking Execute code.

Copy the code to your VBA Project and adjust it to VBA

Now just copy the above mentioned code into your VBA project. VBA and VBscript are very similar programming languages (or scripting languages as preferred by others). VBA does not identify the Wscript.Echo method which in VBA is represented by the Debug.Print equivalent. See an example below of the VBscript vs. VBA code.

VBscript (original from WMI Code Creator):

strComputer = "." 
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "rootcimv2") 
Set colItems = objWMIService.ExecQuery( _
    "SELECT * FROM Win32_PerfFormattedData_PerfOS_Processor",,48) 
For Each objItem in colItems 
    Wscript.Echo "-----------------------------------"
    Wscript.Echo "Win32_PerfFormattedData_PerfOS_Processor instance"
    Wscript.Echo "-----------------------------------"
    Wscript.Echo "PercentProcessorTime: " & objItem.PercentProcessorTime
Next

VBA (adjusted):

strComputer = "." 'Local machine, can be adjusted to access remote workstations
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "rootcimv2") 
Set colItems = objWMIService.ExecQuery( _
    "SELECT * FROM Win32_PerfFormattedData_PerfOS_Processor",,48) 
For Each objItem in colItems 
    Debug.Print "-----------------------------------"
    Debug.Print "Win32_PerfFormattedData_PerfOS_Processor instance"
    Debug.Print "-----------------------------------"
    Debug.Print "PercentProcessorTime: " & objItem.PercentProcessorTime
Next

Example: Measure CPU usage in VBA

Using WMI and the code above I quickly coded an Excel VBA project workbook that allows you to measure CPU performance in real time (1-2 second intervals :)).

The macro works as follows:

• When hitting start the macro generates as many cores as identified using WMI
• Every 2 seconds the metrics will refresh showing both CPU percentage processor time and the core utilization

Screenshot below:

Download the example

Feel free to download the code sample below:
[purchase_link id=”8221″ style=”button” color=”green” text=”Download” direct=”true”]