excelvbaaspen

How can I create dynamic variable names without using a dictionary?

I am linking Excel with Aspen Hysys which allows me to import data from the second one. Hysys has a large hierarchy three of objects including the Stream object. The Stream object contains variables such Temperature, VaporFraction and so on and I access them through object syntax is like:

Dim hyApp As HYSYS.Application
Dim hyCase As HYSYS.SimulationCase
Dim hyStream As HYSYS.Streams
Dim temperature As Double

Set hyApp = CreateObject("HYSYS.Application")
Set hyCase = hyApp.ActiveDocument
Set hyStream = hyCase.Flowsheet.MaterialStreams.Item(0)
temperature = hyStream.TemperatureValue

Now if I need, the VaporFraction, I need to do the following:

Dim vaporFraction As Double
vaporFraction = hyStream.VaporFractionValue

But, what if I need other properties? Or need 100 properties?

Is there a way to convert strings (stored in cells) to variable names. I understand the concept of using dictionaries. But in this case, it will not work.

What I would expect is something like this:

properties = ("Temperature", "VaporFraction")
For Each property in properties
    createVaribleName(property + "Value")
Next property
Solution

  • Yes, for Classes you can use the CallByName function. See here: CallByName

    Returning a class property, example:

    Dim MyCollection As Collection: Set MyCollection = New Collection
MyCollection.Add "A"
MyCollection.Add "B"
Debug.Print CallByName(MyCollection, "Count", VbMethod)

    If you are working with code you wrote in a standard module, take a look at Application.Run.