Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using worksheetfunction from macro?
Hi,
I have problem when I try to use for next line when table size is bigger than 6140 line(comes run time error: Application.WorksheetFunction.Average(B_results()) where B_results is array in macro. cheers, Vesa |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using worksheetfunction from macro?
I presume that you mean 5140, and that you are using Excel 2000 or
earlier. This was a limit in the row size of arrays (not cell ranges) that worksheet functions could handle. Limited to 65536 (=2^16) in XP. Jerry Vesa Virkki wrote: Hi, I have problem when I try to use for next line when table size is bigger than 6140 line(comes run time error: Application.WorksheetFunction.Average(B_results()) where B_results is array in macro. cheers, Vesa |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using worksheetfunction from macro?
Oops, in 2000 and earlier the limit was 5461 (0 to 5460 with Option Base
0) cells, not rows. Limit in XP is correctly stated as 65536 rows. Jerry Jerry W. Lewis wrote: I presume that you mean 5140, and that you are using Excel 2000 or earlier. This was a limit in the row size of arrays (not cell ranges) that worksheet functions could handle. Limited to 65536 (=2^16) in XP. Jerry Vesa Virkki wrote: Hi, I have problem when I try to use for next line when table size is bigger than 6140 line(comes run time error: Application.WorksheetFunction.Average(B_results()) where B_results is array in macro. cheers, Vesa |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using worksheetfunction from macro?
Hi,
Thank you! Yes I use 2000 and I mean limit 5461 cheers, Vesa -----Original Message----- Oops, in 2000 and earlier the limit was 5461 (0 to 5460 with Option Base 0) cells, not rows. Limit in XP is correctly stated as 65536 rows. Jerry Jerry W. Lewis wrote: I presume that you mean 5140, and that you are using Excel 2000 or earlier. This was a limit in the row size of arrays (not cell ranges) that worksheet functions could handle. Limited to 65536 (=2^16) in XP. Jerry Vesa Virkki wrote: Hi, I have problem when I try to use for next line when table size is bigger than 6140 line(comes run time error: Application.WorksheetFunction.Average(B_results()) where B_results is array in macro. cheers, Vesa . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using worksheetfunction from macro?
Jerry
I believe, that the maximun array in Excel 97 and on is limited only by available memory or the worksheet maximum size 65536 rows x 256 columns. The limit of 5461 elements is only relevant, when you use the TRANSPOSE-function. -- Best Regards Leo Heuser Excel MVP Followup to newsgroup only please. "Jerry W. Lewis" skrev i en meddelelse ... Oops, in 2000 and earlier the limit was 5461 (0 to 5460 with Option Base 0) cells, not rows. Limit in XP is correctly stated as 65536 rows. Jerry Jerry W. Lewis wrote: I presume that you mean 5140, and that you are using Excel 2000 or earlier. This was a limit in the row size of arrays (not cell ranges) that worksheet functions could handle. Limited to 65536 (=2^16) in XP. Jerry Vesa Virkki wrote: Hi, I have problem when I try to use for next line when table size is bigger than 6140 line(comes run time error: Application.WorksheetFunction.Average(B_results()) where B_results is array in macro. cheers, Vesa |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using worksheetfunction from macro?
We are each half right. The array limit is different from the range
limit (number of rows on the worksheet), but it appears that there are two array limits. For a worksheet array formula like =AVERAGE(IF(ISERROR(rng),"",rng)) the limit in Excel 2000 is 65535 rows (one less than the number of rows in the spreadsheet). For calling WorksheetFunction.Average() from VBA, the limit is 5461 elements, as I experimentally verified before responding to the OP. I also experimentally verified that the limit from VBA in XP is 65535. Jerry Leo Heuser wrote: Jerry I believe, that the maximun array in Excel 97 and on is limited only by available memory or the worksheet maximum size 65536 rows x 256 columns. The limit of 5461 elements is only relevant, when you use the TRANSPOSE-function. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using worksheetfunction from macro?
Thanks, Jerry!
The limitation for using Average() from VBA is new to me. Maybe VBA internally uses TRANSPOSE() in this situation?? LeoH "Jerry W. Lewis" skrev i en meddelelse ... We are each half right. The array limit is different from the range limit (number of rows on the worksheet), but it appears that there are two array limits. For a worksheet array formula like =AVERAGE(IF(ISERROR(rng),"",rng)) the limit in Excel 2000 is 65535 rows (one less than the number of rows in the spreadsheet). For calling WorksheetFunction.Average() from VBA, the limit is 5461 elements, as I experimentally verified before responding to the OP. I also experimentally verified that the limit from VBA in XP is 65535. Jerry Leo Heuser wrote: Jerry I believe, that the maximun array in Excel 97 and on is limited only by available memory or the worksheet maximum size 65536 rows x 256 columns. The limit of 5461 elements is only relevant, when you use the TRANSPOSE-function. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
WorksheetFunction help | Excel Discussion (Misc queries) | |||
Need help with Application.WorksheetFunction | Excel Discussion (Misc queries) | |||
Can't use WorksheetFunction | Excel Discussion (Misc queries) | |||
WorksheetFunction with VBA | Excel Discussion (Misc queries) | |||
worksheetfunction | Excel Programming |