How to Iterate for a Solution with an Excel Macro: The Manning Formula for Open-Channel Flow

The Manning formula for open-channel flow is:  

where
V= Velocity
n = Manning’s “n”,
r = Hydraulic radius = area / wetted perimeter = b*y / (2*y + b) for a rectangular channel (where y = depth and b = width), and
s = slope of channel (m/m). 

Discharge Q = V*A = V*b*y. 

We cannot solve this directly for the depth, y.  So, we have to try successive values of y until the calculated discharge is close enough to the target.

The approach adopted here is to guess a value for y and see what value of Q it produces. We then multiply the guess by (Desired Discharge / Calculated Discharge) for the next guess. We have two buttons on the form shown below.  The first, Manual Iteration, does one iteration per click.  The Automatic Iteration button iterates until the answer is within the required accuracy:

Range names in column A apply to the cells in column B.  Thus, cell B8 is named “Q”.

Formulas in column B are documented in column D.

The macros used for the buttons are:

Sub Iterate1()
' Manual Iteration 22/02/1999 by Rick Raubenheimer.
' Run once for each iteration.
  Dim y#, Q#, Qq#
  y = Names("y").RefersToRange
  Q = Names("Q").RefersToRange
  Qq = Names("Qq").RefersToRange
  y = y * Q / Qq
  Application.Goto Reference:="y"
  ActiveCell.Formula = y
End Sub

Sub Iterate2()
' Automatic Iteration 22/02/1999 by Rick Raubenheimer.
' Run once for all iterations to take place.
  Dim Err#, TargErr#, It%
  It% = 0
  TargErr = Names("Ert").RefersToRange
  Do
    Call Iterate1
    It% = It% + 1
    Names("Iter").RefersToRange = It%
    Err = Names("Err").RefersToRange
  Loop Until Err <= TargErr
End Sub

Instead of y = Names("y").RefersToRange, we could have used y = Range("y").Value with equal effect.

The above spreadsheet and code are in our sample file Manning.xlsm.  You can get the file free of charge from us.

Of course, there are many ways to do the same thing.  We could have used Goal Seek.  We could have done the entire solution in a macro, but using the spreadsheet makes the workings visible.