12
\$\begingroup\$

I would like to find out if my current solution to the problem described below is "good enough" or if there is an alternative way of achieving it. All I care about is the length (no. of lines) of the code and its efficiency. I am trying to follow the "DRY" principle and come up with something that when seen by another developer in the future will be considered a "good practice".

I am forced[1] to enter a few formulas into spreadsheet cells via . The formula is for a calculation of an inflation (say labour rate) on volume/year. It has some constants and some variants but the tricky bit is a variable inflation rate added each year. In my real life example this is far more complicated but I have shortened it and came up with an SSCCE.

SSCCE

You can view the example as a spreadsheet on Google Docs, or have a quick look through below:

enter image description here

The formulas are:

D6 = -$B$12*D4*(D8+1)
E6 = -$B$12*E4*(D8+1)*(E8+1)
F6 = -$B$12*F4*(D8+1)*(E8+1)*(F8+1)
G6 = -$B$12*G4*(D8+1)*(E8+1)*(F8+1)*(G8+1)
H6 = -$B$12*H4*(D8+1)*(E8+1)*(F8+1)*(G8+1)*(H8+1)
I6 = -$B$12*I4*(D8+1)*(E8+1)*(F8+1)*(G8+1)*(H8+1)*(I8+1)
J6 = -$B$12*J4*(D8+1)*(E8+1)*(F8+1)*(G8+1)*(H8+1)*(I8+1)*(J8+1)
K6 = -$B$12*K4*(D8+1)*(E8+1)*(F8+1)*(G8+1)*(H8+1)*(I8+1)*(J8+1)*(K8+1)
L6 = -$B$12*L4*(D8+1)*(E8+1)*(F8+1)*(G8+1)*(H8+1)*(I8+1)*(J8+1)*(K8+1)*(L8+1)

I tried it but didn't like it. There are 9 really long and ugly lines (remember, in my real life example there are many more variables).

Range("D6").Formula = "-$B$12*D4*(D8+1)"
Range("E6").Formula = "-$B$12*E4*(D8+1)*(E8+1)"
Range("F6").Formula = "-$B$12*F4*(D8+1)*(E8+1)*(F8+1)"
Range("G6").Formula = "-$B$12*G4*(D8+1)*(E8+1)*(F8+1)*(G8+1)"
Range("H6").Formula = "-$B$12*H4*(D8+1)*(E8+1)*(F8+1)*(G8+1)*(H8+1)"
Range("I6").Formula = "-$B$12*I4*(D8+1)*(E8+1)*(F8+1)*(G8+1)*(H8+1)*(I8+1)"
Range("J6").Formula = "-$B$12*J4*(D8+1)*(E8+1)*(F8+1)*(G8+1)*(H8+1)*(I8+1)*(J8+1)"
Range("K6").Formula = "-$B$12*K4*(D8+1)*(E8+1)*(F8+1)*(G8+1)*(H8+1)*(I8+1)*(J8+1)*(K8+1)"
Range("L6").Formula = "-$B$12*L4*(D8+1)*(E8+1)*(F8+1)*(G8+1)*(H8+1)*(I8+1)*(J8+1)*(K8+1)*(L8+1)"

Or Cells:

Cells(6, 4).Formula = "-$B$12*D4*(D8+1)"
Cells(6, 5).Formula = "-$B$12*E4*(D8+1)*(E8+1)"
Cells(6, 6).Formula = "-$B$12*F4*(D8+1)*(E8+1)*(F8+1)"
Cells(6, 7).Formula = "-$B$12*G4*(D8+1)*(E8+1)*(F8+1)*(G8+1)"
Cells(6, 8).Formula = "-$B$12*H4*(D8+1)*(E8+1)*(F8+1)*(G8+1)*(H8+1)"
Cells(6, 9).Formula = "-$B$12*I4*(D8+1)*(E8+1)*(F8+1)*(G8+1)*(H8+1)*(I8+1)"
Cells(6, 10).Formula = "-$B$12*J4*(D8+1)*(E8+1)*(F8+1)*(G8+1)*(H8+1)*(I8+1)*(J8+1)"
Cells(6, 11).Formula = "-$B$12*K4*(D8+1)*(E8+1)*(F8+1)*(G8+1)*(H8+1)*(I8+1)*(J8+1)*(K8+1)"
Cells(6, 12).Formula = "-$B$12*L4*(D8+1)*(E8+1)*(F8+1)*(G8+1)*(H8+1)*(I8+1)*(J8+1)*(K8+1)*(L8+1)"

But try to play with the right hand side of the statement. I see so much potential** in terms of optimising it, but the best I could have come up with in is:

Sub Main()

    Dim i As Long
    Dim s As String

    For i = 4 To 12
        s = s + "*(" + Cells(8, i).Address + "+1)"
        Cells(6, i).Formula = "=-$B$12*" + Cells(4, i).Address + s
    Next i

End Sub

I realize that I am concatenating Strings to achieve my goal. The code is working fine in this SSCCE and in my real life problem. It's not slow but I am purely curious if there is any other approach or if my current one can be optimized any further.

