How to implement mySum(Sheet1:Sheet3!B1)?

J

joeu2004

I can call SUM(Sheet1:Sheet3!B1) in A1.

But I cannot call mySum(Sheet1:Sheet3!B1).

What type of object is passed for that kind of range?

Why doesn't the following work? The second MsgBox displays Error.

Function mySum(ParamArray x())
MsgBox LBound(x) & " " & UBound(x)
MsgBox TypeName(x(0))
End Function
 
D

Dave Peterson

Yes. That's what I'm saying.

A range belongs to a single sheet -- excel doesn't have 3D ranges.
 
J

joeu2004

Yes.  That's what I'm saying.

Thanks for the clarification.

A range belongs to a single sheet -- excel doesn't have 3D ranges.

Perhaps I'm using the wrong term.

SUM(Sheet1:Sheet3!A1) does work in XL2003 and later, although I have
found that its interpretation is error-prone.

For example, if our worksheets have are Sheet100, Sheet99,..., Sheet1,
Sheet2, Sheet3 in tab order and we write SUM(Sheet100:Sheet1!A1) [*],
XL2003 fails to include Sheet2 and Sheet3 presumably because Sheet1 is
encountered first.
 
D

Dave Peterson

If you had called it a reference, not a range, I'd have no problem.

But excel can handle those 3 dimensional references nicely. I think you'll have
much more irritation in your UDF.



Yes. That's what I'm saying.

Thanks for the clarification.

A range belongs to a single sheet -- excel doesn't have 3D ranges.

Perhaps I'm using the wrong term.

SUM(Sheet1:Sheet3!A1) does work in XL2003 and later, although I have
found that its interpretation is error-prone.

For example, if our worksheets have are Sheet100, Sheet99,..., Sheet1,
Sheet2, Sheet3 in tab order and we write SUM(Sheet100:Sheet1!A1) [*],
XL2003 fails to include Sheet2 and Sheet3 presumably because Sheet1 is
encountered first.

-----
Endnotes

[*] If we enter SUM(Sheet1:Sheet100!A1) with the tab order indicated,
XL2003 changes it to SUM(Sheet100:Sheet1!A1).
 
J

joeu2004

A range belongs to a single sheet -- excel doesn't have 3D ranges.

SUM(Sheet1:Sheet3!A1) does work in XL2003 and later

If you had called it a reference, not a range, I'd have no problem.

SUM(Sheet1:Sheet3!A1:C1) works in XL2003 and later, as well.

And your buddies at http://j-walk.com/ss/excel/eee/eee003.txt call at
least that a "3D range" [*].

So whether you thought I was talking about "3D reference" or "3D
range", you still seem to be mistaken.

-----
Endnotes

[*] From http://j-walk.com/ss/excel/eee/eee003.txt (emphasis added):

"Excel allows you to make 3D formulas based on the following syntax:
Sheet1:Sheet4!A2:B5
[....]
BTW, If you create a defined name for this type of __3D_range__
[...]."
 
D

Dave Peterson

It's not a range -- no matter what terms they use.

How am I mistaken?

I never wrote that excel can not use a reference like: =sum(sheet1:sheet3!a1:a5)

I wrote that your UDF will be more irritating to write. Are you saying you have
a nice way to write your function?

If you do, then share it.


A range belongs to a single sheet -- excel doesn't have 3D ranges.

SUM(Sheet1:Sheet3!A1) does work in XL2003 and later

If you had called it a reference, not a range, I'd have no problem.

SUM(Sheet1:Sheet3!A1:C1) works in XL2003 and later, as well.

And your buddies at http://j-walk.com/ss/excel/eee/eee003.txt call at
least that a "3D range" [*].

So whether you thought I was talking about "3D reference" or "3D
range", you still seem to be mistaken.

-----
Endnotes

[*] From http://j-walk.com/ss/excel/eee/eee003.txt (emphasis added):

"Excel allows you to make 3D formulas based on the following syntax:
Sheet1:Sheet4!A2:B5
[....]
BTW, If you create a defined name for this type of __3D_range__
[...]."
 
D

Dave Peterson

You, too, Joe.



Sorry. We seem to be misunderstanding each other.

Anyway, you answered my question some time ago. Thanks again.

Happy Holidays, Dave.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top