Results 1 to 4 of 4

User Defined Function

Threaded View

  1. #1
    Registered User
    Join Date
    01-16-2009
    Location
    Houston
    MS-Off Ver
    Excel 2007
    Posts
    2

    User Defined Function

    Hello all! I downloaded code to run the "bisection method" on a continuous function. However, it is hard coded for a specific equation. Is there any way that i can use a cell for a user to input his/her function f(x) = xxxxxx, then run the sub to iterate for the root?

    Here is an example of how the function is hard coded (see bold at the bottom). This is a little user unfriendly, because the person using this would have to open VBE and manually change the function each time. I would like for the user to enter the function in a specific cell, and then run the sub. How can i pass the function input by a user into a cell to the sub? Thanks in advance!
    Option Explicit
    Dim a As Single, b As Single, c As Single
    Dim k As Integer, Nmax As Integer
    Dim epsilon As Single
    
    Private Sub cmdAutomatic_Click()
        epsilon = Range("L16")
        Nmax = Range("L17")
        a = Range("b4")
        b = Range("c4")
        Range("d4").Value = f00(a)
        Range("e4").Value = f00(b)
        If f00(a) * f00(b) > 0 Then
            MsgBox ("For a = " & Str(a) & " and b = " & Str(b) & ", f(a)*f(b)>0." _
                    & " Try new values of a and b.")
            Exit Sub
        End If
        
        c = 0.5 * (a + b)
        k = 0
        
        Do While Abs(f00(c)) > epsilon And k < Nmax
           Call cmdStep_Click
        Loop
    End Sub
    
    Private Sub cmdClear_Click()
        Dim i As Integer, j As Integer
        For i = 1 To 100
            For j = 2 To 8
                Cells(3 + i, j).Value = ""
                Cells(3 + i, j).Font.Bold = False
            Next j
        Next i
        Range("b4").Value = 0
        Range("c4").Value = 1
    End Sub
    
    Private Sub cmdStart_Click()
        a = Range("b4")
        b = Range("c4")
        Range("d4").Value = f00(a)
        Range("e4").Value = f00(b)
        If f00(a) * f00(b) > 0 Then
            MsgBox ("For a = " & Str(a) & " and b = " & Str(b) & ", f(a)*f(b)>0." _
                    & " Try new values of a and b.")
        Else
            MsgBox ("Use button [Next Step] to iterate solution")
        End If
        k = 0
    End Sub
    
    Private Sub cmdStep_Click()
        k = k + 1
        c = 0.5 * (a + b)
        Cells(3 + k, 6).Value = c
        Cells(3 + k, 7).Value = f00(c)
        Cells(3 + k, 8).Value = f00(b) * f00(c)
        If f00(b) * f00(c) > 0 Then
            b = c
            Cells(4 + k, 3).Font.Bold = True
        Else
            a = c
            Cells(4 + k, 2).Font.Bold = True
        End If
        Cells(4 + k, 2) = a
        Cells(4 + k, 4) = f00(a)
        Cells(4 + k, 3) = b
        Cells(4 + k, 5) = f00(b)
        
    End Sub
    
    Private Function f00(x As Single) As Single
        f00 = x ^ 3 - 5 * x ^ 2 + 2 * x - 24
    End Function
    Last edited by royUK; 01-29-2009 at 01:21 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1