VBA: Numerical Methods

The best way that I can learn VB is by practice and just get on with it. I never realized that starting something and ending something is harder than the in between process. Good thing it is object-oriented and somehow close to Java. They are almost alike. But not all. Sobrang nangapa ako sa codes and shortcuts. It's been a very long time since I did some programming.

VBA Shorcuts

• Alt-F11 - Activate VBE
• F5 - Execute Module

The formulas are assumed to be correct. If I put on another char like $y$ in the formula, there will be an error. I can't seem to fix this. Here are the codes:

Colorit.bas


Something Fun. Coloring the highlighted Red. Just learning on the buttons.

Sub Red()
'
' Red Macro
'
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 192
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Sub NoFill()
'
' NoFill Macro
'
'
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Bisection.bas

This contain some of the functions found in the other methods. i didn't include it anymore in the other methods.


Sub Bisectiontest()
    
    'ASSUME THAT THE FORMULA IS CORRECT
    '0nly after 1000 iterations of f(x) = 0
    
    form = Range("D4")
    a = Range("E6").Value
    b = Range("E7").Value
     
    'checks the inputs
    If Inputx(form, 0, a, b) = False Then
        MsgBox "Please check your inputs"
        Exit Sub
    'wrong property of bisection
    ElseIf Px(form, a) * Px(form, b) > 0 Then
        MsgBox "Please change your interval"
        Exit Sub
    Else
        'applying the method
        Call Bisection(form, a, b)
    End If
    
End Sub

Public Function Inputx(form, form2, a, b) As Boolean
    'Missing input
    If form = "" Or form2 = "" Or a = "" Or b = "" Then
        Inputx = False
    'wrong input on interval
    ElseIf a Like "[A-Z,a-z]" Or b Like "[A-Z,a-z]" Then
        Inputx = False
    Else
        Inputx = True
    End If
    
End Function

Public Function Px(f, x)
    'This is to evaluate the x variable only
    
    'replacing x with the value
    f1 = Application.WorksheetFunction.Substitute(f, "x", x)
    
    'evaluate the string
    Px = Evaluate(f1)
    
End Function

Private Sub Bisection(form, a, b)
    
    Static counter As Integer
    xn = (a + b) / 2
    fa = Px(form, a)
    fxn = Px(form, xn)
    fb = Px(form, b)
    
    'stop with 1000 iteration
    counter = counter + 1
    If counter = 1000 Then
       Range("F9").Value = xn
        Exit Sub
    Else
    
    'checker for next iteration
    'assigning new a or b
    
        If fa = 0 Then
            Range("F9").Value = a
        ElseIf fxn = 0 Then
            Range("F9").Value = xn
        ElseIf fb = 0 Then
            Range("F9").Value = b
        ElseIf fa * fxn < 0 Then
            b1 = xn
            Call Bisection(form, a, b1)
        Else
            a1 = xn
            Call Bisection(form, a1, b)
        End If
    End If
End Sub

Newton.bas


Sub Newtontest()
    'ASSUME THAT THE FORMULAS inputed are CORRECT
    '0nly after 1000 iterations of f(x) = 0
    
    form = Range("E5")
    form2 = Range("E6")
    a = Range("F8").Value
     
    'checks the inputs
    If Inputx(form, form2, a, 0) = False Then
        MsgBox "Please check your inputs"
        Exit Sub
    'property of newton
    ElseIf Px(form2, a) = 0 Then
        MsgBox "Please change your x. The first derivative is 0."
        Exit Sub
    Else
        'applying the method
        Call Newton(form, form2, a)
    End If
End Sub

Private Sub Newton(form, form2, a)
    
    Static counter As Integer
    
    f = Px(form, a)
    fprime = Px(form2, a)
    
    'stop with 1000 iteration
    counter = counter + 1
    If Not counter = 1000 Then
        'checker for next iteration
        'assigning new xn
        If f = 0 Then
            Range("G10").Value = a
        Else
            xn = a - (f / fprime)
            Call Newton(form, form2, xn)
        End If
    Else
        Range("G10").Value = a
        Exit Sub
    End If
End Sub

Secant.bas


Sub Secanttest()
    'ASSUME THAT THE FORMULAS inputed are CORRECT
    '0nly after 1000 iterations of f(x) = 0
    
    form = Range("E5")
    x1 = Range("F7").Value
    x2 = Range("F8").Value
    
    'checks the inputs
    If Inputx(form, 0, x1, x2) = False Then
        MsgBox "Please check your inputs"
        Exit Sub
    'property of secant
    ElseIf Px(form, x1) = Px(form, x2) Then
        MsgBox "f(x1) = f(x2). Please change your points"
        Exit Sub
    Else
        'applying the method
        Call Secant(form, x1, x2)
    End If
