Calculation of an inflation on volume/year I would like to find out if my current solution to the problem described below is &quot;good enough&quot; 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 &quot;DRY&quot; principle and come up with something that when seen by another developer in the future will be considered a &quot;good practice&quot;.
I am forced[1] to enter a few formulas into spreadsheet cells via vba. 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:

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(&quot;D6&quot;).Formula = &quot;-$B$12*D4*(D8+1)&quot;
Range(&quot;E6&quot;).Formula = &quot;-$B$12*E4*(D8+1)*(E8+1)&quot;
Range(&quot;F6&quot;).Formula = &quot;-$B$12*F4*(D8+1)*(E8+1)*(F8+1)&quot;
Range(&quot;G6&quot;).Formula = &quot;-$B$12*G4*(D8+1)*(E8+1)*(F8+1)*(G8+1)&quot;
Range(&quot;H6&quot;).Formula = &quot;-$B$12*H4*(D8+1)*(E8+1)*(F8+1)*(G8+1)*(H8+1)&quot;
Range(&quot;I6&quot;).Formula = &quot;-$B$12*I4*(D8+1)*(E8+1)*(F8+1)*(G8+1)*(H8+1)*(I8+1)&quot;
Range(&quot;J6&quot;).Formula = &quot;-$B$12*J4*(D8+1)*(E8+1)*(F8+1)*(G8+1)*(H8+1)*(I8+1)*(J8+1)&quot;
Range(&quot;K6&quot;).Formula = &quot;-$B$12*K4*(D8+1)*(E8+1)*(F8+1)*(G8+1)*(H8+1)*(I8+1)*(J8+1)*(K8+1)&quot;
Range(&quot;L6&quot;).Formula = &quot;-$B$12*L4*(D8+1)*(E8+1)*(F8+1)*(G8+1)*(H8+1)*(I8+1)*(J8+1)*(K8+1)*(L8+1)&quot;

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

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 vba is:
Sub Main()

    Dim i As Long
    Dim s As String
    
    For i = 4 To 12
        s = s + &quot;*(&quot; + Cells(8, i).Address + &quot;+1)&quot;
        Cells(6, i).Formula = &quot;=-$B$12*&quot; + 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

Can this be optimized any further?
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.
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