API Toolkit with VBA/Excel

[sandcastler]

It seems I can't utilize epanet3.dll with Excel. My goal is to compare 2.0 to 3.0 and would like to 'port' my EXCEL setup for running models to 3.0.

It seems the EN_CreateProject function returns a EN_Project object/pointer (not sure). But regardless, i'm declaring functions from the dll and I continue to get 'file not found' error 48. Is the DLL, as written, 'friendly' with Excel or will I need a wrapper?

[sandcastler]

So Obviously, need to compile for 32bit which I fixed. However, curious on thoughts on to handle the EN_Project when interfacing from Excel. Should I create a function that somehow takes the Project location pointer and converts to a variable type that excel can handle?

[sandcastler]

To clarify. Declarations as below. Long seems to allow a project to be created (and returns value). When that value is placed in any other function, it crashes Excel. Any idea why it won't take the Long. My (very basic) understanding is that it wants a project pointer which I thought was returned by EN_CreateProject

See below, it crashes after creating the project.

Declare Function EN_createProject Lib "C:\EPANET3\epanet3.dll" () As Long
Declare Function EN_runEpanet Lib "C:\EPANET3\epanet3.dll" (ByVal inpfile As String, ByVal rptFile As String, ByVal outFile As String) As Integer
Declare Function EN_loadProject Lib "C:\EPANET3\epanet3.dll" (ByVal inpfile As String, ByRef prj As Long) As Long

Sub ExampleModel()

Dim En_Proj As Long

En_Proj = EN_createProject()
Err = EN_loadProject("C:\DummyModel\TestModel.inp", En_Proj) 'THIS CRASHES EXCEL!

EN_deleteProject (En_Proj) 'THIS CRASHES EXCEL!

End Sub

[LRossman]

Try using As Any instead of As Long for the project pointer.

[sandcastler]

I tried as Any and still crashed for

Declare Function EN_loadProject Lib "C:\EPANET3\epanet3.dll" (ByVal inpfile As String, ByRef prj As Any) As Long

It can only be defined as Any in the Function Declarations. It can't be defined as Any in the function return variable OR dim EN_Project AS Any

To add; The first thing I tried was

En_Proj=ENloadProject()

EN_deleteProject (En_Proj)

[LRossman]

