+ Reply to Thread
Results 1 to 7 of 7

Worksheetfunction.min not working with array

  1. #1
    Registered User
    Join Date
    08-04-2012
    Location
    England
    MS-Off Ver
    Office 365 MSO 64-Bit
    Posts
    6

    Unhappy Worksheetfunction.min not working with array

    Please Login or Register  to view this content.
    After this code runs STR2 contains "8718,1552,3930,1538" which is also converted in to a zero based Array (arr). I want to find the min value (1538 in this case) but worksheetfunction.min(arr) returns 0 and worksheetfunction.min(str2) returns 8.71815523930153E+15

    what am i doing wrong? can someone please help me?

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Worksheetfunction.min not working with array

    If I were debugging this, I would look carefully at the data types of each variable/array element.
    After this code runs STR2 contains "8718,1552,3930,1538"
    which would be a text string.
    In my experience, the Split() function returns an array of strings, and Excel's MIN() function is programmed to ignore strings (including numbers stored as strings), worksheetfunction.min(arr) returning 0 seems correct to m, assuming that arr() is an array of strings at this point (you should be able to see the data type in your locals window).

    worksheetfunction.min(str2) returning that large number surprises me a little. It appears that VBA is doing some kind of string to number conversion where it is assuming those commas are thousands separators, so it assumes that the entire text string is one large number. Of course, the minimum of one large number is whatever that number happens to be (in this case 871815523930153 -- up to the 15 digits that a double will hold).

    If I were doing this, I think I would just build the array inside of the For... Next loop rather than a comma delimited text string. maybe something like
    Please Login or Register  to view this content.
    that should build arr as an array of longs that shows where in midbit each of the strings in str1 is found. (I cannot test it here, but you seem to have enough skill to test, debug, and edit as needed).
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Worksheetfunction.min not working with array

    Please Login or Register  to view this content.
    arr is a string type array, so no calculation
    You need to create Variant or numeric type array.
    e.g
    Please Login or Register  to view this content.

  4. #4
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Worksheetfunction.min not working with array

    I believe the min function needs an array as the argument, not a string.

  5. #5
    Registered User
    Join Date
    08-04-2012
    Location
    England
    MS-Off Ver
    Office 365 MSO 64-Bit
    Posts
    6

    Re: Worksheetfunction.min not working with array

    Quote Originally Posted by jindon View Post
    Please Login or Register  to view this content.
    arr is a string type array, so no calculation
    You need to create Variant or numeric type array.
    e.g
    Please Login or Register  to view this content.
    Thanks for this but getting Subscript out of range on Dim Arr() however I had already declared Arr as a variant and that worked with your code but still gave me zero as the result? Anyway found a way around it so all good but thanks for your time much appreciated.

  6. #6
    Registered User
    Join Date
    08-04-2012
    Location
    England
    MS-Off Ver
    Office 365 MSO 64-Bit
    Posts
    6

    Re: Worksheetfunction.min not working with array

    Quote Originally Posted by MrShorty View Post
    If I were debugging this, I would look carefully at the data types of each variable/array element.
    which would be a text string.
    In my experience, the Split() function returns an array of strings, and Excel's MIN() function is programmed to ignore strings (including numbers stored as strings), worksheetfunction.min(arr) returning 0 seems correct to m, assuming that arr() is an array of strings at this point (you should be able to see the data type in your locals window).

    worksheetfunction.min(str2) returning that large number surprises me a little. It appears that VBA is doing some kind of string to number conversion where it is assuming those commas are thousands separators, so it assumes that the entire text string is one large number. Of course, the minimum of one large number is whatever that number happens to be (in this case 871815523930153 -- up to the 15 digits that a double will hold).

    If I were doing this, I think I would just build the array inside of the For... Next loop rather than a comma delimited text string. maybe something like
    Please Login or Register  to view this content.
    that should build arr as an array of longs that shows where in midbit each of the strings in str1 is found. (I cannot test it here, but you seem to have enough skill to test, debug, and edit as needed).
    Much shorter version of Array building than Jindon but i'm afraid I am getting zero regardless. Arr as long or Arr as Variant or Arr() building from string or building longs. Mmmmmmm. Anyway adapted and overcome and move on, thanks for your time though.

  7. #7
    Registered User
    Join Date
    08-04-2012
    Location
    England
    MS-Off Ver
    Office 365 MSO 64-Bit
    Posts
    6

    Re: Worksheetfunction.min not working with array

    Quote Originally Posted by dangelor View Post
    I believe the min function needs an array as the argument, not a string.
    Yep got that, thanks for your time.

+ 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] WorksheetFunction.CountA not working
    By timtim89 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-13-2021, 06:11 AM
  2. [SOLVED] vba for using WorksheetFunction with array formula
    By Andy Swain in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-29-2019, 11:52 AM
  3. Array Formula Using WorksheetFunction
    By tbaker818 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-06-2017, 03:41 AM
  4. [SOLVED] VBA for Application.Worksheetfunction.Vlookup not working
    By sintek in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-01-2017, 02:03 PM
  5. [SOLVED] WorksheetFunction.Sum() is not working, any advice?
    By EMyk08 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-03-2014, 06:29 PM
  6. [SOLVED] Do while with worksheetfunction.sum not working
    By timtim89 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-21-2012, 03:39 PM
  7. Array Formula Using WorksheetFunction
    By xcelion in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-18-2005, 07:15 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