+ Reply to Thread
Results 1 to 20 of 20

WorksheetFunction Limitations (specifically .max & .min)

  1. #1
    Registered User
    Join Date
    10-03-2012
    Location
    St. Louis, Missouri
    MS-Off Ver
    MS 2013, 64Bit, 8 Cores, 20GB
    Posts
    18

    WorksheetFunction Limitations (specifically .max & .min)

    Is anyone aware of any Maximum or Minimum WorksheetFunction limitations when working with arrays?

    I have a dataset of 240,000 datum in a worksheet...
    In VBA, when I set a range (dataSet) equal to Selection and use Application.Min(dataSet) I get the correct value.
    However, when I use a loop to create a copy of dataSet as an array (dataArray) and then use Application.Min(dataArray) - I get a value that is NOT the minimum of the array.

    I have verified that dataArray is an exact copy of dataSet (both have 240,000 elements), but the minimum value returned from the array is not the correct value (which should be the value in array element 119975).

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: WorksheetFunction Limitations (specifically .max & .min)

    Most worksheet functions used in VBA (including Min) will return a runtime (type mismatch) error with arrays larger than 65536 rows.
    Last edited by shg; 07-19-2017 at 02:24 PM.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    20,554

    Re: WorksheetFunction Limitations (specifically .max & .min)

    I don't know of any limitations that would apply to an array but would not apply to a worksheet range of the same size. Can you provide the file, or at least your code?

    By the way, if you declare your array as a Variant, it is trivial to load a range into it. You don't need a loop.

    Dim A As Variant
    A = Selection
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: WorksheetFunction Limitations (specifically .max & .min)

    @6SJ:

    Please Login or Register  to view this content.

  5. #5
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: WorksheetFunction Limitations (specifically .max & .min)

    Quote Originally Posted by shg View Post
    Most worksheet functions used in VBA (including Min) will return a runtime (type mismatch) error with arrays larger than 65536 rows.
    I believe that depends on your version of Excel. The latest build of 2016 for example, will work with a 1,000,000 row array.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: WorksheetFunction Limitations (specifically .max & .min)

    Wow, I'm surprised that MS has made any beneficial changes to VBA. I tested in 2010.

    6SJ or xl, can you test in 2013?

  7. #7
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: WorksheetFunction Limitations (specifically .max & .min)

    It seems to be working in my Excel 2013 build as well. Both are on the same machine, which I suspect means they may use the same dll for VBA, which could be a factor. Both are also 64 bit.

    I wonder if it was a fix for the issue where some worksheet functions would not error but instead return the wrong information- such as Index used to extract a column from a large array.

    Update: I still receive a Type Mismatch with 2010- again on the same machine.
    Last edited by xlnitwit; 07-19-2017 at 02:56 PM.

  8. #8
    Registered User
    Join Date
    10-03-2012
    Location
    St. Louis, Missouri
    MS-Off Ver
    MS 2013, 64Bit, 8 Cores, 20GB
    Posts
    18

    Re: WorksheetFunction Limitations (specifically .max & .min)

    [content deleted]
    Last edited by gth826a; 07-19-2017 at 03:48 PM.

  9. #9
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: WorksheetFunction Limitations (specifically .max & .min)

    Are you running 2010? Is your array 1 dimensional or more?

  10. #10
    Registered User
    Join Date
    10-03-2012
    Location
    St. Louis, Missouri
    MS-Off Ver
    MS 2013, 64Bit, 8 Cores, 20GB
    Posts
    18

    Re: WorksheetFunction Limitations (specifically .max & .min)

    Quote Originally Posted by shg View Post
    Most worksheet functions used in VBA (including Min) will return a runtime (type mismatch) error with arrays larger than 65536 rows.

    Interesting... I have never received a "type mismatch" error running this script. And, I'm not using "on error resume next" to avoid error messages.
    Perhaps because of the aforementioned reason - using Excel 2013.
    Last edited by gth826a; 07-19-2017 at 03:44 PM.

  11. #11
    Registered User
    Join Date
    10-03-2012
    Location
    St. Louis, Missouri
    MS-Off Ver
    MS 2013, 64Bit, 8 Cores, 20GB
    Posts
    18

    Re: WorksheetFunction Limitations (specifically .max & .min)

    Quote Originally Posted by xlnitwit View Post
    Are you running 2010? Is your array 1 dimensional or more?

    2013, 1D array, 64Bit machine, 20GB ram, no "type mismatch" error message...

    Section of the Code:

    Please Login or Register  to view this content.
    The correct value, at index 119975, is not showing up as the dataMin value... Instead it's some value near index 55,000 (can't recall exactly).




    Thanks in advance,
    Last edited by gth826a; 07-19-2017 at 03:49 PM.

  12. #12
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: WorksheetFunction Limitations (specifically .max & .min)

    Unresponsive to the original question, if you are looping through the data, why not find the min in the loop?

  13. #13
    Registered User
    Join Date
    10-03-2012
    Location
    St. Louis, Missouri
    MS-Off Ver
    MS 2013, 64Bit, 8 Cores, 20GB
    Posts
    18

    Re: WorksheetFunction Limitations (specifically .max & .min)

    Quote Originally Posted by shg View Post
    Unresponsive to the original question, if you are looping through the data, why not find the min in the loop?
    That would work, and is the solution I used... I'm just inquiring as to if there's a deficiency in the Application.Min WorksheetFunction, or if I'm overlooking something else.

  14. #14
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: WorksheetFunction Limitations (specifically .max & .min)

    Are you certain the value in question is not being stored as a String?

  15. #15
    Registered User
    Join Date
    10-03-2012
    Location
    St. Louis, Missouri
    MS-Off Ver
    MS 2013, 64Bit, 8 Cores, 20GB
    Posts
    18

    Re: WorksheetFunction Limitations (specifically .max & .min)

    Quote Originally Posted by xlnitwit View Post
    Are you certain the value in question is not being stored as a String?

    Yes.
    All values in the data range are numbers, and formated as such.
    The incorrect value returned has the exact same format properties, and came from the exact same data-logger file, as the value that should be returned (the correct minimum value of the data range).
    Last edited by gth826a; 07-20-2017 at 10:11 AM.

  16. #16
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: WorksheetFunction Limitations (specifically .max & .min)

    Values don't have format properties. Without seeing the data I couldn't really comment as to why it isn't working for you.

  17. #17
    Registered User
    Join Date
    10-03-2012
    Location
    St. Louis, Missouri
    MS-Off Ver
    MS 2013, 64Bit, 8 Cores, 20GB
    Posts
    18

    Re: WorksheetFunction Limitations (specifically .max & .min)

    Quote Originally Posted by xlnitwit View Post
    Values don't have format properties. Without seeing the data I couldn't really comment as to why it isn't working for you.
    Understood.
    To be more specific - the range/cells in the Excel worksheet that are being referenced by the macro/script are formatted as numbers.
    Did that answer your question, or am I misunderstanding your speculation to dataSet containing text values?

  18. #18
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: WorksheetFunction Limitations (specifically .max & .min)

    The format of a cell doesn't necessarily reflect the type of its content unfortunately. If you change the format of the cell in question, does the displayed value alter accordingly?

  19. #19
    Registered User
    Join Date
    10-03-2012
    Location
    St. Louis, Missouri
    MS-Off Ver
    MS 2013, 64Bit, 8 Cores, 20GB
    Posts
    18

    Re: WorksheetFunction Limitations (specifically .max & .min)

    Quote Originally Posted by xlnitwit View Post
    The format of a cell doesn't necessarily reflect the type of its content unfortunately. If you change the format of the cell in question, does the displayed value alter accordingly?
    Yes.
    When the format changed in the workbook (without VBA coding) the entire range's values change accordingly.

  20. #20
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: WorksheetFunction Limitations (specifically .max & .min)

    Then again I wouldn't be able to comment without seeing the workbook.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Dynamic VBA fields (date specifically)?
    By mrbusto71 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-01-2016, 05:44 PM
  2. [SOLVED] Help Using Wildcards (specifically-*) with UDFs
    By bananius in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-26-2014, 11:05 AM
  3. [SOLVED] Mod function to find specifically 5 only
    By Journeyman3000 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-08-2014, 02:33 AM
  4. [SOLVED] Have a number value convert to text specifically
    By pfaz73 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-27-2012, 12:58 PM
  5. Formatting a cell specifically by date
    By rymember in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-14-2011, 09:04 AM
  6. Code specifically for CombBox auto population
    By ZIXILPLIX in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-03-2010, 11:31 PM
  7. Activating workbook without naming it specifically
    By Patrick Young in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-08-2005, 11:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1