+ Reply to Thread
Results 1 to 6 of 6

Pushing to an Array in VBA

  1. #1
    Registered User
    Join Date
    02-10-2013
    Location
    Frankfurt, Germany
    MS-Off Ver
    Excel 2003
    Posts
    57

    Pushing to an Array in VBA

    Hi,

    It'd be extremely useful for me if I could push values to an already existing array which I can dynamically resize.

    I understand about ReDim, and I wrote a little subroutine which does what I want. However, it's making my code run really slowly:

    Please Login or Register  to view this content.
    Is it the case that ReDim actually copies the whole of the array to a different location every time you do it? So the more times you call a sub like the one that I listed above, the slower it gets. Is there some way around this?

    I heard Collections might be a solution to this, but I don't really know much about them.

    Many thanks!

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Pushing to an Array in VBA

    Yes, redim preserve will slow down the process.

    What's generally recommended is to set dimension of array at start of loop.

    Alternately you could use Scripting.Dictionary or Collection as you've mentioned.

    But without sample workbook and what you are trying to accomplish. It's bit hard to give you appropriate solution.

    Have a read of link. It's good introduction to Collection/Array/Dictionary model.
    http://analystcave.com/excel-vba-dic...ta-structures/

    Edit: You can also use ArrayList. Have a read of links below.
    http://www.snb-vba.eu/VBA_Arraylist_en.html
    http://www.aspheute.com/english/20001025.asp
    Last edited by CK76; 12-18-2017 at 10:11 AM. Reason: See Edit:
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Pushing to an Array in VBA

    Arrays can be redimensioned, they typically should not be. Collections and Dictionary are much better when you need to constantly add/remove elements from the set.

    You could also potentially restructure your code to redimension 1 time instead of many. So if for example your conditions for adding something to the array was pretty simple you may do a 2 pass solution. First pass to just simply get the count of items to add the array, then redimension it 1 time, then a second pass to actually add items to the array.

    If the array of values is coming from a range of cells, you may also consider filtering the range and then dumping the results into an array (much quicker than evaluating cells and looping cells in a range).

    As mentioned without a sample file, all the code, what your end goal is its hard to give a solid answer.
    Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.

    "I am here to help, not do it for people" -Me

  4. #4
    Registered User
    Join Date
    02-10-2013
    Location
    Frankfurt, Germany
    MS-Off Ver
    Excel 2003
    Posts
    57

    Re: Pushing to an Array in VBA

    Hi,

    Thanks so much for the reply. I'll certainly take a look at those resources.

    A brief explanation of what I need it for:

    I've been working on a JSON parser for vba, and part of it works by making an array which records as an integer the position of every control character in the string.

    Anyway, I'll see if the Collection solution is any good.

  5. #5
    Registered User
    Join Date
    02-10-2013
    Location
    Frankfurt, Germany
    MS-Off Ver
    Excel 2003
    Posts
    57

    Re: Pushing to an Array in VBA

    Hi,

    Thanks a lot for the two-step solution - I guess this would be way faster than redim, but it's still really annoying that you can't just push it to an array.

    The purpose of the array in this case is to record the string positions of certain characters, within a string, so the length of the array will certainly be less than the length of the string. So maybe I could specify the lenth of the array to be the same length as the string, and then redim it one time only at the end. Though perhaps the two step solution is better than that.

    Many thanks!

    I also considered supplying an upper limit

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Pushing to an Array in VBA

    If you need to parse JSON, there's few add-in/module out there already. No need to reinvent the wheel.

    https://github.com/VBA-tools/VBA-JSON

    You can also use "ScriptControl" to parse JSON. However, do note that it's dangerous to parse unverified JSON string via this method.
    As ScriptControl can expose your system to malicious JS code, see links below for details.
    http://ashuvba.blogspot.ca/2014/09/j...rough-net.html
    https://stackoverflow.com/questions/...94373#30494373

+ 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. Pulling/Pushing Data
    By ethanT in forum Excel General
    Replies: 1
    Last Post: 07-18-2014, 01:53 PM
  2. Pushing Excel to Word
    By PippiLaRue in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-11-2013, 05:36 AM
  3. Pushing data into excel
    By SEMMatt in forum Excel General
    Replies: 6
    Last Post: 09-04-2011, 02:57 PM
  4. Pushing data
    By gyra in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 07-30-2010, 02:12 PM
  5. row pushing macro
    By wertti9 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-17-2006, 09:20 AM
  6. Pushing results HELP!
    By Zuboloma in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-19-2005, 02:05 PM
  7. [SOLVED] Pushing my luck
    By gb_S49 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-11-2005, 01:06 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