VBA Code

Custom Worksheet Function - Quadratic Equation

Public Function QUADRATIC(sngValueA As Single, _ 
                          sngValueB As Single, _ 
                          sngValueC As Single) As Variant 

Dim vReturnArray() As String 
Dim sngDeterminant As Single 
Dim sngreal As Single 
Dim sngimaginary As Single 
  
   Call Application.Volatile(True) 
   ReDim vReturnArray(2) 
   
   If sngValueA = 0 Then Call MsgBox("The value of A cannot be 0") 
   If sngValueA = 0 Then Exit Function 

   sngDeterminant = (sngValueB * sngValueB) - (4 * sngValueA * sngValueC) 
   
   Select Case sngDeterminant 
   
      Case Is < 0 
         vReturnArray(0) = "Two Complex" 
   
         sngreal = -sngValueB / (2 * sngValueA) 
         sngimaginary = VBA.Sqr(-sngDeterminant) / (2 * sngValueA) 
                  
         If sngreal <> 0 Then vReturnArray(1) = VBA.Round(sngreal, 2) 
         If sngreal <> 0 Then vReturnArray(2) = VBA.Round(sngreal, 2) 
         
         If sngreal <> 0 And sngimaginary <> 0 Then 
            vReturnArray(1) = vReturnArray(1) & "+" 
            vReturnArray(2) = vReturnArray(2) & "-" 
         End If 
         If sngimaginary <> 0 Then 
            vReturnArray(1) = vReturnArray(1) & VBA.Round(sngimaginary, 2) & "i" 
            vReturnArray(2) = vReturnArray(2) & -VBA.Round(sngimaginary, 2) & "i" 
         End If 
            
      Case Is = 0 
         vReturnArray(0) = "One Real" 
         vReturnArray(1) = -sngValueB / (2 * sngValueA) 
         vReturnArray(1) = VBA.Round(vReturnArray(1), 3) 
         
         vReturnArray(2) = "-" 
   
      Case Is > 0 
         vReturnArray(0) = "Two Real" 
         vReturnArray(1) = (-sngValueB + VBA.Sqr(sngDeterminant)) / (2 * sngValueA) 
         vReturnArray(1) = VBA.Round(vReturnArray(1), 3) 
         
         vReturnArray(2) = (-sngValueB - VBA.Sqr(sngDeterminant)) / (2 * sngValueA) 
         vReturnArray(2) = VBA.Round(vReturnArray(2), 3) 
         
   End Select 

   QUADRATIC = vReturnArray 
End Function 

Goal Seek

Lets suppose that cell "A1" contains a formula whose result depends on cell "B1".

Range("A1").GoalSeek Goal:=0.5, _ 
                     ChangingCell:=Range("B1"). 

Goal Seek will return the result (eithre True or False) according to whether the goal value is achieved or not.

Goal seek has the disadvantage that only a single dependent cell is varied.

The main problem with calling the function from VBA is that this is not documented anywhere.
It is often best to record a macro to obtain the desired result.

Solver

For more complicated situations you should use the Solver add-in which can handle several dependent cells and can also take certain conditions into consideration.
You must have the Solver add-in installed first.

and the goal of the optimisations (parameter MaxMinVal)
SolverSolve carries the optimisations.
After the optimisation, the dialog box can be displayed using the SendKeys and then executed.

supports recorded macros
created by solver.com

SolverReset

SolverAdd

SolverAdd CellRef:=$A$1, Relation:=2, FormulaText="some text" 

SolverOptions

SolverOptions sets the options of the Solver.

SolverOK

SolverOK specifies the cells to which the solver is to be applied

SolverFinish

SolverChange

SolverSolve

SolverSolve(True)