Excel user defined function working on Windows, but does not work on a Mac

[drWireMORE]

From another thread, a MVP offered a solution which gives you UTC time in a cell. A user defined function similar but different than now()

It works flawlessly on my windows workstation, but presents a value error on my Apple Mac Workbook. Microsoft learn has not revealed why or how to fix it. Do understand behind the covers, the numbers/numeric values may be different, but this is an approved call. Pretty vanilla. (Function is the body of work from D, MVP, Canada)

In lieu of a UTC date/time value, on a Mac it returns #VALUE!

Excel/M365. Excel/Microsoft 365 Version 2312 enterprise.

SWbemDateTime object (Wbemdisp.h) - Win32 apps

The SWbemDateTime object is a helper object to parse and set Common Information Model (CIM) datetime values.

learn.microsoft.com

Function GimmeUTC()
' Returns current date/time at UTC-GMT as an Excel serial date value
Dim dt As Object
Set dt = CreateObject("WbemScripting.SWbemDateTime")
dt.SetVarDate Now
GimmeUTC = dt.GetVarDate(False)
End Function

drW

 

[frabulator]

The problem comes from the 'WbemScripting' being part of the Windows framework, something that Mac OSX doesnt have.

Here is a previous post that should answer your question, but there might not be a straight forward solution to your problem. I have ran into several issues where Mac simply can not preform simple solutions inside of Windows, and viceaersa.

 

[drWireMORE]

The problem comes from the 'WbemScripting' being part of the Windows framework, something that Mac OSX doesnt have.

Here is a previous post that should answer your question, but there might not be a straight forward solution to your problem. I have ran into several issues where Mac simply can not preform simple solutions inside of Windows, and viceaersa.

Thank you - that explains it in part, a windows framework vs Mac OSX are different. I tried the solution including adding a PtrSafe preface and it was no-joy.

It does present me this option: check the OS
If Windows to use the UTC() or GimmeUTC() UDF
If MAC to use now() - a hard coded 5/24 or 6/24 as an approximation of UTC in the midwest, ignoring spring fwd, fall back. <<< but not very elegant.

It did help, just might be a dated approach, or I haven't tried enough combinations to make it work. TY