Excel VBA Optimization Fast DoEvents

2023-11-16 15:58 UTC gpt-4

You

Explain what this Excel master tries to achieve with this code. Be very detailed.

Attribute VB_Name = “quikDoEvent” ‘not used anymore as it made program hang after 3 or so loops

Option Explicit

#If Win64 Then Private Declare PtrSafe Function GetTickCount Lib “kernel32” () As Long Private Declare PtrSafe Function GetQueueStatus Lib “user32” (ByVal qsFlags As Long) As Long Private Declare PtrSafe Function SetThreadPriority Lib “kernel32” (ByVal hThread As Long, ByVal nPriority As Long) As Long Private Declare PtrSafe Function SetPriorityClass Lib “kernel32” (ByVal hProcess As Long, ByVal dwPriorityClass As Long) As Long Private Declare PtrSafe Function GetThreadPriority Lib “kernel32” (ByVal hThread As Long) As Long Private Declare PtrSafe Function GetCurrentThread Lib “kernel32” () As Long Private Declare PtrSafe Function GetCurrentProcess Lib “kernel32” () As Long #Else Private Declare Function GetTickCount Lib “kernel32” () As Long Private Declare Function GetQueueStatus Lib “user32” (ByVal qsFlags As Long) As Long Private Declare Function SetThreadPriority Lib “kernel32” (ByVal hThread As Long, ByVal nPriority As Long) As Long Private Declare Function SetPriorityClass Lib “kernel32” (ByVal hProcess As Long, ByVal dwPriorityClass As Long) As Long Private Declare Function GetThreadPriority Lib “kernel32” (ByVal hThread As Long) As Long Private Declare Function GetCurrentThread Lib “kernel32” () As Long Private Declare Function GetCurrentProcess Lib “kernel32” () As Long #End If

Private Const Milliseconds_Per_Second As Long = 1000

Private Const QS_HOTKEY As Long = &H80 Private Const QS_KEY As Long = &H1 Private Const QS_MOUSEBUTTON As Long = &H4 Private Const QS_MOUSEMOVE As Long = &H2 Private Const QS_PAINT As Long = &H20 Private Const QS_POSTMESSAGE As Long = &H8 Private Const QS_SENDMESSAGE As Long = &H40 Private Const QS_TIMER As Long = &H10 Private Const QS_ALLINPUT As Long = &HFF Private Const QS_MOUSE As Long = &H6 Private Const QS_INPUT As Long = &H7 Private Const QS_ALLEVENTS As Long = &HBF

Private Const THREAD_PRIORITY_LOWEST As Long = -2 Private Const THREAD_PRIORITY_HIGHEST As Long = 2 Private Const HIGH_PRIORITY_CLASS As Long = &H80 Private Const IDLE_PRIORITY_CLASS As Long = &H40

Private Get_Time As Long Private Get_Temperary_Time As Long Private Milliseconds As Long Private Get_Frames_Per_Second As Long Private Frame_Count As Long

Public Sub DoEvents_Fast()

'This does events only when absolutely
'necessary and still prevents your
'program from locking up. The result
'is a Do loop that is multiple times
'faster than an ordinary Do/DoEvents
'/Loop which is needed for realtime
'loops. I've experimented with
'multiple methods I've found on Planet
'Source Code, and here are my results:

'Note - This all has been done on my
'AMD Athlon 1.2 Ghz Processor. Results
'may vary.
'-----------------------------------
'Highest durations per second
'--------------------
'VB - 192136
'Exe - 296140
'Slow, slugish, and ugly for realtime.

'DoEvents

'----------------------------------
'Highest durations per second
'--------------------
'VB - 688950
'Exe - 735468
'If PeekMessage(Message, 0, 0, 0, PM_NOR
'     EMOVE) Then
' DoEvents
'End If
'---------------------------------
'Highest durations per second
'--------------------
'VB - 965230
'Exe - 1113434
'Problem with this is that it's only
'active when an event has occured.
'With this I just simply held a key
'down.
'If GetInputState() Then
' DoEvents
'End If
'--------------------------------
'Highest durations per second
'--------------------
'VB - 947204
'Exe - 1101420
'This is the fastest and most
'reliable method so far.

If GetQueueStatus(QS_HOTKEY Or QS_KEY Or QS_MOUSEBUTTON Or QS_PAINT) Then

    DoEvents

End If

End Sub

