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

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
By Myrna Larson and David Hager



On 12/26/2010 22:39, joeu2004 wrote:
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

--
Dave Peterson

On Dec 27, 5:31*am, Dave Peterson wrote:
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)?

Yes. That's what I'm saying.

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



On 12/27/2010 08:15, joeu2004 wrote:
On Dec 27, 5:31 am, Dave wrote:
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

On Dec 27, 9:51*am, Dave Peterson wrote:
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).

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.



On 12/27/2010 15:27, joeu2004 wrote:
On Dec 27, 9:51 am, Dave wrote:
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).

--
Dave Peterson

On Dec 27, 9:51 am, Dave Peterson wrote:
A range belongs to a single sheet -- excel doesn't have 3D ranges.

On Dec 27, 1:27 pm, joeu2004 wrote:
SUM(Sheet1:Sheet3!A1) does work in XL2003 and later

On Dec 27, 2:05*pm, Dave Peterson wrote:
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__
[...]."

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.


On 12/27/2010 18:13, joeu2004 wrote:
On Dec 27, 9:51 am, Dave wrote:
A range belongs to a single sheet -- excel doesn't have 3D ranges.

On Dec 27, 1:27 pm, wrote:
SUM(Sheet1:Sheet3!A1) does work in XL2003 and later

On Dec 27, 2:05 pm, Dave wrote:
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

On Dec 28, 5:45*am, Dave Peterson wrote:
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.

You, too, Joe.



On 12/28/2010 11:47, joeu2004 wrote:
On Dec 28, 5:45 am, Dave wrote:
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