0

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?

braX
  • 11,506
  • 5
  • 20
  • 33
Ottmar Schaub
  • 45
  • 6
  • `ComponentList` should be av`Variant` type (which may be vague...), but if accepts a ranges value (seen in your clip), it is probably to be an array or a range. Then, where is your temperature parameter in the `Sub` you try? It would be more relevant to show us the whole function code. Only looking at what you has shown, I would try `Pres = VaporPressure(Range("B1:C1"),CStr(0.2))`. If it does not work, try `Pres = VaporPressure(Range("B1:C1").value,CStr(0.2))`. Without seeing the whole function it is difficult to imagine the most appropriate value for `ComponentList`... – FaneDuru May 31 '20 at 10:47
  • If not clear enough what I suggested above, if my suggestions do not work, either, please edit your question and show us the whole function code. – FaneDuru May 31 '20 at 10:56
  • That's my problem. It's a password protected Add-in, so I cannot view the code of the functions. – Ottmar Schaub May 31 '20 at 22:07
  • For your first suggestion, I get error 424, Object required. For the second suggestion, I get error 9, Subscript out of range. Is there anything else I might do or try to help? – Ottmar Schaub May 31 '20 at 22:28
  • Write me on the profile mail, please. – FaneDuru Jun 01 '20 at 01:37
  • It looks, the second variant uses correct parameters type, but the last parameter is out of the accepted range. Try, please `Pres = VaporPressure(Range("B1:C1").value,CStr(0.1))` or `Pres = VaporPressure(Range("B1:C1").value,0.1)`. It the add-in in discussion a VBA one (xlam)? – FaneDuru Jun 01 '20 at 06:02
  • I got error 9 in both cases. The add-in's file is called Aspen Properties.xla Thank you for your patience. – Ottmar Schaub Jun 01 '20 at 08:22
  • Can you send me a mail. Now I am busy and I will give you instructions by mail... – FaneDuru Jun 01 '20 at 08:41
  • Already did. The subject is stack overflow question. – Ottmar Schaub Jun 01 '20 at 09:04
  • I did not correctly observed what you were written looking at your clip... Try, please `Pres = VaporPressure(Range("B1:C1").value, CStr(Range(“D2”).value))`. 373 it is more probably to be in the range of used pressure... I saw 0.2 instead of 'D2'. That's why the above proposals... – FaneDuru Jun 01 '20 at 18:45

0 Answers0