+ Reply to Thread
Results 1 to 10 of 10

Passing Multi-Dimensional Arrays

Hybrid View

  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:

    Sub SelectFiles()
    
    Dim IntDump As Integer
    Dim wkBookName As String
    Dim DataArray As Variant
    Dim NewArray() As Long
    
    
    
    ArTemp = Application.GetOpenFilename(FileFilter:="All files (*.*), *.*", MultiSelect:=True)
     '(where ArTemp is a variant)
    For i = 1 To UBound(ArTemp)
    myfile = ArTemp(i)
    Workbooks.OpenText Filename:=myfile
    ' (do suff with this file)
    wkBookName = GetFilenameFromPath(myfile)
    Workbooks(wkBookName).Activate
    
    
    
    
    
    
    
    NewArray = Application.Run("'" & ActiveWorkbook.Name & "'" & "!ThisWorkbook.RetrieveData")
    'ReDim NewArray(1 To UBound(DataArray), 1 To 11)
    ThisWorkbook.Activate
    
    'Create a new worksheet to dump data, if it's already open, clear it's data.
    Set wSheet = Sheets("Data Dump")
        If wSheet Is Nothing Then
            Worksheets.Add.Name = "Data Dump"
        Else
            Worksheets("Data Dump").Cells.ClearContents
        End If
        
    'Worksheets("Data Dump").Cells(1, 1).Value = RetrieveData(1, 1)
    'MsgBox (UBound(DataArray))
    
    
    MsgBox (UBound(DataArray))
    'NumberOfDays = UBound(DataArray)
    
    'MsgBox (NumberOfDays)
    
    For IntDump = 1 To NumberOfDays
        Worksheets("Data Dump").Cells(IntDump, 1).Value = DataArray(IntDump, 1)
        Worksheets("Data Dump").Cells(IntDump, 2).Value = DataArray(IntDump, 2)
        Worksheets("Data Dump").Cells(IntDump, 3).Value = DataArray(IntDump, 3)
        Worksheets("Data Dump").Cells(IntDump, 4).Value = DataArray(IntDump, 4)
        Worksheets("Data Dump").Cells(IntDump, 5).Value = DataArray(IntDump, 5)
        Worksheets("Data Dump").Cells(IntDump, 6).Value = DataArray(IntDump, 6)
        Worksheets("Data Dump").Cells(IntDump, 7).Value = DataArray(IntDump, 7)
        Worksheets("Data Dump").Cells(IntDump, 8).Value = DataArray(IntDump, 8)
        Worksheets("Data Dump").Cells(IntDump, 9).Value = DataArray(IntDump, 9)
        Worksheets("Data Dump").Cells(IntDump, 10).Value = DataArray(IntDump, 10)
        Worksheets("Data Dump").Cells(IntDump, 11).Value = DataArray(IntDump, 11)
    Next IntDump
    
    MsgBox ("Success Dumping Data")
    
    Next i
    
    
    End Sub
    
    
    Function GetFilenameFromPath(ByVal strPath As String) As String
    ' Returns the rightmost characters of a string upto but not including the rightmost '\'
    ' e.g. 'c:\winnt\win.ini' returns 'win.ini'
        
        If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then
            GetFilenameFromPath = GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
        End If
    End Function
    Option Base 1
    
    Public Function RetrieveData() As Long()
    
    Dim IntPull As Integer, _
    IntDump As Integer, _
    wSheet As Worksheet, _
    RowCount As Integer, _
    DataStartRow As Double, _
    DataFinishRow As Double, _
    NumberOfDays As Integer
    Dim DataArray() As Long
    
    Worksheets("Weekly Output").Select
    Range("H3").Select
    
    Do Until Selection.Value <> 0
        Selection.Offset(1, 0).Select
    Loop
    
    DataStartRow = ActiveCell.Row
    
    
    Range("H3").End(xlDown).Offset(1, 0).Select
    
    Do Until Selection.Value <> 0
        Selection.Offset(-1, 0).Select
    Loop
    
    DataFinishRow = ActiveCell.Row
    
    'MsgBox (DataStartRow & " + " & DataFinishRow)
    
    Range("H" & DataStartRow, "H" & DataFinishRow).Select
    
    
    NumberOfDays = DataFinishRow - DataStartRow + 1
    
    ReDim DataArray(1 To NumberOfDays, 1 To 11) As Long
    
    RowCount = DataStartRow
    
    For IntPull = 1 To NumberOfDays
        DataArray(IntPull, 1) = Worksheets("Weekly Output").Cells(RowCount, 1).Value
        DataArray(IntPull, 2) = Worksheets("Weekly Output").Cells(RowCount, 2).Value
        DataArray(IntPull, 3) = Worksheets("Weekly Output").Cells(RowCount, 3).Value
        DataArray(IntPull, 4) = Worksheets("Weekly Output").Cells(RowCount, 8).Value
        DataArray(IntPull, 5) = Worksheets("Weekly Output").Cells(RowCount, 9).Value
        DataArray(IntPull, 6) = Worksheets("Weekly Output").Cells(RowCount, 14).Value
        DataArray(IntPull, 7) = Worksheets("Weekly Output").Cells(RowCount, 15).Value
        DataArray(IntPull, 8) = Worksheets("Weekly Output").Cells(RowCount, 20).Value
        DataArray(IntPull, 9) = Worksheets("Weekly Output").Cells(RowCount, 21).Value
        DataArray(IntPull, 10) = Worksheets("Weekly Output").Cells(RowCount, 26).Value
        DataArray(IntPull, 11) = Worksheets("Weekly Output").Cells(RowCount, 27).Value
        RowCount = RowCount + 1
    Next IntPull
    
    MsgBox ("Success on inputting data into array")
    
    'MsgBox ("Array Size is " & UBound(DataArray) - LBound(DataArray) + 1 & " Given size of " & NumberOfDays - 1)
    'MsgBox (UBound(DataArray))
    
    
    'Create a new worksheet to dump data, if it's already open, clear it's data.
    Set wSheet = Sheets("Data Dump")
        If wSheet Is Nothing Then
            Worksheets.Add.Name = "Data Dump"
        Else
            Worksheets("Data Dump").Cells.ClearContents
        End If
    MsgBox (UBound(DataArray))
    
    RetrieveData = DataArray()
    
    
    End Function

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    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 Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

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

    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:

    Sub snb()
      with workbooks("source.xls").sheets(1).cells(1).currentregion
         worksheets("goal.xls").sheets(1).cells(rows.count,1).end(xlup).offset(1).resize(.rows.count,.columns.count)=.Value
      End With
    End Sub
    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:

    NewArray = Application.Run("'" & ActiveWorkbook.Name & "'" & "!ThisWorkbook.RetrieveData")
    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...

+ 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