I would like to define a VBA function that:

1. returns a column vector of the main diagonal elements of a matrix;
2. returns a square diagonal matrix with the elements of vector on the main diagonal;
3. automatically returns a matrix/vector, without the need to press Ctrl Shift Enter;

I am working on this code:

Public Function DIAG(matrix As Variant) As Variant

Dim i As Long
Dim j As Long
Dim nRows As Long
Dim nCols As Long
Dim tempArray As Variant

nRows = matrix.Rows.Count
nCols = matrix.Columns.Count

For i = 1 To nRows
    For j = 1 To nCols
        If i = j Then
           tempArray(i) = matrix(i, j)
        End If
    Next j
Next i

DIAG = tempArray

End Function

which is only for the first purpose of the function, but it is not working. I get:

#VALUE

CodePudding user response：

Your function returns such an error when called as UDF and, if I correctly understood what you want, please use the next adapted function:

Public Function DIAG(matrix As Range) As Variant
 Dim i As Long, j As Long, k As Long, nRows As Long, nCols As Long
 Dim tempArray As Variant

 nRows = matrix.Count: nCols = matrix.Columns.Count

 ReDim tempArray(nRows * nCols) 'without this step it will return an error when try loading
 For i = 1 To nRows
    For j = 1 To nCols
        If i = j Then
           tempArray(k) = matrix(i, j): k = k   1
        End If
    Next j
 Next i
 ReDim Preserve tempArray(k - 1) 'preserving only the elements keeping data
 DIAG = tempArray
End Function

The intermediary array should be ReDim and then preserve only the elements keeping data...

Ending Statement (suggested by @Dominique): If you create a user-defined function, known as UDF, then first test it by calling it in a macro, and only when this returns what you need, without any error, you might call it directly from a cell.

CodePudding user response：

I believe something is wrong with the structure of your application: are you sure the module where your function is residing, is accessible from your Excel workbook?

Just as an example, I've made a similar function, I used it in my Excel workbook (in a cell, I entered the formula =DIAG(J5:Q25)) and everything is working fine, hereby a screenshot:

Oops, I just tried something else and I have the same error message:

Public Function DIAG(matrix As Variant) As Variant
 Dim tempArray As Variant

 tempArray(1) = 1
 tempArray(2) = 2
 DIAG = tempArray
End Function

Are you sure you can return an entire matrix or even a single-dimension array as a return of a function, and call it that easily?

For your information, I tried this, using =DIAG(J5:Q25) in one cell, in two cells, and as an array formula.

CodePudding user response：

The code from @FaneDuru did help me, but I successfully coded my UDF in this way:

Public Function DIAG(matrix As Range) As Variant

Dim i As Long, j As Long, nRows As Long, nCols As Long
Dim tempArray As Variant

nRows = matrix.Rows.Count
nCols = matrix.Columns.Count

If nCols = 1 Then
 
 ReDim tempArray(nRows - 1, nRows - 1)

 For i = 1 To nRows
  tempArray(i - 1, i - 1) = matrix(i)
 Next i

Else

 If nCols = nRows Then

 ReDim tempArray(nRows - 1, 0)

 For i = 1 To nRows
  For j = 1 To nCols
   If i = j Then
    tempArray(i - 1, 0) = matrix(i, j)
   End If
  Next j
 Next i

 Else
  tempArray = CVErr(xlErrValue)
 End If

End If

DIAG = tempArray

End Function