Results 1 to 14 of 14
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,015
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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.

    Code:
    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]
    Attached Images Attached Images
    Andrew

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    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

  4. #3
    Platinum Lounger
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    3,545
    Thanks
    7
    Thanked 225 Times in 213 Posts
    Try using dictionaries instead, much more memory allowed there.
    var y = new ActiveXObject("Scripting.Dictionary")

    cheers, Paul

  5. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,015
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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).
    Andrew

  6. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,876
    Thanks
    0
    Thanked 80 Times in 76 Posts
    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!).
    Regards,
    Rory
    Microsoft MVP - Excel.

  7. #6
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,015
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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)
    Andrew

  8. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,876
    Thanks
    0
    Thanked 80 Times in 76 Posts
    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.
    Regards,
    Rory
    Microsoft MVP - Excel.

  9. #8
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,015
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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.
    Andrew

  10. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,212
    Thanks
    14
    Thanked 338 Times in 331 Posts
    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

  11. #10
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,015
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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.
    Andrew

  12. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,212
    Thanks
    14
    Thanked 338 Times in 331 Posts
    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

  13. #12
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,015
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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.
    Andrew

  14. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,212
    Thanks
    14
    Thanked 338 Times in 331 Posts
    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

  15. #14
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,015
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I did suggest this. I am seeing them tomorrow so I will go over options with them.
    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •