using relative named ranges directly in functions

[Thunder]

'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

 

[Niek Otten]

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]

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

 

[Peter T]

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

 

[John Broderick]

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.