+ Reply to Thread
Results 1 to 10 of 10

Passing Multi-Dimensional Arrays

  1. #1
    Registered User
    Join Date
    03-24-2011
    Location
    Edmonton, Alberta, Canada
    MS-Off Ver
    Excel 2010
    Posts
    7

    Passing Multi-Dimensional Arrays

    Hi There,

    I'm trying to pass a multidimensional array from one workbook into another in an effort to make a comprehensive summary of project status for all-time. I don't know what's going on but, all of the data makes it into the array, but when I try to move it over to the next workbook it changes types or something and I don't think it's recognized as an Array anymore. Anyone understand why it would be doing this? Or am i just declaring some Variables incorrectly? I have tried battling with this for a while now...


    I'm dealing with the following two pieces of code:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

  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,678

    Re: Passing Multi-Dimensional Arrays

    What is RetrieveData() and where is it located?

    You have many undeclared variables. Are they declared elsewhere?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    03-24-2011
    Location
    Edmonton, Alberta, Canada
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Passing Multi-Dimensional Arrays

    I have two workbooks going, the SelectFiles() Is a macro in WB1, and RetrieveData() is a macro found in WB2. I'm trying to have WB1 be a master file that collects data from any files you select in the open dialog box that initially opens up. I want it to open up the files, find where the data is located, pull it out and put it into an array, pass that array back to the master file and dump it into the spreadsheet. Eventually I will be adding more code to add successive values together based on the date in the first column of the array. All variables that are used have been declared within these two files.

  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,678

    Re: Passing Multi-Dimensional Arrays

    I guess I don't understand your code. You're opening a text file, which can't have a macro in it, but it appears that your code does.

    RetrieveData loads NewArray but the code stores DataArray.

  5. #5
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,647

    Re: Passing Multi-Dimensional Arrays

    Assume 2 workbooks: 'source.xls' and 'goal.xls'

    To 'import' a 2 dimensional array from sheet1 in 'source.xls' to the first empty row in sheet1 in 'goal.xls' the basic code is:

    Please Login or Register  to view this content.
    Some may call this:
    - copying from source.xls to goal.xls
    - exporting from source.xls to goal.xls
    - importing from source.xls into goal.xls
    - transferring a range from source.xls to goal.xls
    - transferring an array from source.xls to goal.xls
    - passing a multidimensional array

    But the result willl be identical.
    Last edited by snb; 03-24-2011 at 06:37 PM.



  6. #6
    Registered User
    Join Date
    03-24-2011
    Location
    Edmonton, Alberta, Canada
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Passing Multi-Dimensional Arrays

    The SelectFiles() Macro is in the file 'MasterUpdates.xlsm', when you run this macro, it initally opens up a 'OPEN' Dialog box where the user may select the amount of files to include in the report. This is because we are needing to draw information off several different files (40-50). The user selects the files, and then a new function is called in the line:

    Please Login or Register  to view this content.
    It goes through the for loop for the files selected by the user, picking one at a time and running RetrieveData() Function.

    This function, starts at the top of the sheet and travels down the "Fabrication Tonnage" column until it finds a value other then zero (meaning when fabrication began). Then it starts at the bottom of the page and works it's way up to determine the last day of manufacturing... thus when the project was completed. An array is created based off the size of cells we just found, and data from eleven different columns is extracted. The first column is dates, the rest are all numbers.

    It is at this point, I would like to send this array back over to the parent sub where it will dump the information into the sheet Data Dump.

    This will be performed for numerous different projects and the goal is to create a master ton/day sheet with a weekly breakout, summing up the total tonnage on each day for every different project. This is where i'm trying to go.

    I need to do this in such a way that I can control the first column of the array to be able to sort the data by date, and then combine similar dates by adding everything up. I believe I have made a mistake in the data types i'm calling, or how the function is setup...

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

    Re: Passing Multi-Dimensional Arrays

    Did you read this?
    RetrieveData loads NewArray but the code stores DataArray.

  8. #8
    Registered User
    Join Date
    03-24-2011
    Location
    Edmonton, Alberta, Canada
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Passing Multi-Dimensional Arrays

    I did read that, how do you know that? I dont understand how RetrieveData loads a stored value? I want it to export a stored value.

    I have tried using DataArray in position of NewArray, one was simply declared a Variant and the other a Long.
    Last edited by dldecler; 03-24-2011 at 08:26 PM.

  9. #9
    Registered User
    Join Date
    03-24-2011
    Location
    Edmonton, Alberta, Canada
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Passing Multi-Dimensional Arrays

    I Believe I'm going wrong somewhere in these areas,

    in the macro selectfiles()

    Please Login or Register  to view this content.
    Or in RetrieveData()

    Please Login or Register  to view this content.
    Does anyone understand where I am going wrong? I have tried so many different things and have been battling with this for a while? I'm stuck...

  10. #10
    Registered User
    Join Date
    03-24-2011
    Location
    Edmonton, Alberta, Canada
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Passing Multi-Dimensional Arrays

    Thx for all the support on this one..

    I just wanted to post the solution in case anyone else ran into this problem. Seems trying to return a value from a different macro on a different workbook, doesn't really work... All I had to do was combine the macro's on the same 'Workbook' and it worked fine... Here is the finished code with a little bit more work done to it.

    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)

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