Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
using relative named ranges directly in functions
'In any column to the right of column "B", I want to add the value in
cell "A1" 'to the value in the current row of column "B" 'I would like not to have to pass the 2 input cells as arguments to the function 'A1val is an absolute named range defined as: Sheet1!$A$1 'Bval is a relative named range defined as: Sheet1!$B1 (with the active cell being in row 1 at the time of name definition) ' 'Type the following in Sheet1: ' A B C D E '1 33 10 =A1val+Bval =Add1(A1val, Bval) =Add2() '2 20 =A1val+Bval =Add1(A1val, Bval) =Add2() '3 30 =A1val+Bval =Add1(A1val, Bval) =Add2() '4 40 =A1val+Bval =Add1(A1val, Bval) =Add2() ' 'From this I get the following results: ' A B C D E '1 100 10 110 110 110 '2 20 120 120 110 '3 30 130 130 110 '4 40 140 140 110 ' 'I want to get the same result in columns C, D and E, but obviously don't. 'It seems that the absolute named range and relative named range both work for Columns C and D, 'but not for Add2() in Column E. Function Add1(x, y) Add1 = x + y End Function Function Add2() Add2 = Range("A1val") + Range("Bval") End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
using relative named ranges directly in functions
Never reference ranges in worksheets directly from within functions; always
pass them via the arguments of the function. Reason is Excel doesn't know when to recalculate, certainly not if you use relative named ranges. It is sometimes suggested that including "Application.Volatile" solves the problem, but that might not work in the next version and I have serious doubts about the correct order of calculation, certainly with relative named ranges. -- Kind Regards, Niek Otten Microsoft MVP - Excel "Thunder" wrote in message ups.com... 'In any column to the right of column "B", I want to add the value in cell "A1" 'to the value in the current row of column "B" 'I would like not to have to pass the 2 input cells as arguments to the function 'A1val is an absolute named range defined as: Sheet1!$A$1 'Bval is a relative named range defined as: Sheet1!$B1 (with the active cell being in row 1 at the time of name definition) ' 'Type the following in Sheet1: ' A B C D E '1 33 10 =A1val+Bval =Add1(A1val, Bval) =Add2() '2 20 =A1val+Bval =Add1(A1val, Bval) =Add2() '3 30 =A1val+Bval =Add1(A1val, Bval) =Add2() '4 40 =A1val+Bval =Add1(A1val, Bval) =Add2() ' 'From this I get the following results: ' A B C D E '1 100 10 110 110 110 '2 20 120 120 110 '3 30 130 130 110 '4 40 140 140 110 ' 'I want to get the same result in columns C, D and E, but obviously don't. 'It seems that the absolute named range and relative named range both work for Columns C and D, 'but not for Add2() in Column E. Function Add1(x, y) Add1 = x + y End Function Function Add2() Add2 = Range("A1val") + Range("Bval") End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
using relative named ranges directly in functions
Niek,
Thanks for your suggestions. I inserted Application.Volatile into the Add2() function. I also inserted a MsgBox statement at the end of the function and forced a recalculaton of the sheet. The Add2() function gets called 4 times as expected, but on each call it adds cell "B1" to "A1", instead of using the cell in column B from the same row as it was called from i.e B1, B2, B3, B4. I was trying to make the function call simpler to avoid the possibbility of referring to the wrong cell when entering the formula into the cell, but I guess this is not the way to do it. Regards John |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
using relative named ranges directly in functions
Hi John,
In your function Add2, Range("Bval") is not relative to anything. For interest try: Function Add2() Add2 = Range("A1val") + Range("Bval")(Application.Caller.Row, 1) 'or 'Add2 = Range("A1val") + Range("Bval").cells(Application.Caller.Row, 1) End Function I don't see any reason why this would be unsafe as such other than returning the Application.Caller reference might be somewhat slower if used in many cells calculated at the same time. Niek may be able to advise either way. If your function does nothing more than as posted, why not use a named formula: Name: MySum Refersto: =A1Val+Bval and simply enter: =MySum You should be able to use Mysum on any sheet in any cells, except in A1 and Column B on the sheet where A1val & Bval are defined. Regards, Peter T "Thunder" wrote in message oups.com... Niek, Thanks for your suggestions. I inserted Application.Volatile into the Add2() function. I also inserted a MsgBox statement at the end of the function and forced a recalculaton of the sheet. The Add2() function gets called 4 times as expected, but on each call it adds cell "B1" to "A1", instead of using the cell in column B from the same row as it was called from i.e B1, B2, B3, B4. I was trying to make the function call simpler to avoid the possibbility of referring to the wrong cell when entering the formula into the cell, but I guess this is not the way to do it. Regards John |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
using relative named ranges directly in functions
Thanks Peter.
Your suggestions for changes to Add2() work. However, as I am conerned with execution speed, I think your suggestion of a named formula may be the way to go. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
relative Named Ranges based on worksheet | Excel Worksheet Functions | |||
Use OFFSET and COUNT functions within Named Ranges | Excel Discussion (Misc queries) | |||
Why use Subtotals 1-11 instead of the built-in functions directly? | New Users to Excel | |||
Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) | |||
named ranges - changing ranges with month selected | Excel Programming |