Questions

  1. Can this be optimized any further?
  2. Would you do this differently? If so, how? Why would your solution be any better than my current one?

[1] Forced because the real life example pulls its values from multiple places like another closed workbook, database, website, user input etc. For example, INDIRECT() does not work with closed workbooks so I can't rely on pure built-in functions.

\$\endgroup\$
8
+50
\$\begingroup\$

Here is what I would do if I were you:

Sub Main()
    Dim b12_value As Double
    Dim inflation As Double

    'Seed the inflation variable and store the static B12 value
    inflation = 1
    b12_value = Range("$B$12").value

    'Loop through each cell in D6:L6 and calculate its value.
    For Each cell In Range("D6:L6")
        inflation = inflation * (Cells(8, cell.Column).Value + 1)
        cell.Value = -b12_value * Cells(4, cell.Column).Value * inflation
    Next

End Sub

Since you are concerned about number of lines, I chose not to store the volume for each calulation in its own variable (which I would prefer to do as it makes for more readable code).

The solution is only 1 line longer than your original solution and it does not rely on string concatenation (which is slow). It also simply does the formula work itself instead of setting the cell's value instead of setting the formula then having Excel calculate the value from said formula.

NOTE: If you wanted the formula visible in Excel (instead of just its value), I would think your original solution (with a couple tweaks) is sufficient for you needs:

Sub Main()
    'Use a more descriptive variable name than "s". Descriptive names improve
    'readability and better facilitates user understanding.
    Dim inflation_string as String

    'Using the for each syntax creates more readable code. Plus, as a bonus,
    'it removed the use of the index variable i.    
    For Each cell In Range("D6:L6")
        inflation_string = inflation_string + "*(" + Cells(8, cell.Column).Address + "+1)"
        cell.Formula = "=-$B$12*" + Cells(4, cell.Column).Address + inflation_string
    Next

End Sub
\$\endgroup\$
  • 1
    \$\begingroup\$ ya man that's what I am talking about ( the s is used just in this example but it's worth mentioning for any future readers definitely), I like the for each syntax and actually haven't realized that Range("") also has a hidden _NewEnum so for each is more efficient in this case. Just trying to adopt your tips to my real life example so far so good so thanks a lot for your input. I can't award the bounty for another 17 hours but I will do tomorrow. \$\endgroup\$ – user28366 May 12 '14 at 15:10
  • \$\begingroup\$ Glad I could help! :D \$\endgroup\$ – BeetDemGuise May 12 '14 at 16:38
2
\$\begingroup\$

Since you need to multiply by another interest rate every year, why not precalculate this interest rate using a temp variable? You precalculate the interest in row 10 and then refer to the relevant field in the calculation column.

It might also be worth to check out the default Excel formulas involving financial calculations: http://office.microsoft.com/en-001/excel-help/financial-functions-reference-HP010342519.aspx

\$\endgroup\$
  • 1
    \$\begingroup\$ I am sorry but this is a very generalized answer. I am doing this for a financial/commercial team and because of its complexity I can't use any/many built-in functions -> btw. name one function that I could use here....I am not sure what you mean by using a temp variable, precalculate interest in row 10 and refer to the relevant field? Can you demonstrate that somehow because those 2 sentences do not make a lot of sense to me. Why would this approach be any better than current one and how would it reduce/optimize code? Thanks \$\endgroup\$ – user28366 May 12 '14 at 11:30
  • \$\begingroup\$ about the temp variable: in D10, write =(D8+1). in E10, write =(D8+1)*(E8+1). repeat this for all other cells. then you can just write in D6 = $B%12*D4*D10 and copy this code to the entire 6th row. Those Excel formulas are a set of formulas to calculate, among other things, compound interest, monthly payments (both capital and interest), interest rates and eventual payments. There might be a formula that's suitable for calculating those separate numbers based on the acumulated inflation. \$\endgroup\$ – Nzall May 12 '14 at 13:18
  • 1
    \$\begingroup\$ thanks I get it now but I am sorry can't see how this is any more optimized in terms of VBA? It would require more code than I currently have and another temp variable..maybe I am still missing something here so do you want to demo a sample? \$\endgroup\$ – user28366 May 12 '14 at 13:32
  • \$\begingroup\$ My remark was meant to imply that you don't exactly require VBA to generate this, at least in this situation. I don't know how your complete situation is with the entire spreadsheet and how much more complicated it is, so it's entirely possible that the complete setup would not be feasible without VBA. \$\endgroup\$ – Nzall May 12 '14 at 13:43
  • \$\begingroup\$ I thought I was clear in the first sentence of the second paragraph in my original post. Thanks for your effort. I can't use pure formulas because the components/elements which build it come from all around the house, ( some come from another excel file, some from a DB, some from an email etc, like indirect() doesn't work with closed excel files etc it just comes down to efficiency now). Hope it's clear now \$\endgroup\$ – user28366 May 12 '14 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy