{"id":142,"date":"2012-01-25T12:26:21","date_gmt":"2012-01-25T03:26:21","guid":{"rendered":"http:\/\/fujiitoshiki.com\/improvesociety\/?p=142"},"modified":"2014-07-31T13:53:48","modified_gmt":"2014-07-31T04:53:48","slug":"to-change-the-number-of-the-first-element-of-two-dimensional-dynamic-array-excel-vba","status":"publish","type":"post","link":"https:\/\/www.fujiitoshiki.com\/improvesociety\/?p=142","title":{"rendered":"How to change the number of the first element of two-dimensional dynamic array in EXCEL VBA?"},"content":{"rendered":"

EXCEL VBA restricts two-dimensional dynamic array to change the number of element in the last dimension only. Although the constraint, what could you do when you would like to change the first dimension of two-dimensional dynamic array? Please see the code below;<\/p>\n

\r\nOption Explicit\r\nSub DynamicArray_Sample()\r\nDim mySht   As Worksheet\r\nDim myRng   As Range\r\nDim tmpAr   As Variant\r\nDim myAr()  As String\r\nDim myID()  As String\r\nDim myStr() As String\r\nDim i       As Long\r\nSet mySht = Worksheets.Add\r\nWith mySht\r\n    .Range(\"A1\").Value = \"10001\"\r\n    .Range(\"A2\").Value = \"10002\"\r\n    .Range(\"A3\").Value = \"10003\"\r\n    .Range(\"A4\").Value = \"10004\"\r\n    .Range(\"A5\").Value = \"10005\"\r\n    .Range(\"B1\").Value = \"aaaaa\"\r\n    .Range(\"B2\").Value = \"bbbbb\"\r\n    .Range(\"B3\").Value = \"ccccc\"\r\n    .Range(\"B4\").Value = \"ddddd\"\r\n    .Range(\"B5\").Value = \"eeeee\"\r\nEnd With\r\nSet myRng = mySht.UsedRange\r\ntmpAr = myRng\r\nFor i = LBound(tmpAr) To UBound(tmpAr)\r\n    ReDim Preserve myID(i)\r\n    ReDim Preserve myStr(i)\r\n    myID(i) = myAr(i, 0)\r\n    myStr(i) = myAr(i, 1)\r\nNext i\r\nReDim Preserve myID(UBound(myID) + 1)\r\nReDim Preserve myStr(UBound(myStr) + 1)\r\nmyID(UBound(myID)) = \"10006\"\r\nmyStr(UBound(myStr)) = \"fffff\"\r\nReDim myAr(UBound(myID), 1)\r\nFor i = LBound(myAr) To UBound(myAr)\r\n    myAr(i, 0) = myID(i)\r\n    myAr(i, 1) = myStr(i)\r\nNext i\r\nSet mySht = Worksheets.Add\r\nWith mySht\r\n    .Range(\"A1:B6\") = myAr\r\nEnd With\r\nSet mySht = Nothing\r\nEnd Sub\r\n<\/pre>\n