Excel 2016 For Mac 64-Bit Update

[jfrench9]

Excel 2016 for Mac was upgraded to 64-bit from 32-bit versions. This has caused issues with the VBA-Web functionality in Excel 2016 for Mac. Microsoft acknowledges that this is an issue for many add-ins.

It appears that some of the core functionality that makes this work in Excel 2016 for Mac may not be configured for the new 64-bit versions that were just rolled out yesterday. Any idea on how to resolve this issue?

[timhall]

Hi @jfrench9 I don't currently have access to a Mac for testing, so I'm not sure what exactly the issues are or how to resolve them. Do you have more specifics (error messages, compilation issues, etc) on the issues that you are receiving?

[jfrench9]

When I run the add-on I created with VBA-Web integrated, I am getting this error:

I believe this error is referring to the declare statements in the Mac code:
https://github.com/VBA-tools/VBA-Web/blob/master/src/WebHelpers.bas#L147-L150
https://github.com/VBA-tools/VBA-Web/blob/master/src/WebHelpers.bas#L240-L243

If I add PtrSafe to the Declare statements similar to the Windows code, Excel crashes every time.

This worked just fine before the update referenced above was pushed out on Aug 22nd. There is quite a bit of chatter about Excel 2016 for Mac being upgraded from 32 bit to 64 bit, so I would imagine the libraries referenced in these declare statements are not playing nicely with the new architecture.

[rikochet]

@jfrench9 I am currently experiencing the same issue as you. Although I don't believe it's as simple as adding PtrSafe to the Declare statements.. It would appear that Long types may need to be changed to LongPtr..

i.e.
Private Declare PtrSafe Function utc_popen Lib "libc.dylib" Alias "popen" (ByVal utc_Command As String, ByVal utc_Mode As String) As Long

becomes

Private Declare PtrSafe Function utc_popen Lib "libc.dylib" Alias "popen" (ByVal utc_Command As String, ByVal utc_Mode As String) As LongPtr

I'm not familiar with VB though perhaps this link will help?

https://msdn.microsoft.com/library/gg264421.aspx?f=255&MSPPError=-2147217396

[rikochet]

@jfrench9, @timhall
Ok, so this solved the issue for me..

#If Mac Then
Private Declare PtrSafe Function web_popen Lib "libc.dylib" Alias "popen" (ByVal command As String, ByVal mode As String) As LongPtr
Private Declare PtrSafe Function web_pclose Lib "libc.dylib" Alias "pclose" (ByVal file As LongPtr) As Long
Private Declare PtrSafe Function web_fread Lib "libc.dylib" Alias "fread" (ByVal outStr As String, ByVal size As LongPtr, ByVal items As LongPtr, ByVal stream As LongPtr) As Long
Private Declare PtrSafe Function web_feof Lib "libc.dylib" Alias "feof" (ByVal file As LongPtr) As LongPtr
#End If

Public Function ExecuteInShell(web_Command As String) As ShellResult
#If Mac Then
    Dim web_File As LongPtr
    Dim web_Chunk As String
    Dim web_Read As Long

    On Error GoTo web_Cleanup

    web_File = web_popen(web_Command, "r")

    If web_File = 0 Then
        ' TODO Investigate why this could happen and what should be done if it happens
        Exit Function
    End If

    Do While web_feof(web_File) = 0
        web_Chunk = VBA.Space$(50)
        web_Read = web_fread(web_Chunk, 1, Len(web_Chunk) - 1, web_File)
        If web_Read > 0 Then
            web_Chunk = VBA.Left$(web_Chunk, web_Read)
            ExecuteInShell.Output = ExecuteInShell.Output & web_Chunk
        End If
    Loop

web_Cleanup:

    ExecuteInShell.exitCode = web_pclose(web_File)
#End If
End Function

I'm assuming if you want this to work on multiple Excel versions then might need to add a few more If VBA7 Then statements to either set the variable types to either Long or LongPtr..

[timhall]

@jfrench9 @rikochet thanks for the additional info! I'll make these changes shortly.

[av44]

@timhall do you plan to release these changes? I am experiencing the same issue and, if possible, would prefer to get an "official" update instead of patching myself.
Thanks!

Any update about this one ? I have the same issue and I don't even know where to patch it myself.

[general-albatross]

@timhall are you planning to pull this into main branch any time soon?

[timhall]

@blackphoenixinf I hadn't had an opportunity to test this on Mac as my Mac died a while back, but I was able to test this yesterday and will merge it in today.

[general-albatross]

@timhall if you need any help or feedback with testing this on Mac, I can give you a hand.

[markcollins]

I have a related issue after updating the most recent version of Office 365: Version 16.11.1 (180319) released 3/18/18. VBA is also version 7.1. When I run compile I get "Type Mismatch" error for "utc_Read". If I change it from LongPtr to Long, then "utc_popen" throws the error. This continues until I convert all LongPtr to Long.