How to write one D of a Multi-D array in one statement, in VBA?

Question

How can i write an entire dimension of a multidimensional array, in a single statement?

Edit 2: Clarification:

Simply want to update one slice (vertical or horizontal) of a VBA array, without looping/processing the entire array.

• I'm assuming we need to convert a 2D to 1D, or 1D to 2D, array. Not sure that's necessary.

• My example uses a worksheet range as a source, but don't be distracted by that. I'm not trying to update the worksheet-- trying to update an array. The solution i seek should not require worksheet read/write.

Edit 1: I've got a 2D array in VBA. It contains cell data pulled in from a sheet:

dim blockData
blockData = [a1:d10]

Now, i want to copy a different cell range into that array, overwriting just one row:

dim rowData
' just one row of data, but same width as 2D blockData
rowData = [a20:d20]

' load into array, overwriting row 3 with this new data. 
' This is the one-liner i seek:
blockData(3,) = rowData

So, how can i write a whole 1D array to one D of a 2D array in a single statement, without looping?

Some ideas:

• I can get a single column from the array using the INDEX trick. Maybe that can be exploited somehow: colArray = WorksheetFunction.Index(rangeArray, 0, 1) colArray (1,2)="New cell value"
• Use Transpose(colArray), but that has not helped so far.
• Use Redim Preserve to somehow combine the arrays, but not sure how that can help.
• Use VarPtr to write my changes directory to the original array, but not sure that can be done without looping.
• It's easy to copy an entire array to another variable in one statement. Maybe that can be exploited somehow: Dim a1, a2 a1=array(1,2,3) a2=a1

thx

Answer 1

You can try this technique whereby you populate the array by using Evaluate with an array formula targeting the source range. The array formula contains an IF statement checking either the ROW or COLUMN of the target cells meaning you can isolate a particular row or column of the array and change it before you map it back to the source range.

See the sample code below. Note the middle line is basically the answer to your question 'So, how can i write a whole 1D array to one D of a 2D array in a single statement, without looping?' :

Dim varRange As Variant
varRange = [IF(ROW(D1:H7)=7,"X",D1:H7)]
Sheet1.Range("D1:H7").Value = varRange

You can do more complex examples but the technique seems to need Evaluate rather than the [] shorthand:

Dim rngArray As Range
Dim strAddress As String
Dim varRange As Variant
Dim strUpdateValue As String
Dim intTargetRow As Integer
Dim intTargetColumn As Integer

' set array from range - 7 rows x 5 columns of 1s
Set rngArray = Sheet1.Range("D1:H7")

' get full address else Evaluate uses Activesheet
strAddress = rngArray.Address(External:=True)

' update a row e.g IF(ROW(D1:H7)=4,"Robin",D1:H7)
strUpdateValue = "Robin"
intTargetRow = 4
varRange = Evaluate("IF(ROW(" & strAddress & ")=" & intTargetRow & ",""" & strUpdateValue & """," & strAddress & ")")

' set back to range
rngArray.Value = varRange

' update a column e.g IF(COLUMN(D1:H7)=5,"Robin",D1:H7)
strUpdateValue = "Robin"
intTargetColumn = 5
varRange = Evaluate("IF(COLUMN(" & strAddress & ")=" & intTargetColumn & ",""" & strUpdateValue & """," & strAddress & ")")

' set back to range
rngArray.Value = varRange

Answer 2

There is no one liner to update a column/row in 2D array in VBA, you have to loop.

You have choices. If want to stay in VBA, and you want to keep things short, the only way is to create a function that loops for you:

Function RowSwap(Arr1, Arr2, Row) As Variant
For col = LBound(Arr1, 2) To UBound(Arr1, 2)
   Arr1(Row, col) = Arr2(1, col)
Next col
RowSwap = Arr1
End Function

Then you could just write this in VBA:

blockData = RowSwap(blockData, rowData, 3)

This does what you need, overwriting row 3 of blockData with rowData

Otherwise I think changing the source data in your sheets is best, if necessary in a temporary sheet.

Answer 3

-- if it's a meta-array (array of arrays), then a single row can be updated in a single statement: ArrayOfArrays(12) = NewRowArray

-- if it's a 2D array, a single column can be updated with CopyMemory. Not for the faint-of-heart, and you can crash if you do it wrong (don't forget the cleanup part). I've not work out the details, but some clues: https://stackoverflow.com/a/24843721/209942