I don't believe there is a direct equivalent in VBA for INDIRECT. However,
you can use Offset to refer other cells.

myValue = Range("B2").Offset(0, -1)

This would give you the value in Cell A2.



"H.G. Lamy" wrote in message
...
Hello,

by this:

=INDEX(INDIRECT($A$1),(RAND()*COUNTA(INDIRECT($A$1 )))+1)

I often pick random elements from a list (named range), whose name is
typed into $A$1.

However, the attempt to make this a UDF function in VBA fails, since
"INDIRECT()" is not part of the "worksheetfunction"-collection in VBA.

Is there any equivalent that may do the trick ?

Thank you in advance.

Kind regards,

H.G. Lamy