Replacing Large Amount of Formulas with Code

[reberryjr]

I need some help with how to best design an application. The current structure of this application uses hundreds of formulas across all of the sheets. The vast amount of formulas is drastically slowing down the processing of the application. With that said, I'm trying to find alternative methods of getting the results of the formulas, without sacrificing processing speed/performance. In Googling the issue, I've seen a couple of different possibilities on how to handle this, but I'm looking for advice based on experience, as to which approach is best.

Option A - Create a separate worksheet that holds each formula, and on the sheet where the formula would normally be entered, have that cell refer to the formula sheet instead.
Option B - Create a constant for each formula, and in the code, refer to the contstant, instead of entering the formula.
Option C - Enter the formulas onto the sheets as I am doing now, and copy - paste special values. Then, every time that an event occurs that should recalculate the formula, re-enter the formula, and copy - paste special values.

This is far more advanced than what I have been dealing with so far, so any assistance offered, is appreciated.

 

[Johnny C]

The answer would depend on the formulae, are they consistent (i.e. all the same, all the same in each column or row) in blocks.

if so, the way I would do it is to use VBA. On each block of cells with the formulae in, keep a row or column with the 'master' formula, and each time a recalculate is needed, use VBA to copy the formulas to the end, let it recalculate, then copy and paste values from the row/column beyond where the master formulae are kept.

it would help if you are familiar with dynamic ranges as these let you pick up all the data correctly each time.

Option A - reasonable approach, but there's the risk of links getting out of kilter if rows/columns are added/deleted when the other sheet isn't open. You would know how much of a risk that was.

Option B - by constant do you mean build the formula into the 'refers to' for a range name?

Option C - more or less what I would do but I'd use VBA if it was something that needed to be done often. But you would need VBA skills.

 

[reberryjr]

Here's an example of the formulae being added. This code would be entered on roughly 40 sheets (it can grow, but not much more than that).

