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

    With Statements get split unnecessarily!! (Excel VBA (Macro Recording))

    Why does the macro recorder split some With Statements for no apparent reason? If I record a macro to create a header and footer for example, it does this:

    Sub HF()
    With ActiveSheet.PageSetup
    .PrintTitleRows = ""
    .PrintTitleColumns = ""
    End With

    ActiveSheet.PageSetup.PrintArea = ""

    With ActiveSheet.PageSetup
    .LeftHeader = "Rudi"
    .CenterHeader = "&T"
    .RightHeader = "&D"
    .LeftFooter = ""
    .....
    .....
    End With
    End Sub

    ActiveSheet.PageSetup is the same object...even for ActiveSheet.PageSetup.PrintArea = ""?????

    Why split and have to make 2 With Statements?
    (I notice this occurence on other recordings too!!)
    Regards,
    Rudi

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

    Re: With Statements get split unnecessarily!! (Excel VBA (Macro Recording)

    I'm only guessing here Rudi, but from the look of the "splits", it seems that the "processor" that turns groups of keystrokes into equivalent VBA "mini procedures" is probably working on fixed groups of things at a time. In your example, it seems to be determining the settings for PrintTitle-related things first, then PrintArea settings, then header/ footer data etc. It appears that each step of the analysis generates a (more or less) self-contained block of statements. When these are chained together, probably no consideration is given to any "commonality" that might coincidentally exist between neighbouring blocks. Maybe...

    Alan

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

    Re: With Statements get split unnecessarily!! (Excel VBA (Macro Recording)

    I agree with Alan. The macro recorder does not know in advance what you're going to do. As soon as it has a logically contained set of actions, it'll write the VBA code for it, not waiting to see if your next action is related. So although the macro recorder is very useful to get an idea of the code needed to do something, it will not necessarily generate elegant or efficient code.

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

    Re: With Statements get split unnecessarily!! (Excel VBA (Macro Recording)

    That does make it clearer. Thanx for the insight. I'll never complain about the recorder though...its highly useful!
    Tx guys!
    Regards,
    Rudi

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

    Re: With Statements get split unnecessarily!! (Excel VBA (Macro Recording)

    I guess it sits somewhere between a totally dumb "play back your keystrokes" macro recorder, and something at the other end the computer gene pool, like the (amazing) 4GL technology that enables non-procedural languages like SQL to work the way they do. As Hans says, once you've done enough to make sense as a logical block of code, the macro recorder processes it. With SQL, the entire query is processed and optimized, so effectively you're describing the answer you want to the program, and it works out the code to give it to you... if that makes any sense at this time of night.

    Alan

  6. #6
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    vancouver, BC, Br. Columbia
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: With Statements get split unnecessarily!! (Excel VBA (Macro Recording)

    Makes a lot of sense and is fascinating.

Posting Permissions

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