The ID Property

No attribution, so I assume it’s David Hagar

EXCEL 2000 TIP

An intriguing property was added to the Range object in Excel 2000. It
is the ID property. In the normal scheme of things, it assigns a string
to a worksheet cell, which is used in a HTML tag when the worksheet is
saved as a web page. If the worksheet is saved in a normal manner, the
ID does not appear to be persistent. However, if ID’s of cells are set
when a workbook is opened, they can be used in some interesting ways.
As an example, consider the following:

Sub Auto_Open()
    With Sheets(1)
    .Range(“a1”).ID = “Test”
    End With
End Sub
 
‘in Sheet1 module
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ActiveCell.Address <> Target.Address Then Exit Sub
    If Target.ID <> “” Then
        MsgBox Target.ID
    End If
End Sub

In this case, if A1 is selected, the MsgBox dialog will appear with
the text “Test”. Thus, this provides a method of creating cell comments
that do not have to be stored with the cells. Further, if the assignment
of ID strings to cells is criteria-based rather than address-based, then
this provides a dynamic method of documenting cells of varying properties.

Another use of the ID property would be to apply the Collection class
for the Tag property developed by Ken Getz and Mike Gilbert (shown in the
Nov ’98 issue of MOD magazine, p.36) to this system. I leave that as an
exercise to the reader.

Editor’s Note: It’s too bad this property doesn’t persist. Maybe when Excel goes XML, it will.

Posted in Uncategorized

10 thoughts on “The ID Property

  1. Billkamm: It’s not written any more. J-Walk has been hosting the archives since the beginning, but he and David agreed to allow me to reproduce them here. I test the solution, provide some examples and commentary, but otherwise don’t really add much value to them. It’s just an attempt to get the information out to a group of people who may not have otherwise seen them.

  2. DIck – Speaking about EEE. What was the main reason for not continuing to publish it?

    Yes, I learned a lot from the EEE-letters :)

    Kind regards,
    Dennis

  3. Dear dailydoseofexcel,
    The cell K2 = sum( I2*AS2)
    in which
    I2= 20871 where the forumla here is I2=SUM(G2+H2)
    AS2 =0.077 – where the forumla here is
    =IF(ISERROR((AG2/(AG2+AJ2+AM2)*AD2)*A2*1.05/840/AF2), “”,((AG2/(AG2+AJ2+AM2)*AD2)*A2*1.05/840/AF2))
    So, the correct result should be K2= 1607.067.
    Why it calculates to the cell of K2 result return now is
    1598.588 – it is wrong answer.

    For details, pls see the wrapwrepusage.zip file

    It is a big different.
    Pls instruct !
    thanks
    gary
    mY EMAIL: garywwww3@hotmail.com

  4. Not quite sure how this K2, I2, whatever problem fits in with the ID discussion but…

    I suspect K2 shows 0.077 as the result of the format/column width settings. The actual number is probably closer to 0.076593743.

    Also, =SUM(I2*AS2) is unnecessary. Just =I2*AS2 will do.

  5. OK, guys.
    Last time I looked, I could not access the Getz & Gilbert article. Can’t find it again now. Anyone have a link?

  6. worksheet A
    cell F name is called product code
    cell F2 is Q921
    cell F3 is Q5201
    cell G name is called yarn count

    but
    worksheet B
    cell A name is called product code same as cell F of worksheetA
    Cell B name is called yarn count same as cell G of worksheetA

    if user plugin cell B62 with value 1234 and cell A62 at worksheet B is
    equal to worksheetA cell F2.

    How it link up and automatically display on G2 and the rest…
    but must be able to match and identify the product code of worksheet A
    and B
    in order for user to plugin lots of different number on worksheetB-
    column cellB
    and it will display back to worksheetA – column cellG

    Is vlookup and please give formula.
    thanks

    please instruct!
    thanks
    garywwww3@hotmail.com

Leave a Reply

Your email address will not be published. Required fields are marked *