O parâmetro de macro do Excel é passado com uma referência ou uma constante

Question

No Excel, escrevi uma função vba Separate assim:

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

Esta função destina-se a juntar um número arbitrário de conteúdos separados por uma string dada como o primeiro argumento.

Por exemplo, =Separate(", ",A1:B2,A2) retorna Alan, Bill, Carl, Dale, Bill com sucesso. (Suponha que esses nomes tenham sido armazenados nas células A1 , A2 , B1 e B2 .)

Se torna um problema se uma constante, em vez de uma referência, é passada para o segundo (e terceiro e assim por diante) argumento, como =Separate(", ","Alan",A2:B2) . Isso porque meu código pressupõe que ArgList() As Variant é Range .

Como pode vba determinar se cada argumento é passado com uma referência ou uma constante? Ou como modificar o código para que ele faça as mesmas coisas, independentemente de um argumento ser passado com uma referência ou uma constante?

    

Answer 1

Existe uma função VBA que determinará o tipo de variável que está sendo tratada: VarType() .

O VarType de uma string é vbString (8); o VarType de um intervalo é vbArray+vbVariant (8204). Você precisa testar cada elemento da sua matriz passada e lidar com isso de acordo. Uma maneira de fazer isso que é fácil de seguir é com 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

E, é claro, o Select pode ser expandido para cada um dos vbInteger, vbLong, vbSingle, vbDouble, vbCurrency, vbDate, vbBoolean, vbVariant, vbDecimal, vbByte e até mesmo vbError se você tiver um parâmetro vazio.

    

Answer 2

Se você declarar um argumento como Variante no cabeçalho da função, poderá determinar sempre o que o chamador lhe passa da seguinte forma:

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