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

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

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

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

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

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

--
Rick (MVP - Excel)


"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

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

Thanks you !

hgl

"Rick Rothstein" wrote in message
...
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

--
Rick (MVP - Excel)


"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

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

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 wrote:
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

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

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/sh...d.php?t=157454

Microsoft Office Help

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

Thank you very much !

hgl

"joel" wrote in message
...

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/sh...d.php?t=157454

Microsoft Office Help

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

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

"H.G. Lamy" wrote:

Thank you very much !

hgl

"joel" wrote in message
...

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/sh...d.php?t=157454

Microsoft Office Help



.

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

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 range
that is specified on the current sheet.


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

Microsoft Office Help

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

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

"joel" wrote:


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 range
that is specified on the current sheet.


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

Microsoft Office Help

.

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

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" wrote:


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 range
that is specified on the current sheet.


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

Microsoft Office Help

.

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

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 number
of cells at one time you would get the same slow down.

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

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


There are different methods of writting VBA code and some VBA code runs
more efficiently than others. For example if you had to copy a range of
cells from sheet 1 to sheet 2 using VBA code you can copy Range A1:B100
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 the
macro up
Application.ScreenUpdating = False

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

Application.ScreenUpdating = True


On large workbooks some people disble the Auto Recalation mode so the
workbook doesn't recaculates everytime a cell is changed orwhen the
workbook is opened but you have to remember to manually perform the
recaculation after all you changes are made to speed up a workbook.
Using VBA code does a similar speed imporvement because most macros only
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 VBA
when necessary (can't easily be done using formulas) or when the number
of formulas grealy slows down a workbook that I use often.

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

There are other people who love to write complicated formulas that only
they can figure out. Often after I post a macro somebody will also
answer with a complicated formula. I use good judgment when answering
posting and try to recommend either formulas or VBA depending on the
situation. From VBA I also use worksheet functions s required. Most of
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 are
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 to
your question.


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

Microsoft Office Help