title: Variable Array based on cell value
body: <p>The following code shows an array with fixed size, with six values.  </p>
  
  <p>What should I do to make its size depends on a cell value? For example, from 1 to 10?</p>
  
  <pre><code>Dim MySlideArray As Variant
          Dim MyRangeArray As Variant
  'List of PPT Slides to Paste to
     MySlideArray = Array(2, 3, 4, 5, 6, 7)
   'List of Excel Ranges to Copy from. For now, assume that it's always 6 ranges
    With tb.DataBodyRange
       MyRangeArray = Array(Worksheets(.Cells(1, 1).Value).Range(.Cells(1, 2).Value), _
                  Worksheets(.Cells(2, 1).Value).Range(.Cells(2, 2).Value), _
                  Worksheets(.Cells(3, 1).Value).Range(.Cells(3, 2).Value), _
                  Worksheets(.Cells(4, 1).Value).Range(.Cells(4, 2).Value), _
                  Worksheets(.Cells(5, 1).Value).Range(.Cells(5, 2).Value), _
                  Worksheets(.Cells(6, 1).Value).Range(.Cells(6, 2).Value))
  
  
  
  'Loop through Array data
  For x = LBound(MySlideArray) To UBound(MySlideArray)
  'Copy Excel Range
  MyRangeArray(x).Copy
  
  'Paste to PowerPoint and position
    On Error Resume Next
      Set shp = myPresentation.Slides(MySlideArray(x)).Shapes.PasteSpecial(DataType:=2) 'Excel 2007-2010
      Set shp = PowerPointApp.ActiveWindow.Selection.ShapeRange 'Excel 2013
    On Error GoTo 0]
  </code></pre>