Creating API requests and handling JSON in VBA for Mac is similar to Windows but with some limitations and differences, primarily due to the different objects available for HTTP requests and the process of adding libraries.

Making API Calls in VBA for Mac

On Mac, the Microsoft XML, v6.0 library (MSXML2) is not available, making network requests particularly tricky. However, a novel solution lies in directly interfacing with macOS’s Unix layer using system-level functions from the libc.dylib library, a standard component of macOS. This approach involves using C library functions like popen, pclose, fread, and feof within VBA, enabling the execution of Unix commands and handling of file streams.

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

Function executeInShell(web_Command As String) As String

    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
        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 = executeInShell & web_Chunk
        End If
    Loop

web_Cleanup:

    web_pclose (web_File)

End Function

How the Code Works

Leveraging C Library Functions:

The VBA code starts by declaring functions from libc.dylib: popen, pclose, fread, and feof. These are essential for executing Unix commands and managing output streams. The PtrSafe keyword ensures compatibility with 64-bit versions of Excel.

Executing Unix Commands with popen:

popen opens a Unix shell in ‘read’ mode, allowing VBA to execute a command and read its output. This is where a curl command for an API request is passed.

Reading Command Output:

The code employs a loop with feof to read the output stream. fread is used to read the output into a VBA string, chunk by chunk. The size of these chunks can be adjusted as needed.

Closing the Process:

The loop continues until the end of the file (EOF) is reached. pclose then closes the process and returns the exit code of the command.

Function Usage:

The function is versatile and can capture the output of any shell command. For API calls, a curl command specific to the desired API is used.

Example: Making an API Call

Here’s an example of how to write a simple GET request:

Sub GETMac() 

  Dim Cmd As String
  
  Cmd = "curl --get -H ""Content-Type"":""application/json"" -d  '""' " & URL & ""
  
  GETResult = executeInShell(Cmd)
    
End Function

Here’s an example of how to write a simple POST request:

Sub POSTMac() 

  Dim Cmd As String
  
  jsonString = "{""name"":""John Doe"",""age"":30,""city"":""New York""}"
  
  Cmd = "curl  -X POST -H ""Content-Type"":""application/json"" -d  '" & JsonString & "' " & URLCompleta & ""
  
  POSTResult = executeInShell(Cmd)

End Function

This script demonstrates the execution of a curl command to fetch data from the JSONPlaceholder API, a typical use case for testing API interactions.

Parsing JSON in VBA for Mac

To parse JSON in VBA on a Mac, you can still use the VBA-JSON library. The process of adding the library to your project remains the same:

1. Download VBA-JSON from its GitHub.
2. Import the JsonConverter.bas file into your VBA project.
3. Since Microsoft Scripting Runtime is not available on Mac, you’ll have to use late binding or modify the library for Mac compatibility as suggested in its documentation.

Once added, you can parse the JSON as shown in the previous examples:

Sub ParseJSONExample()
    Dim jsonString As String
    Dim jsonObject As Object

    ' Example JSON string
    jsonString = "{""name"":""John Doe"",""age"":30,""city"":""New York""}"

    ' Parse the JSON string
    Set jsonObject = JsonConverter.ParseJson(jsonString)

    ' Use the jsonObject
    MsgBox "Name: " & jsonObject("name") & ", Age: " & jsonObject("age")
End Sub

Limitations and Considerations

• The MacScript function with AppleScript is less robust than MSXML2 and might have limitations with complex requests, headers, or methods.
• Error handling in AppleScript is less straightforward compared to VBA.
• Always test your code thoroughly on a Mac, as there can be differences in how VBA code executes compared to Windows.

Conclusion

This method unlocks a new level of functionality for Mac VBA users, especially for tasks like API interactions that are otherwise challenging in this environment. By directly interacting with the Unix layer of macOS, VBA scripts can be significantly more powerful and versatile. This approach is especially useful for users who need to integrate external data or services into their Excel workflows on macOS.

Follow us on Social Media

facebooklinkedin

Unlock Your Potential

Excel

Access

Power BI

Help us grow the project