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

[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

 

[joeu2004]

Take a look at what you'll need to do at John Walkenbach's old site:
http://j-walk.com/ss/excel/eee/eee003.txt
and look for SumProduct3D, SumIf3D, CountIf3D

As I understand that, that requires that we call mySUM("Sheet1:Sheet3!
A1"). Right?

Are you implying that we cannot call mySUM(Sheet1:Sheet3!A1) per se (a
3D range, not a string)?

 

[Dave Peterson]

Yes. That's what I'm saying.

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

 

[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.

 

[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).

 

[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__
[...]."

 

[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__
[...]."

 

[joeu2004]

How am I mistaken?

Sorry. We seem to be misunderstanding each other.

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

Happy Holidays, Dave.

 

[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.