Automatic Goal Seek and Calculate Active Sheet Code

[grifbomber]

Hey guys, I am pretty handy with Excel except when it comes to the VBA part of it. I did some programming several years ago and have a basic understanding but that was mostly in Java not VBA.

Basically I am working on a spreadsheet for work that takes a user input APR from an installment loan contract, does the APR calculation, and compares it to the user input to see if it is correct. I found code online to perform an Automatic Goal Seek on my APR calculations to determine what the correct APR should be and it works great! But there's one problem, I need to be able to make it where the user can have a different tab for each contract examined, this could be around 100 tabs in some extreme cases. When I create more than 3 tabs of my APR calculation it starts to take about 45 seconds to perform the calculations. I have a feeling this is because maybe my code is inefficient. I also have a calculate button on each tab running a calculate sheet macro when it is pressed. My understanding is that it should only by calculating the active sheet but I think it may be recalculating every sheet and this is causing the slow down. I need to have this spreadsheet do the calculations as close to instantly as possible.

Here is the code for the Automatic Goal Seeks and the calculate macro, any suggestions or answers are greatly appreciated!

Automatic Goal Seek code:

Private Sub Worksheet_Calculate()
Static isWorking As Boolean

With Sheet1 // The first if statement calculates APR for one scenario and the second calculates APR for a different scenario
If Round(.Range("P46").Value, 6) <> 0 And Not isWorking Then
isWorking = True
.Range("P44").Value = 0
.Range("P46").GoalSeek Goal:=Range("B13").Value, ChangingCell:=.Range("P44")
isWorking = False
End If

If Round(.Range("P51").Value, 6) <> 0 And Not isWorking Then
isWorking = True
.Range("P49").Value = 0
.Range("P51").GoalSeek Goal:=Range("B13").Value, ChangingCell:=.Range("P49")
isWorking = False
End If
End With
End Sub

Calculate Sheet Macro:

Sub Calculate_Sheet()
'
' Calculate_Sheet Macro
ActiveSheet.Calculate
End Sub

[MrShorty]

This forum can be strict about rules like putting code tags around code. You want to comply so your topic does not get locked.

It is probably going to be difficult to really help here without seeing a working, dummy copy of your spreadsheet that exhibits the problem. Upload a sample spreadsheet to the forum (Go advanced --> manage attachments -> upload file)

Do you understand and can you explain the math behind your problem? How is APR defined? Sometimes the easiest way to solve this kind of problem is to resort (heaven forbid) to algebra and solve for APR algebraically, then put that formula into Excel. Or, if this is more of a present value/future value/ internal rate of return type problem (you would have to understand the business math well enough to identify that kind of problem), then you can use the appropriate formula in Excel (list of functions: https://support.office.com/en-us/art...1-da16e8168cbd look through the list of financial functions. The PV functions seem to have the most information on these kind of problems).

If you were interested, I suspect the strategy I describe here (http://www.excelforum.com/tips-and-t...ind-roots.html ) to write your own root finding algorithm in the spreadsheet would work at least as well if not better for your root finding problem.