+ Reply to Thread
Results 1 to 10 of 10

In Procedure: When use Actual Range Object vs Create Local Object Using Set

  1. #1
    Registered User
    Join Date
    09-07-2011
    Location
    Sacramento
    MS-Off Ver
    Excel 2007
    Posts
    37

    In Procedure: When use Actual Range Object vs Create Local Object Using Set

    Hi All,

    This is probably a basic question... I have run into a couple of issues that lead me to believe that I may not completely understand Set object. I am hoping someone can clear things up.

    I have always thought that the following code creates an object INSTANCE (e.g. a copy, but no ties to original object) of the object:
    [EDIT: Incorrect assumption. Use the NEW keyword to create an instance of the object. SET by itself does in fact create a reference.]

    Please Login or Register  to view this content.
    I could then use the data in myRange as needed within the procedure. If, however, I wanted to affect the actual cells on the spreadsheet say to update a cell, I would have to point to the original object:

    Please Login or Register  to view this content.
    vs
    Please Login or Register  to view this content.
    As I have worked through some coding issues today, I have discovered that when using some object methods/properties my assumption is correct. In other cases it is not.

    Can someone explain this further?
    Thanks,
    John
    Last edited by Filibuster; 07-20-2012 at 02:11 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: In Procedure: When use Actual Range Object vs Create Local Object Using Set

    As I have worked through some coding issues today, I have discovered that when using some object methods/properties my assumption is correct. In other cases it is not.
    Your assumption is not correct -- the range object allows all of the range's properties and methods to be manipulated. Provide an example that you believe validates it.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    09-07-2011
    Location
    Sacramento
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: In Procedure: When use Actual Range Object vs Create Local Object Using Set

    Hi shg

    Well, I went back and played with the original code that was failing, and now it seems to work. Here is what was causing me issues:

    This works:
    Please Login or Register  to view this content.
    This was causing problems (but is no longer):
    Please Login or Register  to view this content.
    Are their any circumstances where the 2nd code snippit would fail?
    Last edited by Filibuster; 07-20-2012 at 01:59 PM.

  4. #4
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: In Procedure: When use Actual Range Object vs Create Local Object Using Set

    Please Login or Register  to view this content.
    or

    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.



  5. #5
    Registered User
    Join Date
    09-07-2011
    Location
    Sacramento
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: In Procedure: When use Actual Range Object vs Create Local Object Using Set

    snb - thanks for correction on my first post

    shg - here is a working/not-working example

    Works:
    Please Login or Register  to view this content.
    Does not work:
    Please Login or Register  to view this content.
    The CountIf function does not return the correct results in the 2nd example. I am guessing there is a problem with the way I am passing the range to the function.
    Last edited by Filibuster; 07-20-2012 at 02:00 PM.

  6. #6
    Registered User
    Join Date
    09-07-2011
    Location
    Sacramento
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: In Procedure: When use Actual Range Object vs Create Local Object Using Set

    Also - is there a way to use myUnique table with something like this:

    Please Login or Register  to view this content.
    Use somehow in this statement:
    Please Login or Register  to view this content.
    Last edited by Filibuster; 07-20-2012 at 02:00 PM.

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: In Procedure: When use Actual Range Object vs Create Local Object Using Set

    Filibuster, would you please take a few minutes to read the forum rules, and then edit all of your posts to add CODE tags?

  8. #8
    Registered User
    Join Date
    09-07-2011
    Location
    Sacramento
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: In Procedure: When use Actual Range Object vs Create Local Object Using Set

    Sorry - I did not know about [CODE]. Fixed.

  9. #9
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: In Procedure: When use Actual Range Object vs Create Local Object Using Set

    Gracias .

  10. #10
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: In Procedure: When use Actual Range Object vs Create Local Object Using Set

    Please Login or Register  to view this content.
    Josie

    if at first you don't succeed try doing it the way your wife told you to

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1