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

 

[joel]

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)

 

[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

.

 

[joel]

I think something simple like this

Function IndirectSht(Target As Range, ShtName As String)
InDirectSht = Sheets(ShtName).Range(Target.Address)
End Function


Call with
=IndirectSht(A6,"sheet2")


The function will return the data in another sheet at the same rang
that is specified on the current sheet

 

[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!

 

[joel]

I think you caused a lot of calculations to be performed at one time.
If you removed your original formula, then copied it to the same numbe
of cells at one time you would get the same slow down.

The speed difference can go either way when you replace workshee
formulas with VBA code....

Most worksheet functions are writen extremely efficiently and generall
run faster than equivalent VBA code. But lots of formulas in a workboo
slows down the workbook because a lot of recaulations are need to b
performed when a cell is changed. And some worksheet formulas require
lot of calculations like SUMPRODUCT and it is sometimes possible t
replace with VBA that runs using les instructions.


There are different methods of writting VBA code and some VBA code run
more efficiently than others. For example if you had to copy a range o
cells from sheet 1 to sheet 2 using VBA code you can copy Range A1:B10
one cell at a time which is slow or make the copy using one instruction

Slow method
with sheets("sheet1")
For RowCount = 1 to 100
For ColCount = 1 to 2
Sheets("Sheet2").Cells(RowCount,ColCount) = _
.Cells(RowCount,ColCount)
Next Colcount
Next rowCount
end with


Fast Method
Sheets("Sheet1").Range("A1:B100").Copy _
Destination:=Sheets("Sheet2").Range("A1")



Also in VBA disabling the recalculations during a macro will speed th
macro up
Application.ScreenUpdating = False

But you must renable the screen updates at the end of the macr
otherwise the workbook appears to be hung up.

Application.ScreenUpdating = True


On large workbooks some people disble the Auto Recalation mode so th
workbook doesn't recaculates everytime a cell is changed orwhen th
workbook is opened but you have to remember to manually perform th
recaculation after all you changes are made to speed up a workbook.
Using VBA code does a similar speed imporvement because most macros onl
run manually.


The real answer is which every method (macro or worksheet functions
requires less computer machine instructions to execute will run faster.
And memory usage is also a factor. I'm a expert at VBA and only use VB
when necessary (can't easily be done using formulas) or when the numbe
of formulas grealy slows down a workbook that I use often.

I don't like large complicated formulas because they are difficult t
get working and ae hard for me to understaqnd if I have to come back
months later and make changes. Also it is much easier to documen
macros than worksheet functions.

There are other people who love to write complicated formulas that onl
they can figure out. Often after I post a macro somebody will als
answer with a complicated formula. I use good judgment when answerin
posting and try to recommend either formulas or VBA depending on th
situation. From VBA I also use worksheet functions s required. Most o
the worksheet functions are accessable from VBA list This

Mytotal = worksheetfunction.sum("A1:B100")

I also use VBA code to add formulas to a worksheet so changes ar
automatically reculated without running a macro

Range("B102").formula = "=Sum(A1:B100)"

I hope I didn't confuse you too much, but there is no precise answer t
your question