Old 32-bit DLL; cannot get calls working in 64-bit MS-Excel - PtrSafe or more?

[mcm91201]

I am not a programmer so please beer with me.


I am running:


Windows v10 Pro ver 1607 OS Build 14393.953
MS-Office 365 ProPlus Version 1701 Build 7766.2060 (there is a Star Trek joke in there somewhere)
MS-Excel 2016 MSO (16.0.7766.7080) 64-bit


I am trying to get an old dll running in new MS-Excel. I use a tool developed by the French space agency (CNES) to calculate satellite link parameters. The tool can be found at https://logiciels.cnes.fr/content/propa?language=en: . The tool consists of a 32-bit dll (propa.dll) and a VBA file (propa.bas). To get the tool to work you put the dll in the MS-Excel path (I stuff it in c:\windows\system32), open up MS-Excel, import the .bas (I stuff it in the same directory as the dll), and start using the dll using the functions defined in the bas file. This worked great all the way through Win v8.1 and MS-Excel 2013 (32-bit). Now upgraded to 64-bit and not so good with the dll/bas. I have reviewed as much as I can find on the web and have some hints but appear to not know what I am doing.


I have the original "VBA6" bas file and a modified "VBA7" bas file listed below. Here are questions comments running through my head; all I care about is getting it working but these questions may give me some understanding to someone else's solution.


VBA6 bas file has an Attribute line; no idea what it does. https://msdn.microsoft.com/EN-US/library/office/gg264344.aspx


