Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA does not recognize Excel worksheet function "indirect" | Excel Programming | |||
Is there an Excel 2003 equivalent to Word's "versions" function? | Excel Discussion (Misc queries) | |||
"MAXIF" Equivalent function in Excel | Excel Worksheet Functions | |||
Mround function equivalent that does not require "add-in" | Excel Discussion (Misc queries) | |||
VBA does not recognize Excel worksheet function "indirect" | Excel Programming |