Results 1 to 9 of 9
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cell Aliases (2000)

    I'm trying to dream up an efficient method of assigning "aliases" to columns of a master sheet, with the aim of using them in VBA code. A partial sheet is shown in the attachment - essentially just a table, with the alias names in <font color=red>red</font color=red>. I'd like to be able to refer to properties like "bulk" rather than the column E cell, in the current or active row. Not only would this make the code more readable, but it would allow more flexibility in changing the sheet structure with minimal impact on changing the underlying VBA.

    I thought of writing an Item class, with each alias as a data member. This may be overkill, athough I can see some potential advantages. Short of this, is it viable to use alias names like this in VBA code? If so, what might be some examples/benefits?

    thanks

    Alan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell Aliases (2000)

    Try using named ranges. If you select A2: E10 (or as far down as needed), you can assign names to the columns by Insert | Name | Create..., check only "Top Row", or you can assign names to rows and columns by leaving "Top Row" and "First Column" checked. You can then refer to Range("desc"), and in the latter case even to Range("P128_ desc")
    Note: the row with P128 is named P128_ because P128 is also a cell reference.

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Aliases (2000)

    Thank you Hans. This may be what I'm after, but I'm a bit at sea with the referencing method. If I wanted to refer to the "desc" cell in the active row say, what would be the argument for Range()? Could I give the active row a name like "item_" and use "item_desc"? Or could I somehow call the variable item_desc the value of the Range or Cell (activerow,item)? I can't envisage the code for this.

    Alan

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell Aliases (2000)

    Named ranges are useful if you want to make worksheet formulas independent of the actual cell addresses, and can be used in VBA to to refer to ranges without knowing where they are exactly. I try to avoid setting the selection or active cell in code unless it can't be avoided. I don't know if there is an easy way to do what you want, perhaps one of the Excel gurus has ideas about this.

  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: Cell Aliases (2000)

    Maybe I am looking at it too simply, but If you give aliases (insert - name -define) to the cells as described in <post#=283302>post 283302</post#>
    A2="post"
    B2="short"
    C2="desc"
    D2="price"
    E2="bulk"

    You could get any item in the table using VB by using the cells property:
    MyValue = cells(activecell.Row,range("bulk").Column)

    If you wanted a particular NAMED column heading in the active row.

    Or if the names were always 1 column more, you could just name "post" and use an "offset"
    MyValue=cells(activecell.Row,range("post").Column+ 4) to get the same column.
    With this scheme, you could get each one with a loop (x = 0 to 5)
    MyValue = cells(activecell.Row,range("post").Column+x)

    Steve

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Aliases (2000)

    Thanks Hans. What you have given me in terms of effectively naming the columns is a good start for what I'm trying to do. The main purposes are, I suppose, to allow the VBA to require minimum modification if the column structure of the sheet is changed and to make the code more easily understood by others. I'm not fiddling with the active cell/ selection in any way here BTW, but I do need to know what it is in order to retrieve or set appropriate column values.

    I'm thinking now that it might even be easier to define a set of global const values for the aliases within the VBA project. These could then be the sole place where alterations need occur. I have also written set() and get() functions for attributes, which make the code read a lot more simply:
    Cells(ActiveCell.row, price2).Value becomes GetVal(row, price2) and
    Cells(ActiveCell.row, price2).Value = something becomes SetVal(row, price2, something)

    A bit rough, but this is the general idea. Thanks for the advice... needs more work now <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Alan

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Aliases (2000)

    Thanks Steve. I may have at least begun to sort this out, as described in my reply to Hans. But I think your referencing method is probably better than the one I'm using:
    <hr>You could get any item in the table using VB by using the cells property:
    MyValue = cells(activecell.Row,range("bulk").Column)<hr>
    I had been thinking of defining names in the VBA code. With your method, the user of the sheet could fiddle with its column structure, and provided they redefined the names properly, the code should still work. This may be a significant consideration in its end use.

    Alan

  8. #8
    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: Cell Aliases (2000)

    Another technique NOT involving range names is to define in the code what text to look for in the headers. At the start of the code, look through the range to try and find each of the text. Then define the variable for each item as the column number where you find it. No need to name, just must know what the column names are. If you don't find them all, you can pop up a message.

    You could also have the user HIGHLIGHT the range (with an input box) of the appropriate column for "post" when the code is run. Get the col number of this cell and assign it for "post" column
    "short" = post +1, "desc" = post + 2, "price" = post +3, "bulk" = past + 4

    There are other techniques, depending on exactly how you want to use it and how often it might change.
    Steve

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Aliases (2000)

    Thanks again Steve. I'll "catalog" that method as another possible. An "integrity check" on opening the workbook is probably a worthwhile addition. Trouble is I don't know how often changes will be made, or to what extent. There are also several "levels" of user for this workbook, and those dealing with the "master" I've described will probably be competent enough to be able to maintain its integrity in terms of column names. Fortunately, Hans put me onto the VeryHidden worksheet status, so the majority of users are never afforded the chance to mess up the master, only various "views" that are recreated anyway, each time the workbook is opened.

    Alan

Posting Permissions

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