3

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

nimo
  • 229
  • 2
  • 6
  • 14
  • Which application? Am I correct in assuming Excel? – lc. May 26 '10 at 09:20
  • Yes, It is VBA application in Excel. – nimo May 26 '10 at 09:23
  • As Alex K. points out below, you can get a Range from a string like "Q42:Z99" that refers to some actual cell(s) on some actual worksheet. You can't convert arbitrary strings to Range objects, though. Excel will happily and automatically do the reverse, though. So, if you can define your function to take a *String* argument, you can then pass it either strings or (single-cell) Range references and it should work just fine. – jtolle May 26 '10 at 13:13

6 Answers6

5

A string with a cell address? if so:

Dim r As Range: Set r = Range("B3")
MsgBox r.ColumnWidth
Alex K.
  • 171,639
  • 30
  • 264
  • 288
  • Actually it can be a any string value, not necessarily be a cell address, that is why I'm having problem in converting other string value to a Range object – nimo May 26 '10 at 09:29
  • Can you give some examples of the string data? – Alex K. May 26 '10 at 09:30
  • Dim myStirng as String myString = "AnyValue" – nimo May 26 '10 at 09:32
  • Ah reread your Q, so you have a function that expects a Range() but you can only pass a string? Can you change the function proto to make the Range param optional? Why can you only pass a string? Does the function work if the range it expects isn't valid? – Alex K. May 26 '10 at 09:51
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"))
Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73
2

Here is my solution that involves no need for a new function.

1.Make dynamic string variable first

2.Then finalize it by creating a range object out of this string via a range method: Set dynamicrange= range (dynamicstring)

You can manipulate dynamicstring as you want to, I just kept it simple so that you can see that you can make range out of a string variable.

Sub test()

Dim dynamicrangecopystring As String
Dim dynamicrangecopy As range
dynamicrangecopystring = "B12:Q12"
Set dynamicrangecopy = range(dynamicrangecopystring)

End Sub
sedreddin
  • 21
  • 3
1

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 ?

Charles Williams
  • 23,121
  • 5
  • 38
  • 38
1

This simple function will convert string arguments into a range object, usable in other excel functions.

Function TXT2RNG(text) As Variant

Set TXT2RNG = Range(text)

End Function
Andrew
  • 11
  • 1
  • This solution has been sitting here for a while without acknowledgment. I have a userform with a list box that contains a range and of course, this range is in the form of a string, including worksheet name, e.g "'Sheet10'!$A$286" (in my case the worksheet name contains spaces). I want to click on an item in the listbox, extract the range given in the item, then go to that range using Application.Goto Reference:=rng, Scroll:=True. My situation here seems to be what the original question is also addressing. Andrew's solution works very well. – awsmitty Jul 11 '20 at 07:05
0

Let's say Sheet1!A1 has the text value "Sheet1!B1" and Sheet1!B1 has the value "1234". The following code will use the range address stored as text in A1 as an input and copy the range B1 to A2:

Sub Tester()

  Sheet1.Range(Range("A1")).Copy

  Sheet1.Range("A2").PasteSpecial xlPasteAll

End Sub
Daniel H
  • 1