Excel VBA - How to Redim a 2D array

Question

In Excel via Visual Basic  I am iterating through a CSV file of invoices that is loaded into Excel  The invoices are in a determinable pattern by client   I am reading them into a dynamic 2D array  then writing them to another worksheet with older invoices  I understand that I have to reverse rows and columns since only the last dimension of an array may be Redimmed  then transpose when I write it to the master worksheet   Somewhere  I have the syntax wrong  It keeps telling me that I have already Dimensionalized the array  Somehow did I create it as a static array  What do I need to fix in order to let it operate dynamically   WORKING CODE PER ANSWER GIVEN  Sub InvoicesUpdate      Application Settings Application ScreenUpdating   False Application DisplayAlerts   False Application Calculation   xlCalculationManual   Instantiate control variables Dim allRows As Long  currentOffset As Long  invoiceActive As Boolean  mAllRows As Long Dim iAllRows As Long  unusedRow As Long  row As Long  mWSExists As Boolean  newmAllRows As Long   Instantiate invoice variables Dim accountNum As String  custName As String  vinNum As String  caseNum As String  statusField As String Dim invDate As String  makeField As String  feeDesc As String  amountField As String  invNum As String   Instantiate Workbook variables Dim mWB As Workbook  master Dim iWB As Workbook  import   Instantiate Worksheet variables Dim mWS As Worksheet Dim iWS As Worksheet   Instantiate Range variables Dim iData As Range   Initialize variables invoiceActive   False row   0   Open import workbook Workbooks Open   path excel invoices csv   Set iWB   ActiveWorkbook Set iWS   iWB Sheets  excel invoices csv   iWS Activate Range  A1   Select iAllRows   iWS UsedRange Rows Count  Count rows of import data   Instantiate array  include extra column for client name Dim invoices   ReDim invoices 10  0     Loop through rows  Do       Check for the start of a client and store client name     If ActiveCell Value    Account Number  Then          clientName   ActiveCell Offset -1  6  Value      End If      If ActiveCell Offset 0  3  Value  lt  gt  Empty And ActiveCell Value  lt  gt   Account Number  And ActiveCell Offset 2  0    Empty Then          invoiceActive   True           Populate account information          accountNum   ActiveCell Offset 0  0  Value         vinNum   ActiveCell Offset 0  1  Value          leave out customer name for FDCPA reasons         caseNum   ActiveCell Offset 0  3  Value         statusField   ActiveCell Offset 0  4  Value         invDate   ActiveCell Offset 0  5  Value         makeField   ActiveCell Offset 0  6  Value      End If      If invoiceActive   True And ActiveCell Value   Empty And ActiveCell Offset 0  6  Value   Empty And ActiveCell Offset 0  9  Value   Empty Then           Make sure something other than  0 was invoiced         If ActiveCell Offset 0  8  Value  lt  gt  0 Then               Populate individual item values              feeDesc   ActiveCell Offset 0  7  Value             amountField   ActiveCell Offset 0  8  Value             invNum   ActiveCell Offset 0  10  Value               Transfer data to array             invoices 0  row      TODAY                invoices 1  row    accountNum             invoices 2  row    clientName             invoices 3  row    vinNum             invoices 4  row    caseNum             invoices 5  row    statusField             invoices 6  row    invDate             invoices 7  row    makeField             invoices 8  row    feeDesc             invoices 9  row    amountField             invoices 10  row    invNum               Increment row counter for array             row   row   1               Resize array for next entry             ReDim Preserve invoices 10 row            End If      End If       Find the end of an invoice     If invoiceActive   True And ActiveCell Offset 0  9   lt  gt  Empty Then           Set the flag to outside of an invoice         invoiceActive   False      End If       Increment active cell to next cell down     ActiveCell Offset 1  0  Activate   Define end of the loop at the last used row Loop Until ActiveCell row   iAllRows   Close import data file iWB Close

User · Answer

Here is how I do this   Dim TAV   As Variant Dim ArrayToPreserve   as Variant  TAV   ArrayToPreserve ReDim ArrayToPreserve nDim1  nDim2  For i   0 To UBound TAV  1      For j   0 To UBound TAV  2          ArrayToPreserve i  j    TAV i  j      Next j Next i

User · Answer

This isn t exactly intuitive  but you cannot Redim VB6 Ref  an array if you dimmed it with dimensions  Exact quote from linked page is       The ReDim statement is used to size or resize a dynamic array that has   already been formally declared using a Private  Public  or Dim   statement with empty parentheses  without dimension subscripts     In other words  instead of dim invoices 10 0   You should use   Dim invoices   Redim invoices 10 0    Then when you ReDim  you ll need to use Redim Preserve  10 row   Warning  When Redimensioning multi-dimensional arrays  if you want to preserve your values  you can only increase the last dimension  I E  Redim Preserve  11 row  or even  11 0  would fail

User · Answer

i solved this in a shorter fashion   Dim marray   as variant  array2   as variant  YY  ZZ as integer YY 1 ZZ 1  Redim marray 1 to 1000  1 to 10  Do while ZZ lt 100   this is populating the first array marray ZZ YY    something  ZZ ZZ 1 YY YY 1  Loop  this part is where you store your array in another then resize and restore to original array2  marray Redim marray 1 to ZZ-1  1 to YY  marray   array2

User · Answer