End Sub

Private Sub Secant(form, x1, x2)
    
    Static counter As Integer
    
    f1 = Px(form, x1)
    f2 = Px(form, x2)
    
    'stop with 1000 iteration
    counter = counter + 1
    If Not counter = 1000 Then
        'checker for next iteration
        'assigning new xn
        If f1 = 0 Then
            Range("G10").Value = x1
        ElseIf f2 = 0 Then
            Range("G10").Value = x2
        Else
            xn = x2 - f2 * ((x2 - x1) / (f2 - f1))
            Call Secant(form, x2, xn)
        End If
    Else
        Range("G10").Value = x2
        Exit Sub
    End If
End Sub

Fixedpoint.bas


Sub Fixedpointtest()
    'ASSUME THAT THE FORMULAS inputed are CORRECT
    '0nly after 1000 iterations of f(x) = 0
    
    form = Range("E5")
    form2 = Range("E6")
    a = Range("F8").Value
     
    'checks the inputs
    If Inputx(form, form2, a, 0) = False Then
        MsgBox "Please check your inputs"
        Exit Sub
    Else
        'applying the method
        Call Fixedpoint(form, form2, a)
    End If
End Sub

Private Sub Fixedpoint(form, form2, a)
    
    Static counter As Integer
    
    f = Px(form, a)
    g = Px(form2, a)
    
    'stop with 1000 iteration
    counter = counter + 1
    If Not counter = 1000 Then
        'checker for next iteration
        'assigning new xn
        If f = g Then
            Range("G10").Value = a
        Else
            xn = g
            Call Fixedpoint(form, form2, xn)
        End If
    Else
        Range("G10").Value = a
        Exit Sub
    End If
End Sub

I realized how i missed programming. Its so much different that recording it into a macro. Although that helps but you can't define a function or apply my own kinda style in programming. Maybe a mixture of the two can help me this time.

Secant Method Exercise

This exercise is also on numericalmethods.xlsx

Given:  $ f(x) = x^2-10x+9$, find the roots using the secant method.

Computation:

1. We choose two points for $x_{-1}$ and $x_{0}$.
2. Solve for the next points using the formula: \[  x_n = x_{n-1} - f(x_{n-1}) \frac{x_{n-1}-x_{n-2}}{f(x_{n-1})-f(x_{n-1})}   \]

This formula is derived from getting the slope of the secant lines through the points $(x_n,f(x_n))$, $(x_{n-1},f(x_{n-1}))$ and  $(x_{n-2},f(x_{n-2}))$.

\[  \frac{f(x_n) - f(x_{n-1})}{x_n - x_{n-1}} = \frac{f(x_{n-1}) - f(x_{n-2})}{x_{n-1} - x_{n-2}} \]

Since $f(x_n)=0$, then
\[ \frac{0 - f(x_{n-1})}{x_n - x_{n-1}} = \frac{f(x_{n-1}) - f(x_{n-2})}{x_{n-1} - x_{n-2}} \]

Solve for $x_n.$

\[ \begin{split}
- f(x_{n-1})(x_{n-1} - x_{n-2})      &= (x_n - x_{n-1})( f(x_{n-1}) - f(x_{n-2})) \\
\frac{- f(x_{n-1})(x_{n-1} - x_{n-2})}{ f(x_{n-1}) - f(x_{n-2})}    &=  x_n - x_{n-1} \\
 x_{n-1} - \frac{ f(x_{n-1})(x_{n-1} - x_{n-2})}{ f(x_{n-1}) - f(x_{n-2})}    &=  x_n \\
 x_n &=  x_{n-1} - \frac{ f(x_{n-1})(x_{n-1} - x_{n-2})}{ f(x_{n-1}) - f(x_{n-2})} \end{split}\]

3. Continue to iterate until $f(x) = 0$ or we've applied the stopping rules.

This is my answer.

I also applied secant method on $ f(x) = cos(x) - x$ and arrived at this result.

So far in excel it works. How about applying these methods in Visual Basic? Will it work? What can be my possible problems?

Newton, Fixed Point and Secant Method

Newton Method

This method is based on the Taylor polynomial series. It uses the tangent line to find the next point ($x$) until its $f(x)$ approaches zero.

