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.VBA
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:VBA
Here’s an example of how to write a simple POST request:
Sub GETMac()
Dim Cmd As String
Cmd = "curl --get -H ""Content-Type"":""application/json"" -d '""' " & URL & ""
GETResult = executeInShell(Cmd)
End FunctionVBA
This script demonstrates the execution of a curl command to fetch data from the JSONPlaceholder API, a typical use case for testing API interactions.
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 FunctionParsing 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:- Download VBA-JSON from its GitHub.
- Import the JsonConverter.bas file into your VBA project.
- 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.
VBA
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 SubLimitations 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.Unlock Your Potential
Excel
Access
Power BI
