0

I would like to get fractional timer resolution in an Excel VBA project that has to be compatible with Windows Excel 2007 and Mac OS Excel 2011. Ive found a method in windows utilizing sleep from kernel32.dll and a method on Mac OS using MacScript("GetMilliSec").

I currently have limited access to a Mac, so I am not able to test this out directly right now. If I declare Sleep like this:

Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)

Will an error be raised when the file is opened in a Mac? Or will the error be flagged only when/if Sleep is called in a Mac?

Community
  • 1
  • 1
Hari Seldon
  • 1,060
  • 2
  • 13
  • 27

2 Answers2

2

I believe you'll need to use conditional compilation to avoid your API declaration causing an error on a Mac.

   #If Mac Then
      MsgBox "I'm a Mac"
   #Else
      MsgBox "I'm a PC"
   #End If
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • I have seen this code before... is `Mac` something that would be recognized in the Conditional Compilation "code"? It is not feasible for me to distribute a second version of the project to Mac users only, so I couldnt use something `#CONST Mac = True` and then manually change it and redistribute with `#CONST Mac = False` – Hari Seldon Aug 30 '11 at 17:02
  • @Hari - I'd thought there were some "built-in" conditional compilation constants, but it seems that may not be the case. I did test my code (without declaring the "Mac" constant) and it appeared to work, but now I see it doesn't error even if you use an undeclared constant - it just gives it a default value of False. – Tim Williams Aug 30 '11 at 18:02
  • @Tim... Thanks for your efforts! That gives me what I need to know. – Hari Seldon Aug 30 '11 at 18:42
1

I have a Mac using Office 2011 and can confirm that an error will be raised when the sub calls sleep with File not found: kernel32

If you have an alternative approach to use the sleep command in Mac, please share and use the below logic to select Mac or Windows:

Public Sub WINorMAC()
'Test for the operating system.
    If Not Application.OperatingSystem Like "*Mac*" Then
        'Is Windows.
        Call Windows_specific_function()
    Else
        'Is a Mac and will test if running Excel 2011 or higher.
        If Val(Application.Version) > 14 Then
            Call Mac_specific_function()
        End If
    End If
End Sub

You can also use this code to set a timer with up to 7 decimals, which works for both Mac and Windows:

'---------TIMER MACRO--------'
'PURPOSE: Determine how many seconds it took for code to completely run
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim StartTime As Double
Dim SecondsElapsed As Double
Dim minutesElapsed As Double

'Remember time when macro starts
StartTime = Timer

'*************************************
'------Start to run code--------'
'*************************************

        Your code goes here


'*************************************
'----------End code----------'
'*************************************


'Determine how many seconds code took to run

'If you want in seconds with two decimals, use this line:
'SecondsElapsed = Round(Timer - StartTime, 2)

'If you want with up to 7 decimals, use this line:
SecondsElapsed = Timer - StartTime

'Notify user how long the macro took
If SecondsElapsed > 60 Then
    minutesElapsed = Int(SecondsElapsed / 60)
    SecondsElapsed = Int(SecondsElapsed - (minutesElapsed * 60))

    Msgbox "This code ran successfully in " & minutesElapsed & " minutes and " & SecondsElapsed & " seconds", vbInformation

Else
    Msgbox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation

End If
Niklas Johansson
  • 54
  • 6