COFE functions in Excel - Get/set

[HenkF]

Hi Jasper,
as you indicated a while ago, apart from getting data from a flowsheet, it also is possible to set values from external sources. I'm trying to make flowsheets and results available to my colleagues, with some opportunities of changing some data: T,P and feed compositions (to some extent).

I would prefer an Excel-based I/O, where results may be presented, and to some extent input data (temperature, pressure, feed fractions, a U.A factor in heat exchangers, etc) changed.
The best thing: most of it already is available!
* a good development environment for model creation and basic data reference. To me COFE is a perfect hatstand to build upon.
* COFE provides an excellent Excel integration
* From other sources OPC integration, and real time data, is available

Only one thing lacking : the setters for temperature, pressure, feed fractions, and e.g. U.A for a HeatX? Could you please give me a hint, preferably some code snippets, on how to make this working?

Wish you and all board members a happy and loving New Year!
Henk

[jasper]

Mostly you can just copy a "getter" and modify it into a "setter" by editing the VBA (you may want to do this in the template, or directly in the xls).

For example, you can change

Code: Select all

Public Function COFE_GetStreamTemperature(streamID)
Dim str As ICOFEStream
Dim v
Dim str1 As ICapeThermoMaterialObject
On Error GoTo handleError
Set str = COFE_GetStream(streamID)
On Error GoTo noMaterialStream
Set str1 = str
On Error GoTo matError
v = str1.GetProp("temperature", "overall", Empty, "", "")
COFE_GetStreamTemperature = v(0)
Exit Function
handleError:
COFE_GetStreamTemperature = Err.Description
Exit Function
noMaterialStream:
COFE_GetStreamTemperature = "Not a material stream"
Exit Function
matError:
COFE_GetStreamTemperature = COFE_GetCOFEDoc.GetError(str1, Err.Number)
End Function

into a temperature setter:

Code: Select all

Public Sub COFE_SetStreamTemperature(streamID, T as double)
Dim str As ICOFEStream
Dim v() as Double
Dim str1 As ICapeThermoMaterialObject
On Error GoTo handleError
Set str = COFE_GetStream(streamID)
On Error GoTo noMaterialStream
Set str1 = str
On Error GoTo matError
Redim v(0)
v(0) = T
str1.SetProp("temperature", "overall", Empty, "", "", v)
Exit Function
handleError:
MsgBox Err.Description
Exit Function
noMaterialStream:
MsgBox "Not a material stream"
Exit Function
matError:
MsgBox COFE_GetCOFEDoc.GetError(str1, Err.Number)
End Sub 

Notice that v is an array of doubles here, as per CAPE-OPEN convention.

This is a Sub, not a Function, so cannot be directly called from a cell. But you could make a function that gives you the desired flowsheet outputs after providing the flowsheet with the desired inputs and run the flowsheet. For running the flowsheet you could do

Code: Select all

Public Sub COFE_CalculateFlowsheet()
Dim COFEDocument As COFE_Document
Set COFEDocument = COFE_GetCOFEDoc
COFEDocument.SolveFlowsheet
End Sub

Does that meet your requirements? Note that the COFE_Document object is described here:

http://cocosimulator.org/index_help.php ... cument.htm

And the very best wishes for 2015 to you.

[jasper]

Also note that you are not bound to Excel for doing this; any environment that easily supports automation, for example VB.net, can be used to drive COFE.

[HenkF]

Hi Jasper,
I will try this option and report the results. I'm aware of other, and maybe more convenient environments that suit my needs, but I like to incorporate thie options within a VBA environment.
Many thanks!
regards, Henk

[kevin032]

Good morning all,

I desperatly try to set parameters on my COCO file with VBA but I don't manage to do so.

I tried the SetStreamTemperature you defined in previous post but it returns an error : str1.SetProp("temperature", "overall", Empty, "", "", v) (= is needed).
Moreover, I don't manage to make this subroutine accessible in the Macros windows.

Besides, I noticed that the COFE_CompleteCOFERecalc() Macro doesn't seem to work well on my file. Everytime, I set MANUALLY a parameter (for instance a Temperature in the first stream), It doesn't converge and I get the following message :

