VBA equivalent for Excel "indirect()" function in UDF

[H.G. Lamy]

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

 

[JLGWhiz]

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.

 

[Rick Rothstein]

You can refer to the range referenced in A1 with Range(Range("A1").Value),
for example...

Set DefinedNameRange = Range(Range("A1").Value)

or...

MsgBox Range(Range("A1").Value).Address

 

[H.G. Lamy]

Thanks you !

hgl

You can refer to the range referenced in A1 with Range(Range("A1").Value),
for example...

Set DefinedNameRange = Range(Range("A1").Value)

or...

MsgBox Range(Range("A1").Value).Address

 

[keiji kounoike]

I don't think your formula below would work correctly. I guess you mean
a formula like this.

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

If this is the case, the UDF equivalent to the formula above is

Function myfunc(rng As Range) As Variant
Dim R As Range
Set R = Evaluate(rng.Value)
With Application
myfunc = .Index(R.Value, Int((Rnd() * .CountA(R.Value)) + 1))
End With
End Function

or following Rick's example,

Function myfunc(rng As Range) As Variant
Dim R As Range
Set R = Range(rng.Value)
With Application
myfunc = .Index(R.Value, Int((Rnd() * .CountA(R.Value)) + 1))
End With
End Function

Keiji

 

[H.G. Lamy]

Thank you very much !

hgl

 

[RobG]

Hi, iam new to VBA but have a similar dilema. How would i create a UDF to
replace the following mega formula. The formula is part of an intermediate
sheet that cast % of budgets to consolidation pages. I have several of these
transfer sheets so the SS size balloons.

=INDIRECT(ADDRESS(CELL("row",M12),CELL("col",M12),,,$A$3))*INDIRECT(ADDRESS(CELL("row",M12),$E$3,,,$A$3))*INDIRECT(ADDRESS($F$3,CELL("col",M12),,,$A$3))

The inputs are $a$3 the sheetname where data is $A$3 the column number where
the % lives $F$3 the number of rows in that sheet

Help

Thank you very much !

hgl

too complicated. If you have a named range JOEL on the worksheet


A = "Joel"
set Myrange = Range(A)



the named range is a string like Range("JOEL"). So you can put any
valid sting inside the Range() function that matches the name in the
worksheet. You can even do his

A = "JO"
B = "EL"
Set MyRange = Range(A & B)


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread:
http://www.thecodecage.com/forumz/showthread.php?t=157454

Microsoft Office Help

.

 

[RobG]

Legend!

i ended up writing this which ismy first UDF. Cheers

Function RoboCell(Curcell As Range, ShtName As String, ColNum As Integer, _
RowNum As Integer)

'returns the value of the current cell location in another sheetname
'multiplied by the % in colnum * % in rownum

RoboCell = Sheets(ShtName).Range(Curcell.Address) * _
Sheets(ShtName).Cells(Curcell.Row, ColNum) * _
Sheets(ShtName).Cells(RowNum, Curcell.Row)

End Function

 

[RobG]

One further question,

The Calculation speed by replacing the large Formulae to the UDF decreased
exponentially. ie a nano second to 20 seconds.

Is this a general inefficiency of UDF's or is there something i have done
wrong in the code? I am only pasting this function on a 200x24 grid!