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.