You could do this array 0   array 0 1 2 3    Sub add new data array   As Variant  new data   As Variant      Dim ar2   As Variant  fl As Integer     If Not  isEmpty data array     True Then         fl   0     Else         fl   UBound data array    1     End If     ReDim Preserve data array fl      data array fl    new data End Sub  Sub demo       Dim dt   As Variant  nw 0  1  As Variant     nw 0  0     Hi      nw 0  1     Bye      Call add new dt  nw      nw 0  0     Good      nw 0  1     Bad      Call add new dt  nw  End Sub

User · Answer

I stumbled across this question while hitting this road block myself  I ended up writing a piece of code real quick to handle this ReDim Preserve on a new sized array  first or last dimension   Maybe it will help others who face the same issue   So for the usage  lets say you have your array originally set as  MyArray 3 5   and you want to make the dimensions  first too   larger  lets just say to MyArray 10 20   You would be used to doing something like this right    ReDim Preserve MyArray 10 20    lt -- Returns Error   But unfortunately that returns an error because you tried to change the size of the first dimension   So with my function  you would just do something like this instead    MyArray   ReDimPreserve MyArray 10 20    Now the array is larger  and the data is preserved  Your ReDim Preserve for a Multi-Dimension array is complete      And last but not least  the miraculous function  ReDimPreserve     redim preserve both dimensions for a multidimension array  ONLY Public Function ReDimPreserve aArrayToPreserve nNewFirstUBound nNewLastUBound      ReDimPreserve   False      check if its in array first     If IsArray aArrayToPreserve  Then                 create new array         ReDim aPreservedArray nNewFirstUBound nNewLastUBound           get old lBound uBound         nOldFirstUBound   uBound aArrayToPreserve 1          nOldLastUBound   uBound aArrayToPreserve 2                    loop through first         For nFirst   lBound aArrayToPreserve 1  to nNewFirstUBound             For nLast   lBound aArrayToPreserve 2  to nNewLastUBound                  if its in range  then append to new array the same way                 If nOldFirstUBound  gt   nFirst And nOldLastUBound  gt   nLast Then                     aPreservedArray nFirst nLast    aArrayToPreserve nFirst nLast                  End If             Next         Next                      return the array redimmed         If IsArray aPreservedArray  Then ReDimPreserve   aPreservedArray     End If End Function   I wrote this in like 20 minutes  so there s no guarantees  But if you would like to use or extend it  feel free   I would ve thought that someone would ve had some code like this up here already  well apparently not  So here ya go fellow gearheads

User · Answer

A small update to what  control freak and  skatun wrote previously  sorry I don t have enough reputation to just make a comment   I used skatun s code and it worked well for me except that it was creating a larger array than what I needed  Therefore  I changed   ReDim aPreservedArray nNewFirstUBound  nNewLastUBound    to   ReDim aPreservedArray LBound aArrayToPreserve  1  To nNewFirstUBound  LBound aArrayToPreserve  2  To nNewLastUBound    This will maintain whatever the original array s lower bounds were  either 0  1  or whatever  the original code assumes 0  for both dimensions

User · Answer

I know this is a bit old but I think there might be a much simpler solution that requires no additional coding   Instead of transposing  redimming and transposing again  and if we talk about a two dimensional array  why not just store the values transposed to begin with  In that case redim preserve actually increases the right  second  dimension from the start  Or in other words  to visualise it  why not store in two rows instead of two columns if only the nr of columns can be increased with redim preserve   the indexes would than be 00-01  01-11  02-12  03-13  04-14  05-15     0 25-1 25 etcetera instead of 00-01  10-11  20-21  30-31  40-41 etcetera   As only the second  or last  dimension can be preserved while redimming  one could maybe argue that this is how arrays are supposed to be used to begin with  I have not seen this solution anywhere so maybe I m overlooking something

User · Answer

here is updated code of the redim preseve method with variabel declaration  hope  Control Freak is fine with it    Option explicit  redim preserve both dimensions for a multidimension array  ONLY Public Function ReDimPreserve aArrayToPreserve As Variant  nNewFirstUBound As Variant  nNewLastUBound As Variant  As Variant     Dim nFirst As Long     Dim nLast As Long     Dim nOldFirstUBound As Long     Dim nOldLastUBound As Long      ReDimPreserve   False      check if its in array first     If IsArray aArrayToPreserve  Then          create new array         ReDim aPreservedArray nNewFirstUBound  nNewLastUBound           get old lBound uBound         nOldFirstUBound   UBound aArrayToPreserve  1          nOldLastUBound   UBound aArrayToPreserve  2           loop through first         For nFirst   LBound aArrayToPreserve  1  To nNewFirstUBound             For nLast   LBound aArrayToPreserve  2  To nNewLastUBound                  if its in range  then append to new array the same way                 If nOldFirstUBound  gt   nFirst And nOldLastUBound  gt   nLast Then                     aPreservedArray nFirst  nLast    aArrayToPreserve nFirst  nLast                  End If             Next         Next          return the array redimmed         If IsArray aPreservedArray  Then ReDimPreserve   aPreservedArray     End If End Function

User · Answer

Here ya go   Public Function ReDimPreserve ByRef Arr  ByVal idx1 As Integer  ByVal idx2 As Integer       Dim newArr       Dim x As Integer     Dim y As Integer      ReDim newArr idx1  idx2       For x   0 To UBound Arr  1          For y   0 To UBound Arr  2              newArr x  y    Arr x  y          Next     Next      Arr   newArr  End Function

[arrays] Excel VBA - How to Redim a 2D array?

Examples related to arrays

Examples related to excel

Examples related to vba

Examples related to multidimensional-array