Given the \[ P(x_0) = \frac{f(x_0)}{0!} + \frac{f'(x_0)(x-x_0)}{1!} + \cdots\]
Equate it 0 and solve for $x$.
\[ \begin{split} \frac{f(x_0)}{0!} + \frac{f'(x_0)(x-x_0)}{1!} + \cdots &= 0\\
f(x_0) &= -f'(x_0)(x-x_0) \\
x-x_0 &= \frac{f(x_0)}{-f'(x_0)}\\
x&=x_0 - \frac{f(x_0)}{f'(x_0)} \end{split} \]

Example: As solved in the numericalmethods.xlsx

Given $x^2-10x+9$ Find a root of the equation using the starting points: 15,-1000 and 5.

We first solve for the derivative of  $x^2-10x+9$ which is $2x-10$. And then we look for the next $x$'s until its $f(x)$ is zero or we followed our stopping rules.

Problem arises if the first derivative of the function is 0 and we must have a good intuition at the starting point.

Secant Method: Assignment

Compute for the zeroes of $x^2-10x+9$ using the Secant Method. Show how you derive at the iterative process.

This method uses finite differences instead of differential calculus. We estimate for the first derivative as "rise over run" and there is a need for three points this time to secure succeeding estimate.

Fixed Point Method

We plug in $x=g(x)$.

Example:

Given $f(x)=x^2-10x+9$, we first find the $g(x)$ by equating $f(x)=0$. And then isolate $x$.

\[ \begin{split} x^2-10x+9 &=0\\
10x &= x^2+9\\
x&= \frac{x^2}{10}+\frac{9}{10} \end{split} \]

We don't know how many iteration it will end but end it when $x=g(x)$ or when they are approximately the same.

Basic Numerical Methods: Self Review

I actually found a good book for the Numerical Methods topic. It explains the concepts easily and this is what I summarized. The pictures were taken from the same book Introductory Numerical Analysis by Aquino-Ruivivar and Cureg.

Bisection Method. Basically what the Bisection method does, is to get its midpoint of the $x$'s such that the $f(x)$'s is approaching 0. It continues to iterate until the $f(x)=0$ or you tell it to stop using the valid stopping rules.

Here's what to remember.

1. Given the interval $[a,b]$, $f(a)f(b)<0$. That means there is a sign change in the $f(x)$'s.

2. The next point which is denoted by \[ x_1=\frac{a+b}{2} \]

3. We replace the value of $a$ or $b$ depending on the sign of $f(x_1)$.

4. Continue doing so, until $f(x)$ is close to zero, or then again used the stopping rules.

As excel reference, we can use this:

Regula Falsi. This method on the other hand, chooses the next point to be the one that passes through the x -axis. As shown on the picture below, it continues to iterate until the f(x) is closer to zero. Again it will stop because of the stopping rules.

Here's what to remember.

1. Given the interval $[a,b]$, $f(a)f(b)<0$. That means there is a sign change in the $f(x)$'s. $b

2. The next points which is denoted by \[ x_n=b_{n-1}-\frac{f(b_{n-1})(b_{n-1}-a_{n-1})}{f(b_{n-1}-f(a_{n-1}} \]

3. Continue doing so, until $f(x)$ is close to zero, or then again used the stopping rules.

As excel reference, we can use this:

Fixed Point Method. I got a little confused about this method. All I know is that we choose a $g(x)$ from the original $f(x)$ and then iterate until $g(x)=x$. For reference, the convergence and divergence of fixed point method is as follows:

Newton's Method. In the Newton's Method, it uses the slope of a point or the tangent line to get the next point to iterate. The $f(x)$ converges faster to zero than the other methods. However, there are some restrictions. 

Restrictions:

1. If the point we choice is not a tangent line, it will create an infinite loop.

2. If $f(x)=0$, then the next iterate will fly into infinity.

Here's a good representation of the Newton's method.

As excel reference, we can use the table below. We can get the next point by using this formula: \[x_n = x_{n-1}- \frac{f(x_{n-1})}{f'(x_{n-1})}\]

Secant Method. This method is like the Regula Falsi method. Only the sign change in the $f(x)$ doesn't matter. It is also like the Newton's Method, only that this method uses two points. For representation, look at the figure below. The $x_{-1}$ and $x_0$ will be your given points. It computes for the next point which is $x_1$ . Then we obtain the next point $x_2$ by connecting the points $x_0$ and $x_1$ to make a line. It goes on an on until $f(x)$ converges to 0.

Given $x_{-1}$ and $x_0$, to compute for the next points, the formula is as follows:

\[  x_n = x_{n-1} - f(x_{n-1}) \frac{x_{n-1}-x_{n-2}}{f(x_{n-1})-f(x_{n-1})}   \]

As excel reference, we can use the table below:

Bisection Method

Root Finding

This the reliance of finding the roots/zeroes.

Optimization

Given a polynomial $f(x)$, these are the following steps to determine the minimum or maximum points.

1. Solve for the first derivative of  $f(x)$.
2. Equate $f(x)=0$ and solve for the critical points.
3. Solve for the second derivative of $f(x)$.
4. If $f''(x)=0$, then there is an inflection.
    If $f''(x)>0$, then the critical point is a minimum. (concave up)
    If $f''(x)<0$, then the critical point is a maximum. (concave down)

Finding for the zeroes

Given $f(x)=x^2-10x+9$, we can solve for the zeroes by factoring. Hence,
 \[ \begin{split} x^2-10x+9 &= 0 \\
 (x-1)(x-9) &= 0 \\
x & =1,9 \end{split} \]
There are other ways to find the zero using the quadratic formula, completing the square, graphing, etc. But in real life, nobody knows what the values of $x$. And if we are given polynomials that are non linear or difficult to find the zeroes, we can use the numerical methods such as the bisection methods. Bisection Method

Assumptions:
1. Function is continuous.
2. There is a certain point of time that it passes through the horizontal line.

Method:
1. Choose two points $x_1$ and $x_2$. Solve for $f(x_1)$ and f(x_2)$.
2. If one of $f(x) is 0, we stop. If not, solve for its midpoint $md = \frac{x_1+x_2}{2}$ and its $f(md)$.
3. If $f(md)=0$, we stop. If not, replace one of the two points $x_1$ and $x_2$ with $md$ of the same sign.
4. Continue steps 2 and 3 and stop only when f(x)=0.

Example:  Can be seen on numericalmethods.xlsx

Given $f(x)=x^2-10x+9$, use the bisection method to get one of the points.

Core issues:

If you have bad starting point, when will you stop?
1. if answer keeps on repeating (logic of the rule), or no change in iterate
2. trivial answer: $f(x)=0$

Valid stopping rules:
3. $|x_n-x_n-1|<absolute error$
4. limit number or iterations
5. percentage change of midpoints(or the relative absolute error) is small enough.

Taylor Polynomial

I always forget this basic series. So recall:
\[  P(x)=\frac{f(x_0)(x-x_0)^0}{0!} + \frac{f'(x_0)(x-x_0)^1}{1!} + \frac{f''(x_0)(x-x_0)^2}{2!}+ \cdots \]
Sample Application: Please see numericalmethods.xls
\[ \begin{split} ln(1.10) &= ln(1+0.10) \\ & \Rightarrow ln(1+x), \,\,\  x=0.10 \end{split}\]
\[  \begin{split}
 ln(1+x) &= \int {\frac {1}{1+x}dx} \\
&= \int{1-x+x^2-x^3+x^4-\cdots} \\
&= x - \frac{x^2}{2}+\frac{x^3}{3}-\frac{x^4}{4}+\frac{x^5}{5}-\cdots
\end{split} \]
If $x=0.10$, then
\[  \begin{split}
g(x)&=ln(1+x)\\
&= x - \frac{x^2}{2}+\frac{x^3}{3}-\frac{x^4}{4}+\frac{x^5}{5}-\cdots \\
&= 0.10 - \frac{0.10^2}{2}+\frac{0.10^3}{3}-\frac{0.10^4}{4}+\frac{0.10^5}{5}-\cdots
&= 0.09531
\end{split} \]
And as you involve higher polynomials, you will arrive at higher or exact answers.

Errors in Computation II

As I recall from one of my textbooks, Introductory Numerical Analysis by Aquino-Ruvivar and Cureg, this is how a prof of mine defined the types of error.

Errors that arises during computation:

Input error - error inherent in the source data. usually occur in experimental data or in the encoding stage or formulation of the mathematical computation.

Round-off error - same definition as above. It occurs because of the limited capacity of the computer.

Truncation error - errors that are due to the specific algorithm or formula that is used in a particular numerical method. Example of it is evaluating a specific number using Taylor Series. It is usually truncated at the first few terms of the series.

The prof also discussed that sizes of the error are actually generated when particular methods are used somewhat differ from the expected amount of errors. It is usually expressed in two forms:

Absolute Error: $|x-\tilde{x}|$

Relative Error:  $\frac{|x-\tilde{x}|}{|x|}$

Relative error, though is the better measure than the absolute error.

Errors in Computations

This is the preliminary in Basic Numerical Methods.

Computer and Errors

There are many types of error. In class, we discussed four.

Round-off error - errors made when rounding off numbers or truncating numbers.

 Example:

Given: 2.54674  
Computer rounding it off to the nearest hundreds: 2.55

Propagation - error usually occur in software programming, a normal error
                     - error within an error, what we call the butterfly effect

Example:    Given:  The machine can take in two numbers to the right of the decimal places.
                              $f(x)=x^2+7x, x=0.12$

Manual Solution:
$ f(0.12)=(0.12)^2+7(0.12)=0.0144+0.84=0.8544$
Machine's Solution:
$f(0.12)=(0.12)^2+7(0.12)=0.014+0.84=0.85$

Underflow and Overflow - too much error in the exponents, if the number is too small or to big.