Issues with calling C++ DLLs from Excel if they depend on other libraries RRS feed

  • Question

  • I have created a 64 bit DLL in C++ as well as an Excel macro which calls the DLL by listing the full path to it in VBA. However, it only works with no issues if I open the containing Excel file in Visual Studio debug mode (by right clicking on the C++ project and selecting Debug->Start New Instance). 

    In order to get any dependent DLLs to work at all when called from Excel, including this case, it appears I have to do the following. Let's say the dependent DLL I want to call directly is "A" and an installed one it depends on is DLL "B". In order to call any function from the dependent DLL A, I first have to call any function from the independent DLL B. It doesn't matter which function or even if the function I'm calling from Excel directly depends on the DLL B. Using this hack, I can get many functions to run. If I open the Excel file directly from the File Explorer and try to run the DLL without this hack, it gives me assorted errors like VALUE or "cannot find the dll". 

    However even with this hack, it appears Excel is still having a problem when my functions depend on certain standard libraries such as fstream. The following code is called seamlessly in the Visual Studio debug instance of Excel, and returns -1 if I run the same spreadsheet directly from the File Explorer:

    C++ Code:

    int testReadFileVector(char** input, int columns){string filename = string(*input);std::fstream file;//create a stream for the, std::ios::in);//open the file to read inif (!file.is_open()){return -1;}file.close();//close the filereturn 0;} 

    The VBA that calls it:

    Private Declare PtrSafe Function testReadFileVector Lib _

    "[path to DLL]" (ByRef x As String, ByVal y As Long) As Long

    Function testRead(x As String, y As Long) As Long

        On Error GoTo Catch

            testRead = testReadFileVector(x, y)


            If Err <> 0 Then

                MsgBox (Err.Description)

            End If

    End Function

    When I search for the fstream library, it appears to be a plain "file" in  Program Files with no extension. This would be very awkward to try to call directly with a VBA filepath, and there is a good chance it would not work. There are other libraries like this that I would need to use as well.

    My question is twofold. Is there a less hacky way for Excel to recognize DLLs that are depended upon by DLLs you are calling. How am I supposed to get Excel to recognize standard C++ libraries such as fstream outside of a debug environment?

    There must be a way, because my team has made similar Excel-called input/output functions in .NET using an extension, and C++ should be more compatible for this purpose if I understand correctly.

    Tuesday, September 17, 2019 3:01 PM

All replies