Max Array size in Excel 2007 Worksheet Function?

[AndrewKKWalker]

Is there a way round or a Patch for this limitation

Although Excel 2007 has far more rows than the Old 65536
it would appear that you cannot pass an array bigger than 65536 rows to a Worksheet Function
There may be Patch for this.
Anyone found a solution other than splitting the array or using worksheet cells

If you pass the worksheet functions a range then they can cope with the full number of sheet rows.
If an array, then you get the Mismatch error.
This is NOT a sheet in compatibility mode.

Sub TestWorkSheetFunctionArray()

'Change conLimit over 65536 you get an error
Const conLimit As Long = 65536
Dim lngTestArray() As Long, i As Long

ReDim lngTestArray(1 To conLimit)

For i = 1 To conLimit
    lngTestArray(i) = i
Next i

MsgBox "Sum = " & Application.Sum(lngTestArray) & vbLf & "Match Found in Row ... " & Application.Match(7, lngTestArray, 0)

End Sub

conLimit = 65536

[attachment=88709:Array65536OK.jpg]

conLimit > 65536

[attachment=88708:ArrayError.jpg]

[tfspry]

Hello AKW - It seems that Arrays in Excel 2007 are not limited to 65K rows. The limit seems to be number of rows up to 1 million or the amount of memory.

Take a look at these links .....

Description of the limitations for working with arrays in Excel
http://support.microsoft.com/kb/166342

Excel Memory Limits
http://www.decisionmodels.com/memlimitsc.htm

Excel specifications and limits
http://office.microsoft.com/en-us/ex...738491033.aspx

This link may also help, although it is pre-release of final Excel 2007 ......

Improving Performance in Excel 2007
http://msdn.microsoft.com/en-us/library/aa730921.aspx

Hope these help.

Tim

[Paul T]

Try using dictionaries instead, much more memory allowed there.
var y = new ActiveXObject("Scripting.Dictionary")

cheers, Paul

[AndrewKKWalker]

In answer to both replies

Thanks

I will certainly look into dictionaries as a solution

Regarding array sizes (did you run the code and get it to work with > 65536 elements?)

It is not the array size that is the issue here
It is passing a data array to an Excel Worksheet function from VBA
Rather than passing a Range Object.
It is passing the data array that triggers the error message.
This is just a single vector of > 65536 items

Passing the data as a range object behaves like it does on a worksheet ok.

Indeed the size of array that vba can work with is also pretty much based on memory.

It is just that Application.WorksheetFunction.RangeFunction(DataAr ray) call that fails

Unless I missing something (which is not unlikely).

[rory]

You are not missing anything - the VBA capabilities in that respect have not altered from prior versions. There are no workarounds I can think of that you haven't mentioned (other than not using worksheet functions in code!).

[AndrewKKWalker]

Quick question regarding Dictionary Objects.
I can see that these have a use, especially with the ability to have a key.

BUT in the context of this thread, how would you pass a scripting dictionary object to an Excel Built in Worksheet Function from VBA?
Indeed, is it possible?

I just tried it and all I get is Application or User Defined Error.

The implications being that for example the Worksheet Function SUM cannot receive a Dictionary object as a parameter.
That being the case, (which it looks like it is), then unfortunately this would not resolve this issue.

Am I off the mark in this assumption?

(All New knowledge is good)

[rory]

You can't pass a Dictionary to a worksheet function. You could pass its values as an array but then you are back where you started. You need to use a range, or the alternatives already mentioned.

[AndrewKKWalker]

Thanks Rory.

I had come to that conclusion.
Not an issue, I was just trying to resolve a problem that someone posed to me,
and before I said NO you'll have to do it another way, I just thought I'd make sure I was NOT going to give them false information.

BUT, they'll be using an alternative method now.

Alternatively, they could slice it up into acceptable size arrays,
inside a loop.

[sdckapr]

The limit in XL2002 is the same as the limit in XL2007. The limit for the worksheet functions in XL2000 is 5461 with 5462 giving the type "mismatch error"

The alternative approach for SUM and Match is to loop through the array and keep a running sum and also compare to the target and note the index value of the match. If you don't need the sum (so looping through the whole list is not required you can stop after the exact match or if an inexact match and the list is sorted, you can just look at "searching in halves" which can go through long lists much quicker

Steve

[AndrewKKWalker]

Thanks Steve.

I will leave it up to them as to the solution they finally apply.
It is just trying to find the fastest method with high volumes of data, (generally in Excess of 250,000 rows).
The Sum and Match functions where just a couple of test functions, they might not be the actual functions used.

Anyway, the decision is not mine, fortunately.

[sdckapr]

I guess the question becomes whether to work with the builtin functions on actual range objects or read the data into an array and then have VB read through the arrays with custom functions created to mimic worksheetfunction mechanics. For me unless the builtin routines are so slow, I would not try to rewrite my own functions to work with arrays. More often than not I have found using VB functions slower than XL functions.

Steve

[AndrewKKWalker]

That was the main reason for using the Built In Functions with the arrays of data.
It was the fastest solution.
I guess the way to go might be to split the arrays into 65536 or less chunks.
Process them via the built in functions, and record the results.
Depending upon the functions required (mainly lookup I think),
then they ought to be ok.

Two or three calls to built in functions are probably quicker than writing an alternative.

Anyway we'll see what they decide.

There's always Office 2010 when it comes out, Maybe (but I doubt that would be an option)

Thanks again.

[sdckapr]

What happens if instead of using arrays, you have the values in a worksheet and use the on the range objects or even use a worksheet formula to calculate it and have VB read the value from the worksheet? This would eliminate the problems with Arrays...

Steve

[AndrewKKWalker]

I did suggest this. I am seeing them tomorrow so I will go over options with them.