Input box method example - iteration with bisection

Iteration with bisection


The task

The task is to develop the VBA code for a simple number guessing game.


Here is the code logic:


About the bisection section method:


GuessNumber macro

A solution is provided in code 1. You can copy the code to your VBA module.



Code 1: Guess number macro
Sub GuessNumber()

Dim Number As Integer
Dim Title As String, Prompt As String
Dim Min As Integer, Max As Integer
Dim MaxIterations As Integer, Iteration As Integer
Dim a As Integer, b As Integer, MidPoint As Double
Dim Guess As Integer

'' Initialize variables
Min = 1: Max = 100
MaxIterations = 100
Title = "xlf::Iteration demonstrator #1     "
Prompt = "Enter a whole number between 1 and 100"
  
'' Input from user
Number = Application.InputBox(Prompt:=Prompt, Title:=Title, Type:=1)    '' Type 1 means a number
    If Number < 1 Or Number > 100 Then
        MsgBox "Your number is out of range!"
        Exit Sub
    End If
    
a = Min: b = Max    '' assign to a and b, consistent with equation variables

'' Iteration loop
With Application.WorksheetFunction
    For Iteration = 1 To MaxIterations
        If Number = Guess Then Exit For
            MidPoint = (a + b) / 2
        If Number > MidPoint Then
            a = MidPoint
        Else
            b = MidPoint
        End If
        Guess = .RoundDown(MidPoint, 0)
        '' ActiveCell.Offset(Iteration - 1, 0).Value = .RoundDown(MidPoint, 0)
    Next Iteration
End With

'' Return value to user
MsgBox "The number is: " & Guess & vbNewLine & vbNewLine & _
            "Iterations completed: " & Iteration - 1, , Title

End Sub


About code 1: The Excel RoundDown function at line 35 handles the special case where the number is 1.


The iteration sequence is illustrated in figure 1. The number is set to 34, and 7 iterations are required to achieve convergence. The code to generate the chart series is in line 36.


xlf-iteration-n-equals34
Fig 1: Iteration sequence - bisection method with n = 34.