Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to implement mySum(Sheet1:Sheet3!B1)?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to implement mySum(Sheet1:Sheet3!B1)?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to implement mySum(Sheet1:Sheet3!B1)?
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)? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to implement mySum(Sheet1:Sheet3!B1)?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to implement mySum(Sheet1:Sheet3!B1)?
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). |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to implement mySum(Sheet1:Sheet3!B1)?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
= Today()+1 into Sheet1, Sheet2, and Sheet3 | Excel Programming | |||
Copy logo from sheet1 to new sheet3-10 | Excel Programming | |||
A1 in sheet1 =” =SUM('sheet2:sheet3'!A1)” | Excel Programming | |||
consoildate all the worksheet(example sheet1,sheet2 and sheet3 etc | Excel Worksheet Functions | |||
user changes sheet1 - have the macro xyz run on sheet3 | Excel Programming |