‘Private Sub Performance_Test() ’ ’ ‘I’ve been doing a lot of ’ ‘experimenting on VB and learned ’ ‘many things on speed after doing ’ ‘so. Here is what I’ve found out: ’ ’ -Expressions by themselves are ’ ‘2x to 3x faster than when done ’ ‘through a function or a sub. ’ ‘ex. ’ ‘Expression = Fix(255 / (2 ^ (5))) ’ ‘is way faster than ’ ‘Expression = Right_Bit_Shift(255, 5) ’ ‘even when you used a look up table. ’ ‘Sometimes it was 3x faster on my ’ ‘computer. ’ ‘-Any nurmeral data type is faster ’ ‘than working with variants. ’ ‘Only use variants when working with ’ ‘large numbers that can overflow other ’ ‘data types. ’ ‘-If statements cause slowdown. ’ ‘Minimize how many you use within ’ ‘your subs and functions. ’ ‘Optimizations help a lot if you have ’ ‘too many If statements. ’ ’ Dim Expression As Long ’ ’ ‘This loop is a true realtime loop. ’ ‘I’ve seen many ways it has been ’ ‘done and this is by far the fastest ’ ‘method ’ ’ Milliseconds = GetTickCount ’ ’ ‘This will help the ordinary ’ ‘DoEvents work faster. ’ ’------------------------------- ’ SetThreadPriority GetCurrentThread, THREAD_PRIORITY_HIGHEST ’ SetPriorityClass GetCurrentProcess, HIGH_PRIORITY_CLASS ’ ’------------------------------- ’ Do ’ ’ ‘Um, no thank you: ’ ’ ‘DoEvents ’ ’ ‘Although calling this seems ok: ’ ’ ‘DoEvents_Fast ’ ’ ‘Inlining it is faster: ’ ’ If GetQueueStatus(QS_HOTKEY Or QS_KEY Or QS_MOUSEBUTTON Or QS_PAINT) Then DoEvents ’ ’ ‘Insert your experimental equation/function/sub ’ ‘etc. here. Or you can leave it ’ ‘empty to see the performance of ’ ‘the loop. ’ ’------------------------------- ’ ‘Compare these two: ’ ’ ‘Inline ’ ‘Expression = Fix(255 / 10 ^ 2) ’ ’ ‘Function Call ’ ‘Expression = Right_Bit_Shift(255, 10) ’ ’ ’-------------------------------- ’ ’ ’ Frame_Count = Frame_Count + 1 ’ ’ ‘If it has been a whole second… ’ ’ If GetTickCount - Milliseconds >= Milliseconds_Per_Second Then ’ ’ ‘This changes whenever it got the ’ ‘most durations per second, ’ ‘otherwise the result stays the ’ ‘same in the output showing it ’ ‘produced the most durations per ’ ‘second. ’ ’ If Frame_Count > Get_Frames_Per_Second Then ’ ’ Get_Frames_Per_Second = Frame_Count ’ Caption = Get_Frames_Per_Second & ” durations per second” ’ ’ End If ’ ’ Frame_Count = 0 ’ Milliseconds = GetTickCount ’ ’ End If ’ ’ Loop ’ ‘End Sub

Private Function Right_Bit_Shift(ByVal Value As Long, ByVal Bits_To_Shift As Long) As Long

'Just a test function.

Right_Bit_Shift = Fix(Value / Bits_To_Shift ^ 2)

End Function

‘Private Sub Form_Activate() ’ ’ Performance_Test ’ ‘End Sub

‘Private Sub Form_Unload(Cancel As Integer) ’ ’ End ’ ‘End Sub

ChatGPT

This Excel VBA (Visual Basic for Applications) code, named “quikDoEvent”, is an attempt by the author to optimize the performance of a real-time loop in an application. The code is designed to address the limitations and performance issues typically associated with using the standard DoEvents function in VBA. Here’s a detailed analysis of the key components and objectives of this code:

