title: VBA Excel 2-Dimensional Arrays
body: <p>I was trying to find out how to declare a 2-Dimensional array but all of the examples I have found so far are declared with set integers.  I'm trying to create a program that will utilize two 2-Dimensional arrays and then perform simple operations on those arrays (such as finding difference or percent).  The arrays are populated by numbers in Excel sheets (one set of numbers is on Sheet1 and another set is on Sheet2, both sets have the same number of rows and columns).</p>
  
  <p>Since I don't know how many rows or columns there are I was going to use variables.</p>
  
  <pre><code>Dim s1excel As Worksheet
  Dim s2excel As Worksheet
  Dim s3excel As Worksheet
  Dim firstSheetName As String
  Dim secondSheetName As String
  Dim totalRow As Integer
  Dim totalCol As Integer
  Dim iRow As Integer
  Dim iCol As Integer
  
  Set s1excel = ThisWorkbook.ActiveSheet
  
  ' Open the "Raw_Data" workbook
  Set wbs = Workbooks.Open(file_path &amp; data_title)
  wbs.Activate
  ActiveWorkbook.Sheets(firstSheetName).Select
  Set s2excel = wbs.ActiveSheet
  
  ' Find totalRow, totalColumn (assumes there's values in Column A and Row 1 with no blanks)
  totalRow = ActiveSheet.Range("A1").End(xlDown).Row
  totalCol = ActiveSheet.Range("A1").End(xlToRight).Column
  
  Dim s2Array(totalRow, totalCol)
  Dim s3Array(totalRow, totalCol)
  
  For iRow = 1 To totalRow
      For iCol = 1 To totalCol
          s2Array(iRow, iCol) = Cells(iRow, iCol)
      Next iCol
  Next iRow
  
  ActiveWorkbook.Sheets(secondSheetName).Select
  Set s3excel = wbs.ActiveSheet
  
  For iRow = 1 To totalRow
      For iCol = 1 To totalCol
          s3Array(iRow, iCol) = Cells(iRow, iCol)
      Next iCol
  Next iRow
  </code></pre>
  
  <p>When I attempt to run this I get a compile-time error at the <code>Dim s2Array(totalRow, totalCol)</code> saying that a constant expression is required. The same error occurs if I change it to <code>Dim s2Array(1 To totalRow, 1 To totalCol)</code>.  Since I don't know what the dimensions are from the get go I can't declare it like <code>Dim s2Array(1, 1)</code> because then I'll get an out-of-bounds exception.</p>
  
  <p>Thank you,</p>
  
  <p>Jesse Smothermon</p>