Run Time Error "13" Type Mismatch

[ExcelMonkey]

I am getting a Run Time Error '13' Type Mismatch Error on the following line
of code:

Var1 = Application.WorksheetFunction.Average(Array1)

The Array is filled with data from the cells in the spreadsheet while in a
For next loop. It populates the arrray after a Calculate. Whenever I
increase the loop > 65,000 runs, I get the error on this line of code. The
Array is dimensioned as a Variant. Is a Type Mismatch of this kind occuring
becuase I am loading a spreadsheet error (i.e. DIV/0 etc) into the array and
the Average function fails? It does not seem to fail on runs less than
65000.

How can I check the contents of the array to see what would cause the
Average function to fail?

Thanks

 

[ExcelMonkey]

Came across this note on the microsoft site:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q177991

Suggesting that the error occurs because the function is contrained by the
65326 rows contraints in Excel. I find this confusing as I am always loading
the array from the same cell in Excel.

How do I use the Average, STD, Min, Max worksheet functions from Excel in
VBA if I have this following contraints given that my array will have well
over 65000 items?

Thanks

EM

 

[Tom Ogilvy]

You won't use them. You will have to write your own functions that provide
that information without the limitation.

 

[NickHK]

If you are looping anyway to generate the array, why not calculate you
average in the loop yourself:
for each cell in YourRange
RunTotal=RunTotal+csng(cell.value)
cellcount=cellcount+1
Next
MyAvg=RunTotal/cellcount

However, I'm sure this method will be slow.

What about :
with application.worksheetfunction
MyAvg=.Sum(...ranges..)/.CountA(...ranges..)
end with

NickHK

 

[ExcelMonkey]

Sorry I tried to post a reply but my Internet explorer crashed. This sounds
like a common issue Tom. Is this a bug in Excel or simply due to the fact
that I have loaded my array from a changing cell in Excel vs loading it from
calcs within VBA.

Do you know of any common functions for Avv, STD, Min, Max which can be
applied to an array afters its filled? I want to minimize the time lag in my
code.

Still a little shocked that Excel cannot accomodate this.

EM