ws6.Range("A" & LastRow6 + 1).Value = "=Today()"
ws6.Range("B" & LastRow6 + 1).Value = Now() 'Should this be Now or User Defined?
ws6.Range("C" & LastRow6 + 1).Value = "Initial"
ws6.Range("D" & LastRow6 + 1).Value = Me.txt_ClientID
ws6.Range("E" & LastRow6 + 1).Value = Me.txt_Name
ws6.Range("F" & LastRow6 + 1).Value = Me.txt_Nickname
ws6.Range("G" & LastRow6 + 1).Value = "=IF(RC[3]="""",""Inactive"",IF(RC[4]>=RC[3],""Paid"",""Pending""))"
If Not Len(Me.txt_DPDate) = 0 Then ws6.Range("H" & LastRow6 + 1).Value = CDate(Me.txt_DPDate)
ws6.Range("I" & LastRow6 + 1).Value = "=RC[-8]-RC[-1]"
If Not Len(Me.txt_DPAmt) = 0 Then ws6.Range("J" & LastRow6 + 1).Value = CCur(Me.txt_DPAmt)
If Not Len(Me.txt_DPPaid) = 0 Then ws6.Range("K" & LastRow6 + 1).Value = CDate(Me.txt_DPPaid)
ws6.Range("L" & LastRow6 + 1).Value = "=IF(RC[-2]="""",0,IF(RC[-1]<rc[-2],rc[-11]-rc[-4],0))"
ws6.Range("M" & LastRow6 + 1).Value = "=IF(RC[3]="""",""Inactive"",IF(RC[4]>=RC[3],""Paid"",""Pending""))"
If Not Len(Me.txt_TPDate) = 0 Then ws6.Range("N" & LastRow6 + 1).Value = CDate(Me.txt_TPDate)
ws6.Range("O" & LastRow6 + 1).Value = "=RC[-14]-RC[-1]"
If Not Len(Me.txt_TPAmt) = 0 Then ws6.Range("P" & LastRow6 + 1).Value = CCur(Me.txt_TPAmt)
If Not Len(Me.txt_TPPaid) = 0 Then ws6.Range("Q" & LastRow6 + 1).Value = CDate(Me.txt_TPPaid)
ws6.Range("R" & LastRow6 + 1).Value = "=IF(RC[-2] = """",0,IF(RC[-1]<rc[-2],rc[-17]-rc[-4],0))"
ws6.Range("S" & LastRow6 + 1).Value = "=IF(RC[10]<>"""",""Inactive"",IF(RC[1]="""",""Inactive"",""Active""))"
If Not Len(Me.txt_TRStart) = 0 Then ws6.Range("T" & LastRow6 + 1).Value = CDate(Me.txt_TRStart)
ws6.Range("U" & LastRow6 + 1).Value = "=IF(RC[-1]="""",""0"",IF(RC[8]<>"""",RC[8]-RC[-1],RC[-20]-RC[-1]))"
If Not Len(Me.txt_TR1stPymt) = 0 Then ws6.Range("V" & LastRow6 + 1).Value = CDate(Me.txt_TR1stPymt)
If Not Len(Me.txt_TRPymtAmt) = 0 Then ws6.Range("W" & LastRow6 + 1).Value = CCur(Me.txt_TRPymtAmt)
If Not Len(Me.cobo_TRFreq) = 0 Then ws6.Range("X" & LastRow6 + 1).Value = Me.cobo_TRFreq
ws6.Range("Y" & LastRow6 + 1).Value = "=IF(RC[-6]=""Inactive"","""",IF(RC[-3]<>"""",RC[-3],IF(R[-1]C[-1]=""B"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+14),IF(R[-1]C[-1]=""W"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+7),IF(R[-1]C[-1]=""M"",DATE(YEAR(R[-1]C),MONTH(R[-1]C)+1,DAY(R[-1]C)))))))"
ws6.Range("Z" & LastRow6 + 1).Value = "=IF(RC[-7]=""Inactive"",""Inactive"",IF(RC[53]<=0,""Paid"",IF(RC[-1]>=RC[-24],""Current"",""Late"")))"
ws6.Range("AA" & LastRow6 + 1).Value = "=IF(RC[-8]=""Inactive"",0,IF(RC[-2]<=RC[-26],RC[-4],0))"
ws6.Range("AB" & LastRow6 + 1).Value = "=IF(RC[-8]=""Late"",RC[-27]-RC[-3],0)"
'ws6.Range("AC" & LastRow6 + 1).Value  Nothing mapped to this field, as End Dates aren't populated when Clients are being added.
ws6.Range("AD" & LastRow6 + 1).Value = "=IF(RC[10]<>"""",""Inactive"",IF(RC[1]="""",""Inactive"",""Active""))"
If Not Len(Me.txt_DCStart) = 0 Then ws6.Range("AE" & LastRow6 + 1).Value = CDate(Me.txt_DCStart)
ws6.Range("AF" & LastRow6 + 1).Value = "=IF(RC[-1]="""",""0"",IF(RC[8]<>"""",RC[8]-RC[-1],RC[-31]-RC[-1]))"
If Not Len(Me.txt_DC1stPymt) = 0 Then ws6.Range("AG" & LastRow6 + 1).Value = CDate(Me.txt_DC1stPymt)
If Not Len(Me.txt_DCPymtAmt) = 0 Then ws6.Range("AH" & LastRow6 + 1).Value = CCur(Me.txt_DCPymtAmt)
If Not Len(Me.cobo_DCFreq) = 0 Then ws6.Range("AI" & LastRow6 + 1).Value = Me.cobo_DCFreq
ws6.Range("AJ" & LastRow6 + 1).Value = "=IF(RC[-6]=""Inactive"","""",IF(RC[-3]<>"""",RC[-3],IF(R[-1]C[-1]=""B"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+14),IF(R[-1]C[-1]=""W"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+7),IF(R[-1]C[-1]=""M"",DATE(YEAR(R[-1]C),MONTH(R[-1]C)+1,DAY(R[-1]C)))))))"
ws6.Range("AK" & LastRow6 + 1).Value = "=IF(RC[-7]=""Inactive"",""Inactive"",IF(RC[45]<=0,""Paid"",IF(RC[-1]>=RC[-36],""Current"",""Late"")))"
ws6.Range("AL" & LastRow6 + 1).Value = "=IF(RC[-8]=""Inactive"",0,IF(RC[-2]<=RC[-37],RC[-4],0))"
ws6.Range("AM" & LastRow6 + 1).Value = "=IF(RC[-1]=""Late"",RC[-38]-RC[-3],0)"
'ws6.Range("AN" & LastRow6 + 1).Value  Nothing mapped to this field, as End Dates aren't populated when Clients are being added.
ws6.Range("AO" & LastRow6 + 1).Value = "=IF(RC[10]<>"""",""Inactive"",IF(RC[1]="""",""Inactive"",""Active""))"
If Not Len(Me.txt_OCStart) = 0 Then ws6.Range("AP" & LastRow6 + 1).Value = CDate(Me.txt_OCStart)
ws6.Range("AQ" & LastRow6 + 1).Value = "=IF(RC[-1]="""",""0"",IF(RC[8]<>"""",RC[8]-RC[-1],RC[-42]-RC[-1]))"
If Not Len(Me.txt_OC1stPymt) = 0 Then ws6.Range("AR" & LastRow6 + 1).Value = CDate(Me.txt_OC1stPymt)
If Not Len(Me.txt_OCPymtAmt) = 0 Then ws6.Range("AS" & LastRow6 + 1).Value = CCur(Me.txt_OCPymtAmt)
If Not Len(Me.cobo_OCFreq) = 0 Then ws6.Range("AT" & LastRow6 + 1).Value = Me.cobo_OCFreq
ws6.Range("AU" & LastRow6 + 1).Value = "=IF(RC[-6]=""Inactive"","""",IF(RC[-3]<>"""",RC[-3],IF(R[-1]C[-1]=""B"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+14),IF(R[-1]C[-1]=""W"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+7),IF(R[-1]C[-1]=""M"",DATE(YEAR(R[-1]C),MONTH(R[-1]C)+1,DAY(R[-1]C)))))))"
ws6.Range("AV" & LastRow6 + 1).Value = "=IF(RC[-7]=""Inactive"",""Inactive"",IF(RC[34]<=0,""Paid"",IF(RC[-1]>=RC[-47],""Current"",""Late"")))"
ws6.Range("AW" & LastRow6 + 1).Value = "=IF(RC[-8]=""Inactive"",0,IF(RC[-2]<=RC[-48],RC[-4],0))"
ws6.Range("AX" & LastRow6 + 1).Value = "=IF(RC[-1]=""Late"",RC[-49]-RC[-3],0)"
'ws6.Range("AY" & LastRow6 + 1).Value  Nothing mapped to this field, as End Dates aren't populated when Clients are being added.
ws6.Range("AZ" & LastRow6 + 1).Value = "=IF(RC[10]<>"""",""Inactive"",IF(RC[1]="""",""Inactive"",""Active""))"
If Not Len(Me.txt_CTIStart) = 0 Then ws6.Range("BA" & LastRow6 + 1).Value = CDate(Me.txt_CTIStart)
ws6.Range("BB" & LastRow6 + 1).Value = "=IF(RC[-1]="""",""0"",IF(RC[8]<>"""",RC[8]-RC[-1],RC[-53]-RC[-1]))"
If Not Len(Me.txt_CTI1stPymt) = 0 Then ws6.Range("BC" & LastRow6 + 1).Value = CDate(Me.txt_CTI1stPymt)
If Not Len(Me.txt_CTIPymtAmt) = 0 Then ws6.Range("BD" & LastRow6 + 1).Value = CCur(Me.txt_CTIPymtAmt)
If Not Len(Me.cobo_CTIFreq) = 0 Then ws6.Range("BE" & LastRow6 + 1).Value = Me.cobo_CTIFreq
ws6.Range("BF" & LastRow6 + 1).Value = "=IF(RC[-6]=""Inactive"","""",IF(RC[-3]<>"""",RC[-3],IF(R[-1]C[-1]=""B"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+14),IF(R[-1]C[-1]=""W"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+7),IF(R[-1]C[-1]=""M"",DATE(YEAR(R[-1]C),MONTH(R[-1]C)+1,DAY(R[-1]C)))))))"
ws6.Range("BG" & LastRow6 + 1).Value = "=IF(RC[-7]=""Inactive"",""Inactive"",IF(RC[23]<=0,""Paid"",IF(RC[-1]>=RC[-58],""Current"",""Late"")))"
ws6.Range("BH" & LastRow6 + 1).Value = "=IF(RC[-8]=""Inactive"",0,IF(RC[-2]<=RC[-59],RC[-4],0))"
ws6.Range("BI" & LastRow6 + 1).Value = "=IF(RC[-1]=""Late"",RC[-60]-RC[-3],0)"
'ws6.Range("BJ" & LastRow6 + 1).Value  Nothing mapped to this field, as End Dates aren't populated when Clients are being added.
ws6.Range("BK" & LastRow6 + 1).Value = "=IF(RC[10]<>"""",""Inactive"",IF(RC[1]="""",""Inactive"",""Active""))"
If Not Len(Me.txt_CTOStart) = 0 Then ws6.Range("BL" & LastRow6 + 1).Value = CDate(Me.txt_CTOStart)
ws6.Range("BM" & LastRow6 + 1).Value = "=IF(RC[-1]="""",""0"",IF(RC[8]<>"""",RC[8]-RC[-1],RC[-64]-RC[-1]))"
If Not Len(Me.txt_CTO1stPymt) = 0 Then ws6.Range("BN" & LastRow6 + 1).Value = CDate(Me.txt_CTO1stPymt)
If Not Len(Me.txt_CTOPymtAmt) = 0 Then ws6.Range("BO" & LastRow6 + 1).Value = CCur(Me.txt_CTOPymtAmt)
If Not Len(Me.cobo_CTOFreq) = 0 Then ws6.Range("BP" & LastRow6 + 1).Value = Me.cobo_CTOFreq
ws6.Range("BQ" & LastRow6 + 1).Value = "=IF(RC[-6]=""Inactive"","""",IF(RC[-3]<>"""",RC[-3],IF(R[-1]C[-1]=""B"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+14),IF(R[-1]C[-1]=""W"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+7),IF(R[-1]C[-1]=""M"",DATE(YEAR(R[-1]C),MONTH(R[-1]C)+1,DAY(R[-1]C)))))))"
ws6.Range("BR" & LastRow6 + 1).Value = "=IF(RC[-7]=""Inactive"",""Inactive"",IF(RC[12]<=0,""Paid"",IF(RC[-1]>=RC[-69],""Current"",""Late"")))"
ws6.Range("BS" & LastRow6 + 1).Value = "=IF(RC[-8]=""Inactive"",0,IF(RC[-2]<=RC[-70],RC[-4],0))"
ws6.Range("BT" & LastRow6 + 1).Value = "=IF(RC[-1]=""Late"",RC[-71]-RC[-3],0)"
'ws6.Range("BU" & LastRow6 + 1).Value  Nothing mapped to this field, as End Dates aren't populated when Clients are being added.
ws6.Range("BV" & LastRow6 + 1).Value = "=RC[-64]+RC[-58]+RC[-47]+RC[-36]+RC[-25]+RC[-14]+RC[-3]"
ws6.Range("BW" & LastRow6 + 1).Value = CCur(Me.txt_AmtApplied)
ws6.Range("BX" & LastRow6 + 1).Value = 0
ws6.Range("BY" & LastRow6 + 1).Value = 0
ws6.Range("BZ" & LastRow6 + 1).Value = 0
ws6.Range("CA" & LastRow6 + 1).Value = 0
ws6.Range("CB" & LastRow6 + 1).Value = "=RC[-5]+RC[-4]+RC[-3]+RC[-2]+RC[-1]"
ws6.Range("CC" & LastRow6 + 1).Value = "=IF(R[-1]C[1]<0,ABS(R[-1]C[1]),0)"
ws6.Range("CD" & LastRow6 + 1).Value = "=IF(ISNUMBER(R[-1]C),RC[-8]-RC[-2]-RC[-1]+R[-1]C,RC[-8]-RC[-2]-RC[-1])"
ws6.Range("CE" & LastRow6 + 1).Value = "=IF(RC[-1]<=0,""Paid"",IF(OR(RC[-57]=""Late"",RC[-46]=""Late"",RC[-35]=""Late"",RC[-24]=""Late"",RC[-13]=""Late""),""Late"",""Current""))"

</rc[-2],rc[-17]-rc[-4],0))"
</rc[-2],rc[-11]-rc[-4],0))"

 

