Return string to VBA in MacOSX

Question

I'm currently writing a C dynamic library in MacOSX, the functions are then used in Excel (2011) using the VBA, e.g.:

Declare Function TestF Lib "path_to_lib:mylib.dylib" Alias "test" (ByRef res As String) As String

This works fine for functions that return integers etc., but I can't figure out how to pass strings back to VBA. My C function looks like this:

char* test(char *res)
{
  res = "test";
  return res;
}

but calling the function TestF in VBA as

Dim res As String
res = TestF(res)

crashes Excel... If I use a function that is provided by the MathLink (Mathematica) library, e.g.

#include "mathlink.h"
char* test(MLINK link, char *res)
{
  MLGetString(link, &res);
  return res;
}

this function successfully assigns a string to "res" that I can use in VBA. The information on MLGetString can be found here: http://reference.wolfram.com/mathematica/ref/c/MLGetString.html.

So apparently, this function is able to create a string and pass it to res and I can use the string in VBA then. So any ideas how this function does that, or how I could pass a string to VBA without having to use OLE etc.?

score 2 · Accepted Answer · edited Mar 29 '12 at 06:41

Strings in Visual Basic are represented using BSTR objects: they consist of a pointer to a null-terminated wide-character string (UTF-16) which is preceded by a 4-byte length prefix. For example, here's what the 4-character string "test" looks like in memory on a little-endian machine:

+-------------+-------+-------+-------+-------+-------+
| 08 00 00 00 | 74 00 | 65 00 | 73 00 | 74 00 | 00 00 |
+-------------+-------+-------+-------+-------+-------+
 Length:        't'     'e'     's'     't'     '\0'
 8 bytes
 (not including null)

Most importantly, the BSTR pointer itself points to the start of the string (the first 74 byte in this example), not the length prefix.

For reading BSTRs, you can just treat them as regular pointers to wide-character strings, and you'll be all right for the most part. But if you need to write/create them, it's a little trickier.

When creating BSTRs, you normally allocate them using SysAllocString (and its relatives) and deallocate them using SysFreeString. You need to figure out how Excel on OS X exposes those functions (likely through the OLE dynamic library), since they're not standard C. If you try to return a string that wasn't allocated through SysAllocString, then Excel is going to try to free it using SysFreeString, which will likely corrupt your heap and crash your program. So don't do that.

Also note that by default, a wchar_t has a size of 4 bytes, not 2, on OS X, so you can't use the wchar_t data type when dealing with BSTRs—you need to use an explicit 16-bit data type (such as uint16_t), or compile with the -fshort-wchar compiler option to force wchar_t to be 2 bytes, but be aware of the binary incompatibility issues that that may cause.

Very helpful, thanks! Since the other library I'm using in C (MathLink from Mathematica) is apparently able to pass on strings to VBA without using the OLE library, I was hoping that I can also do this in my functions? For example, the function [MLGetString](http://reference.wolfram.com/mathematica/ref/c/MLGetString.html) works fine when passing on the string argument to Excel through a C function. — Martin, Mar 29 '12 at 07:37
This is actually false. The memory layout of a `BSTR` in VBA under mac os x (office 2011 excel let's say) is not the one of a windows `BSTR` as you describe. It uses instead the memory layout of a null-terminated `char*` string. — Olórin, Mar 11 '16 at 11:11
See remark 2 in my question here : http://stackoverflow.com/questions/35940136/bstrs-and-variants-under-mac-os-x — Olórin, Mar 11 '16 at 12:22

mwag · Answer 2 · 2014-09-11T17:28:58.787

My original post was an attempt to imitate SysAllocStringByteLen() using malloc(), but this will fail when Excel tries to free the returned memory. Using Excel to allocate the memory fixes that issue, and is less code as well, e.g.:

in test.c:

#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#define LPCSTR const char *
#define LPSTR char *
#define __declspec(dllexport)
#define WINAPI

char *saved_string = NULL;
int32_t saved_len = -1;

#define _CLEANUP if(saved_string) free(saved_string)

__attribute__((destructor))
static void finalizer(void) {
  _CLEANUP;
}

int32_t __declspec(dllexport) WINAPI get_saved_string(LPSTR pszString, int cSize) {
  int32_t old_saved_len = saved_len;
  if(saved_len > 0 && cSize >= saved_len)
    strncpy(pszString, saved_string, saved_len);
  if(saved_string) {
    free(saved_string);
    saved_string = NULL;
    saved_len = -1;
  }
  return old_saved_len;
}

int32_t __declspec(dllexport) WINAPI myfunc(LPCSTR *pszString) {
  int len = (pszString && *pszString ? strlen(*pszString) : 0);
  saved_string = malloc(len + 5);
  saved_len = len + 5;
  sprintf(saved_string, "%s%.*s", "abc:", len, *pszString);
  return saved_len;
}

Compile the above with

gcc -g -arch i386 -shared -o test.dylib test.c

Then, in a new VBA module, use the below and run "test", which will prepend "abc:" to the string "hi there" and output the result the debug window:

Public Declare Function myfunc Lib "<colon-separated-path>:test.dylib" (s As String) As Long
Public Declare Function get_saved_string Lib "<colon-separated-path>:test.dylib" (ByVal s As String, ByVal csize As Long) As Long

Option Explicit

Public Function getDLLString(string_size As Long) As String
    Dim s As String
    If string_size > 0 Then
        s = Space$(string_size + 1)
        get_saved_string s, string_size + 1
    End If
    getDLLString = s
End Function

Public Sub test()
Debug.Print getDLLString(myfunc("hi there"))
End Sub

I added a C version of the above (no C++) at http://stackoverflow.com/questions/9833808/mac-office-2011-vba-and-dylib/25023122#25023122. Basically, I just removed the usage of C++ strings, which has the added benefit of not mangling the name when compiling with gcc. I am keeping the above answer as is because some may prefer C++, and the modification from the above to plain C is pretty easy anyway. — mwag, Sep 02 '14 at 17:31

score 0 · Answer 3 · edited May 23 '17 at 12:15

Actually, the memory layout of a BSTR in VBA under mac os x (office 2011 excel let's say) is not the one of a windows BSTR described in Adam Rosenfield's answer. It uses instead the memory layout of a null-terminated char * string.

See for instance remark 2 in my question here :

BSTR's and VARIANT's under... mac os x

Now, your problem is the following : you allocate memory for a char * on the c++/dylib side, under windows this memory will be deallocated on the VBA side, whereas mac OS X this is not possible, as the deallocation task cannot be passed to another process, as it can be under windows...

Return string to VBA in MacOSX

3 Answers3

Linked