How to set a default value for an optional Range argument? [duplicate]

Question

I'm trying to write a function which would have a Range as an optional argument. Here's a minimal example of such function:

Function Example(s As String, Optional r As Range) As String
    Example = "Test"
End Function

This is ok and the VBA Module can be compiled. But I'm struggling to find out how to set a default value for this optional range argument, e.g. "B:B".

Function Example(s As String, Optional r As Range **= Range("B:B")**) As String
    Example = "Test"
End Function

This does not compile. It throws the error "Constant expression required."

Answer 1

Check if the argument was passed inside the function and set it there if not.

Function Example(s As String, Optional r As Range) As String

    If r Is Nothing then Set r = Range("B:B")

    Example = "Test"

End Function