Consulting

Page 1 of 4 1 2 3 ... LastLast
Results 1 to 20 of 68

Thread: Advice needed to speed this code.

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Tutor
    Joined
    Sep 2009
    Posts
    231
    Location

    Advice needed to speed this code.

    Hello Everybody,

    Am actually using the below code to place formula for calculation in VBA. I need to fill it in almost 104 columns and down to rows till we have data right actually it is to 660 rows but later can grow further.

    The actual time taken to complete the filling of the formula across the column and rows is taking a lot of time which is around from 55 sec to 68 secs just for this part and sometimes the screen get freeze and sometimes saying Excel Not responding.

    Here is my code:

    Sub Main_Normal_Calculation()
    With Application
        '.Cursor = xlWait
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    
    startTime = Timer
        Dim lLR As Long
        
        Call Block_date_Start_Date
        
        
        
        With ThisWorkbook.Sheets("Shadow_Normal_Calc")
            lLR = Cells(Rows.Count, "A").End(xlUp).Row
        End With
    
    'assigning the main loading formula
         Application.StatusBar = " Automated Planning : Computing ...."
        With Range("AY59:EX" & lLR)
            .Formula = "=IF(OR($AF59="""",$AF59="""",$AJ59=""""),0,IF(AY$58<$AJ59,0,IF(AY$58>=$AJ59,IF(0<($AN59),MIN(($AN59-SUM(AX59:$AX59)),$AO59,SUMIF(Shadow_Dept_Lines_Sum_Col,$AM59,AY$4:AY$56)-SUMIF($AM$58:$AM58,$AM59,AY$58))))))"
            '.Application.Calculation = xlCalculationAutomatic
            .Value = .Value
        End With
        
        'assigning the Load control qty
         Application.StatusBar = " Automated Planning : Computing the Load Control qties"
        With Range("AW59:AW" & lLR)
            .Formula = "=AN59-SUM(AY59:EX59)"
            '.Application.Calculation = xlCalculationAutomatic
            .Value = .Value
        End With
        
        MsgBox Timer - startTime & " secs."
    
    
    
    With Application
        .Cursor = xlDefault
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
    
    End Sub
    I would much appreciate if help on how i can speed this code please. I have been struggle for almost a month now but has not been able to figure it out.

    Am attaching a sample of the file where you can see the calculation time taken by clicking on the buttons place on the sheet.

    Thanking in advance for any help, advise and recommendations.


    Vishal.
    Attached Files Attached Files

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    try this
    Sub Main_Normal_Calculation()
        With Application
             '.Cursor = xlWait
            .ScreenUpdating = False
            .DisplayAlerts = False
            .EnableEvents = False
            .Calculation = xlCalculationManual
        End With
         
        startTime = Timer
        Dim lLR As Long
         
        'Call Block_date_Start_Date
         
        MsgBox "Block_date_Start_Date took " & Timer - startTime & " secs."
        startTime = Timer
        
         
        With ThisWorkbook.Sheets("Shadow_Normal_Calc")
            lLR = Cells(Rows.Count, "A").End(xlUp).Row
         
             'assigning the main loading formula
            'Application.StatusBar = " Automated Planning : Computing ...."
            Range("AY59").Formula = "=IF(OR($AF59="""",$AF59="""",$AJ59=""""),0,IF(AY$58<$AJ59,0,IF(AY$58>=$AJ59,IF(0<($AN59),MIN(($AN59-SUM(AX59:$AX59)),$AO59,SUMIF(Shadow_Dept_Lines_Sum_Col,$AM59,AY$4:AY$56)-SUMIF($AM$58:$AM58,$AM59,AY$58))))))"
            With Range("AY59:EX" & lLR)
                .FillDown
                .Calculate
                .Formula = .Value
            End With
             
             'assigning the Load control qty
            'Application.StatusBar = " Automated Planning : Computing the Load Control qties"
            Range("AW59").Formula = "=AN59-SUM(AY59:EX59)"
            With Range("AW59:AW" & lLR)
                .FillDown
                .Calculate
                .Formula = .Value
            End With
        End With
         
        MsgBox "Main Code took " & Timer - startTime & " secs."
         
         
         
        With Application
            .Cursor = xlDefault
            .ScreenUpdating = True
            .DisplayAlerts = True
            .EnableEvents = True
            .Calculation = xlCalculationAutomatic
        End With
         
    End Sub
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    At the beginning of a formula you have:
    .Formula = "=IF(OR($AF59="""",$AF59="""",$AJ59="""")
    where $AF59 is checked twice; is this an unnecessary double check or should one of the $AF59s be different?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    I notice also that row 59 is different from the ones below it in columns A:AR but your code puts the same formulae in row 59 in columns AY:EX as in the rows below.
    Shouldn't the code only put formulae in from row 60 downwards rather then from row 59 downwards?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    In the range AV5:AV56 will there ever be duplicate values?
    I ask because you've got a SUMIF in the formula (SUMIF(Shadow_Dept_Lines_Sum_Col,$AM59,AY$4:AY$56)) which currently only ever returns one value from the range rather than a sum of several values.
    Last edited by p45cal; 05-14-2016 at 02:54 PM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Is it necessary to use formulas in the WS and then make them values?

    A 'calculation' macro that only put the final results into the cells would be faster
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    There's something odd about your formula. In the code the end of the formula is -SUMIF($AM$58:$AM58,$AM59,AY$58)
    In cell BE66 this results in: -SUMIF($AM$58:$AM65,$AM66,BE$58)
    The third argument is usually a range that you want to sum, but at the moment it's only the date in row 58 above BE66:
    Capture3.JPG
    Surely not right?!
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  8. #8
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    psssst; if you or your company are going to use pirated versions of MS Office you would do well to hide it!
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  9. #9
    VBAX Tutor
    Joined
    Sep 2009
    Posts
    231
    Location
    Hi Paul,I did not get you well . can you please explain to me. As its been already month i am trying to figure out but is still block with that. So am ready to try any new advise.

  10. #10
    VBAX Tutor
    Joined
    Sep 2009
    Posts
    231
    Location
    Hi,Thanks SamT , i will try and revert if its ok with the timing. thanks you for your kind help.P45Cal: Yes you are right the formula shall start on row 60 and not on row 59. Well concerning pirate or not. I cannot tell you as its the administrator who do all in the installation and also my pc has just been bought. So am not in a situation to confirm if this is pirate or no.But still thanks for helping.

  11. #11
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by VISHAL120 View Post
    Yes you are right the formula shall start on row 60 and not on row 59.
    Now you have to tackle the other questions in msgs #3, #6 anf #7. I can get the time down to around 1 second I think, if I know the answers to these.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  12. #12
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I was suggesting that instead of using the formula


    "=IF(OR($AF59="""",$AF59="""",$AJ59=""""),0,IF(AY$58<$AJ59,0,IF(AY$58>=$AJ59,IF(0<($AN59),MIN(($AN59-SUM(AX59:$AX59)),$AO59,SUMIF(Shadow_Dept_Lines_Sum_Col,$AM59,AY$4:AY$56)-SUMIF($AM$58:$AM58,$AM59,AY$58))))))"
    if you could calculate the results using VBA and put the answer in the cells it might be faster and less round about than making values out of the formula
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  13. #13
    VBAX Tutor
    Joined
    Sep 2009
    Posts
    231
    Location

    Advice needed to speed this code.

    Hi Paul,

    this is what am trying to figure out, how to do this on VBA instead of using the formula.

    P45Cal: thanks again for the help. replying:

    msg 3: yes it's an error we can remove one of the check on it.

    Msg 6: No it shall not have duplicate as 1 row represent one dept therefore if one dept is declare it shall not be duplicated.

    Msg 7: -SUMIF($AM$58:$AM65,$AM66,BE$58).

    Normally this sumif is suppose to do the calculation based on the capacity set by dept from rows AV5:AV56. example.

    if Cut Capacity is 6,500

    then the calculation shall look like that:

    Cut 6500 6500 6500 6500 6500 6500 6500
    order total mins Minimum mins block date still to load Date Mon-09-May Tue-10-May Wed-11-May Thu-12-May Fri-13-May Sat-14-May Mon-16-May
    1 15000 4000 10-May 0 4000 4000 4000 3000
    2 12000 3500 10-May 0 2500 2500 2500 2500 2000

    It shall check if there is still capacity to load and by how much like order 1 has already taken 4000 mins therefore the balance available mins is 2500 which is allocated to order 2 as even the Minimum mins to load is 3500 it can allocate only 2500 mins.


    This is what this sumif is suppose to do about. Well I don't know if am doing it right that is why i need some advise and recommendations. Am attaching a file to illustrate an example of that.

    I hope this can help you to figure out what the calculation is doing.

    thanks sir for helping me for this problem.

    vishal
    Attached Files Attached Files

  14. #14
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by VISHAL120 View Post
    Msg 7: -SUMIF($AM$58:$AM65,$AM66,BE$58).
    Normally this sumif is suppose to do the calculation based on the capacity set by dept from rows AV5:AV56. example.
    It shall check if there is still capacity to load and by how much like order 1 has already taken 4000 mins therefore the balance available mins is…
    This is what this sumif is suppose to do about. Well I don't know if am doing it right that is why i need some advise and recommendations. Am attaching a file to illustrate an example of that.
    I hope this can help you to figure out what the calculation is doing.
    OK, so it's just msg#7 that I'm not 100% sure about but I'm guessing that part is supposed to be (in cell BE66):
    -SUMIF($AM$58:$AM65,$AM66,BE$58:BE65)

    So unless I hear to the contrary I'll press on with that.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  15. #15
    VBAX Tutor
    Joined
    Sep 2009
    Posts
    231
    Location
    Hi SamT,

    i have just tested the code it still running at 54 secs which is still time consuming. thank again for the help

  16. #16
    VBAX Tutor
    Joined
    Sep 2009
    Posts
    231
    Location
    Hi P45Cal,

    Good morning. Yes you are right it shall be same as you mentioned for this sumif that is :-SUMIF($AM$58:$AM65,$AM66,BE$58:BE65).
    My omission.

    thank again for helping. And i hope that we can find the solution to speed up as actually its a seriou headache for still turning around 65- 70 secs.

    thanking you in advance.

  17. #17
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Progress report: While I'm getting similar results with faster code, whether I use my code or your code I still need to run it several times to get stable results; there's some kind of circular reference in there and I'm trying to find it.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  18. #18
    VBAX Tutor
    Joined
    Sep 2009
    Posts
    231
    Location
    Hi P45Cal,

    Thank you in advance sir. i will wait for the end results for sure. As its a real problem for me right now and even for the other users also.

    Again thank you for the kind help.

  19. #19
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    I think I know why your code is so slow, the With range statement means the code accesses the spreadsheet multiple times, the way to do it is to write all the equations into an array and then write the array to the spreadsheet in one go . I just tired this code and it was a factor of 50 times faster than yours.

    With ThisWorkbook.Sheets("Sheet1")
            lLR = Cells(Rows.Count, "A").End(xlUp).Row
        End With
         inarr = Range("AY59:EX" & lLR)
         'assigning the main loading formula
        Application.StatusBar = " Automated Planning : Computing ...."
    '    With Range("AY59:EX" & lLR)
    '        .Formula = "=IF(OR($AF59="""",$AF59="""",$AJ59=""""),0,IF(AY$58<$AJ59,0,IF(AY$58>=$AJ59,IF(0<($AN59),MIN(($AN59-'SUM(AX59:$AX59)),$AO59,SUMIF(Shadow_Dept_Lines_Sum_Col,$AM59,AY$4:AY$56)-SUMIF($AM$58:$AM58,$AM59,AY$58))))))"
     '        '.Application.Calculation = xlCalculationAutomatic
    '        .Value = .Value
    '    End With
          endarray = 59 - lLR
          For i = 1 To endarray
            inarr(i, 1) = "=IF(OR($AF59="""",$AF59="""",$AJ59=""""),0,IF(AY$58<$AJ59,0,IF(AY$58>=$AJ59,IF(0<($AN59),MIN(($AN59-SUM(AX59:$AX59)),$AO59,SUMIF(Shadow_Dept_Lines_Sum_Col,$AM59,AY$4:AY$56)-SUMIF($AM$58:$AM58,$AM59,AY$58))))))"
          Next i
           Range("AY59:EX" & lLR) = inarr
    Last edited by SamT; 05-20-2016 at 04:26 PM.

  20. #20
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Not bad for a first time poster
    Welcome aboard.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

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