Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Multiple sheets (2000 SR-1)

    In a workbook I have 12 sheets, named Jan thru Dec, that are layed out identically. The user can select from 1 to 12 sheets then invoke a macro that processes the data on each selected sheet. The relevant code is as follows:
    <pre> For Each wks In ActiveWindow.SelectedSheets
    wks.Activate
    ProcessSheet
    Next wks</pre>

    [Activating the sheet is deliberate - so that the user can see what's going on]. Within the ProcessSheet subroutine, I just refer to Activesheet rather than using the wks object.
    It works fine, except for how long it takes to run. If just one sheet is selected it processes in under one second. If two or more sheets are selected each sheet takes anywhere from 20 to 45 seconds. Anybody have any clue as to why? I wondered if it had to do with memory(?), but I have 320K, which I would have thought was plenty...

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Multiple sheets (2000 SR-1)

    Colin,
    Can you post the ProcessSheet code? My first thought would be that there's something there. (I'm assuming you meant 320MB not 320K!)
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Multiple sheets (2000 SR-1)

    Thanks, Rory. I could post the ProcessSheet code, but it wouldn't really help. It just calls other subroutines and checks a return code variable after each call. Since the called subroutines don't use the wks object, I can't see how it would have any significance(?). FWIW, the lower level code is just copying and pasting different sections into another workbook.
    I'm thinking it must have to do with the "overhead" of having multiple sheets selected, in some way. Right now I'm trying changing the code to initially loop thru' the selected sheets and populate an array with the indexes (indices?) of the sheets that were selected. I can then use that array to sequentially activate the selected sheets. I'll let you know if it makes a difference.
    Thanks for your help.
    You're right, of course, about the amount of memory I have. I thought it looked odd when I wrote it...

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Multiple sheets (2000 SR-1)

    Do you do any processing that involves PageSetup. Those routines are slow esp with multiple sheets.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Multiple sheets (2000 SR-1)

    Good question, but no. It's just copying and pasting. I have looked at the code executing, and the difference relates to the .PasteSpecial lines where I'm pasting data into another workbook. When it's being executed with multiple sheets selected, it's W-A-Y slower. Which leads me back to thinking it has something to do with the way the Excel code itself is written in terms of how the application handles switching to another workbook when the original workbook is in Group mode...

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Multiple sheets (2000 SR-1)

    No good. I created the array first - from within the "For each wks" construct - then used the array to sequentially activate each sheet that had been selected (i.e., after the "Next wks" statement). It didn't make a blind bit of difference.
    I'm even more confused now, though, because it's not behaving consistently. Before I'd tried the following code:
    <pre> For i = 1 To 12
    Sheets(i).Activate
    ProcessSheet
    Next i</pre>

    and it was really fast (about six seconds total to process all 12 sheets). However, I just tried it again and it took > 20 secs per sheet again.
    Very strange. The only apps I have open are Excel (2 workbooks), Outlook and IE, and that's been the same all day.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple sheets (2000 SR-1)

    Would setting calc to manual make a difference?
    Also: why not set an object with the selected sheets, then unselect the sheets and work with the object:

    <pre>Sub test()
    Dim oSheets As Object
    Dim oSheet As Object
    Set oSheets = ActiveWindow.SelectedSheets
    ActiveSheet.Select
    For Each oSheet In oSheets
    MsgBox oSheet.Name
    Next
    oSheets.Select
    End Sub
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Multiple sheets (2000 SR-1)

    Aha. Now it's starting to make (some) sense. The first line of my code is actually as follows:
    <pre> intCalcMode = Application.Calculation
    Application.Calculation = xlCalculationManual</pre>

    i.e., I store the user's current setting and set calculation to manual (restoring it at the end of the macro).
    However, what I think is happening is that despite the above code, when the data is pasted into the other workbook it's ignoring the setting and recalc'ing. Since the setting is at the application level, I presume it should apply to all workbooks(?).
    If I manually set calculation to manual then run the macro, it works fine.
    Thanks for pointing me in the right direction. Would you agree that the code above SHOULD work ok? Could the 'calculation mode' of the other workbook be overriding it somehow?

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple sheets (2000 SR-1)

    It should work as you coded it. Maybe somewhere calc mode is set back to auto? (or maybe you have a Application.Calculate somewhere?)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Multiple sheets (2000 SR-1)

    No, I'm thinking it must be a bug. I definitely don't have any Application.Calculate 's anywhere, nor do I touch the calc mode again, except to restore it at the end. As a workaround, I've added a line of code in one of the lower level subroutines to (again!) set calculation to manual. That does the trick.
    Thanks for your help. For now I would say it's just a glitch to be aware of...

  11. #11
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Multiple sheets (2000 SR-1)

    Was just looking at this again, and FWIW here's what I think is going on. When you set Calculation to Manual using Tools | Options, it switches the calculation mode of ALL open workbooks to Manual. i.e., it's at the Application level as it's intended to be. However, when you set it to manual using VBA, only the calculation mode of the active workbook is set to manual. In my work-around, after the macro activated a different workbook, I had to set Calc to manual again in order stop it from slowing to a crawl.

Posting Permissions

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