CODEWITHSUNDEEP
excelvba
bitdigester
bitdigester

Reputation: 1

Shouldn't Excel support passing off-sheet cell range references to functions?

You would think so but this fails with a 1004 error

Rem This macro is running on sheet2

Dim avg As double

Names.Add Name:="sheet1Avg", RefersTo:="sheet1!$C$2:$C$24"
avg = Application.WorksheetFunction.Average(range("sheet1Avg"))

So does this:

avg = Application.WorksheetFunction.Average(range("sheet1!$C$2:$C$24))

I would hate to have to copy C2:C24 to the active sheet every time I wanted to use a math function.

Any ideas?

Upvotes: 0

Views: 81

Answers (2)

L42
L42

Reputation: 19737

GSerg already explained it very well so take his answer.
Below is just another way of getting your Average using the Evaluate function.

avg = Evaluate("AVERAGE(" & Split(Names(1).RefersTo, "=")(1) & ")")

If you created the Name correctly as what GSerg pointed out (adding = upfront your address) above will work as you expect it returning the average of the address you specify.

Another way is to be explicit in assigning the name. Instead of setting the scope in Sheet2, set it in Workbook. Something like:

ThisWorkbook.Names.Add Name:="sheet1Avg2", RefersTo:="=Sheet1!$C$2:$C$24"

Then you need to explicitly call it as well like this:

avg = Application.WorksheetFunction.Average(Sheet1.Range("sheet1Avg2"))

where Sheet1 is the Sheet Codename. HTH and not confuse you.

Upvotes: 0

GSerg
GSerg

Reputation: 78182

I doubt you actually need this twisted logic, but if you actually do, fix your RefersTo:

Names.Add Name:="sheet1Avg", RefersTo:="=sheet1!$C$2:$C$24"

and change the way you reference the created range - you cannot dereference it with Range because it is supposed to return a range on its parent sheet, not on any other sheet:

avg = Application.WorksheetFunction.Average(Names("sheet1Avg").RefersToRange)

Otherwise store your range references in local variables of type Range and only use named ranges when you wouldn't need to define them at runtime.

Upvotes: 1

Related Questions