excel - 将不同类型的数组传递给 UDF 函数

Question

我有一个函数可以在 Excel 工作表中显示数组的内容。我的函数的标题如下所示:

Public Sub afficher_signal(ByRef signal() As Integer, ByVal nb_ligne As Integer)

问题是有时我想用它来显示一个整数数组，有时我想显示一个 double 组。有没有办法改变ByRef signal() 作为整数通过接受任何类型的数组的东西？

非常感谢!

Answer 1

替换

Public Sub afficher_signal(ByRef signal() As Integer, ByVal nb_ligne As Integer)

与

Public Sub afficher_signal(ByRef signal, ByVal nb_ligne As Integer)

或

Public Sub afficher_signal(ByRef signal as Variant, ByVal nb_ligne As Integer)

下面演示了 afficher_signal

的示例使用

Public Sub Main()
    Dim arr As Variant
    arr = Array(1, "yo", 3, 10 Mod 2, 5.25)
    afficher_signal arr, 0
    afficher_signal2 arr, 0
End Sub

Public Sub afficher_signal(ByRef signal As Variant, ByVal nb_ligne As Integer)
    Dim i As Long
    For i = LBound(signal) To UBound(signal)
        Debug.Print signal(i)
    Next i
End Sub

Public Sub afficher_signal2(ByRef signal, ByVal nb_ligne As Integer)
    Dim i As Long
    For i = LBound(signal) To UBound(signal)
        Debug.Print signal(i)
    Next i
End Sub