5

I want to add a wait block for 500 milliseconds in macOS VBA editor for Excel. I know

Application.Wait (Now + TimeValue("0:00:01"))

Works, but this can't go under 1 second. I have tried the sleep command too:

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
...
Sleep 500

But this gives me an error saying

File Not Found "kernel32"

Any way to achieve this?

braX
  • 11,506
  • 5
  • 20
  • 33
Haardik Chopra
  • 77
  • 6
  • Does [Cross Compatibility of Millisecond Timer Resolution in Excel VBA](https://stackoverflow.com/questions/7246526/cross-compatibility-of-millisecond-timer-resolution-in-excel-vba) help? – Andrew Morton Sep 12 '21 at 13:53
  • From what I understood, it does outline a way to time how long your code took to execute, but I want to add a slight 500 milliseconds delay to my code rather than count how long it takes. Please correct me if I'm wrong, thanks! – Haardik Chopra Sep 12 '21 at 14:03
  • I'm not familiar with using Excel on a Mac, but some of the answers to [AppleScript “delay” command not working since switch to Yosemite](https://apple.stackexchange.com/questions/161749/applescript-delay-command-not-working-since-switch-to-yosemite) look like they could be useful. – Andrew Morton Sep 12 '21 at 14:13

2 Answers2

3

This will work on both Windows and Mac:

Option Explicit

#If Mac Then
    #If VBA7 Then
        Public Declare PtrSafe Sub USleep Lib "/usr/lib/libc.dylib" Alias "usleep" (ByVal dwMicroseconds As Long)
    #Else
        Public Declare Sub USleep Lib "/usr/lib/libc.dylib" Alias "usleep" (ByVal dwMicroseconds As Long)
    #End If
#Else 'Windows
    #If VBA7 Then
        Private Declare PtrSafe Sub MSleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
    #Else
        Private Declare  Sub MSleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
    #End If
#End If

Public Sub Sleep(ByVal dwMilliseconds As Long)
#If Mac Then
    USleep dwMilliseconds * 1000&
#Else
    MSleep dwMilliseconds
#End If
End Sub

Edit #1

The following code fixes overflow issues for Mac:

Option Explicit

#If Mac Then
    #If VBA7 Then
        Public Declare PtrSafe Sub USleep Lib "/usr/lib/libc.dylib" Alias "usleep" (ByVal dwMicroseconds As Long)
    #Else
        Public Declare Sub USleep Lib "/usr/lib/libc.dylib" Alias "usleep" (ByVal dwMicroseconds As Long)
    #End If
#Else 'Windows
    #If VBA7 Then
        Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
    #Else
        Public Declare Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
    #End If
#End If

#If Mac Then
Public Sub Sleep(ByVal dwMilliseconds As Long)
    If dwMilliseconds And &H80000000 Then
        USleep &HFFFFFFFF
    ElseIf dwMilliseconds > &H418937 Then
        USleep &HFFFFFFFF
    ElseIf dwMilliseconds > &H20C49B Then
        USleep (dwMilliseconds * 500& Or &H80000000) * 2&
    Else
        USleep dwMilliseconds * 1000&
    End If
End Sub
#End If

Edit #2

With the help of Guido, the Mac Sleep now behaves exactly as the Windows counterpart:

Option Explicit

#If Mac Then
    #If VBA7 Then
        Public Declare PtrSafe Sub USleep Lib "/usr/lib/libc.dylib" Alias "usleep" (ByVal dwMicroseconds As Long)
    #Else
        Public Declare Sub USleep Lib "/usr/lib/libc.dylib" Alias "usleep" (ByVal dwMicroseconds As Long)
    #End If
#Else 'Windows
    #If VBA7 Then
        Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
    #Else
        Public Declare Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
    #End If
#End If

#If Mac Then
Public Sub Sleep(ByVal dwMilliseconds As Long)
    Const maxMicro As Long = &HFFFFFED8  'unsigned &HFFFFFFFF divided by 1000 is not round
    Const maxMilli As Long = &H418937    'unsigned &HFFFFFED8 divided by 1000
    Const maxMilliPos As Long = maxMilli \ 2 '&H20C49B
    Const signBit As Long = &H80000000
    Const maxMilliNeg As Long = maxMilli Or signBit
    '
    Do While dwMilliseconds And signBit
        USleep maxMicro
        If dwMilliseconds < maxMilliNeg Then
            dwMilliseconds = (dwMilliseconds Xor signBit) - maxMilli Xor signBit
        Else
            dwMilliseconds = dwMilliseconds - maxMilli
        End If
    Loop
    Do While dwMilliseconds > maxMilli
        USleep maxMicro
        dwMilliseconds = dwMilliseconds - maxMilli
    Loop
    If dwMilliseconds > maxMilliPos Then
        USleep (dwMilliseconds * 500& Or signBit) * 2&
    Else
        USleep dwMilliseconds * 1000&
    End If
End Sub
#End If
Cristian Buse
  • 4,020
  • 1
  • 13
  • 34
-2

Your code block above will work with one small addition. Simply divide the TimeValue by 2 to get 500ms

Application.Wait (Now + TimeValue("0:00:01") / 2)
over40dev
  • 1
  • 1