Page 193 - Excel for Scientists and Engineers: Numerical Methods

170 EXCEL: NUMERICAL METHODS

This procedure contains code, not found in other procedures in this book, that
allows the macro to accept a polynomial equation as a reference to a cell that
contains a formula or as a reference to a cell that contains a formula as text. The
procedure also handles an implicit reference.

Option Explicit
...........................................................
Function Bairstow(equation, reference)
'Obtains the coefficients of a regular polynomial (maximum order = 6).
'Polynomial is a cell formula.
'Polynomial can contain cell references or names.
'Poynomial can be text.
'Reference can be a cell reference or a name.
Dim A() As Double, Root() As Double
Dim J As Integer, N As Integer
Dim pl As Integer, p2 As Integer, p3 As Integer
Dim expnumber As Integer, ParenFlag As Integer
Dim R As Integer, C As Integer
Dim FormulaText As String, Reffext As String, NameText As String
Dim char As String, term As String
ReDim A(6)
' GET equation EITHER AS CELL FORMULA OR AS TEXT.
If Application.lsText(equation) Then
FormulaText = equation
'If in quotes, remove them.
If Asc(Left(FormulaText, 1)) = 34 Then -
FormulaText = Mid(FormulaText, 2, Len(Formu1aText) - 1)
Else
FormulaText = equation.Formula
End If
If Left(FormulaText, 1) = "=" Then FormulaText = Mid(FormulaText, 2, 1024)
FormulaText = Application.ConvertFormula(FormulaText, xlAl , xlAl , -
' xlAbsolute)
FormulaText = Application.Substitute(FormulaText, " 'I, "") 'remove all spaces
'GET THE NAME CORRESPONDING TO reference
NameText = ""
On Error Resume Next 'Handles case where no name has been assigned
NameText = reference.Name.Name
On Error GoTo 0
NameText = Mid(NameText, InStr(1, NameText, "!") + 1)
'HANDLE CASE WHERE reference IS A RANGE
'by finding cell in same row or column as cell containing function.
If reference.Rows.Count > 1 Then
R = equation.Row
Set reference = Intersect(reference, Range(R & ":" & R))
Elself reference.Columns.Count > 1 Then
C = equation.Column
Set reference = Intersect(reference, Range(C & ":" & C))