Transferring string arrays from VBA7 to C++

Question

I'm currently working on a project where I want to be able to manipulate and graph data with relative ease in Excel. However, a lot of the data involved exceeds Excels row limits considerably and it needs a degree of preprocessing before I can work with it.

To solve this I decided to write a backend in C++ to handle the preprocessing so that it's acceptable for Excel. The aim is to be able to pick several files within excel which are then sent to the .dll to be parsed, preprocessed, and have some averaging applied to make it more easily handled in excel. The data is then passed back to Excel to be graphed, etc.

I've already worked out how to send arrays of data from the .dll to Excel reliably. However, sending data from Excel to the .dll, usually an array of BSTR which hold file paths, has proven more difficult.

After reading through a few questions:

• How to create an array of strings in VBA/Excel and send it to a C++ DLL so that it can be iterated through in the DLL
• VBA/Excel, and C++ DLL, specifically problems with strings
• Calling C++ function from Excel and VBA using DLL created in C++

I thought the following code should work:

// lib.cpp

/*
* clear_log() & write_log(...) both write to a specified log file
*/

inline std::string WINAPI
bstr_string_convert( const BSTR bstr ) {
    const auto bstrlen = SysStringLen(bstr);
    const auto buffer = new char[bstrlen + 1];
    size_t n_char_converted{ 0 };
    if ( bstrlen > 0 ) {
        const auto err =
            wcstombs_s( &n_char_converted,
                        buffer, bstrlen + 1,
                        bstr, bstrlen );
    }
    else {
        buffer[0] = '\0';
    }
    return std::string{ buffer };
}

const std::string log_location{
    "C:\\\\path\\to\\log\\location\\"
};

void WINAPI
clear_log( const std::string_view filename ) {
    std::fstream log_file( log_location + filename,
                     std::ios_base::out | std::ios_base::trunc );
    log_file.close();
}

void WINAPI
write_log( const std::string_view filename, const std::string_view str ) {
    std::fstream log_file( log_location + filename,
                            std::ios_base::out | std::ios_base::app );
    log_file << str << "\n";
    log_file.close();
}

// This works
__declspec(dllexport) LPSAFEARRAY WINAPI
get_double_array( _In_ const LPSAFEARRAY* ppsa ) {
    CComSafeArray<double> csa(*ppsa);

    clear_log("double_log.txt");
    write_log( "double_log.txt",
               std::format("size: {}", csa.GetCount()) );
    for ( LONG i{ csa.GetLowerBound() }; i < csa.GetUpperBound(); ++i ) {
        write_log( "double_log.txt",
                   std::format("{}: {}", i, csa.GetAt(i)) );
    }

    return csa.Detach();
}

// This doesn't
__declspec(dllexport) LPSAFEARRAY WINAPI
get_str_array( _In_ const LPSAFEARRAY* ppsa ) {
    CComSafeArray<BSTR> csa(*ppsa);

    clear_log("string_log.txt");
    write_log( "string_log.txt",
               std::format("size: {}", csa.GetCount()));
    for (LONG i{ csa.GetLowerBound() }; i < csa.GetUpperBound(); ++i ) {
        write_log( "string_log.txt",
                   std::format( "{}: {}",
                                i,
                                bstr_string_convert(csa.GetAt(i))
                              ) );
    }

    return csa.Detach();
}

Declare PtrSafe Function send_string_array _
    Lib "path\to\dll" _
    Alias "_get_str_array@4" _
    (ByRef first_element() As String) As String()
    
Declare PtrSafe Function send_double_array _
    Lib "path\to\dll" _
    Alias "_get_double_array@4" _
    (ByRef ptr() As Double) As Double()

(Not sure what's making the syntax highlighting weird here)

Sub test()
    Dim doubles(3) As Double
    doubles(0) = 3.141592
    doubles(1) = 1235.12617
    doubles(2) = -1266.2346
    
    Dim d_result() As Double
    d_result = send_double_array(doubles)
    
    Dim n As Long
    For n = 0 To 2
        Debug.Print d_result(n)
    Next n
    
    Dim strings(3) As String
    strings(0) = "This"
    strings(1) = "is a "
    strings(2) = "test."
    
    Dim result() As String
    result = send_string_array(strings)
    
    For n = 0 To 2
        Debug.Print result(n)
    Next n

End Sub

Immediate Window:

 3.141592 
 1235.12617 
-1266.2346 
??
??
??

double_log.txt:

size: 4
0: 3.141592
1: 1235.12617
2: -1266.2346

string_log.txt:

size: 4
0: 
1: 
2: 

So my question is what am I missing that causes get_double_array(...) to work, but get_str_array(...) doesn't?

As an aside, why does CComSafeArray::GetCount() return 4 for an array declared with 3 elements? Seems like something weird is going on there.