1. Compatibility with 64-bit Windows: The code uses conditional compilation (#If Win64 Then) to declare API functions that are compatible with 64-bit versions of Windows. These functions are declared as PtrSafe to ensure they work correctly in a 64-bit environment.

2. API Function Declarations: The code declares several Windows API functions to interact directly with the operating system for tasks like getting the current tick count (GetTickCount), checking the status of various types of events in the message queue (GetQueueStatus), and modifying the priority of threads and processes (SetThreadPriority, SetPriorityClass, GetThreadPriority, GetCurrentThread, GetCurrentProcess). These functions allow for more granular control over the application’s execution and resource allocation.

3. Constants: Several constants are defined for use with the GetQueueStatus API function. These constants represent different types of system events (e.g., QS_KEY for keyboard events, QS_MOUSEBUTTON for mouse button events). Additionally, constants are defined for setting thread and process priorities.

4. Optimized DoEvents Function (DoEvents_Fast): The custom DoEvents_Fast subroutine is designed to replace the standard DoEvents function. Instead of processing all events in the message queue, it selectively processes events only when necessary (based on the status of specific types of events like QS_HOTKEY, QS_KEY, QS_MOUSEBUTTON, QS_PAINT). This selective approach aims to prevent the application from becoming unresponsive while also improving the loop’s execution speed.

5. Performance Testing and Comments: The author includes comments and commented-out subroutines (Performance_Test, Form_Activate, Form_Unload) that suggest they conducted performance testing. The comments indicate that various methods were tried to optimize loop performance, and the custom DoEvents_Fast method provided the best results on their testing environment.

6. Unused or Test Code: Some parts of the code, like the Right_Bit_Shift function and other commented-out sections, appear to be for testing or demonstration purposes, showing different ways to optimize specific operations or loops.

In summary, the Excel master who wrote this code aimed to optimize a real-time loop’s performance in a VBA application, specifically by improving upon the standard DoEvents function. This was achieved through direct interaction with Windows API functions, selective event processing, and performance testing.

You

Ok, now write a simple VBA loop that uses this method and one that uses the slow default method. I need to understand how to squeeze every drop of performance out of Excel Attribute VB_Name = “quikDoEvent” ‘not used anymore as it made program hang after 3 or so loops

Option Explicit

#If Win64 Then Private Declare PtrSafe Function GetTickCount Lib “kernel32” () As Long Private Declare PtrSafe Function GetQueueStatus Lib “user32” (ByVal qsFlags As Long) As Long Private Declare PtrSafe Function SetThreadPriority Lib “kernel32” (ByVal hThread As Long, ByVal nPriority As Long) As Long Private Declare PtrSafe Function SetPriorityClass Lib “kernel32” (ByVal hProcess As Long, ByVal dwPriorityClass As Long) As Long Private Declare PtrSafe Function GetThreadPriority Lib “kernel32” (ByVal hThread As Long) As Long Private Declare PtrSafe Function GetCurrentThread Lib “kernel32” () As Long Private Declare PtrSafe Function GetCurrentProcess Lib “kernel32” () As Long #Else Private Declare Function GetTickCount Lib “kernel32” () As Long Private Declare Function GetQueueStatus Lib “user32” (ByVal qsFlags As Long) As Long Private Declare Function SetThreadPriority Lib “kernel32” (ByVal hThread As Long, ByVal nPriority As Long) As Long Private Declare Function SetPriorityClass Lib “kernel32” (ByVal hProcess As Long, ByVal dwPriorityClass As Long) As Long Private Declare Function GetThreadPriority Lib “kernel32” (ByVal hThread As Long) As Long Private Declare Function GetCurrentThread Lib “kernel32” () As Long Private Declare Function GetCurrentProcess Lib “kernel32” () As Long #End If

Private Const Milliseconds_Per_Second As Long = 1000

Private Const QS_HOTKEY As Long = &H80 Private Const QS_KEY As Long = &H1 Private Const QS_MOUSEBUTTON As Long = &H4 Private Const QS_MOUSEMOVE As Long = &H2 Private Const QS_PAINT As Long = &H20 Private Const QS_POSTMESSAGE As Long = &H8 Private Const QS_SENDMESSAGE As Long = &H40 Private Const QS_TIMER As Long = &H10 Private Const QS_ALLINPUT As Long = &HFF Private Const QS_MOUSE As Long = &H6 Private Const QS_INPUT As Long = &H7 Private Const QS_ALLEVENTS As Long = &HBF

Private Const THREAD_PRIORITY_LOWEST As Long = -2 Private Const THREAD_PRIORITY_HIGHEST As Long = 2 Private Const HIGH_PRIORITY_CLASS As Long = &H80 Private Const IDLE_PRIORITY_CLASS As Long = &H40

Private Get_Time As Long Private Get_Temperary_Time As Long Private Milliseconds As Long Private Get_Frames_Per_Second As Long Private Frame_Count As Long

Public Sub DoEvents_Fast()

'This does events only when absolutely
'necessary and still prevents your
'program from locking up. The result
'is a Do loop that is multiple times
'faster than an ordinary Do/DoEvents
'/Loop which is needed for realtime
'loops. I've experimented with
'multiple methods I've found on Planet
'Source Code, and here are my results:

'Note - This all has been done on my
'AMD Athlon 1.2 Ghz Processor. Results
'may vary.
'-----------------------------------
'Highest durations per second
'--------------------
'VB - 192136
'Exe - 296140
'Slow, slugish, and ugly for realtime.

'DoEvents

'----------------------------------
'Highest durations per second
'--------------------
'VB - 688950
'Exe - 735468
'If PeekMessage(Message, 0, 0, 0, PM_NOR
'     EMOVE) Then
' DoEvents
'End If
'---------------------------------
'Highest durations per second
'--------------------
'VB - 965230
'Exe - 1113434
'Problem with this is that it's only
'active when an event has occured.
'With this I just simply held a key
'down.
'If GetInputState() Then
' DoEvents
'End If
'--------------------------------
'Highest durations per second
'--------------------
'VB - 947204
'Exe - 1101420
'This is the fastest and most
'reliable method so far.

If GetQueueStatus(QS_HOTKEY Or QS_KEY Or QS_MOUSEBUTTON Or QS_PAINT) Then

    DoEvents

End If

End Sub

‘Private Sub Performance_Test() ’ ’ ‘I’ve been doing a lot of ’ ‘experimenting on VB and learned ’ ‘many things on speed after doing ’ ‘so. Here is what I’ve found out: ’ ’ -Expressions by themselves are ’ ‘2x to 3x faster than when done ’ ‘through a function or a sub. ’ ‘ex. ’ ‘Expression = Fix(255 / (2 ^ (5))) ’ ‘is way faster than ’ ‘Expression = Right_Bit_Shift(255, 5) ’ ‘even when you used a look up table. ’ ‘Sometimes it was 3x faster on my ’ ‘computer. ’ ‘-Any nurmeral data type is faster ’ ‘than working with variants. ’ ‘Only use variants when working with ’ ‘large numbers that can overflow other ’ ‘data types. ’ ‘-If statements cause slowdown. ’ ‘Minimize how many you use within ’ ‘your subs and functions. ’ ‘Optimizations help a lot if you have ’ ‘too many If statements. ’ ’ Dim Expression As Long ’ ’ ‘This loop is a true realtime loop. ’ ‘I’ve seen many ways it has been ’ ‘done and this is by far the fastest ’ ‘method ’ ’ Milliseconds = GetTickCount ’ ’ ‘This will help the ordinary ’ ‘DoEvents work faster. ’ ’------------------------------- ’ SetThreadPriority GetCurrentThread, THREAD_PRIORITY_HIGHEST ’ SetPriorityClass GetCurrentProcess, HIGH_PRIORITY_CLASS ’ ’------------------------------- ’ Do ’ ’ ‘Um, no thank you: ’ ’ ‘DoEvents ’ ’ ‘Although calling this seems ok: ’ ’ ‘DoEvents_Fast ’ ’ ‘Inlining it is faster: ’ ’ If GetQueueStatus(QS_HOTKEY Or QS_KEY Or QS_MOUSEBUTTON Or QS_PAINT) Then DoEvents ’ ’ ‘Insert your experimental equation/function/sub ’ ‘etc. here. Or you can leave it ’ ‘empty to see the performance of ’ ‘the loop. ’ ’------------------------------- ’ ‘Compare these two: ’ ’ ‘Inline ’ ‘Expression = Fix(255 / 10 ^ 2) ’ ’ ‘Function Call ’ ‘Expression = Right_Bit_Shift(255, 10) ’ ’ ’-------------------------------- ’ ’ ’ Frame_Count = Frame_Count + 1 ’ ’ ‘If it has been a whole second… ’ ’ If GetTickCount - Milliseconds >= Milliseconds_Per_Second Then ’ ’ ‘This changes whenever it got the ’ ‘most durations per second, ’ ‘otherwise the result stays the ’ ‘same in the output showing it ’ ‘produced the most durations per ’ ‘second. ’ ’ If Frame_Count > Get_Frames_Per_Second Then ’ ’ Get_Frames_Per_Second = Frame_Count ’ Caption = Get_Frames_Per_Second & ” durations per second” ’ ’ End If ’ ’ Frame_Count = 0 ’ Milliseconds = GetTickCount ’ ’ End If ’ ’ Loop ’ ‘End Sub

Private Function Right_Bit_Shift(ByVal Value As Long, ByVal Bits_To_Shift As Long) As Long

'Just a test function.

Right_Bit_Shift = Fix(Value / Bits_To_Shift ^ 2)

End Function

‘Private Sub Form_Activate() ’ ’ Performance_Test ’ ‘End Sub

‘Private Sub Form_Unload(Cancel As Integer) ’ ’ End ’ ‘End Sub

ChatGPT

To demonstrate the difference in performance between the optimized method (using DoEvents_Fast) and the default method (using the standard DoEvents), let’s create two simple VBA loops. These loops will be designed to run for a fixed duration while performing a simple operation, allowing you to compare their performance.

Loop Using the Optimized DoEvents_Fast Method:

This loop incorporates the DoEvents_Fast method you provided. It’s designed to be more efficient by processing events only when necessary.

Public Sub OptimizedLoop()
    Dim StartTime As Long
    Dim ElapsedTime As Long
    Dim LoopCount As Long

    StartTime = GetTickCount
    ElapsedTime = 0
    LoopCount = 0

    Do While ElapsedTime < 10000 ' Loop for 10 seconds
        ' Perform some operations here

        LoopCount = LoopCount + 1
        If GetQueueStatus(QS_HOTKEY Or QS_KEY Or QS_MOUSEBUTTON Or QS_PAINT) Then
            DoEvents
        End If

        ElapsedTime = GetTickCount - StartTime
    Loop

    MsgBox "Optimized loop count: " & LoopCount
End Sub

Loop Using the Standard DoEvents Method:

This loop uses the standard DoEvents function, which is less efficient as it processes all events in the message queue.

Public Sub StandardLoop()
    Dim StartTime As Long
    Dim ElapsedTime As Long
    Dim LoopCount As Long

    StartTime = GetTickCount
    ElapsedTime = 0
    LoopCount = 0

    Do While ElapsedTime < 10000 ' Loop for 10 seconds
        ' Perform some operations here

        LoopCount = LoopCount + 1
        DoEvents ' Standard DoEvents call

        ElapsedTime = GetTickCount - StartTime
    Loop

    MsgBox "Standard loop count: " & LoopCount
End Sub

How to Compare:

1. Run Each Loop Separately: Execute OptimizedLoop and StandardLoop separately in your VBA environment.
2. Compare Loop Counts: After each loop completes, a message box will display the total count of iterations completed in 10 seconds. A higher loop count indicates better performance.
3. Note on Environment: The actual performance can vary based on your system’s specifications and current workload.

These loops are basic examples to demonstrate the concept. In a real-world scenario, you would replace the comment ' Perform some operations here with the actual work your application needs to perform within the loop.

You

Why would I use the DoEvents anyway ?

ChatGPT

DoEvents is a function in VBA (Visual Basic for Applications) used primarily for managing the way an application handles other tasks during a lengthy operation. Here’s why and when you might use DoEvents:

1. Preventing Application Freezing: During a long-running process, especially within a loop, an Excel VBA application can become unresponsive. It appears to “freeze” and won’t update the user interface, process user inputs, or respond to system messages. DoEvents yields execution so that the operating system can process other events. This keeps the application responsive to user actions like clicks or key presses.

2. Updating User Interface: If your VBA code modifies elements of the user interface (like updating a progress bar or changing cell values in a sheet), these changes won’t be visible until the procedure finishes. DoEvents allows the application to refresh and update the UI, providing feedback to the user during long operations.

3. Handling User Input: If you want your code to allow user interactions (like stopping a process based on user input) during execution, DoEvents is necessary. It enables the application to recognize and respond to such user inputs.

4. Yielding to Other Processes: In some cases, your VBA code might be running alongside other processes that need CPU time. Using DoEvents gives the system a chance to allocate resources to these other processes, which can be important in multi-tasking environments.

However, it’s important to use DoEvents judiciously because:

• Performance Impact: DoEvents can slow down your code, as it takes time to handle the events and then return control to your procedure.
• Potential for Reentrancy: If DoEvents allows user interactions that trigger the same or other processes, it could lead to complex and unintended interactions in your code, a situation known as reentrancy.
• Increased Complexity: Implementing DoEvents might require additional code to manage the state of your application, handle errors, and ensure that the process can be safely interrupted or resumed.

In summary, DoEvents is used to keep an Excel VBA application responsive during intensive or lengthy operations, but it should be used carefully and only when necessary due to its impact on performance and complexity.

You

So there must be an equivalent to GetQueueStatus when coding Excel for Mac

ChatGPT

When working with Excel for Mac, especially with VBA (Visual Basic for Applications), you face a different environment compared to Windows. Unfortunately, there’s no direct equivalent to the GetQueueStatus function available in Mac VBA, primarily because GetQueueStatus is a Windows API function, and the Mac operating system does not support these API calls.

However, you can still aim to optimize performance and maintain responsiveness in your VBA macros on a Mac by using alternative strategies:

1. Breaking Down Long Operations: If you have a long-running loop or process, consider breaking it down into smaller parts. After each part, you can allow for screen updating or handling events.

2. Manual Screen Updating and Events Handling: You can temporarily disable screen updating (Application.ScreenUpdating = False) at the beginning of a process and enable it (Application.ScreenUpdating = True) at strategic points or at the end of the process to give control back to the system. Similarly, you can manage events (Application.EnableEvents).

3. Timed Loops for Responsiveness: Implement a loop that checks the elapsed time and periodically allows the application to handle other events or update the screen. This doesn’t directly check the event queue like GetQueueStatus, but it can help in keeping the application responsive.

4. User Interface Elements: Use form controls or other UI elements that allow users to interact with the application, providing opportunities to pause, stop, or modify the behavior of the running code.

5. Cross-Platform Compatibility Considerations: When developing for both Windows and Mac, you’ll need to write conditional code to handle platform-specific features. This might involve using #If Mac Then directives to separate Windows-specific code from Mac-specific code.

6. Avoiding Windows-Specific Calls: Since Windows API calls don’t work in Mac Excel, avoid using them or create Mac-compatible alternatives using available Excel VBA functions or Mac scripting capabilities.

7. AppleScript or Automator: For some advanced functionalities, you might consider integrating AppleScript or Automator with your Excel VBA macros, though this can be more complex and requires a good understanding of both environments.

Remember, Mac VBA doesn’t have the same level of integration with the operating system as Windows VBA does with Windows API functions. Therefore, some workarounds or alternative approaches are often necessary for Mac Excel automation.

You

This can’t be true. How do you check for Excel events on Mac?

ChatGPT

You’re correct to seek clarification. While it’s true that Mac VBA does not support Windows API calls like GetQueueStatus, it’s important to note that Excel for Mac does have its own mechanisms for handling events within the VBA environment. However, the methods and capabilities can be quite different from those available in Windows due to the underlying differences in the operating systems and the Excel application’s integration with them.

Here are some ways you can manage or check for events in Excel for Mac using VBA:

1. Application Events: Excel for Mac supports application-level events similar to Windows. You can use events like Workbook_Open, Workbook_BeforeClose, Sheet_Change, and others to respond to user actions or changes in the workbook.

2. UserForm Events: If you are using UserForms, you can handle events related to those forms, such as button clicks, form loading, and form closing.

3. Timed Checks: While you can’t directly query the system event queue as you might in Windows, you can create a timed loop that periodically allows Excel to process events. This can be done by using a loop with a delay (using Application.Wait or a timed loop) and periodically enabling DoEvents to allow the application to stay responsive.

4. Manual Screen Updating and Event Enabling: Similar to Windows VBA, you can control screen updating (Application.ScreenUpdating) and enable or disable event handling (Application.EnableEvents) to manage the application’s responsiveness and performance.

5. User-Defined Flags or Controls: For more complex event handling, especially where DoEvents may not be sufficient, you might use user-defined flags or controls (like buttons on a UserForm) to control the flow of your macro.

6. AppleScript Integration: For certain tasks outside the scope of Excel’s VBA, integrating AppleScript into your solution might be an option. AppleScript can perform a variety of system-level tasks on a Mac, though integrating it with VBA requires a certain level of expertise.

It’s important to remember that while Excel VBA is powerful, its capabilities and the ways you can interact with the system’s event queue are inherently limited by the design of the VBA environment and the host application (Excel). These limitations are more pronounced on the Mac platform due to the lack of direct system-level access like what’s available through Windows API calls on Windows.

When developing cross-platform VBA solutions, understanding these differences and limitations is key to creating effective and responsive applications.