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.
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"))
do_something_to_string&do_something_to_rangeStringargument?TEXTJOIN, which if you don't have access to in your version of Excel, you can still find an example of a UDF that does the trick here on SO.doSomeThing(arg As Variant)then checkTypeName(arg)to see what was passed in