How to convert a String to a Range Object

Question

Hi,

Is there anyway to convert a string value to a Range object ? I'm having a function which takes a Range object as a argument and need to pass a single string parameter to it

Thank You

Answer 1

A string with a cell address? if so:

Dim r As Range: Set r = Range("B3")
MsgBox r.ColumnWidth

Answer 2

I don't like this one bit, but if you can't change the function that requires a range, you could create a function that converts a string to a range. You'd want to be sure that the only thing the first function cares about is the Value or Text properties.

Function FuncThatTakesRange(rng As Range)

    FuncThatTakesRange = rng.Value

End Function

Function ConvertStringToRange(sInput As String) As Range

    Dim ws As Worksheet

    Set ws = Workbooks.Add.Sheets(1)

    ws.Range("A1").Value = sInput

    Set ConvertStringToRange = ws.Range("A1")

    Application.OnTime Now + TimeSerial(0, 0, 1), "'CloseWB """ & ws.Parent.Name & """'"

End Function

Sub CloseWb(sWb As String)

    On Error Resume Next
        Workbooks(sWb).Close False

End Sub

Use in the Immediate Window like

?functhattakesrange(convertstringtorange("Myvalue"))

Answer 3

Why not change the function argument to a variant and then in the function determine Using VarType etc) if you have been passed a Range and use error handling to check for a string which can be converted to a range or a string that cannot be converted to a range ?