Announcement

Collapse
No announcement yet.

Application.Goto Trick

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Application.Goto Trick

    This is just a favorite trick that I've used in the past to avoid having to parse range references in VBA just so I can navigate to some range. I was working on a project this morning and the situation presented itself once again; figured I'd take a moment to share it.


    I have a range reference like this: "Apr!$L$72:$O$76"
    ...and I need my macro to goto or operate on that address.

    Assuming the string:
    Code:
    GotoAddr = "Apr!$L$72:$O$76"
    If we try to use either of the following, the code fails:
    Code:
    Range(GotoAddr).Select
    Application.Goto GotoAddr
    So frequently (perhaps always?) you see people parsing the string address into seperate references for the sheet and then the range, this way they can select the sheet and with the sheet selected get to the range.

    For instance, either of the following would be acceptable to VBA:
    Code:
    Sheets("Apr").Range("$L$72:$O$76").Select
    Application.Goto Reference:=Worksheets("Apr").Range("$L$72:$O$76")
    ...but of course we have to parse the original string to break it up into the necessary components.

    There is a handy little trick I've used in the past to avoid the parsing. For whatever reason, the Goto method does allow R1C1 sheet/range references, it just doesn't like them in A1 format. We also have a nifty little command in VBA called "ConvertFormula" that we can use to easily switch between these two reference styles.

    So now comes the neat trick...
    Code:
    GotoAddr = "Apr!$L$72:$O$76"
    Application.Goto Application.ConvertFormula(GotoAddr, xlA1, xlR1C1)
    
    'or if you prefer...
    GotoAddr = "Apr!$L$72:$O$76"
    GotoAddr = Application.ConvertFormula(GotoAddr, xlA1, xlR1C1)
    Application.Goto GotoAddr

    Granted, if you can get the range string reference in R1C1 format first, you don't even need to convert it.
    Last edited by Aaron Blood; January 9th, 2009, 00:59.
    Sub All_Macros(Optional control As Variant)

  • #2
    Re: Application.Goto Trick

    Thanks for sharing, Aaron

    I know you dislike Named Ranges, but I would use one or, the sheet CodeName so tab name change has no adverse effect;


    Code:
    Application.GoTo Range("MyRange")
    Code:
    Application.GoTo Sheet1.Range("$L$72:$O$76")
    BTW, rather than
    Code:
    GotoAddr = "Apr!$L$72:$O$76" 
    Range(GotoAddr).Select 
    Application.Goto GotoAddr
    Try

    Code:
    GotoAddr = "Apr!$L$72:$O$76"
    Application.Goto Range(GotoAddr)

    Comment


    • #3
      Re: Application.Goto Trick

      Yeah, it's not really a situation where range names are handy... I'm processing like several hundred string references. Actually, it's working from a dump of defined workbook names (insert|name|paste|pastelist) and it provides all the refersto addresses in A1 string format. I wanted to d-click a cell to follow the link, so I do stuff like this in the event procedure of the sheet so I can see where the names are.

      I haven't tried that last combination you suggested though. I'll maybe give that one a shot tomorrow. Seems like it shouldn't work... but who am I to say what's logical in VBA.
      Sub All_Macros(Optional control As Variant)

      Comment


      • #4
        Re: Application.Goto Trick

        it's working from a dump of defined workbook names (insert|name|paste|pastelist) and it provides all the refersto addresses in A1 string format
        Why not read the Names themselves direct into GoTo rather than their address and sheet?

        The last combo, works, at least in 2007.

        Comment


        • #5
          Re: Application.Goto Trick

          I didn't really want to get too far into discussing the app I'm working on so much as the concept... but it's a good question.

          It's a WB name manager worksheet, I dump the names in there, make a bunch of changes to em, sometimes I want to navigate to the changed reference before I actually assign it to the name.

          This just happened to be one situation where the issue cropped up again. I've had other situations where I've needed to navigate to a string range reference in this sheet!range format. Seems to me there are some objects that I've come across that return string addresses in this format...

          Oh and I did test your last suggestion:
          Code:
          GotoAddr = "Apr!$L$72:$O$76"
          Application.Goto Range(GotoAddr)
          As I suspected, it doesn't work (maybe they tweaked it a bit in 2007). Too bad, it would've been cleaner.
          That suggests to me that in 2007 you might be able to just use:
          Code:
          GotoAddr = "Apr!$L$72:$O$76"
          Range(GotoAddr).Select
          Is that true?
          Sub All_Macros(Optional control As Variant)

          Comment


          • #6
            Re: Application.Goto Trick

            No, not really, the Apr sheet MUST be active for that syntax to work.

            I'm astounded to see 1 improvement in 2007 after mostly see steps backwards since upgrading

            Comment

            Working...
            X