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

Thank You Charlie Nuttelman : Excel VBA Stuff

So much I didn't know about - man - you don't get to be a trillion $ company with a cash cow unless it has some serious bells and w.

Relative referencing while recording macros - this can make you more productive building a macro to automate a recurring task - check it out!
The Locals window - watch your productivity zoom, already!

The thing that didn't do much for me was the Object Browser - a clear tutorial might help there.. (BTW, you might find his start in Lesson 14 adorable. You'll see how you easily put together something like

Private Sub Workbook_WindowResize(ByVal Wn As Window)
MsgBox ("You resized me!")
End Sub

which is very cute :) )

Other things I must thank the Nutty Professor for :

1. Exit For, Exit Sub, Exit Do -- sometimes ease trumps elegance
2. Option Base 1 -- when you want to be a lay person and count starting from 1 :)
3. ReDim Preserve -- you declare a variable and now, you know how big your array is going to be (or you need to resize it - make it one bigger, and, if you don't say Preserve, you're going to reset all elements to 0... So..
4. Relative Referencing while recording macros - so cool :)
5. The Locals Window! What a lifesaver
6. Declare (Dim) at the Module level, and you can access in all modules
7. Optional arguments to Functions and Static Variables
8. All Dim'd variables start out with a value of 0
9. Debugging : run to cursor : CTRL-F8
10. If you want your code to automatically enter debug mode upon a certain condition being met : Debug.Assert <condition>
11. If you're in the VB Editor, ALT-F11 takes you back to the Workbook - you'd think I'd know that one :)
12. XLAM - Excel Macro Add in - how to Add-in/make a function available
13. How to run a VBA routine from another file
14. The Object Browser - like I said the YT tutorial by ExcelVBAHelp is better
15. You can do .Range of a Range, because a Range is now its own Spreadsheet!
16. Application.ScreenUpdating = False -- to get stuff to finish faster
17. Goal Seek, Solver, Bisection Method, Circular Calculation enabling
18. The power of Dim'ing as Variant - for an array - assign selection without for loops..

Building a Live Calculator (Complicated) in Excel

That is, say you have a complex equation that tells you how Y depends on x - a closed for expression basically.

But, you want to tabulate x for Y - that is, someone gives you a table of Y values and now, you want to fill in the x values - remember, you only have the equation for going the other way!

Thanks to Charlie Nuttelman - the nutty professor, we have a solution..

Here's what you do...

Get up to speed on the bisection method for problem solving. That is, you want to know what x gives f(x) = Target. So, cast the problem as f(x) - Target = 0 and now find the x that zeroes the LHS. If you're guaranteed only one root in a particular range, you can use bisection. I know... read up!
Implement the bisection! That is the first row is
min , f(min)-Tgt , (min+max)/2 , f(mid)-Tgt, max , f(max)-Tgt

2nd row is (now using g for f - Tgt to save my fingers :)
min = IF( g(min_prev_row)*g(mid_prev_row) < 0, min_prev_row, mid_prev_row ), g(min), mid=…
you get the idea :) I hope :)

Once you have the 2nd row done, you just drag that guy down to 20 rows (at which point your guaranteed to be within (I think) 0.1% of the final solution)

NOTE that the Tgt must exist in cell of the sheet so you can refer to it later.. Capture also the result (mid from row 20 of your drag) into another cell..

Then, you set up a table with

Tgt_val , =<cell where you captured the "answer" - see two lines above >
5
10
15
20
…
valN
valN+1
…

Yes, it's not pretty that one column has something that looks like a header and the other has a reference to another cell - what can I say - M$ is not Apple.

Now, select all of this - from the Tgt_val cell to the bottom right cell of these two columns and then go to  Data > What if Analysis > Data Table

For the Column Input Cell, you give it the cell where Tgt exists - which your bisection iteration table uses..

And that's it! Thank you Mr. Jerry Lewis. You'll impress a few folks with this one!