Параметр макроса Excel передается либо со ссылкой, либо с константой

Question

В Excel я написал Separate функцию vba следующим образом:

Function Separate(sp As String, ParamArray ArgList() As Variant)
    Dim paramLoop As Long
    Dim curRng As Range
    Separate = ArgList(0).Cells(1, 1).Value
    Dim flag As Boolean
    flag = False
    For paramLoop = 0 To UBound(ArgList)
        For Each curRng In ArgList(paramLoop)
            If flag Then
                Separate = Separate & sp & curRng.Value
            End If
            flag = True
        Next curRng
    Next paramLoop
End Function

Эта функция предназначена для объединения произвольного количества содержимого, разделенного строкой, указанной в качестве первого аргумента.

Например, =Separate(", ",A1:B2,A2) возвращает Alan, Bill, Carl, Dale, Bill . (Предположим, что эти имена были сохранены в ячейках A1 , A2 , B1 и B2 .)

Это становится проблемой, если константа, а не ссылка передается второму (и третьему и т.д.) Аргументу, такому как =Separate(", ","Alan",A2:B2) . Это потому, что мой код предполагает, что ArgList() As Variant является объектом Range .

Как VBA может определить, передается ли каждый аргумент со ссылкой или константой? Или как изменить код так, чтобы он выполнял одинаковые действия независимо от того, передан ли аргумент со ссылкой или константой?

Answer 1

Существует функция VBA, которая определяет тип используемой переменной: VarType() .

VarType строки - это vbString (8); VarType для диапазона vbArray+vbVariant (8204). Вам необходимо протестировать каждый элемент вашего переданного массива и обработать его соответствующим образом. Одним из способов сделать это, которому легко следовать, является Select Case:

Function Separate(sp As String, ParamArray ArgList() As Variant)
    Dim paramLoop As Long
    Dim curRng As Range
    Select Case VarType(ArgList(0))
        Case vbArray + vbVariant
            Separate = ArgList(0).Cells(1, 1).Value
        Case vbString
            Separate = ArgList(0)
    End Select
    Dim flag As Boolean
    flag = True
    For paramLoop = 1 To UBound(ArgList)
        Select Case VarType(ArgList(paramLoop))
            Case vbArray + vbVariant
                For Each curRng In ArgList(paramLoop)
                    If flag Then
                        Separate = Separate & sp & curRng.Value
                   End If
                    flag = True
                Next curRng
            Case vbString
                Separate = Separate & sp & ArgList(paramLoop)
        End Select
    Next paramLoop
End Function

И, конечно, Select можно расширить для каждого из vbInteger, vbLong, vbSingle, vbDouble, vbCurrency, vbDate, vbBoolean, vbVariant, vbDecimal, vbByte и даже vbError если у вас есть пустой параметр.

Answer 2

Если вы объявляете аргумент как Variant в заголовке функции, вы всегда можете определить, что вызывающая сторона ДЕЙСТВИТЕЛЬНО передает вам, следующим образом:

Sub MAIN()
   x = WhatIsIt(Range("A1"))
   y = WhatIsIt("what ever")
End Sub

Public Function WhatIsIt(v As Variant) As String
   WhatIsIt = ""
   MsgBox TypeName(v)
End Function