+ Reply to Thread
Results 1 to 8 of 8

Forgetful Arrays

  1. #1
    Registered User
    Join Date
    09-08-2014
    Location
    -
    MS-Off Ver
    -
    Posts
    68

    Forgetful Arrays

    Greetings!
    I'm starting to experiment with arrays to expand my vba proficiency and decided to see if I could use an array to count the number of instances of a bunch of entries in a list. This obviously could then be used to show duplicates or unique values etc. etc.
    I know there are likely more efficient ways to do it - it's not really about the end goal but more about figuring out how to use arrays!

    Anyhow, I have the following code:
    Please Login or Register  to view this content.
    This is what I get from th
    0 Number of instances of 0: 0
    0 Number of instances of 0: 0
    0 Number of instances of 0: 0
    0 Number of instances of 0: 0
    0 Number of instances of 0: 0
    0 Number of instances of 0: 0
    0 Number of instances of 0: 0
    0 Number of instances of 0: 0
    0 Number of instances of 0: 0
    0 Number of instances of 0: 0
    0 Number of instances of 0: 0
    0 Number of instances of 0: 0
    0 Number of instances of 0: 0
    0 Number of instances of 0: 0
    0 Number of instances of 0: 0
    0 Number of instances of 0: 0
    0 Number of instances of 0: 0
    0 Number of instances of 0: 0
    0 Number of instances of 0: 0
    0 Number of instances of 0: 0
    0 Number of instances of 0: 0
    0 Number of instances of 0: 0
    0 Number of instances of 0: 0
    0 Number of instances of 0: 0
    0 Number of instances of 0: 0
    0 Number of instances of 0: 0
    0 Number of instances of 0: 0
    0 Number of instances of 0: 0
    0 Number of instances of 0: 0
    7 Number of instances of 7: 1

    So, for some reason it only remembers the last entry to the table - which is the 7. Everything else seems to get forgotten along the way.
    What am I doing wrong, and do you have any generic tips for dealing with arrays where I'm going horribly wrong? I know it's inefficient to keep redefining the array - could I possibly set the array size from the start to be the total number of entries in the sample I'm analysing (which obviously would be the max number of inputs if there were zero duplicates) and then resize it smaller to match the number of actual entries to the array?
    Any suggestions would be appreciated!
    Thanks,
    Lady Marmalade

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    49,039

    Re: Forgetful Arrays

    You would need to use ReDim Preserve.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,239

    Re: Forgetful Arrays

    Redim deletes all existing contents of an array.

    So:
    Please Login or Register  to view this content.
    Deletes all the current entries and resizes the array.

    You really want
    Please Login or Register  to view this content.
    However it won't work since you can only resize the final dimension of the array ie. 2.

    Might also be worth noting that arrays are indexed from zero unless otherwise specified, so Redim Something(2) has 3 "spaces".

  4. #4
    Registered User
    Join Date
    09-08-2014
    Location
    -
    MS-Off Ver
    -
    Posts
    68

    Re: Forgetful Arrays

    Ah yes, I forgot that arrays started at zero.
    The issue was that when I try using ReDim Preserve, it comes up with the "subscript out of range" error, hence why I deleted it. Any reason why this'd happen?

  5. #5
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,239

    Re: Forgetful Arrays

    Yes, I told you in my post above

  6. #6
    Registered User
    Join Date
    09-08-2014
    Location
    -
    MS-Off Ver
    -
    Posts
    68

    Re: Forgetful Arrays

    Right, I hadn't quite understood properly. So really, my best bet is to just switch the whole thing over - i.e. have it be summary(2, size) instead?
    I'll give that a try and get back to you. Thanks!

  7. #7
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,239

    Re: Forgetful Arrays

    Yep, or use a collection/dictionary.

    This isn't particularly a good example for learning arrays with since it's hard and there's a lot to consider.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    49,039

    Re: Forgetful Arrays

    You might have more success if you simply read the whole range into the array and do all your processing on the contents of the array.

    For example:

    Please Login or Register  to view this content.

+ 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. storing multiple value in a single cell (through arrays or without arrays)?
    By mak51061 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 12-25-2014, 09:27 PM
  2. Reset Application - a tip for forgetful development
    By Olly in forum Tips and Tutorials
    Replies: 0
    Last Post: 02-28-2014, 05:21 PM
  3. Declaring multiple multi-dimensional arrays (jagged arrays) - compile error?
    By dfribush in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-20-2013, 05:06 PM
  4. vba compare arrays and remove exact matching arrays
    By jacojvv in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 10-25-2013, 07:30 AM
  5. [SOLVED] New to arrays-where do I find a good beginners guide to multi dimensional arrays
    By mc84excel in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-04-2013, 07:44 PM

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