VBA补全3×3矩阵自定义函数失效问题求助

Let's break down why your VBA function isn't working and fix it step by step:

The Core Issues With Your Original Code #

1. Parameter Type Confusion
   When you call this function from a worksheet (e.g., =kiegeszito(A1:C3)), you're passing a Range object (the cell area) instead of a VBA array. Directly assigning values to a(i,j) will throw an error because:

   • Range elements require the .Value property to read/write.
   • User-defined functions (UDFs) in Excel can't modify worksheet cells directly—they can only return calculated values.

2. Redundant & Unnecessary Code
   The line Else a(i, j) = a(i, j) does nothing at all. Since your known elements are already present in the matrix, you don't need to reassign them.

Fixed UDF Version (Works in Worksheet) #

This version converts the input range to a VBA array, fills the missing symmetric values, then returns the completed array. You can use it as an array formula in your worksheet:

Function kiegeszito(rng As Range) As Variant
    ' Copy the range's values into a mutable VBA array
    Dim arr As Variant
    arr = rng.Value
    
    ' Fill the upper triangle using symmetric lower triangle values
    Dim i As Integer, j As Integer
    For i = 1 To 3
        For j = 1 To 3
            If i < j Then
                arr(i, j) = arr(j, i)
            End If
        Next j
    Next i
    
    ' Return the completed matrix
    kiegeszito = arr
End Function

How to Use It:

1. Select a blank 3×3 cell range in your worksheet.
2. Type =kiegeszito(A1:C3) (replace A1:C3 with your original known matrix range).
3. Press Ctrl+Shift+Enter (for older Excel versions) or just Enter (Excel 365+, which supports dynamic arrays). The blank cells will fill with the symmetric values automatically.

Alternative: Subroutine to Directly Modify Cells #

If you prefer to update the original range directly (instead of returning a new array), use this subroutine:

Sub FillSymmetricMatrix(rng As Range)
    Dim arr As Variant
    arr = rng.Value
    
    Dim i As Integer, j As Integer
    For i = 1 To 3
        For j = 1 To 3
            If i < j Then
                arr(i, j) = arr(j, i)
            End If
        Next j
    Next i
    
    ' Write the filled array back to the original range
    rng.Value = arr
End Sub

How to Use It:

1. Open the VBA editor with Alt+F11, insert a new module, and paste this code.
2. Go back to your worksheet, select the 3×3 matrix range.
3. Run the FillSymmetricMatrix subroutine (you can also add a button to your worksheet to run it with one click).

内容的提问来源于stack exchange，提问作者Levente Kim