Thank you for responding!
I had no idea this support existed, and you are correct, there are countless examples. Here's the code I was looking for:
Sub Main()
'
'Description: Simple Example of adding a spreadsheet, importing a value, adding a formula
' and exporting a value
'
' Imports a temperature from stream 1 in the current case, and exports it to stream 2
'
' In Excel VBA Requires HYSYS type library to be set under Tools ... References
'
'Declare Variables----------------------------------------------------------------------------
Dim hyApp As HYSYS.Application 'Application
Dim hyCase As HYSYS.SimulationCase 'Case
Dim hySprd As HYSYS.SpreadsheetOp 'Spreadsheet
Dim hySprdCell As HYSYS.SpreadsheetCell 'Cell
Dim hyRV As HYSYS.RealVariable 'Real Variable
'Procedure-------------------------------------------------------------------------------------
'Excel VBA - Link to HYSYS Objects - comment out to use Macro Language Editor
Set hyApp = GetObject(, "HYSYS.Application") 'Only works if HYSYS is open
Set hyCase = hyApp.ActiveDocument
'HYSYS Macro Language Editor - Link to HYSYS Objects - uncomment to use MLE
'Set hyCase = ActiveCase
'Create a new spreadsheet operation
Set hySprd = hyCase.Flowsheet.Operations.Add("MySprd", "spreadsheetop")
'Could link to an existing spreadsheet as follows
'Set hySprd = hyCase.Flowsheet.Operations.Item("MySprd")
'Import the temperature from stream 1 into cell A1
'Link to a particular cell
Set hySprdCell = hySprd.Cell("A1")
'Good practice to clear any existing imports
hySprdCell.DisconnectVariable
'Establish an object for the variable we want to import
Set hyRV = hyCase.Flowsheet.MaterialStreams.Item("1").Temperature
'Link to the cell
hySprdCell.ImportedVariable = hyRV
'Put a formula into B1
Set hySprdCell = hySprd.Cell("B1")
hySprdCell.DisconnectVariable
hySprdCell.CellText = "=A1+10"
'Now export this new value back to stream 2
'Procedure as for imports except use the .ExportedVariable property
'Note there is no need to go via the intermediate RV - can do all in one line
hySprdCell.ExportedVariable = hyCase.Flowsheet.MaterialStreams.Item("2").Temperature
End Sub