XL: Using the Range Method to Convert String to a Range

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows

SUMMARY

In Microsoft Excel, when you use the GoTo method in a Visual Basic for Applications procedure, you must use a Visual Basic object style reference, such as the following:

   Worksheets("Sheet1").Range("A1")

Using string manipulation, it is possible to take a <SheetName>!<CellReference> style reference result and use it as an argument of the Range method. The Range method then converts a <SheetName>!<CellReference> style reference to a Visual Basic object style reference that can be used with the GoTo method. The following are examples of using the Range method to accomplish this.

MORE INFORMATION

Converting Link Formula

To convert a simple Microsoft Excel link formula, for example "=Sheet1!$A$1" to a Visual Basic style reference, you can strip the equal sign (=) from the left side of the string and pass the remainder of the formula to the Range object.

The following line of code selects the cell or range that is referenced by the formula in the active cell:

   Application.GoTo _
      Range(Right(ActiveCell.Formula, Len(ActiveCell.Formula - 1))

Converting Reference in Chart Series

To select a range that is referenced as a y value in the formula of a chart series, the string manipulation becomes a bit more complex. The following procedure strips the section of the series formula between the second and third commas of the currently selected series and passes that string to the Range object to select it.

Sub Select_The_Y_Values()

  Dim StartString As Integer
  Dim EndString As Integer
  Dim SeriesForm As String

    ' Assign the formula property of the selected series to
    ' the variable "SeriesForm"
    SeriesForm = Selection.Formula

    ' If a point is selected rather than the entire series, you will
    ' need to modify the above line to the following
    '  Selection.Parent.Formula

    ' Find the position of the first character after the second
    ' comma in the formula
    StartString = InStr(InStr(SeriesForm, ",") + 1, SeriesForm, ",") + 1

    ' Find the position of the third comma
    EndString = InStr(StartString, SeriesForm, ",")

    ' Strip out the y values (between the second and third commas)
    ref = Mid(SeriesForm, StartString, EndString - StartString)

    ' Pass the Y values to the Range object and select it
    Application.GoTo Range(ref)

End Sub

REFERENCES

"Visual Basic User's Guide," version 5.0, Chapter 3, "Creating User Defined Functions"

"Visual Basic User's Guide," version 5.0, Chapter 5, "Working with Objects in Visual Basic"