Using worksheetfunction from macro?

  • Thread starter Thread starter Vesa Virkki
  • Start date Start date
V

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
 
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
 
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

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.
 
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
 
Thanks, Jerry!
The limitation for using Average() from VBA
is new to me. Maybe VBA internally uses
TRANSPOSE() in this situation??

LeoH
 
Back
Top