# Complex Calculations in Excel VBA

The k^{th} term, T_{k}, of a certain mathematical series is defined by the following formula:

T_{k} |
= | k^{2} + 6k + 1 |

9k + 7 |

The first term, T_{1}, of the series is obtained by substituting k = 1 into the formula i.e.

T_{1} |
= | 1^{2} + 6 + 1 |
= | 1 | and |

9 + 7 | 2 |

T_{2} |
= | 2^{2} + 12 + 1 |
= | 17 | ... and so on |

18 + 7 | 25 |

Below we will look at a program in **Excel VBA** that **calculates** any term T_{k} and summation of terms up to N.

Explanation: the user has the option to enter "All" or "Odd", to respectively calculate the sum of the first N terms of the series or the sum of only the odd terms up to N.

Place a command button on your worksheet and add the following code lines:

1. First, we declare four variables of type Integer and one variable of type String.

Dim sumType As String

2. Second, we initialize the variables.

N = Range("C2").Value

sumType = Range("C3").Value

3. Empty the fields.

Range("C6").Value = ""

4. Determine stepSize.

Case Is = "All"

stepSize = 1

Case Is = "Odd"

stepSize = 2

Case Else

MsgBox "Enter a valid expression in cell C3"

End

End Select

Tip: go through our Select Case program to learn more about the Select Case structure.

5. Do the calculations.

Cells(8 + i, 1).Value = term

Cells(8 + i, 2).Value = (term ^ 2 + (6 * term) + 1) / ((9 * term) + 7)

Range("C6").Value = Range("C6").Value + Cells(8 + i, 2).Value

i = i + 1

Next term

Explanation: we use the Step keyword to specify the increment (1 for "All" and 2 for "Odd") for the counter variable of the loop.

Result: