CODEWITHSUNDEEP
excelvba
Richard Aleint
Richard Aleint

Reputation: 67

How to create identity matrix using Excel VBA?

I used this sub-routine to create an identity matrix of size N

Sub IdMatrix()
    Dim i As Integer, j As Integer, N As Integer
    N = 5
    For i = 1 To N
        For j = 1 To N
            If i = j Then
                Cells(i, j) = 1
            Else
                Cells(i, j) = 0
            End If
        Next j
    Next i
End Sub

Is there a better way? A simple one maybe?

Upvotes: 2

Views: 2535

Answers (5)

VJJ
VJJ

Reputation: 1

IdMx = Application.MUNIT(size)

Upvotes: 0

geo1230
geo1230

Reputation: 252

Actually, there is a built-in function, the MUNIT. Just select the NxN cells you want, type =MUNIT(dimension) and press CTRL+Shift+Enter. Or you can type in the formula in the first cell and then press F2, select the NxN cells and press CTRL+Shift+Enter.

PS. I know the OP probably won't see this but maybe someone else does.

Upvotes: 1

Dirk Reichel
Dirk Reichel

Reputation: 7979

To get a fast sub without loops, just use something like this:

Sub test()
  Dim N As Long
  N = 5
  With [A1].Resize(N, N) '<- do not change this A1
    [A1].Resize(N, N).Value2 = Evaluate("IF(ROW(" & .Address & ")=COLUMN(" & .Address & "),1,0)")
  End With
End Sub

Upvotes: 1

Anastasiya-Romanova 秀
Anastasiya-Romanova 秀

Reputation: 3368

Try this

Sub IdMatrix1()
Dim N As Long, i As Long
N = 5: Range("A1").Resize(N, N) = 0
    For i = 0 To N - 1
        Range("A1").Offset(i, i) = 1
    Next
End Sub

Here is another weird way to create an identity matrix

Sub IdMatrix2()
Dim i As Long, N As Long
N = 5: Range("A1").Resize(N, N) = 0
    For Each cell In Range("A1").Resize(N, N)
        i = i + 1: cell(i) = 1: If i > N - 1 Then Exit For
    Next
End Sub

And for the best performance, use this code inspired by iDevlop's answer

Const N = 5             'Put this constant on top of code editor
Sub IdMatrix3()
    Dim IdMx(1 To N, 1 To N) As Long, i As Long
    For i = 1 To N: IdMx(i, i) = 1: Next
    Cells(1,1).Resize(N, N) = IdMx
End Sub

Upvotes: 1

iDevlop
iDevlop

Reputation: 25262

Accessing the worksheet from VBA is expensive. Creating the result in an array should be a faster: 

Sub idMatrix()
    Dim n As Integer, i As Integer, a() As Integer
    n = 5
    ReDim a(1 To n, 1 To n)
    For i = 1 To n
        a(i, i) = 1
    Next i
    Range("a1").Resize(n, n) = a
End Sub

Note that defining a() as Integer initiates it with zeroes (we can be zeroes :)

Upvotes: 2

Related Questions