title: Dynamic data structures in VBA
body: <p>Currently I am trying to improve the performance of my VBA program, because it takes forever to perform some table operations.</p>
  
  <p>During the programs runtime I am trying to store data in worksheets, but the write-operations take for ever and I would like to store this data dynamically instead of writing it into a worksheet to reduce the time it needs to run.</p>
  
  <p>I was thinking about using arrays instead of the worksheets to store the data but I am not quite sure whether this will work because I do not know how many rows/columns my table exactly has.</p>
  
  <p>Here my code, any help is appreciated!</p>
  
  <pre><code>Public row As Long
  Public rowMax As Long
  Public startRow As Integer
  Public materialType As String
  Public filter As String
  Public col As Integer
  Public colMax As Integer
  Public isUsed As Boolean
  Public a As Integer
  
  Sub bestimmeObFelderGenutzt()
      Debug.Print ("bestimmeObFelderGenutzt:begin" &amp; " " &amp; Now())
      With Sheets("Sheet1")
          filter = "I"
          startRow = 2
          rowMax = Sheets("Sheet1").Cells(.Rows.Count, "F").End(xlUp).row
          colMax = Sheets("Sheet1").Cells(1, .Columns.Count).End(xlToLeft).Column
          materialType = Sheets("Sheet1").Range(filter &amp; startRow).Value
  
          Dim ws As Worksheet
          Set ws = ThisWorkbook.Sheets.Add(After:= _
          ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
          ws.Name = "Nutzung"
  
          For col = 1 To colMax
              Sheets("Nutzung").Cells(1, col + 2).Value = Sheets("Sheet1").Cells(1, col).Value
          Next col
  
          For row = 2 To rowMax
              Sheets("Nutzung").Range("A" &amp; row).Value = Sheets("Sheet1").Range("A" &amp; row).Value
              Sheets("Nutzung").Range("B" &amp; row).Value = Sheets("Sheet1").Range("I" &amp; row).Value
                  For col = 1 To colMax
                      If IsEmpty(Sheets("Sheet1").Cells(row, col)) = False Then
                          isUsed = True
                          Sheets("Nutzung").Cells(row, col + 2).Value = 1
                      Else:
                          Sheets("Nutzung").Cells(row, col + 2).Value = 0
                      End If
                  Next col
          Next row
      End With
      Debug.Print ("bestimmeObFelderGenutzt:end" &amp; " " &amp; Now())
  End Sub
  
  
  
  Sub findeUngenutzteSpalten(ByVal materialType As String, pos As Integer)
      Debug.Print ("findeUngenutzteSpalten:begin" &amp; " " &amp; materialType &amp; " " &amp; Now())
      With Sheets(materialType)
          rowMax = Sheets(materialType).Cells(.Rows.Count, "F").End(xlUp).row
          colMax = Sheets(materialType).Cells(1, .Columns.Count).End(xlToLeft).Column
  
          Sheets("Auswertung").Cells(1, 1).Value = "Spaltenüberschrift:"
          Dim a As Integer
          For a = 1 To colMax
              Sheets("Auswertung").Cells(a + 1, 1).Value = Sheets("Sheet1").Cells(1, a).Value
          Next a
  
          Sheets("Auswertung").Cells(1, pos + 1).Value = materialType
  
          For col = 3 To colMax
              For row = 2 To rowMax
                      If Sheets(materialType).Cells(row, col).Value = 1 Then
                          Sheets("Auswertung").Cells(col - 1, pos + 1).Value = "Ja"
                          GoTo WeiterCol
                      Else:
                          If row = rowMax Then
                              Sheets("Auswertung").Cells(col - 1, pos + 1).Value = "Nein"
                          Else:
                              GoTo WeiterRow
                          End If
                      End If
  WeiterRow:
              Next row
  WeiterCol:
          Next col
      End With
      Debug.Print ("findeUngenutzteSpalten:end" &amp; " " &amp; materialType &amp; " " &amp; Now())
  End Sub
  
  Sub kopiereZeilen(ByVal materialType As String)
      Debug.Print ("kopiereZeilen:begin" &amp; " " &amp; materialType &amp; " " &amp; Now())
      With Sheets("Nutzung")
          rowMax = Sheets("Nutzung").Cells(.Rows.Count, "F").End(xlUp).row
  
          Dim ws As Worksheet
          Set ws = ThisWorkbook.Sheets.Add(After:= _
          ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
          ws.Name = materialType
  
          Sheets("Nutzung").Cells(1, 1).EntireRow.Copy Sheets(materialType).Cells(1, 1)
          Dim unusedRow As Long
  
          For row = 2 To rowMax
              unusedRow = Sheets(materialType).Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0).row
              If Sheets("Nutzung").Cells(row, 2).Value = materialType Then
                  Sheets("Nutzung").Cells(row, 2).EntireRow.Copy Sheets(materialType).Cells(unusedRow, 1)
              End If
          Next row
      End With
      Debug.Print ("kopiereZeilen:end" &amp; " " &amp; materialType &amp; " " &amp; Now())
  End Sub
  
  Sub allesZusammen()
  
      Debug.Print ("Hauptaufruf:begin" &amp; " " &amp; Now())
  
      Dim types(10) As String
      Dim element As Variant
      Dim pos As Integer
  
      bestimmeObFelderGenutzt
  
      types(0) = "A"
      types(1) = "B"
      types(2) = "C"
      types(3) = "D"
      types(4) = "E"
      types(5) = "F"
      types(6) = "G"
      types(7) = "H"
      types(8) = "I"
      types(9) = "J"
      types(10) = "K"
  
      Dim ws As Worksheet
          Set ws = ThisWorkbook.Sheets.Add(After:= _
          ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
          ws.Name = "Auswertung"
  
      For Each element In types
          kopiereZeilen (element)
          pos = Application.Match(element, types, False)
          findeUngenutzteSpalten element, pos
      Next element
      Debug.Print ("Hauptaufruf:end" &amp; " " &amp; Now())
  
  End Sub
  </code></pre>