• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to optimize my code

Bomino

Bomino

Member
Hello

I am a newbie and I was wondering if you could help me optimize the following code.
Thanks.

Code: 
Sub faster()

  Dim ws As Worksheet

With Application
      .DisplayAlerts = False
      .EnableEvents = False
      .ScreenUpdating = False
      .Calculation = xlCalculationManual
    
      End With
    
  
    Set ws = ThisWorkbook.Worksheets("Calculations")
  
    With ws.Range("C3:C54")
        .FormulaR1C1 = "=IF(SUMIF(dtWeekEnded,RC[-1],TotalWages)=0,NA(),SUMIF(dtWeekEnded,RC[-1],TotalWages))"
        .Value = .Value
    End With
  
    With ws.Range("C3:C54")
        .FormulaR1C1 = "=IF(SUMIF(dtWeekEnded,RC[-1],TotalWages)=0,NA(),SUMIF(dtWeekEnded,RC[-1],TotalWages))"
        .Value = .Value
    End With
  
    With ws.Range("D3:D54")
        .FormulaR1C1 = "=IF(VLOOKUP(RC[-2],Look1,4,FALSE)=0,NA(),VLOOKUP(RC[-2],Look1,4,FALSE))"
        .Value = .Value
    End With
  
    With ws.Range("E3:E54")
        .FormulaR1C1 = "=RC[-1]-RC[-2]"
        .Value = .Value
    End With
  
    With ws.Range("F3:F54")
        .FormulaR1C1 = "=IF(SUMIF(dtWeekEnded,RC[-4],OTHours)=0,NA(),SUMIF(dtWeekEnded,RC[-4],OTHours))"
        .Value = .Value
    End With
    With ws.Range("g3:g54")
        .FormulaR1C1 = "=SUMIF(dtWeekEnded,RC[-5],RegHours)"
        .Value = .Value
    End With
  
    With ws.Range("H3:H54")
        .FormulaR1C1 = "=1.5*RC[-2]+RC[-1]"
        .Value = .Value
    End With
  
    With ws.Range("I3:I54")
        .FormulaR1C1 = "=IF(RC[-1]=0,0,RC[-3]/RC[-1])"
        .Value = .Value
    End With
  
    With ws.Range("J3:J54")
        .FormulaR1C1 = "=RC[-2]/40"
        .Value = .Value
    End With
  
    With ws.Range("K3:K54")
        .FormulaR1C1 = "=IF(Collection!R[-1]C[-5]=0,NA(),Collection!R[-1]C[-5])"
        .Value = .Value
    End With
  
    With ws.Range("K3:K54")
        .FormulaR1C1 = "=IF(Collection!R[-1]C[-5]=0,NA(),Collection!R[-1]C[-5])"
        .Value = .Value
    End With
  
    With ws.Range("L3:L54")
        .FormulaR1C1 = "=SUMIF(TMDATE,RC[-10],TMRGHRS)"
        .Value = .Value
    End With
  
    With ws.Range("M3:M54")
        .FormulaR1C1 = "=SUMIF(TMDATE,RC[-11],TMOTHRS)"
        .Value = .Value
    End With
  
    With ws.Range("N3:N54")
        .FormulaR1C1 = "=IF((RC[-2]+RC[-1])=0,NA(),(RC[-2]+RC[-1]))"
        .Value = .Value
    End With
  
    With ws.Range("O3:O54")
        .FormulaR1C1 = "=RC[-7]+RC[-1]"
        .Value = .Value
    End With
  
    With ws.Range("P3:p54")
        .FormulaR1C1 = "=IF(RC[-1]=0,"""",RC[-10]/RC[-1])"
        .Value = .Value
    End With
  
    With ws.Range("Q3:Q54")
        .FormulaR1C1 = "=IF(RC[-2]=0,"""",RC[-2]/40)"
        .Value = .Value
    End With
  
  
  
         With Application
      .DisplayAlerts = True
      .EnableEvents = True
      .ScreenUpdating = True
      .Calculation = xlCalculationAutomatic
    
      End With
  
End Sub
 
Last edited by a moderator:
Hi, Bomino!

Whenever you post code, you should embed it within proper tags to preserve indentation and spacing, as it's displayed just above the reply text box in this page used for posting comments. Or using the related icon from the ribbon toolbar, 5th from th right excluding the last and separated one.

And about your requirement, the posted code is very simple, it just place formulas, so there's nothing than can be done as you've yet played with application objects properties. Just curious, why do you think it needs optimization?

Regards!
 
SirJB7,

Thanks for your prompt response and I do apologize for posting the code the way I did.

I would like to use it with "Worksheet Activate" and its taking few seconds on my machine to load up. I was thinking someone would help me make it a little faster.

Regards.
 
Hi ,

