Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Excel 2000 Macros (Excel 2000 SP3)

    This probably seems really obvious to those of you writing Excel macros in VB every day, but here goes!

    I have lots of fairly complex Excel models, often with many worksheets and formulas using data from cells in those different sheets.

    These models are fairly 'dynamic' (ie. permanently evolving), but this doesn't cause me a problem, because if I insert several rows in Sheet 6 (for eg.) all my formulas will be updated to correct any changed cell references accordingly.

    I am just starting to dabble with macros and VB in an attempt to automate some of the more complex routines, and here in lies the problem.

    Macros seem to be 'hard coded' in the sense that if I now insert some new rows, my cell formulas all update, but any cell references in my macros don't.

    Why is this, and is there any way to get macros to udate the same way that cell formulas do?

    Thanks

    Neil

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel 2000 Macros (Excel 2000 SP3)

    Instead of using "ranges" in the macros, use range names.

    Worksheets("Sheet1").Range("A1:B23") will always refer to this range no matter what rows/columns get inserted or deleted.

    But if you name the range (Insert - name - define) "MyRange" to be "='Sheet1'!A1:B23"

    If you use in your macro:
    Worksheets("Sheet1").Range("MyRange")

    Excel will "update" range references for the named range upon inserting/deleting so when the macro "gets the name" it will be current.

    Steve

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 Macros (Excel 2000 SP3)

    Okay, thanks Steve.

    I've not really used named ranges before. Do you effectively pre-define the outer limits of a table, and set those as the named range, and then make sure that any insertion or deletion of rows or columns takes place within those boundaries? And if your macro refers to both the worksheet name and the range name, then any such deletions or addtions will be updated within the macro automatically?

    If this is correct, then presumably you would have to ensure all macros only called cell references within a named range?

    Am I on the right lines?

    Neil

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 Macros (Excel 2000 SP3)

    Your macro would refer to the name itself, which in turn refers to a range of cells on a sheet.

    You could use code like this to process all cells within a named range:

    Dim oCell as Range
    For each oCell in Activeworkbook.Names("TheName").RefersToRange
    Msgbox oCell.Address & " contains " & oCell.Value
    Next

    When a change is made to the range that particular name refers to (by inserting or deleting cells), the reference inside the name is updated like any normal formula and the code sample I gave above will now work on the new range.

    Managing defined names is simpler with my name manager (see my site below).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel 2000 Macros (Excel 2000 SP3)

    In addition to Jan's remarks, you can also find the "limits of the ranges" at run time (eg)
    Range(range("A1"), range("A65536").end(xlup)).select

    Will select the range from A1 until the last used cell in column A (it goes to the last cell and does an "end-Up"). Now no matter what you do to the spreadsheet at runtime, it will get the "whole range"

    Steve

  6. #6
    Star Lounger
    Join Date
    Mar 2003
    Location
    Ilkeston, Derbyshire
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 Macros (Excel 2000 SP3)

    I helped Neil compile these spreadsheets by doing most of the coding. Unfortunately most of my VBA knowledge comes from Access so the advice on ranges will be most useful.

    The VBA code in question calls (amongst other things) individual cell references and at the moment is 'hard wired' . Presumably if a particular cell is given a name then any reference to that name in VBA will be valid no matter if rows (or columns I suppose) are added or deleted? If individual cells can be named in this way is there a limit to how many I can use?

    Mark

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel 2000 Macros (Excel 2000 SP3)

    According to Help the number of names are limited only by memory.

    Steve

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 Macros (Excel 2000 SP3)

    I've seen workbooks with as many as 1500 names, so go ahead and have a ball!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Star Lounger
    Join Date
    Mar 2003
    Location
    Ilkeston, Derbyshire
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 Macros (Excel 2000 SP3)

    Thanks Steve and Jan

    The limit of range at runtime could well be useful. Incidentally, does this code snippet do the same thing?
    Range("J4").Select
    Range(Selection, Selection.End(xlDown)).Select

    J4 in this case should be static but I guess if it was named it would provide a bit flexibility.

    Mark

  10. #10
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 Macros (Excel 2000 SP3)

    Thanks for all your replies, but I

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel 2000 Macros (Excel 2000 SP3)

    I find it is better to go up from bottom, then down from the top, in case there is a "blank" row or rows in the entire database.

    Also it is best to "avoid" selecting ranges during execution. It only slows down excel.Range("J4").Select

    <pre>dim rng as range
    dim rng2 as range
    set rng = range("J4")
    Set rng2 = Range(rng.Cells(1, 1), Cells(65536, rng.Column).End(xlUp))
    'other code here
    '
    '
    '

    'at end of code
    set rng = nothing
    set rng2 = nothing</pre>


    Once you "set" the ranges, you can use them in the code, they are the range objects.

    Steve

  12. #12
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 Macros (Excel 2000 SP3)

    A Name can designate either a single cell or a large block of cells. It is technically possible to assign the Names using code; altrhough I am not clear enough about the structure of your worksheets to say whether that could actually save you time.
    Gre

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel 2000 Macros (Excel 2000 SP3)

    I think we understand what the "problem" is. It can not be done as you are thinking. The code must be designed to take this into account. If you could give us a more "real" example of your goal we might be able to help with the coding. There are ways other than naming to know the location of something. You can base it on the last row or column in a range. You can "find" a particular column heading, or row value, you can find a max or min value, there are multiples ways of approaching the problem without having to name all the different cells.

    I think what we don't understand is what you want to program and so we can relate a solution relevant to what you want to do.
    Steve

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 Macros (Excel 2000 SP3)

    The simple answer to your question is no, the cell references in macros will not update automatically. The secret is that you have to write your code so that it finds the cell that it needs using some method other than absolute cell references. One way to do this is to give the cells names. Since names are part of the worksheet, not the macro, the name will be updated just like cell references in worksheet formulae.

    There are other things that you can do. For example, if cell G28 is in a column that has the label "Income" in row 1 and a label "Region A" in column A, then you could use some code like this to reference the cell:

    <pre>Dim lCol As Long, lRow As Long
    lCol = Worksheets("Sheet1").Range("1:1").Find(What:="Inco me").Column
    lRow = Worksheets("Sheet1").Range("A:A").Find(What:="Regi on A").Row
    MsgBox Worksheets("sheet1").Cells(lRow, lCol).Value
    </pre>


    With code like that, no matter how many rows or columns are added or deleted, the macro will still use the correct cell.

    There are many ways to get around this. Exactly how will depend on how your worksheet is structured and exactly what you are trying to accomplish. Unfortunately, all of those are going to reguire manually rewriting the code.
    Legare Coleman

  15. #15
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 Macros (Excel 2000 SP3)

    Legare and everyone else who took the trouble to reply.

    Thanks to all of you. I now feel I have a much better understanding of just what is going on with cell references in macros, and how to work around the inherent limitations.

    Any changes I may have to make to existing macros will not be that major, but much more important, I will build the necessary cell identifiers into any future macros I write, now I know what is needed.

    Neil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •