0

I have written a function in a C++ library that should take in a string from a given cell in Excel, then reverse the string, and return the reversed string. I used BSTR-to-stdstring and stdstring-to-BSTR found in this previously asked question (link: BSTR to std::string (std::wstring) and vice versa).

The error arises when I try to actually call the function from my .dll in Excel. For whatever reason, the .dll function does not appear to be properly returning a BSTR that Excel can display. When I input "test" into a cell and use the reverse_word function, I expect it to return "tset", but instead the function does not appear to return anything at all besides an empty string.

Here is my header file and .cpp code in Visual Studio for the .dll.

The header file:

#pragma once

#ifdef EXCELMATHLIB_EXPORTS
#define EXCELMATHLIB_API __declspec(dllexport)
#else
#define EXCELMATHLIB_API __declspec(dllimport)
#endif

extern "C" EXCELMATHLIB_API BSTR reverse_word(BSTR excelW);

The .cpp file:

BSTR WINAPI reverse_word(BSTR excelW)
{
    std::string str = ConvertBSTRToMBS(excelW);
    reverse(str.begin(), str.end());
    return ConvertMBSToBSTR(str);
}

std::string ConvertBSTRToMBS(BSTR bstr)
//function found at: https://stackoverflow.com/questions/6284524/bstr-to-stdstring-stdwstring-and-vice-versa 
{
    int wslen = ::SysStringLen(bstr);
    return ConvertWCSToMBS((wchar_t*)bstr, wslen);
}

std::string ConvertWCSToMBS(const wchar_t* pstr, long wslen)
//https://stackoverflow.com/questions/6284524/bstr-to-stdstring-stdwstring-and-vice-versa 
{
    int len = ::WideCharToMultiByte(CP_ACP, 0, pstr, wslen, NULL, 0, NULL, NULL);

    std::string dblstr(len, '\0');
    len = ::WideCharToMultiByte(CP_ACP, 0 /* no flags */,
        pstr, wslen /* not necessary NULL-terminated */,
        &dblstr[0], len,
        NULL, NULL /* no default char */);

    return dblstr;
}

BSTR ConvertMBSToBSTR(const std::string& str) 
//online at https://stackoverflow.com/questions/6284524/bstr-to-stdstring-stdwstring-and-vice-versa 
{
    int wslen = ::MultiByteToWideChar(CP_ACP, 0 /* no flags */,
        str.data(), str.length(),
        NULL, 0);

    BSTR wsdata = ::SysAllocStringLen(NULL, wslen);
    ::MultiByteToWideChar(CP_ACP, 0 /* no flags */,
        str.data(), str.length(),
        wsdata, wslen);
    return wsdata;
}

This is my excel VBA module code:

Private Declare PtrSafe Function reverse_word_cpp _
    Lib "C:\Users\artil\source\repos\ExcelMathLib\x64\Debug\ExcelMathLib.dll" Alias "reverse_word" _
    (ByRef str As String) As String

Function reverse_word(word As String) 'reverse word wrapper function
    Dim result As String
    result = reverse_word_cpp(word)
    MsgBox result
    reverse_word = StrConv(result, vbFromUnicode)
End Function

I attempted to call the wrapper VBA function directly from a cell (i.e. =reverse_word(A1)). A1 is a cell containing the text "test". The MsgBox result and reverse_word = StrConv(result, vbFromUnicode) both display empty messages/cells. I believe the issue is in my reverse_word_cpp function.

I know that the ConvertMBSToBSTR function does work, at least for a string written directly in C++.

return ConvertMBSToBSTR("test")

results in an output that function = StrConv(..., vbFromUnicode) displays correctly in Excel.

azncookiethief
  • 1
  • 3
  • What is your reason for doing this? Its simple enough to do this reversal in VBA. – freeflow Jun 06 '23 at 17:02
  • 1
    @freeflow Yeah, that is true, but I was trying to experiment with getting VBA to talk with string functions in C++ dlls. Right now this is more or less just practice. It definitely does not have a great practical application. I want to understand how to take in strings from Excel's VBA, do operations on them in C or C++, then return an output to Excel. – azncookiethief Jun 06 '23 at 18:02
  • Yey, but why? If you just want speed consider twinBasic which is a VBA replacement. Creating a dll in twinBasic that can be called from VBA is a doddle. https://twinbasic.com/preview.html – freeflow Jun 06 '23 at 18:10

0 Answers0