Iteration with bisection
The task
The task is to develop the VBA code for a simple number guessing game.
Here is the code logic:
- The user is presented with an InputBox, and is required to enter an integer in the range 1 to 100
- The VBA code will use an iteration (trial and error) technique to guess the entered value
- The main part of the iteration code is to be contained in a For-Next construct
About the bisection section method:
- The bisection divides the range \([a,b]\) into two equal parts at the midpoint \(\left(a+b\right)/2\)
- The function is tested at the mid point, and this determines whether the guess is too high or too low. The sub-intervals are \([a,\left(a+b\right)/2]\) or \([\left(a+b\right)/2,b]\)
- This process is then repeated until a solution is found
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.

- Thanks to Cosmo for useful comments
- Module developed in: Excel 2013 64 bit
- Revised: Friday 24th of February 2023 - 02:37 PM, Pacific Time (PT)
