自定义数组公式函数

对于数组公式，需要返回多个值，即数组

Function Multiply_Range(myrange As Object) As Variant
    Dim temp As Variant
    Dim i As Integer, j As Integer
    temp = myrange.Value                            ' 生成值的数组
    If IsArray(temp) Then
        For i = 1 To UBound(temp, 1)
            For j = 1 To UBound(temp, 2)
                temp(i, j) = i ' temp(i, j) * 100   ' 在数组中返回值
            Next j
        Next i
    Else
        temp = temp * 100                           ' 对于单个
    End If
    Multiply_Range = temp
End Function

示例:

这段代码是一个数组公式, 返回各个单元格是否可见。

Function IsVisible(c As Object) As Variant

    Dim temp As Variant
    
    temp = c.Value

    Dim r As Range
    
    If IsArray(temp) Then
        For i = 1 To UBound(temp, 1)
            For j = 1 To UBound(temp, 2)
                Set r = c.Cells(i, j)
                If r.EntireColumn.Hidden Or r.EntireRow.Hidden Then
                    temp(i, j) = 0
                Else
                    temp(i, j) = 1
                End If
            Next
        Next
    Else
        Set r = c
        If r.EntireColumn.Hidden Or r.EntireRow.Hidden Then
            temp(i, j) = 0
        Else
            temp(i, j) = 1
        End If
            
    End If
    
    IsVisible = temp
    
End Function