CODEWITHSUNDEEP
excelvba
xiaodai
xiaodai

Reputation: 16004

How do I make Excel user-defined function (UDF) accept arguments of String type and range type?

I am trying to make a function do_something_to_string(one_arg) where one_arg can be of string type or be a range.

In the case of a range, I want to concatenate every cell of the range into one long string but processing. But I can't seem to make a function that can accept either a string or a Range as argument.

Upvotes: 1

Views: 9002

Answers (1)

user10307643
user10307643

Reputation:

Either use optional arguments or a variant:

Function MyFunction1(Optional Str As String, Optional Rng As Range) As String
    Dim C As Range, S As String
    If Rng Is Nothing Then
        MyFunction1 = Str
    Else
        S = ""
        For Each C In Rng
            S = S & CStr(C.Value)
        Next
        MyFunction1 = S
    End If
End Function

Function MyFunction2(V As Variant) As String
    Dim C As Range, S As String
    If VarType(V) = vbString Then
        MyFunction2 = V
    ElseIf TypeName(V) = "Range" Then
        S = ""
        For Each C In V
            S = S & CStr(C.Value)
        Next
        MyFunction2 = S
    Else
        Err.Raise 13, , "The argument must be a String or a Range."
    End If
End Function

Then try:

Debug.Print MyFunction1("test")
Debug.Print MyFunction1(, Range("A1:B3"))
Debug.Print MyFunction2("test")
Debug.Print MyFunction2(Range("A1:B3"))

Upvotes: 2

Related Questions