(Solved) – How to determine the type of input variable in a password protected Excel add-in?


I am currently trying to fit some kinetic parameters for a Chemical plant simulation project. My main software is Aspen Plus. While it is a great environment, it’s parameter fitting capabilities are underwhelming, getting easily stuck in local minima.

My next option is to use Excel’s solver to do the parameter fitting. I’ve written some macros to do the formatting and found some VBA code to run the numerical integrator. Aspen has an Excel add-in to calculate some properties, which is great for the simulation. I’ve written the user defined function as the ODESolver requests; I’d like to include a function from the Aspen add-in within it. I’ve already added the reference. However when I try to call it from another function or as a simple Sub, it always returns either runtime error 9, 13 or 424, depending if I try to use a single cell or a range. Even worse, the add in is quite finicky and stops working when there is an error, so you have to close Excel and reopen the Workbook, which takes 1-2 minutes to load the Module.

I’ve included a simple demonstration of how it works within the spreadsheet.

Aspen Properties Add-In example

For instance, the VaporPressure function is described in the object editor as VaporPressure(ComponentList, temperature As String). Here’s a simple example that fails.

Sub PropertiesTest()

Pres = VaporPressure(Range("B1").Value, Range("D2").Value)
Range("B4").Value = Pres

End Sub

And I get error 13. I don’t really get this function. It might be a very obvious Dim problem but I’m too much of a newbie to really get it. Could someone help?

Leave a Reply

Your email address will not be published. Required fields are marked *