The (#if VBA7 - Declare functions - #else) defines everything for VBA7. The (#else - Declare functions - #end if) defines everything for VBA6 or older.
http://www.mrexcel.com/forum/excel-questions/988981-32-bit-visual-basic-applications-64-bit-pc.html


When looking at the VBA code in MS-Excel Developer the VBA7 is compiled "black/blue". The VBA6 stuff is red "error". Trying to call the functions defined in the compiled portion of the bas file (VBA7) gives NAME? errors.


Deleting the #if-then-#else-#end if statements and the VBA6 declarations leaving the VBA7 stuff alone still does not get the VBA7 to work in MS-Excel.


I am guessing that I have two problems here: 1. VBA7 variables in/out of dll are wrong, and 2. if-then-else-endif is wrong.

All of the dll function call input and outputs are of type double. I assume that these are all safe in a 64-bit system or is there something I must do on top of adding the PtrSafe keyword? https://msdn.microsoft.com/en-us/library/office/gg251723.aspx


The aliases all end in an @## is that a clue about variables?

Any help appreciated

Mike


*****Original bas file starts here*****

Attribute VB_Name = "Propa"


Declare Function Agaz Lib "propa" _
           Alias "_Calcule_Agaz@32" (ByVal freq As Double, _
                 ByVal Elevation As Double, _
                 ByVal Temperature As Double, _
                 ByVal ro As Double) As Double


Declare Function Acloud Lib "propa" _
           Alias "_Calcule_Anuages@24" (ByVal freq As Double, _
                 ByVal Elevation As Double, _
                 ByVal L As Double) As Double


Declare Function Arain Lib "propa" _
           Alias "_Calcule_Apluie@64" (ByVal lat As Double, _
                 ByVal freq As Double, _
                 ByVal Elevation As Double, _
                 ByVal Indispo As Double, _
                 ByVal hstation As Double, _
                 ByVal hpluie As Double, _
                 ByVal R001 As Double, _
                 ByVal polar As Double) As Double


Declare Function Iscint Lib "propa" _
           Alias "_Calcule_Scintillation@56" (ByVal Nwet As Double, _
                 ByVal freq As Double, _
                 ByVal Elevation As Double, _
                 ByVal Indispo As Double, _
                 ByVal hstation As Double, _
                 ByVal eta As Double, _
                 ByVal Diam As Double) As Double


Declare Function Nwet Lib "propa" _
           Alias "_Calcule_coindice_refraction@16" (ByVal latitude As Double, _
                 ByVal longitude As Double) As Double


Declare Function TTC Lib "propa" _
           Alias "_Calcule_contenu_eau_liquide@24" (ByVal latitude As Double, _
                 ByVal longitude As Double, _
                 ByVal Indispo As Double) As Double


Declare Function rain_height Lib "propa" _
           Alias "_Calcule_hauteur_pluie@16" (ByVal latitude As Double, _
                 ByVal longitude As Double) As Double


Declare Function rain_intensity Lib "propa" _
           Alias "_Calcule_intensite_pluie@24" (ByVal latitude As Double, _
                 ByVal longitude As Double, _
                 ByVal Indispo As Double) As Double


Declare Function Temperature Lib "propa" _
           Alias "_Calcule_temperature@16" (ByVal latitude As Double, _
                 ByVal longitude As Double) As Double


Declare Function WVC Lib "propa" _
           Alias "_Calcule_vapeur_d_eau@16" (ByVal latitude As Double, _
                 ByVal longitude As Double) As Double


Declare Function iwvc Lib "propa" _
           Alias "_Calcule_contenu_vapeur_eau@24" (ByVal latitude As Double, _
                 ByVal longitude As Double, _
                 ByVal Indispo As Double) As Double


Declare Function Agaz_exceeded Lib "propa" _
           Alias "_Calcule_Agaz_depassee@40" (ByVal freq As Double, _
                 ByVal Elevation As Double, _
                 ByVal Temperature As Double, _
                 ByVal iwvc As Double, _
                 ByVal ro As Double) As Double


Public Declare Function version Lib "propa" _
           Alias "_version@0" () As Long

*****End of original bas file*****


Everything I read says that this edited bas file should do it.


*****Edited bas file*****

#If VBA7 Then
Declare PtrSafe Function Agaz Lib "C:\Windows\System32\propa" _
                   Alias "_Calcule_Agaz@32" (ByVal freq As Double, _
                         ByVal Elevation As Double, _
                         ByVal Temperature As Double, _
                         ByVal ro As Double) As Double


Declare PtrSafe Function Acloud Lib "C:\Windows\System32\propa" _
                   Alias "_Calcule_Anuages@24" (ByVal freq As Double, _
                         ByVal Elevation As Double, _
                         ByVal L As Double) As Double


Declare PtrSafe Function Arain Lib "C:\Windows\System32\propa" _
                   Alias "_Calcule_Apluie@64" (ByVal lat As Double, _
                         ByVal freq As Double, _
                         ByVal Elevation As Double, _
                         ByVal Indispo As Double, _
                         ByVal hstation As Double, _
                         ByVal hpluie As Double, _
                         ByVal R001 As Double, _
                         ByVal polar As Double) As Double


Declare PtrSafe Function Iscint Lib "C:\Windows\System32\propa" _
                   Alias "_Calcule_Scintillation@56" (ByVal Nwet As Double, _
                         ByVal freq As Double, _
                         ByVal Elevation As Double, _
                         ByVal Indispo As Double, _
                         ByVal hstation As Double, _
                         ByVal eta As Double, _
                         ByVal Diam As Double) As Double


Declare PtrSafe Function Nwet Lib "C:\Windows\System32\propa" _
                   Alias "_Calcule_coindice_refraction@16" (ByVal latitude As Double, _
                         ByVal longitude As Double) As Double


Declare PtrSafe Function TTC Lib "C:\Windows\System32\propa" _
                   Alias "_Calcule_contenu_eau_liquide@24" (ByVal latitude As Double, _
                         ByVal longitude As Double, _
                         ByVal Indispo As Double) As Double


Declare PtrSafe Function rain_height Lib "C:\Windows\System32\propa" _
                   Alias "_Calcule_hauteur_pluie@16" (ByVal latitude As Double, _
                         ByVal longitude As Double) As Double


Declare PtrSafe Function rain_intensity Lib "C:\Windows\System32\propa" _
                   Alias "_Calcule_intensite_pluie@24" (ByVal latitude As Double, _
                         ByVal longitude As Double, _
                         ByVal Indispo As Double) As Double


Declare PtrSafe Function Temperature Lib "C:\Windows\System32\propa" _
                   Alias "_Calcule_temperature@16" (ByVal latitude As Double, _
                         ByVal longitude As Double) As Double


Declare PtrSafe Function WVC Lib "C:\Windows\System32\propa" _
                   Alias "_Calcule_vapeur_d_eau@16" (ByVal latitude As Double, _
                         ByVal longitude As Double) As Double


Declare PtrSafe Function iwvc Lib "C:\Windows\System32\propa" _
                   Alias "_Calcule_contenu_vapeur_eau@24" (ByVal latitude As Double, _
                         ByVal longitude As Double, _
                         ByVal Indispo As Double) As Double


Declare PtrSafe Function Agaz_exceeded Lib "C:\Windows\System32\propa" _
                   Alias "_Calcule_Agaz_depassee@40" (ByVal freq As Double, _
                         ByVal Elevation As Double, _
                         ByVal Temperature As Double, _
                         ByVal iwvc As Double, _
                         ByVal ro As Double) As Double


Public Declare PtrSafe Function version Lib "C:\Windows\System32\propa" _
                   Alias "_version@0" () As Long


#Else


Declare Function Agaz Lib "C:\Windows\System32\propa" _
           Alias "_Calcule_Agaz@32" (ByVal freq As Double, _
                 ByVal Elevation As Double, _
                 ByVal Temperature As Double, _
                 ByVal ro As Double) As Double


Declare Function Acloud Lib "C:\Windows\System32\propa" _
           Alias "_Calcule_Anuages@24" (ByVal freq As Double, _
                 ByVal Elevation As Double, _
                 ByVal L As Double) As Double


Declare Function Arain Lib "C:\Windows\System32\propa" _
           Alias "_Calcule_Apluie@64" (ByVal lat As Double, _
                 ByVal freq As Double, _
                 ByVal Elevation As Double, _
                 ByVal Indispo As Double, _
                 ByVal hstation As Double, _
                 ByVal hpluie As Double, _
                 ByVal R001 As Double, _
                 ByVal polar As Double) As Double


Declare Function Iscint Lib "C:\Windows\System32\propa" _
           Alias "_Calcule_Scintillation@56" (ByVal Nwet As Double, _
                 ByVal freq As Double, _
                 ByVal Elevation As Double, _
                 ByVal Indispo As Double, _
                 ByVal hstation As Double, _
                 ByVal eta As Double, _
                 ByVal Diam As Double) As Double


Declare Function Nwet Lib "C:\Windows\System32\propa" _
           Alias "_Calcule_coindice_refraction@16" (ByVal latitude As Double, _
                 ByVal longitude As Double) As Double


Declare Function TTC Lib "C:\Windows\System32\propa" _
           Alias "_Calcule_contenu_eau_liquide@24" (ByVal latitude As Double, _
                 ByVal longitude As Double, _
                 ByVal Indispo As Double) As Double
                 
Declare Function rain_height Lib "C:\Windows\System32\propa" _
           Alias "_Calcule_hauteur_pluie@16" (ByVal latitude As Double, _
                 ByVal longitude As Double) As Double
                 
Declare Function rain_intensity Lib "C:\Windows\System32\propa" _
           Alias "_Calcule_intensite_pluie@24" (ByVal latitude As Double, _
                 ByVal longitude As Double, _
                 ByVal Indispo As Double) As Double


Declare Function Temperature Lib "C:\Windows\System32\propa" _
           Alias "_Calcule_temperature@16" (ByVal latitude As Double, _
                 ByVal longitude As Double) As Double
                 
Declare Function WVC Lib "C:\Windows\System32\propa" _
           Alias "_Calcule_vapeur_d_eau@16" (ByVal latitude As Double, _
                 ByVal longitude As Double) As Double
                 
Declare Function iwvc Lib "C:\Windows\System32\propa" _
           Alias "_Calcule_contenu_vapeur_eau@24" (ByVal latitude As Double, _
                 ByVal longitude As Double, _
                 ByVal Indispo As Double) As Double
                 
Declare Function Agaz_exceeded Lib "C:\Windows\System32\propa" _
           Alias "_Calcule_Agaz_depassee@40" (ByVal freq As Double, _
                 ByVal Elevation As Double, _
                 ByVal Temperature As Double, _
                 ByVal iwvc As Double, _
                 ByVal ro As Double) As Double


Public Declare version Lib "C:\Windows\System32\propa" _
           Alias "_version@0" () As Long


#End If

*****End of edited bas file*****

 

[Jaafar Tribak]

Hopefully, I seem to have found a fix for making the dll work in excel x64bit ... For some reason, loading the 64bit dll dynamically @ runtime seems to work ok. I have tested three functions namely : version, rain_intensity and temperature . You will need to do the same with the remaining functions in the same fashion.

Calling the dll functions dynamically requires that we wrap them in UDFs.
So now we have in the cells :
UDF_version instead of version
UDF_rain_intensity instead of rain_intensity
UDF_temperature instead of temperature

For example, the formula in Cell D20 will now become : =UDF_rain_intensity(D6;D7;0,01)

Here is a x64bit workbook demo :
demoprop.xls

Here is the code for the UDFs:

Option Explicit

'First, Save the propa64.dll in the Windows Syetem32 directory so the user won't need to pass a path.
'===================================================================================================

'Below are the three dll functions tested out.
'============================================
'Follow the same logic with the rest of the dll functions.
'========================================================
'Declare PtrSafe Function version Lib "propa64.dll" () As Long
'Declare PtrSafe Function rain_intensity Lib "propa64.dll" (ByVal latitude As Double, ByVal longitude As Double, ByVal Indispo As Double) As Double
'Declare PtrSafe Function Temperature Lib "propa64.dll" Alias "temperature" (ByVal latitude As Double, ByVal longitude As Double) As Double


'Loading the dll dynamically and calling the functions by their address inside UDT wrappers seems to work.
'========================================================================================================
Declare PtrSafe Function GetProcAddress Lib "kernel32" (ByVal hModule As LongPtr, ByVal lpProcName As String) As LongPtr
Declare PtrSafe Function LoadLibrary Lib "kernel32" Alias "LoadLibraryA" (ByVal lpLibFileName As String) As LongPtr
Declare PtrSafe Function FreeLibrary Lib "kernel32" (ByVal hLibModule As LongPtr) As Long
Declare PtrSafe Sub SetLastError Lib "kernel32.dll" (ByVal dwErrCode As Long)
Declare PtrSafe Function DispCallFunc Lib "OleAut32.dll" (ByVal pvInstance As LongPtr, ByVal offsetinVft As LongPtr, ByVal CallConv As Long, ByVal retTYP As Integer, ByVal paCNT As Long, ByRef paTypes As Integer, ByRef paValues As LongPtr, ByRef retVAR As Variant) As Long



'version api wrapper.
Public Function UDF_version() As Long
    Dim hLib As LongLong, hProc As LongLong
    hLib = LoadLibrary("propa64.dll")
    If hLib Then
        hProc = GetProcAddress(hLib, "version")
        If hProc Then
            UDF_version = DllStdCall(hProc, vbLong)
        End If
        FreeLibrary hLib
    End If
End Function

'rain_intensity api wrapper.
Public Function UDF_rain_intensity(ByVal latitude As Double, ByVal longitude As Double, ByVal Indispo As Double) As Double
    Dim hLib As LongLong, hProc As LongLong
    hLib = LoadLibrary("propa64.dll")
    If hLib Then
        hProc = GetProcAddress(hLib, "rain_intensity")
        If hProc Then
            UDF_rain_intensity = DllStdCall(hProc, vbDouble, latitude, longitude, Indispo)
        End If
        FreeLibrary hLib
    End If
End Function

'temperature api wrapper.
Public Function UDF_temperature(ByVal latitude As Double, ByVal longitude As Double) As Double
    Dim hLib As LongLong, hProc As LongLong
    hLib = LoadLibrary("propa64.dll")
    If hLib Then
        hProc = GetProcAddress(hLib, "temperature")
        If hProc Then
            UDF_temperature = DllStdCall(hProc, vbDouble, latitude, longitude)
        End If
        FreeLibrary hLib
    End If
End Function


'____________________________________________________ Helper function _______________________________________________________
#If Win64 Then
    Private Function DllStdCall( _
        ByVal pAddr As LongLong, _
        ByVal FunctionReturnType As Long, _
        ParamArray FunctionParameters() As Variant _
    ) As Variant
   
        Dim vParamPtr() As LongLong
#Else
    Private Function DllStdCall( _
        ByVal pAddr As Long, _
        ByVal FunctionReturnType As Long, _
        ParamArray FunctionParameters() As Variant _
    ) As Variant
 
        Dim vParamPtr() As Long
#End If


    Const CC_STDCALL As Long = 4
 
    If Not (FunctionReturnType And &HFFFF0000) = 0& Then Exit Function

    Dim pIndex As Long, pCount As Long
    Dim vParamType() As Integer
    Dim vRtn As Variant, vParams() As Variant

    vParams() = FunctionParameters()
    pCount = Abs(UBound(vParams) - LBound(vParams) + 1&)
    If pCount = 0& Then
        ReDim vParamPtr(0 To 0)
        ReDim vParamType(0 To 0)
    Else
        ReDim vParamPtr(0 To pCount - 1&)
        ReDim vParamType(0 To pCount - 1&)
        For pIndex = 0& To pCount - 1&
            vParamPtr(pIndex) = VarPtr(vParams(pIndex))
            vParamType(pIndex) = VarType(vParams(pIndex))
        Next
    End If
 
    pIndex = DispCallFunc(ByVal 0&, pAddr, CC_STDCALL, FunctionReturnType, pCount, vParamType(0), vParamPtr(0), vRtn)
    If pIndex = 0& Then
        DllStdCall = vRtn
    Else
        SetLastError pIndex
        Debug.Print Err.LastDllError
    End If

End Function


'_____________________________________________________ VBA TEST _____________________________________________________

Public Sub Test64()
    'Change func paremeters as required.
    MsgBox UDF_version
    MsgBox UDF_rain_intensity(46.27, 6.12, 0.01)
    MsgBox UDF_temperature(2, 4)
End Sub

 

[aravind kannan]

Hopefully, I seem to have found a fix for making the dll work in excel x64bit ...

I was searching for a solution to make the workbook work and few searches brought me here. Thanks for the help.
storing the workbook in a sharepoint showed error about not able to find the working directory. but storage in the local disks works fine.

Thanks again.

 

[SteradianRK]

Hopefully, I seem to have found a fix for making the dll work in excel x64bit ...

Jafaar, please let me apologise for the slow response. For some reason I was not notified of your reply!! I blame my employer's spam filter!

It looks amazing, thanks a lot for your persistence!! However, for some reason, I get #VALUE errors. When running the "Test64" macro, I see a "Out of stack space" error. Might you have any idea why this happens?

Admittedly my propa64.dll is in a bespoke path in My Documents, but I have modified the path to point at the correct location, so I can't see that that would make a difference...

 

[Jaafar Tribak]

Jafaar, please let me apologise for the slow response. For some reason I was not notified of your reply!! I blame my employer's spam filter!

No worries.

When running the "Test64" macro, I see a "Out of stack space" error. Might you have any idea why this happens?

I was getting that error when I was calling the propa64.dll functions the normal way. That runtime error stoped happening after I started calling the functions dynamically by address via GetProcAddress\DispCallFunc ... To be honest, I have no idea why this is happening as I don't know how the code inside the dll looks like.

Regards.

 

[SteradianRK]

@Jaafar Tribak -- Hello, I have followed your example and got everything working. Thank you so much for your help, it has been invaluable.

 

[Jaafar Tribak]

@Jaafar Tribak -- Hello, I have followed your example and got everything working. Thank you so much for your help, it has been invaluable.

I am happy that you got this working in the end, although I am just not sure how stable\reliable this workaround will be.
Anyways, thanks a lot for letting us know.

 

[SteradianRK]

Hi @Jaafar Tribak . If you are so inclined, I would be really interested to learn from you what exactly this "Helper" function is doing and what it's purpose is... Many thanks (no problem if you are too busy helping others )

So this section is determining whether to define Longs or LongLongs based on whether VBA is 32- or 64-bit.

#If Win64 Then
    Private Function DllStdCall( _
        ByVal pAddr As LongLong, _
        ByVal FunctionReturnType As Long, _
        ParamArray FunctionParameters() As Variant _
    ) As Variant
   
        Dim vParamPtr() As LongLong
#Else
    Private Function DllStdCall( _
        ByVal pAddr As Long, _
        ByVal FunctionReturnType As Long, _
        ParamArray FunctionParameters() As Variant _
    ) As Variant
 
        Dim vParamPtr() As Long
#End If

But what exactly are you doing here? I guess creating the API wrapper, but I can't quite follow the steps..!

    Const CC_STDCALL As Long = 4
 
    If Not (FunctionReturnType And &HFFFF0000) = 0& Then Exit Function

    Dim pIndex As Long, pCount As Long
    Dim vParamType() As Integer
    Dim vRtn As Variant, vParams() As Variant

    vParams() = FunctionParameters()
    pCount = Abs(UBound(vParams) - LBound(vParams) + 1&)
    If pCount = 0& Then
        ReDim vParamPtr(0 To 0)
        ReDim vParamType(0 To 0)
    Else
        ReDim vParamPtr(0 To pCount - 1&)
        ReDim vParamType(0 To pCount - 1&)
        For pIndex = 0& To pCount - 1&
            vParamPtr(pIndex) = VarPtr(vParams(pIndex))
            vParamType(pIndex) = VarType(vParams(pIndex))
        Next
    End If
 
    pIndex = DispCallFunc(ByVal 0&, pAddr, CC_STDCALL, FunctionReturnType, pCount, vParamType(0), vParamPtr(0), vRtn)
    If pIndex = 0& Then
        DllStdCall = vRtn
    Else
        SetLastError pIndex
        Debug.Print Err.LastDllError
    End If

End Function

 

[Jaafar Tribak]

@SteradianRK

But what exactly are you doing here? I guess creating the API wrapper, but I can't quite follow the steps..!

Yes. That DllStdCall function is indeed a convenient wrapper for DispCallFunc which is a function that is exported by the OleAut32 library.

DispCallFunc is normally used to call functions that are contained in COM Virtual Tables (AKA Interfaces) . It is very flexible in that it allows you to call the functions by their address (offset) in memory (ie:= Interface function Poistion multiplied by 4 bytes in 32bit or multiplied by 8 bytes in 64bit). Also, this api function allows you to choose the calling convention as vb's default is CC_STDCALL.

In fact, you can also perform function calls other than COM by passing a NULL to the first argument which is what I did in the above example.

So as you can see, this is a very versatile and useful low level function that can achieve things that are otherwise impossible in vb alone.

A less *intimidating* alternative to DispCallFunc that is sometimes used is the CallWindowProc api which also call functions by their address but you can only use it to call functions that do not exceed 4 parameters (Longs, LongLongs only) , plus it uses only STDCALL convention.

Now, the DllStdCall wrapper that I used in the code was simply to make the call to DispCallFunc api a bit easier and more friendly so the vba user doesn't have to repeatedly worry about computing the parameters types and pointers, the func return type or the number of parameters in each function call..

Here is a nice article I have found with lots of examples in vb6\vba.

I hope that helps a little bit with your question.


EDIT: BTW, did you manage, in the end, to make the other x64 propa.dll functions work ? ( other than version, temperature and rain_intensity)

 

[SteradianRK]

Gosh thanks a lot for the detailed answer. Looks like I have some reading to do!

EDIT: BTW, did you manage, in the end, to make the other x64 propa.dll functions work ? ( other than version, temperature and rain_intensity)

Yes, it was trivial following the example you provided for version, temperature & rain_intensity. I am sure that other readers of this chain will also be able to do the same. I also enhanced the code by adding input error checking (some of the functions are not valid outside of specific ranges etc.) and extra convenient functions (more related to the "physics" aspects rather than the "computer programming" aspects!)

 

[aravind kannan]

Gosh thanks a lot for the detailed answer. Looks like I have some reading to do!

Yes, it was trivial following the example you provided for version, temperature & rain_intensity. I am sure that other readers of this chain will also be able to do the same. I also enhanced the code by adding input error checking (some of the functions are not valid outside of specific ranges etc.) and extra convenient functions (more related to the "physics" aspects rather than the "computer programming" aspects!)

Hello, in the rain intensity, they take the value of unavialability as 0.01%. the say the model they use for rain intensity as the reason for this 0.01% unavailability. But for calculating the rain attenuation, it takes this rain intensity into account and the user defined unavailbility as well. So, in total, it takes two unavailability factors into account. and this 0.01% seems too stringent. what do you think about this ? thanks