[Johnny C]

You've got the formulae, so I'd just go with what you've got ... Option C, insert the formulae per your VBA for each of the 40 sheets, let it recalculate then copy and paste values over the formulas.

If you never change the formulae this is fine. if you find you change them often enough for it to be a pain to change the VBA consider the option above of keeping a top row of master formulae and copying that down, then you can change the formulae easily and not worry about forgetting to change the VBA

One handy hint, if you have a multithread PC and version of Excel (i.e. less than 10 years old), after you do

    Application.Calculate

add a DoEvents

    Application.Calculate
    DoEvents

This tells Excel to let Windows synchronise i.e. let all processes catch up with each other. Otherwise you might copy a version with half the results of the formula not updated.

Ah the joys of debtor late payment analysis ...

 

[reberryjr]

Thank you for the feedback!

 

[reberryjr]

@Johnny C, so I started thinking about this some more, after coding some of it. What would the drawback(s) be if I just set the formula calculation to manual when updates aren't expected? Then, as updates are submitted, set it to automatic, then back to manual. Thoughts?

 

[Johnny C]

That's a simple answer but a good one I've worked on a forecasting model where it took 10mins to calculate and that was what I did with that.

If the workbook is large in storage terms and you've got 10s or 100s of thousands of calculations and it takes minutes to save the VBA optoin might be worth considering, but if not and you're happy to go with the manual option then that's best.

One thing to remember if you do that. when you open Excel, it takes the default calculation mode from the first sheet you open. So if you set that workbook to have manual calcs and it's the first one you open, any other workbooks you open after will also default to manual. Which can be a pain! So either open a small workbook with Calculation set to automatic first, or close Excel after you worked on it.

One more thing, if you've got a ton of calculations, it's worth doing a calculation tree rebuild occasionally. It takes longer than an F9 calc but might reduce inefficiency in the calculation process. This article explains it well if you don't know how to do this, it's a bit complicated but it explains how Excel does it's calculation and shows how Excel finds Circular references (when the bottom of the calculation tree joins with itself higher up). It's not particularly useful in practice but enlightening, and shows just how complicated Excel is behind the scenes.
https://msdn.microsoft.com/en-us/library/office/bb687891.aspx