Iteration with bisection
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
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: Wednesday 6th of December 2017 - 11:28 PM, Pacific Time (PT)