Excel Workbook that Solves an Equation When Any Independent Variable Changed

You get the idea, you have something complicated, like this :

And you want to solve for σ. But, you want to be able to update a, b, G, and lambda on the fly and see the new σ that makes LHS = RHS. How? 😊 Obviously you want one cell to capture LHS - RHS so you can solve for the value that sends this cell to 0.

Start off getting stuff to work using Goal Seek (Data > What if Analysis > Goal Seek.

Now, the cool part is getting that to run on demand through code. What can you do? Use the Macro Recorder obviously. Do that and you have this in your pocket :

Range("F12").GoalSeek Goal:=0, ChangingCell:=Range("C12")

Now, the cool part is getting this to run when the cells in C8-C11 are changed. How? 

In Excel, do ALT-F11 to open the VBA Editor and then, in the Project Explorer, double click the name of the sheet under Microsoft Excel Objects. You now get a new window called filename.xlsm - SheetNamed (Code). In the dropdown on the left, choose "Worksheet" and now, on the right, choose  "Change". This will give you this starting code :

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

All you have to do is :

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range

    Set rng = Intersect(Target, Range("C8:C11"))

    If Not rng Is Nothing Then

' put your magic code here  - what magic? The stuff that copies the numerical value you calculate use a formula for the initial guess into a cell that you tell Goal Seek to vary of course 😊

Put it all together: 

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range

    Set rng = Intersect(Target, Range("C8:C11"))

    If Not rng Is Nothing Then

    '

    ' solve Macro

    ' run goal seek

    '

        Dim sourceCell As Range

        Dim targetCell As Range

        

        ' Set the source and target cells

        Set sourceCell = Range("D12")

        Set targetCell = Range("C12")

        

        ' Copy the numeric value from the source cell to the target cell

        targetCell.Value = Val(sourceCell.Value)

    

        Range("F12").GoalSeek Goal:=0, ChangingCell:=Range("C12")

    End If

End Sub

And you're done :) Enjoy