I think I found what the problems are in getting the DLL to work with VBA (there's more than one):
First the #defines at the top of epanet3.h are incorrect. They should be:

#undef WINDOWS
#ifdef _WIN32
#define WINDOWS
#endif
#ifdef __WIN32__
#define WINDOWS
#endif

// --- define DLLEXPORT
#ifndef DLLEXPORT
  #ifdef WINDOWS
      #define DLLEXPORT __declspec(dllexport) __stdcall
  #elif defined(CYGWIN)
    #define DLLEXPORT __stdcall
  #elif defined(__APPLE__)
    #ifdef __cplusplus
      #define DLLEXPORT
    #else
      #define DLLEXPORT
    #endif
  #else
    #define DLLEXPORT
  #endif
#endif

Next, when you build the DLL you have to include a module definition file (/DEF epanet3.def option in the link command). The contents of the def file are:

LIBRARY EPANET3
EXPORTS
   EN_getVersion
   EN_runEpanet
   EN_createProject
   EN_cloneProject
   EN_deleteProject
   EN_loadProject
   etc.

Then your VBA declarations would look as follows:

Declare Function EN_createProject Lib "epanet3.dll" () As Long
Declare Function EN_deleteProject Lib "epanet3.dll" (ByVal Project As Long) As Long
Declare Function EN_loadProject Lib "epanet3.dll" (ByVal F1 As String, ByVal Project As Long) As Long
Declare Function EN_getElementIndex Lib "epanet3.dll" (ByVal Element As Long, ByVal ID As String, Index As Long, ByVal Project As Long) As Long
Declare Function EN_getNodeResult Lib "epanet3.dll" (ByVal ID As String, ByVal Index As Long, ByVal Result As Long, Value As Double, ByVal Project As Long) As Long
Declare Function EN_setNodeParam Lib "epanet3.dll" (ByVal ID As String, ByVal Index As Long, ByVal Param As Long, ByVal Value As Double, ByVal Project As Long) As Long
Declare Function EN_getNodeParam Lib "epanet3.dll" (ByVal ID As String, ByVal Index As Long, ByVal Param As Long, Value As Double, ByVal Project As Long) As Long
etc.

And finally, you would use the following VBA code to create a Project to work with:

Dim Pr As Long
Pr = EN_createProject

and pass Pr into all of your subsequent DLL function calls.

[sandcastler]

@LRossman this is great and way above my knowledge at this time so thanks for investigating. I'll make the updates ASAP and report back with success/failure. I'll also try to soak this in. Glad to be helpful in this endeavor and will continue to test things out.

[eladsal]

@LRossman don't know why I haven'e added these functions for the 2.x branch. Will check it out.

[sandcastler]

@LRossman I've finally had a some time to focus on this. However, I'm afraid I'm a bit over my head at the moment with the module definition file and how to integrate it into the compiled dll. I've created the epanet3.def and placed it in the /src folder (wasn't 100% sure where this should go). I've researched tirelessly to better understand and am still not where I need to be to properly create the DLL with the .def

How do I :

include a module definition file (/DEF epanet3.def option in the link command)

Is this an edit in the CmakeList.txt or is this an option I need to include when using the developr command line to create the DLL and EXE ( as exampled in the readme):

mkdir build && cd build 
cmake .. 
msbuild /p:Configuration=Release ALL_BUILD.vcxproj

Apologies for the most likely simple question.

[LRossman]

@sandcastler I think you have to modify the CMakeLists.txt file as follows:

Set (CMAKE_LINK_DEF_FILE_FLAG "/DEF:epanet3.def")
add_library(epanet3 SHARED ${epanet_lib_sources} ${epanet_lib_headers} epanet3.def)

[sandcastler]

@LRossman Thanks. Compiled and the exe works. However, still fails for EN_loadProject.
Wehn defined 'ByRef' EN_loadproject crashes
When defined 'ByVal' VBA sends back:

Returns Run-Time error '49' Bad DLL convention:

EN_createProject returns a Project (pointer?). Am i correct to assume this is an integer (Long) value so defining it in VBA as long should work? EN_createProject does not fail in VBA and returns a large number. However, simply executing `EN_deleteProject' immediately after creating it crashes.

Is there a disconnect between the returned long value in VBA from EN_createProject and subsequent functions that ask for a Project. From my understanding the pointer value of the project is what is returned by EN_createProject and then also needed for subsquent calls. However, It seems to be incorrect. Thoughts?

[LRossman]

I've attached a zip file containing the VBA code I used to test running the epanet3.dll in an Excel worksheet. Maybe that will help.
Epanet3-VBA.zip

Full disclosure: the DLL I used was built from my current local version of the code which contains many changes from the version posted on GitHub. I don't think they affect how the API functions interface with VBA, but I might be wrong. I hope to push my local changes to the repo in the near future.

[sandcastler]

Thanks. I've looked at your example and am doing verbatim (other than the full path of the epanet3.dll.) (FYI, didn't have the .frx file so couldn't see the form but used text editor to look at code.) Still fails. I can create a Project but subsequent functions that need Project don't work and give Error 49 (Bad DLL calling convention).

I circumvented pointing to a project by testing EN_runEpanet which actually ran! However, it still gives the Error49 "Bad DLL calling convention" after executing. It creates a report file (shows my trial results) and output file (I assume the output file is binary?)

My 'gut' is there may be some other slight modification you've made that I don't have OR I have my #define headers wrong (stdcall,ect..)

Here are my files that I reckon could be wrong.
epanet3.zip

I added the #defines verbatim to the epanet3.h (There weren't any #define prior).

Created the epanet3.def file and placed in the project folder (same location as the cmakelist.txt)
I've included the epanet3.cpp since I've added the EN_setLinkValue function and this is where the API functions are located.

I have 32-bit excel on a x64 machine. I've compiled these x64-Debug. If you are to push your local changes to the near future, I can be patient and wait; but I'd love to know where I've gone wrong or why this fails.

The positive light: I've gained more understanding on all of this 😣 🍷

[LRossman]

@sandcastler I'm confused as to which platform you're building the DLL for. Up at the top of this thread you said it was 32-bit but your last post mentioned x64 which is 64-bit. The DLL has to be for 32-bit. Sorry you're having such a hard time with this.

[sandcastler]

Excel is 32-bit version, machine is 64-bit.

However, I just put DLLExport infront of my EN_RunEpanet function and it worked!

I stumbled upon this which I believe you are the author for as well.

Is that the correct method. After defining the DLLEXPORT, place it before the name of each function so it can correct the name convention which allows VB to play nicely with it?

int DLLEXPORT EN_runEpanet(const char* inpFile, const char* rptFile, const char* outFile);

and then the same in the epanet3.cpp?

Edit: Just Tested EN_createProject and then EN_deleteProject. No errors! will edit others and test this out

[sandcastler]

Also, thanks for responding. 90% of me having a hard time is my lack of knowldege/experience with C++.

[LRossman]

Oh my gosh -- it'is the DLLEXPORT! It doesn't appear in epanet3.h in the repo master but I had added it to my own local version that I got to work with VBA:

#undef WINDOWS
#ifdef _WIN32
#define WINDOWS
#endif
#ifdef __WIN32__
#define WINDOWS
#endif

// --- define DLLEXPORT
#ifndef DLLEXPORT
  #ifdef WINDOWS
      #define DLLEXPORT __declspec(dllexport) __stdcall
  #elif defined(CYGWIN)
    #define DLLEXPORT __stdcall
  #else
    #define DLLEXPORT
  #endif
#endif

[sandcastler]

Though I was banging my head, I understood it better than you just telling me ;) I consider this a victory!

[sandcastler]

@LRossman Update: I've gone through the various function calls and think this is where I'll have to be patient with your push. So I can load the project and open the files but anything else crashes excel (see example code below with comments of functions crashing). It's weird that the NodeID can be retrieved but the NodeIndex crashes.. Also interesting that you need to define the correct size of your string before getting an ID (both and link). While the nodeID can be retrieved, really nothing else can (getting a count of nodes/links/elements crashes as well as an index). Oddly, even trying to use EN_runsolver crashes. Do any of these errors ring a bell?

I noticed the code you provided has different API function call names so I assume you've resolved these in your local copy. I took a look at the functions in the code and couldn't put my finger on why EN_getNodeID works but EN_GetNodeIndex or EN_getNodeType does not work.

Sub RunEPANET3()
/........./'previous code was called to open files and load project, they all work
NodeID = "01234567"
NodeID2 = "0123456789"
NodeID3 = "012345"
'err = EN_getCount(0, NodeCount, Pr) 'crashes
'EN_getNodeValue 1, 0, Elev, Pr'crashes
'err = EN_getNodeIndex(NodeID, NodeIndex, Pr) 'crashes
err = EN_getNodeId(1, NodeID, Pr) 'works;note the length of NodeID is 8; value=002GVYGK
err = EN_getNodeId(1, NodeID2, Pr) 'value = 002GVYGK�9 since the initial string length is longer (10),  the return value adds a ghost as well as keeps the last two alphanumeric values
err = EN_getNodeId(1, NodeID3, Pr) 'concatenates the returned ID value = 002GVY
LinkID = "123456"
LinkID2 = "123"
EN_getLinkId 1, LinkID, Pr 'same issue as getNodeID
EN_getLinkId 1, LinkID2, Pr ' same issue as getNodeID

err = EN_getNodeType(1, nType, Pr) 'crashes

Do
  err = EN_runSolver(currentTime, Pr) 'crashes
  err = EN_getNodeValue(NodeIndex, EN_HEAD, TankLevel, Pr) 'crashes
  Sheets("Interface").cell(1, 1) = TankLevel
  err = EN_advanceSolver(dt, Pr)
Loop While dt > 0
EN_writeReport Pr

EN_deleteProject Pr

End Sub