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

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

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

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



.

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

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.

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.