Excel Matrix Assignment by Referring to a Cell-CodePudding

I am trying to construct a 2x2 matrix dependent on values in some cells (say B1). The code shall take the reference and make some mathematical manipulations, then assign this value to a new cell.

Sub matrix2()

    Dim matrix(1 To 2, 1 To 2) As String
    
    k1 = Cells(1, 2).Value
    
    For i = 1 To 2
        For j = 1 To 2
            k = (-1) ^ (i   j)
            matrix(i, j) = "=B1*" & k
        Next j
    Next i
    
    Range("D1:E2") = matrix
    
End Sub

In the end, I get what I want but I need to go to each cell and press Enter to convert them in a real value. What I should get here is a matrix dependent on the value in B1. When I change B1, the values in the matrix will automatically change.

Is there any way to make it happen more easily? Because, I will be dealing with 40x40 matrices in the end, and I don't want to go over 1600 cells and press Enter.

CodePudding user response：

I doubt this is going to be helpfull right now, but maybe in the (near) future. With the new

Formula in D3:

=MAKEARRAY(2,2,LAMBDA(i,j,B1*(-1^(i j))))

CodePudding user response：

You have to use a variant-array - not a string-array

Try this:

Sub Matrix2()
Dim arr(1 To 2, 1 To 2) as Variant 'instead of String
    
    k1 = Cells(1, 2).Value
    
    For i = 1 To 2
        For j = 1 To 2
            k = (-1) ^ (i   j)
            arr(i, j) = "=B1*" & k
        Next
    Next
    
    ActiveSheet.Cells(8, 1).Resize(2, 2).Formula = arr
end sub