I am not so sure about optimization , but you can certainly shorten your code and make it a little more readable :
Code: 
Sub faster()
    Dim ws As Worksheet

    With Application
        .DisplayAlerts = False
        .EnableEvents = False
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With


    Set ws = ThisWorkbook.Worksheets("Calculations")

    With ws
        .Range("C3:C54").FormulaR1C1 = "=IF(SUMIF(dtWeekEnded,RC[-1],TotalWages)=0,NA(),SUMIF(dtWeekEnded,RC[-1],TotalWages))"

        .Range("D3:D54").FormulaR1C1 = "=IF(VLOOKUP(RC[-2],Look1,4,FALSE)=0,NA(),VLOOKUP(RC[-2],Look1,4,FALSE))"

        .Range("E3:E54").FormulaR1C1 = "=RC[-1]-RC[-2]"

        .Range("F3:F54").FormulaR1C1 = "=IF(SUMIF(dtWeekEnded,RC[-4],OTHours)=0,NA(),SUMIF(dtWeekEnded,RC[-4],OTHours))"

        .Range("G3:G54").FormulaR1C1 = "=SUMIF(dtWeekEnded,RC[-5],RegHours)"

        .Range("H3:H54").FormulaR1C1 = "=1.5*RC[-2]+RC[-1]"

        .Range("I3:I54").FormulaR1C1 = "=IF(RC[-1]=0,0,RC[-3]/RC[-1])"

        .Range("J3:J54").FormulaR1C1 = "=RC[-2]/40"

        .Range("K3:K54").FormulaR1C1 = "=IF(Collection!R[-1]C[-5]=0,NA(),Collection!R[-1]C[-5])"

        .Range("L3:L54").FormulaR1C1 = "=SUMIF(TMDATE,RC[-10],TMRGHRS)"

        .Range("M3:M54").FormulaR1C1 = "=SUMIF(TMDATE,RC[-11],TMOTHRS)"

        .Range("N3:N54").FormulaR1C1 = "=IF((RC[-2]+RC[-1])=0,NA(),(RC[-2]+RC[-1]))"

        .Range("O3:O54").FormulaR1C1 = "=RC[-7]+RC[-1]"

        .Range("P3:P54").FormulaR1C1 = "=IF(RC[-1]=0,"""",RC[-10]/RC[-1])"

        .Range("Q3:Q54").FormulaR1C1 = "=IF(RC[-2]=0,"""",RC[-2]/40)"

        .Range("C3:Q54").Value = .Range("C3:Q54").Value
    End With

    With Application
        .DisplayAlerts = True
        .EnableEvents = True
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
End Sub

Another point is , why do you want to link its execution to activating the worksheet ?

Narayan
 
Last edited by a moderator:
Narayan,

I would like to create a dashboard down the road and all the data will be derived from the worksheet. I thought worksheet activate will be a good idea....Like I said I am a newbie. Is there a better approach?
Thank you for your help.

Regards.
 
Hi ,

The Worksheet_Activate event procedure is required only if you will be doing frequent data entry on another sheet tab , and you do not wish to calculate again and again , especially if your recalculation times are high.

If your workbook is not a compute-intensive workbook ( your data does not span more than say 10000 rows , and you have not used too many complex formulae ) , then even if your coded formulae are directly entered into the worksheets , there should be no problem.

Coding your formulae , and converting them to values is only if you want to reduce recalculation times.

However , if you plan to create a dashboard , and then want to have it interactive as well , with the interactivity on the same Dashboard tab , then the Worksheet_Activate event procedure will not work , since your Dashboard tab will always be the active sheet. You might want to shift to the Worksheet_Change or Worksheet_Calculate event procedures.

What are your recalculation times if you introduce all the VBA coded formulae directly into your worksheet ? Try this first , so that you can decide whether the shift to VBA is really necessary.

Narayan
 
Bomino said:
I would like to use it with "Worksheet Activate" and its taking few seconds on my machine to load up. I was thinking someone would help me make it a little faster.
Click to expand...
Hi, Bomino!

As you surely read above using the worksheet activate event to place that bunch of formulas won't only speed up calculation time but instead it'd slow it a bit, as besides you should add the run time formula settings to the normal calculation time. So unless it's a very strange dashboard there's no need to do any formula setting at worksheet activating time.

If I were you I'd start giving a look at this:
http://chandoo.org/wp/excel-dashboards/

Regards!
 
Narayan,

Thank you so much for the guidance. Let me provide some details:

Data will be entered in daily and I do not want to take the risk of getting the formulae altered or tempered with, hence the idea of converting them to values. Also, I am planning to use Chandoo's "Interactive chart" technique (http://chandoo.org/wp/2013/04/23/interactive-chart-in-excel-tutorial/); so I thought a bunch of sumifs and Vlookup will slow down the spreadsheet.

I will certainly take into account your advise. I will give it a try and will certainly keep you posted.

Once again thanks a lot!!

Regards.
 
Hi ,

All the best ; in any workbook , it is always a good idea to segregate the input data , the calculations , and the output Dashboard into their own sheets. This way , the calculations tab can be protected so that the user will find it more difficult to access the formulae. The chances of the formulae being altered or tampered with are less.

SUMIFS and VLOOKUPs will not slow down the response till there are thousands of them ; if your data is not that much , you need not worry. As far as possible try to use helper cells / columns.

Narayan
 
Narayan,

Like you have suggested, the Calculations, Dashboard and Raw_data are in separate sheets. The rows in my Input data sheets will be close to six-figure. I will most likely end up with tens of thousands of SUMIFS & VLOOKUPs.
I've tested the "Interactive charts" with the formula in the cells of the "Calculations" sheet and the response of the charts interactivity was kind of sluggish, slow. But when I've replaced the formula with their values, I've noticed a tremendous difference.

I will definitely keep you posted .

SirJB7 and Narayan, I really appreciate your help.

Thanks.
 
You must log in or register to reply here.
Back
Top