Remove chr(0) from (c++ BSTR) string in VBA

Question

I've written a c++ function in a DLL which exports a string to a VBA program:

BSTR _stdcall myFunc()
{
    CRegKey Key;
    CString sValue;
    BSTR Str;

    LONG nA = Key.Open(HKEY_LOCAL_MACHINE, _T("[group path goes here]"), KEY_READ);
    ULONG nValueLength = 0;
    LONG nB = Key.QueryStringValue(_T("[key I want to read goes here]"), NULL, &nValueLength);

    if (nValueLength > 0)
    {
        LONG nC = Key.QueryStringValue(_T("[key I want to read goes here]"), sValue.GetBufferSetLength(nValueLength - 1), &nValueLength);
    }

    Str = _bstr_t(sValue.AllocSysString(), false);

    return Str;

Now Str is something like a version number: let's say "4.10.122".
If I call the function from VBA, I receive instead "4 . 1 0 . 1 2 2", where the "spaces" between each characters are NULL (in VBA they are Chr(0)).

I don't like the idea of having to use the Replace function in my VBA code, so is there any way I can include that step in my c++ code?

EDIT: below the code I'm using to call the function in VBA:

Private Declare Function myFunc Lib "[Path of my DLL here]" () As String

Sub Return_string()

Dim a As String

a = myFunc()

End Sub

That is normal for a utf-16 encoded string, as a BSTR should be. Seems you did not declare the function correctly in the VBA code. Beware of the bug, the `_bstr_t` wrapper calls SysFreeString() just before the function returns. Just get rid of it completely, SysAllocString is enough. — Hans Passant, May 08 '17 at 07:26
Thanks for pointing the bug out. I have just written how I call the function from VBA, but even with your correction about `_bstr_t` , it returns the same string containing `Chr(0)`s. — Noldor130884, May 08 '17 at 08:01
I don't know what kind of incantation is required in VBA to ensure it expects a BSTR function return, I only see docs for argument types. Declaring the function `Unicode` might help, but do test it by calling it a gazillion times so you know there is no memory leak. — Hans Passant, May 08 '17 at 08:17
Is there any other way basically to export a `CString` to VBA? This is the only viable one I found... — Noldor130884, May 08 '17 at 08:33

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

1

Far from being able to help you with this specific need but curious about the subject, if i were to come up with a solution i would start from this answer https://stackoverflow.com/a/43423527/781933

Update

According to the above mentioned @SimonMourier detailed answer, and further readings, you should consider string content encodings. The context of the DLL - VBA marshalling is to be considered and depends also on VBA function declaration (stated by @HansPassant).

From this MSDN documentation:

When a VBA user-defined function is declared as taking a String argument, Excel converts the supplied string to a byte-string in a locale-specific way.
If you want your function to be passed a Unicode string, your VBA user-defined function should accept a Variant instead of a String argument.

So if you use:

Private Declare Function myFunc Lib "[Path of my DLL here]" () As String

conversion from UNICODE to ANSI is needed via StrConv

Dim str As String

str = StrConv(myFunc(), vbFromUnicode)

Otherwise you should get rid of StrConv but using DLL exported BSTR with this declaration:

Private Declare Function myFunc Lib "[Path of my DLL here]" () As Variant

edited Jun 20 '20 at 09:12

Community

1
1

answered May 08 '17 at 06:53

rfb

1,107
1
7
14

I've read that question when I was creating my function, but still I didn't get an easy way of simplifying my string export... – Noldor130884 May 08 '17 at 08:04
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - [From Review](/review/low-quality-posts/16059923) – mkl May 08 '17 at 09:50
I'm working on a more detailed answer. Going on the "in search of an answer" approach that could be, i think, a good way to investigate problems and find answers. – rfb May 08 '17 at 09:56
@mkl hope that my answer could be ammisible and down-vote cancelled. – rfb May 08 '17 at 12:40
@rfb I'm sorry, the downvote was not by me, and I cannot see by whom it was, either. But your edit clearly improves the answer, so if the downvoter sees your answer again, he may well remove it. – mkl May 08 '17 at 12:54
thank you for your reply. I could agree my answer was debatable but i was really considering an "on work" situation. My fault: it could has been better staged in a comment. – rfb May 08 '17 at 12:58
This worked. I had to return a `VARIANT` but to do that I had to store the `.bstrVal` inside a `VT_BSTR`-type variable. – Noldor130884 May 09 '17 at 06:11
glad to be of help to you – rfb May 09 '17 at 11:13

score 1 · Answer 2 · answered May 08 '17 at 15:00

The problem stems from the Declare statement. When interacting with such functions and strings in particular, VBA will always perform an implicit ANSI to Unicode conversion in both directions. In your case, VBA expects an ANSI string as the return value, which it can then expand into a Unicode equivalent.

To deal with this, you'll have to return an ANSI string and resort to SysAllocStringByteLen:

CStringA sValueA(sValue);
Str = SysAllocStringByteLen(sValueA.GetBuffer(), sValueA.GetLength());

As an alternative, you may also embed a type library inside your DLL. This would omit the automatic string conversion.

Remove chr(0) from (c++ BSTR) string in VBA

2 Answers2

Update