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

Excel VBA Hack : Go to the First Column of Your Table (Not Sheet)

You can't use (which you can get from the Macro Recorder) :

Selection.End(xlToLeft).Select  -- because, if you're already at the first column, you exit the Table and, if you're too far to the right (and still in the Table), you don't go to the first column. Why don't they ask you what you're really trying to do :) ?

What does work :

(only within a Sub, mind you :)

Cells(ActiveCell.Row, ActiveCell.ListObject.Range.Column).Select

Excel VBA recipes - better than making a separate post for each one..

How to get the name of the first worksheet in your workbook :

ActiveWorkbook.Worksheets(1).Name

Excel VBA : General Purpose Linear Equation Solver

So simple, I'm surprised this one's not there online someplace already. If you read my older post, you might wonder why this one doesn't need the ".Transpose" thingie. Answer - coz you're feeding it a column vector and Excel is smart enough - for a change :)

Give these a quick browse first for the why - unless you know why already :)
https://stackoverflow.com/questions/23443361/matrix-math-with-vba-system-of-linear-equations
https://www.ozgrid.com/forum/forum/help-forums/excel-general/12863-solve-linear-equations

Point : if you know what you're doing, you have your coefficients in one matrix and the right-hand-side in a column vector and you want to fire a weapon..

You have to select a bunch of cells, enter the formula and hit CTRL-SHIFT-ENTER (see older post)

But, this function could be used within VBA itself - these pictures are just to show what you get.. You don't *have* to use it this way putting the inputs and outputs in cells..

Function solve_lin(coeff As Variant, RHS As Variant) As Variant
    Dim retVal As Variant
   
    retVal = Application.WorksheetFunction.MMult(Application.WorksheetFunction.MInverse(coeff), RHS)
   
    solve_lin = retVal
End Function

Or, since you're most likely to send it a row vector :)

Function solve_lin(coeff As Variant, RHS As Variant) As Variant
    Dim retVal As Variant
   
    retVal = Application.WorksheetFunction.MMult(Application.WorksheetFunction.MInverse(coeff), Application.WorksheetFunction.Transpose(RHS))
   
    solve_lin = retVal

End Function

Excel VBA : Returning a Row Vector and a Column Vector

I want to select four cells in a column and send an array from a function to fill them. How?

See example code below

1. Select four cells
2. Start typing "= retArray()" without the quotes of course.
3. Now, instead of pressing ENTER, press CTRL-SHIFT-ENTER

The code :

Option Explicit
Function retArray() As Variant
    Dim retVal(4) As Integer
   
    retVal(0) = 1
    retVal(1) = 3
    retVal(2) = 4
    retVal(3) = 0
   
    retArray = Application.Transpose(retVal)
End Function

How are you supposed to know to use transpose? Good q.

If you want to write to four cells that are all in the same row, i.e., send a row vector, then you don't need the transpose. Think of it this way - you pick up a book on programming and they get to arrays. Are they going to talk about matrices - in which case the concept of a column vector arises? Nope - they put the entire [a b c.. z] on one line. Moral : the default is a row vector :)

Same thing for the row … and here's the code :

Option Explicit
Function retArray() As Variant
    Dim retVal(4) As Integer
   
    retVal(0) = 1
    retVal(1) = 3
    retVal(2) = 4
    retVal(3) = 0
   
    retArray = retVal
End Function

Kudos :
https://stackoverflow.com/questions/46915012/excel-vba-function-return-array-and-paste-in-worksheet-formula?rq=1
https://www.dummies.com/software/microsoft-office/excel/working-with-vba-functions-that-return-an-array-in-excel-2016/

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..

Microsoft Is (not) Evil

How the hell does a company get to this size having overlooked such a basic feature for 30 years? Damn! Even Cadence has it.

Undo!!

Yes, for manual changes, you can undo. Run a macro and,... you're screwed.

Anyhow, to make up for it, they do let you create a macro to toggle the highlight colour of a cell :

Sub toggle_hilite()
    With Selection.Interior
        If .Color = 16777215 Then ' 25 bits all 1
            .Color = 65535
        Else
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End If
     End With
End Sub

Highlight Selected Cell or Row in Excel - Aus Tom Urtis

It's literally this easy, if you know how.

To highlight the selected cell (automatically that is.. not doing something each time you select something :) :

Get to the Visual Basic editor (might have to enable the Developer tab and then hit View Code -- google this for the way, it's not here)
Then, paste (thanks TU) :

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.ScreenUpdating = False ' Clear the color of all the cells Cells.Interior.ColorIndex = 0 ' Highlight the active cell Target.Interior.ColorIndex = 8 Application.ScreenUpdating = True End Sub

That's it. You just paste this and File > Close and Return to Excel and MAGIC happens! Sufficiently advanced technology!!

And for highlighting the entire row AND column (tweak the code as necessary if you want only..) :

Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub Application.ScreenUpdating = False ' Clear the color of all the cells Cells.Interior.ColorIndex = 0 With Target ' Highlight the entire row and column that contain the active cell .EntireRow.Interior.ColorIndex = 8 .EntireColumn.Interior.ColorIndex = 8 End With Application.ScreenUpdating = True End Sub

And how do you know what number to use for the ColorIndex?

http://dmcritchie.mvps.org/excel/colors.htm