locked
Is there a file format to save VBA collections? RRS feed

  • Question

  • I have written VBA code that opens a huge flat file and loads it into arrays.  I then populate tabs of an MSExcel workbook.

    My current application has two major flaws: 1- it loads data into arrays instead of collections and 2 - the action of loading the arrays is bound to the action of populating the excel spreadsheet.  This means, when I want to change the format or content in the excel output, I have to re-run the import/parsing hokey pokey.

    Now that I know what I want to do and how to do it poorly, I want to replace my application with one that uses collections of objects instead of arrays and I want to save the inputted data in collection format and then separately do the excel output operations.

    Is there a file format that saves data in the collections that I populate?  I'd rather not have to load this data in MSAccess relational database tables if I don't have to.

    Friday, January 8, 2016 2:23 PM

Answers

  • Almost all of my arrays are multiple columns and I will be exporting multiple arrays.  Unless I've misunderstood your example, I don't understand where one array stops and the next starts.

    Don't worry about that, VB knows how to do.

    Andreas.

    Sub Test2()
      Dim Data, Data2, i As Long, j As Long
      
      'Create an array of arrays
      ReDim Data(1 To 3)
      Data(1) = Array(1, 2, 3)
      Data(2) = Array(4, 5, 6)
      Data(3) = Array(7, 8, 9)
      
      'Save as binary file
      If Not WriteArray(ThisWorkbook.Path & "\Data.array", Data) Then
        MsgBox "Write error"
      End If
      
      'Read it back
      If Not ReadArray(ThisWorkbook.Path & "\Data.array", Data2) Then
        MsgBox "Read error"
      End If
      
      'Compare
      For i = LBound(Data) To UBound(Data)
        For j = LBound(Data(i)) To UBound(Data(i))
          If Data2(i)(j) <> Data(i)(j) Then
            Stop 'Uuups. ;-)
          End If
        Next
      Next
    End Sub
    

    • Marked as answer by Denis Backer Monday, January 11, 2016 2:12 PM
    Saturday, January 9, 2016 7:00 AM

All replies

  • Is there a file format that saves data in the collections that I populate? 

    No, but there is one for arrays.

    Andreas.

    Option Explicit
    
    Sub Test()
      Dim Data, Data2, i As Long
      
      'Create an array with 100.000 numbers
      ReDim Data(1 To 100000)
      For i = LBound(Data) To UBound(Data)
        Data(i) = i
      Next
      
      'Save as binary file
      If Not WriteArray(ThisWorkbook.Path & "\Data.array", Data) Then
        MsgBox "Write error"
      End If
      
      'Read it back
      If Not ReadArray(ThisWorkbook.Path & "\Data.array", Data2) Then
        MsgBox "Read error"
      End If
      
      'Compare
      For i = LBound(Data) To UBound(Data)
        If Data2(i) <> Data(i) Then
          Stop 'Uuups. ;-)
        End If
      Next
    End Sub
    
    Function WriteArray(ByVal FileName As String, ByRef Arr, _
        Optional ByVal OverWrite As Boolean = True) As Boolean
      'Writes an array to disk
      Dim ff As Integer
      If Dir(FileName) <> "" Then
        If Not OverWrite Then Exit Function
      End If
      On Error GoTo ExitPoint
      ff = FreeFile
      Open FileName For Binary Access Write Lock Read Write As #ff
      Put #ff, , Arr
      Close #ff
      WriteArray = True
    ExitPoint:
    End Function
    
    Function ReadArray(ByVal FileName As String, ByRef Arr) As Boolean
      'Reads an array from disk
      Dim ff As Integer
      If Dir(FileName) = "" Then Exit Function
      On Error GoTo ExitPoint
      ff = FreeFile
      Open FileName For Binary Access Read Lock Write As #ff
      Get #ff, , Arr
      Close #ff
      ReadArray = True
    ExitPoint:
    End Function
    

    • Proposed as answer by André Santo Friday, January 8, 2016 3:53 PM
    Friday, January 8, 2016 3:08 PM
  • Almost all of my arrays are multiple columns and I will be exporting multiple arrays.  Unless I've misunderstood your example, I don't understand where one array stops and the next starts.

    Friday, January 8, 2016 7:12 PM
  • Almost all of my arrays are multiple columns and I will be exporting multiple arrays.  Unless I've misunderstood your example, I don't understand where one array stops and the next starts.

    Don't worry about that, VB knows how to do.

    Andreas.

    Sub Test2()
      Dim Data, Data2, i As Long, j As Long
      
      'Create an array of arrays
      ReDim Data(1 To 3)
      Data(1) = Array(1, 2, 3)
      Data(2) = Array(4, 5, 6)
      Data(3) = Array(7, 8, 9)
      
      'Save as binary file
      If Not WriteArray(ThisWorkbook.Path & "\Data.array", Data) Then
        MsgBox "Write error"
      End If
      
      'Read it back
      If Not ReadArray(ThisWorkbook.Path & "\Data.array", Data2) Then
        MsgBox "Read error"
      End If
      
      'Compare
      For i = LBound(Data) To UBound(Data)
        For j = LBound(Data(i)) To UBound(Data(i))
          If Data2(i)(j) <> Data(i)(j) Then
            Stop 'Uuups. ;-)
          End If
        Next
      Next
    End Sub
    

    • Marked as answer by Denis Backer Monday, January 11, 2016 2:12 PM
    Saturday, January 9, 2016 7:00 AM
  • Please help

    How to use 

    Test2() To write data to data.array without removing (Append to existign file)

    and how to use it to store data like Type record in VBA?

    ->I have forms and i want to store data in a file

    Thanks

    Friday, August 26, 2016 2:02 PM
  • To write data to data.array without removing (Append to existign file)

    and how to use it to store data like Type record in VBA?

    a) No way. You have to read in the whole file, then expand/modify the array as you like and finally write the whole array back.

    b) Please read the VBA-Help on Open, there is a sample.

    Andreas.

    I'm on vacation next weeks, an answer takes awhile if you have furthermore questions.

    Friday, August 26, 2016 3:17 PM
  • Hi Denis Backer,

    This is the forum to discuss questions and feedback for Excel for Developers, since this issue is more related to VBA, I suggest that you could post your question to the MSDN forum for Visual Basic for Applications (VBA)

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=isvvba

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, 
    and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. 

    Thanks for your understanding.
    Monday, August 29, 2016 9:37 AM