Is it possible to call CoolProp from Excel VBA?

[Fausto A. A. Barbuto]

Hi,

I've been trying to call PropsSI from VBA but no joy so far. I wrote a typical function like:

Public Function ExecCoolProp()
    Dim Wksht1 As Worksheet

    Set Wksht1 = Worksheets("Sheet1")

    Wksht1.Range("F10") = CoolProp.PropsSI("d(T)/d(P)|H", "T", 300, "P", 350000, "CH4")
End Function

that should write (dT/dp) for constant H and (p,T) = (350000 Pa, 300 K) into cell F10 of worksheet Sheet1. But I get a runtime error 424 (Object required) instead. However, if I write

=CoolProp.PropsSI("d(T)/d(P)|H", "T", 300, "P", 350000, "CH4")

into any regular worksheet cell, it returns a value that seems valid (4.3079E-06).

What's the problem? Function ExecCoolProp() should "see" CoolProp and its procedures. But that doesn't seem to be the case. Is that because CoolProp is "public" for cell (worksheet) use but "private" at VBA level?

Thanks for any enlightenment on this topic.

Fausto

[Clifford Bradford]

to call an Excel function from VBA you need to write something like:

Application.WorksheetFunction."functionname"

https://learn.microsoft.com/en-us/office/vba/excel/concepts/events-worksheetfunctions-shapes/using-excel-worksheet-functions-in-visual-basic