Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    SET OBJECT (Excel 2000)

    Hi all,

    I need an understand setting the range object variable. Is it necessary to set every Range variable?
    I need to get this straight in my head

    Thanks
    Darryl.

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SET OBJECT (Excel 2000)

    You need to set a reference to any object you declare. Each Object needs a set statement! You can use the set statement to change the reference as many times within the sub procedure as you like, as long as the reference is of the same object type!
    EG:
    Dim myR as Range
    Set myR = Range("A1")
    Do code with rande A1
    Set myR = Range("A3:B10")
    Do code on this range
    Set myR = Nothing
    End sub
    Regards,
    Rudi

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

    Re: SET OBJECT (Excel 2000)

    A Range is an object that has properties and methods. A variable that represents an object must always be assigned a value by using Set. Before you do so, an object variable is just an empty object that doesn't contain any data.

    If you look up Excel Objects in the VBA help, you will see a chart of the Excel object model; since it is very large some parts are presented on separate pages. The screenshot below is part of the object model for Excel worksheets. Yellow boxes are collections, blue boxes are objects.

  4. #4
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: SET OBJECT (Excel 2000)

    Thank you

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

    Re: SET OBJECT (Excel 2000)

    You only need to set the object variables that you use. Until you set an object variable is set, it is empty (Nothing). If you attempt to use that object variable in any way that uses the object, then an error will result. If you DIM an object variable, but never reference it in a way that requires it to have a reference set, then that is not a problem. You can also use object variables that have not been set in ways that do not cause an error. For example:

    <pre>DIM oRng As Range
    If oRng Is Nothing Then
    </pre>


    The second line uses oRng which has not been set, and this does not cause an error. However, the following would cause an error:

    <pre>DIM oRng As Range
    oRng.Value = oRng.Value + 1
    </pre>

    Legare Coleman

Posting Permissions

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