warning: Simulation Context error in ICapeMaterialTemplateSystem::CreateMaterialTemplate: Invalid stream type name
warning: Material object error in GetComponentConstant: No data
warning: Material object error in GetComponentConstant: One or more missing values
warning: Material object error in GetComponentConstant: No data
warning: Material object error in GetComponentConstant: One or more missing values
warning: Material object error in GetComponentConstant: No data
warning: Material object error in GetComponentConstant: One or more missing values
warning: Simulation Context error in ICapeMaterialTemplateSystem::CreateMaterialTemplate: Invalid stream type name
warning: Material object error in GetComponentConstant: No data
warning: Material object error in GetComponentConstant: One or more missing values
warning: Material object error in GetComponentConstant: No data
warning: Material object error in GetComponentConstant: One or more missing values
warning: Material object error in GetComponentConstant: No data
warning: Material object error in GetComponentConstant: One or more missing values
warning: Simulation Context error in ICapeMaterialTemplateSystem::CreateMaterialTemplate: Invalid stream type name
warning: Material object error in GetComponentConstant: No data
warning: Material object error in GetComponentConstant: One or more missing values
warning: Material object error in GetComponentConstant: No data
warning: Material object error in GetComponentConstant: One or more missing values
warning: Material object error in GetComponentConstant: No data
warning: Material object error in GetComponentConstant: One or more missing values


Would someone be able to help me or to send me a working file with setter so that I could be inspired xD.

Thank you very much for your help.
Best regards,
Kevin

[jasper]

Please note that setting data on the flowsheet is not always safe: https://www.cocosimulator.org/index_hel ... mation.htm

Setting parameter values on unit operations should mostly be ok though.

Can you provide me with the exact error message?

The Macro window shows all public functions that have no arguments. Make sure your macro is public and has no arguments. If the macro is public but does have arguments, you can still use it as a formula from within an Excel cell.

Can you attach an Excel worksheet that demonstrates the problems with COFE_CompleteCOFERecalc?

[jasper]

Also note that there is another manner to manipulate parameters in COFE and evaluate the flowsheet. COFE documents are stored as XML files inside a zip file. And COFEStand allows for calculation of the flowsheet in a programmatic manner. These two features combined make that you can make a script (e.g. in Python) that unzips the XML, manipulates the XML (e.g. changes parameter values in the XML), rezips the XML and runs the flowsheet using COFEStand. Then unzip the XML and parse to get the results.

An example python scipt that unzips, parses and inspects the XML:

Code: Select all

import zipfile
import os
import sys
import xml.etree.ElementTree

if len(sys.argv) != 2 :
	sys.exit('Error, expected fsd file on command line')
	
print ('file:',sys.argv[1])
	
#.py folder
pyFolder=os.path.dirname(os.path.abspath(__file__))

#flowsheet.xml file
xmlFile=os.path.join(pyFolder, "Flowsheet.xml")

#unzip
zip_ref = zipfile.ZipFile(sys.argv[1], 'r')
zip_ref.extractall(pyFolder)
zip_ref.close()

#parse flowsheet.xml
root=xml.etree.ElementTree.parse(xmlFile).getroot()

#remove flowsheet.xml
os.remove(xmlFile)


#extract stream data
for streamNode in root.iter('stream'):
    print ('stream ',streamNode.attrib['name']) #continue here to extract the info of interest

The rest should be simple to figure out from there.

[kevin032]

Hi Jasper,

Thank you for your prompt reply. I didn't expect it that quickly

You can find the excel file as attached. It is just a simple flash with I am trying to communicate...
Honestly, It's been a long time since I haven't code in VBA so I may miss some skills...

If you have a working file with "Set" so that I could be inspired it would be great.

For scripts, I will have a look for sure.

Best regards,
Kevin

[jasper]

I see an error on the "access unit operations example" page, cell G5 contains no unit name or index.

Other than that I am not sure what you are trying to do and where. Can you describe what you want to do with this sheet and provide me with steps to reproduce your issue?