Using worksheetfunction from macro?

[Vesa Virkki]

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

 

[Jerry W. Lewis]

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

 

[Jerry W. Lewis]

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

 

[Vesa Virkki]

Hi,
Thank you!
Yes I use 2000 and I mean limit 5461

cheers,
Vesa

 

[Leo Heuser]

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]

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]

Thanks, Jerry!
The limitation for using Average() from VBA
is new to me. Maybe VBA internally uses
TRANSPOSE() in this situation??

LeoH