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

    Methods and Properties Joined (Excel 2000 > VBA)

    I discovered something I never know while browsing around some VBA code help.

    I notice a line of code like this:
    ActiveWorkbook.Worksheets.Add.Name = "Test"

    I've never seen a method and a property tied together in one line of code??!! The above line adds a new worksheet to the activeworkbook and immediately names the sheet to "Test".

    Does anyone else know of other instances where this can be done, as I tried to play around with joining other methods and properties without much success? I obviously know that the two must pair up and run in context!!

    I thought this could be an interesting way of shortening out and speeding up the running of the code!
    Regards,
    Rudi

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

    Re: Methods and Properties Joined (Excel 2000 > VBA)

    Many methods that create a new object are actually functions with the object as return value. The Worksheets.Add method is one example, as you discovered, the Workbooks.Add method is another. Similarly, in Word, Documents.Add returns a Document object. Methods named Add are obvious candidates, as are methods whose name contains Create.
    If you want to set more than one property, you can use a With ... End With construction, like this:

    With ActiveSheet.Shapes.AddShape(msoShapeRectangle, 10, 10, 100, 20).Fill
    .BackColor.RGB = vbGreen
    .ForeColor.RGB = vbRed
    End With

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

    Re: Methods and Properties Joined (Excel 2000 > VBA)

    Thanx for that insight Hans. When I saw this in the help files, I was very surprized, but in retrospect, it does clarify when you say that properties can join to methods that create/add new objects. It saves on one line of code!
    Cheers
    Regards,
    Rudi

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Methods and Properties Joined (Excel 2000 > VB

    Another example is used to retrieve the e-mail sender's address in Outlook programming, using the Reply method on the current message. I forget the details, but you can find examples here and there. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> While you seem to save a line of code this way, if you intend to do anything else that refers to that tab, you are better off using the Set newobject = xyz and then With newobject...End With, so that VBA can set your properties most efficiently.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Methods and Properties Joined (Excel 2000 > VBA)

    Another example I recently found is that the Execute Method of Word's Find Method can be operated "in-line":

    rng.Find.Execute FindText:=myfindtext, MatchCase:=True, Wrap:=wdFindStop, _
    ReplaceWith:=myreplacetext, Format:=False, Replace:=wdReplaceOne

    though the syntax isn't as legible as the usual 'With rng.Find' ....
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Methods and Properties Joined (Excel 2000 > VBA)

    Thanx guys,
    Its always interesting to learn these new patterns, and a bonus to be guided by further tips and tricks about it!
    Its always valuable to get more insight!!
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

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

    Re: Methods and Properties Joined (Excel 2000 > VBA)

    You should be able to use a property with any method that returns an object.
    Legare Coleman

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

    Re: Methods and Properties Joined (Excel 2000 > VBA)

    Yes, I'm discovering this as I'm playing around with this concept a little more now! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Regards,
    Rudi

Posting Permissions

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