How to create an array of strings in VBA/Excel and

Question

Just as the question says. I would like to be able to make an array of strings in some VBA code, in this code I would like to make them a certain length. Then how would I pass them into a C++ DLL, in the DLL I would like to store a string in each element and make these elements accessible from the VBA afterwords. I have already done this for a single string, and for a double array as follows.

Dim myStr As String * sizeConst

Dim dataArray() As Double
ReDim dataArray(0 To (arrayLength - 1)) As Double

Then passing them to a DLL from within the VBA.

Public Declare Function myFunc _
Lib "PathToDLL.dll" _
(myStr As String, ByVal sizeConst As Integer, dataArray As Double, ByVal arrayLength As Long) As Long

Then in the DLL I can step through each element in the double array. However I don't know how to do this for a string array. I am unsure about the actual memory size of the strings I would be passing in from the VBA would they be of size sizeConst + 1? I need to know this to know how much I should increment to get to the next string element. Can someone show me how to declare a string array in VBA with a constant length for each element. Then how to pass that array to the DLL and how to increment to the next element in the string array in the DLL.

Answer 1

When you declare arr() as string in a Declared function, VB will send out an LPSAFEARRAY* (a double pointer, SAFEARRAY**) consisting of BSTRs (FADF_BSTR | FADF_HAVEVARTYPE). That is, the conversion to LPSTR will not be performed.

On the C++ side you would declare the parameter as LPSAFEARRAY* and use the SafeArray* family of functions to manipulate the data.

Note that you are responsible for freeing BSTRs you are going to replace, if you are going to replace them.

You will get all the information about the array size and dimensions from the LPSAFEARRAY, and each BSTR stores its string length.

---

Same happens when you declare arr() as string * some_length, but this time the SAFEARRAY will be of FADF_HAVEVARTYPE only, and SafeArrayGetVartype will return VT_ERROR. Each element of the safe array will be a preallocated blob of some_length wide chars (some_length * 2 bytes), with no zero terminator or string length stored separately. Arguably it's even easier to work with, because it's preallocated, you can simply fill the existing memory of each element.

---

If you were to use mere strings, without fixed length, you could also pass them in the same way you are passing the pointer to double:

declare function myFunc ... (byval strings as longptr, byval count as long)

dim s() as string
redim s(1 to 5)

myFunc varptr(s(lbound(s))), ubound(s) - lbound(s) + 1

Then on the C++ side you would receive BSTR* of the first string, and to advance to the next string you would add 1 to it like you would normally do with pointer math. You need to pass number of elements as a separate parameter to know how many strings there are.

This will not work for fixed-length strings.