VBA: Add-in for recording macros?

  • Has anyone every written some kind of add-in you could use when recording macros that would somehow create VBA code with "relative" navigation commands? For example, if you want to record a macro to just select from a given cell to the bottom of the column, the macro recorder saves this as actual cell names (eg, A1:A45) when what you may mean is something like A1:xlEnd or whatever.


    Anyone know of any such code, add-ins, products, links, etc?


    Many thanx!


    --Brian

  • Don't know if this is any use but if you add the 'Stop recording' toolbar there's a relative reference button you can use whilst recording


    HTH

    <a href="http://www.mrexcel.com/relayforlife.shtml" target="new"><img src="http://www.myimgs.com/data/vonpookie/anne_relay.gif"></a>

  • Also take a look at the VB help on the End property....


    Directions are
    xlToLeft, xlToRight, xlUp, or xlDown.


    and the syntax is
    expression.End(Direction)


    In your eg
    Range("A1", Range("A1").End(xlDown)).Select


    would do what you wanted


    Will

  • Iridium, that Relative button does set things like Offset, eg,


    ActiveCell.Offset(3, 1).Range("A1:A41").Select


    But if you select from the first cell down to the last one in the column (Ctrl-Shift-DownArrow), it still records the cells you select as "A1:A41" instead of xlEnd or whatever.


    J-Walk.com has provided a very helpful example of some VBA code for navigating and selecting ranges in Excel worlsheets:


    Free .XLS file download, shows how to select various Excel worskheet ranges using VBA code


    It would be nice, though, if Excel would actually use values like xlEnd, xlUp, etc, when you're recording a macro.

  • It does.


    This is the recorded VBA when you select D1 and hit SHIFT+END+DOWNARROW
    copy it, select F1 & paste



    SHIFT+END+one of the arrow keys does what you want.


    Will

  • WillR--


    When I'm recording a macro and I select D1 and hit SHIFT-END-DOWNARROW, here's what I get:


    Range("D4:D45").Select


    Am I doing something wrong?


    (BTW, I'm running Excel 97 here...I have no choice, I'm working on a contracting job for a corporate client that STILL uses Office 97...argh)


    :(


    --Brian

  • Sorry, I no longer have 97 to test this... all I can say is that it works in 2k and 2k2


    :(


    Still, since the written code I first mentioned is more efficient, my advice would still be to learn how to write it "properly" as opposed to recording it.


    You rarely need to select anything with VBA and the recorder selects stuff all over the shop, making your code needlessly long (&slower!)


    Regrds,


    Will

  • Yeah...the reason I was hoping to "cheat" by recording the macro this way is that I have a couple of complex VB and Access routines that I end up exporting as Excel worksheets, and I have to apply some fancy-schmancy formatting to the finished sheets, so I need to select certain cells and ranegs, format them, etc. But the cell contents and number of rows and columns can change depending on the data, so I wanted to just quickly crank out the code that would select the right cells.


    I know how to code it in VBA to pick out the ranges I want, but it's faster to just open my sample output worksheets and zip through formatting 'em with the macro recorder on, then tweak the code a bit afterward.


    Ah, well...


    :barf:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!