Returning Global variables values via UDF

[Boomer67]

I have a sheet with numerous Global variables declared in VBA with part of the name determined by a tenor, e.g. Pillar3m, Pillar6m, etc. I would like to use a UDF to be able to return these values based on the tenor. So if I ask for "3m" I get the Pillar3m value etc. I cannot seem to work out a means to be able to concatenate the value. For example

Application.Evaluate("Pillar" & tenor)

Does not work. Is it possible, and if so, how?
Thanks

 

[RoryA]

You either need to make the variables members of a class (so you can use CallByName) or you need to load them into a Dictionary or Collection so that you can access them with a key.

 

[Boomer67]

You either need to make the variables members of a class (so you can use CallByName) or you need to load them into a Dictionary or Collection so that you can access them with a key.

Thanks Rory. Let me take a look at CallBYName and see if that works. I suspect loading into a collection isn't going to be much of a saving. Thanks.

 

[Kenneth Hobson]

Have you considered creating a named range with the value?

 

[Boomer67]

Kenneth, I have considered that. As most of the time I need the variables in VBA I am trying to avoid the overheads of the Excel / VBA exchanges.