+ Reply to Thread
Results 1 to 5 of 5

Array size unknown until the first "loop", how to correction dim/redim the array

  1. #1
    Registered User
    Join Date
    12-08-2011
    Location
    Kentucky
    MS-Off Ver
    Excel 2010
    Posts
    5

    Array size unknown until the first "loop", how to correction dim/redim the array

    All,

    I am new to VBA, but I am trying to write a bit of code to download financial data from Yahoo, store it temporarily in a spreadsheet, copy the data to an array and repeat for X number of times (X = the number of stocks the user wants to evaluate).

    Array (i, j, k)
    i = the number of rows or the days of information that the user wants to evaluate. This is unknown until the very first loop (look at the first stock data pulled down by yahoo, weekends and holidays make it this way).
    j = the number of columns of data collected for each stock. This is 7 or 6 in the case of an array as it is 0 based (0, 1, ... 6). So j is always 6
    k = the number of stocks or layers to my array cube of data that I am looking to build. I don't know this until the user input is gathered (Ex, they want to look at 10 stocks, I know I should set k to 9.

    How can I correctly dim the array in the beginning of the code with all my other variables and then in the first loop (I have the logic to determine this sum(all counters) = 0 is only true in the very first loop (looking at first row/column of data in the first stock).

    Dim array() ????

    After the size is known (iMax is a variable set to the value of the number of rows -1 for 0 base, similar for kMax and the number of stocks to evaluate)
    Redim array (iMax, 6, kMax)

    This seems like the basic idea, but i can't seem to get to to work correctly. At the point I redim, I do not have any data that I care about in the array (preserve not needed). The easy solution is to set iMax = 30 (never 30 days of trading in 30 days and just limit the search range to a month or less) and similar for kmax, but that would leave a HUGE amount of wasted memory.

    Thanks,
    Mark

  2. #2
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Array size unknown until the first "loop", how to correction dim/redim the array

    ReDim only works on the last dimension, K in your set up. It sounds like the first input from Yahoo should modify both the first and second dimensions (i and j). What you might do is grab that first data, don't put it in your Array, Redim the Array and then import the rest of the Yahoo data.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  3. #3
    Registered User
    Join Date
    12-08-2011
    Location
    Kentucky
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Array size unknown until the first "loop", how to correction dim/redim the array

    Update,

    I tried it that way, but it I got a compile error saying "constant expression required"

    Dim array (iMax, 6, kMax) where the two variables are set based on the download of the first set of data (i know how many days and how many stocks I am looking at). iMax and kMax are set one time and do not change. The dim statement is executed only one time. There has to be a way to dim or resize/dim an array with a variable inside the (). Any help is appreciated.
    Last edited by menichols74; 07-14-2013 at 12:46 PM. Reason: tried the suggestion, new problem

  4. #4
    Registered User
    Join Date
    12-08-2011
    Location
    Kentucky
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Array size unknown until the first "loop", how to correction dim/redim the array

    Quote Originally Posted by mikerickson View Post
    ReDim only works on the last dimension, K in your set up. It sounds like the first input from Yahoo should modify both the first and second dimensions (i and j). What you might do is grab that first data, don't put it in your Array, Redim the Array and then import the rest of the Yahoo data.
    Mike,

    Thank you. I wrote a small separate code that mimicked the size and dynamic nature of the array. I set it (redim) outside of the loop and then looped through with no problem. I then applied it to the real code and pulled down a single stock just to get the number of rows of data, redimmed the array and then did the loops. It worked. I now have a nice cube of data that I have to figure out the logic for analysis.

    Please mark this as solved.

    R/,
    Mark

  5. #5
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Array size unknown until the first "loop", how to correction dim/redim the array

    Please Login or Register  to view this content.
    As you found, Dim requires contants, but Redim will take variables
    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. [SOLVED] Assigning a 2D Array to a bigger size range results in "#N/A" in the uncovered elements
    By TopXorGuy in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-24-2013, 10:45 AM
  2. Assigning values into array using the "Array" vba function.......not working
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-17-2011, 04:21 PM
  3. Assign sheet value to array... and redim the array size
    By Orange.CL in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-27-2010, 07:18 AM
  4. [SOLVED] If changed array formula reduce ""\""\""\ - signs to #Missing, will it make ...
    By Maria J-son in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2006, 08:25 AM
  5. [SOLVED] Open multiple "unknown" filenames within a macro (array setup)
    By need_some_help in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-23-2005, 04: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