Goalseek/Solver with calculation in targetted range

[ajay.bhosale]

I have a few calculation intensive spreadsheets, which take a long time to calculate completely. But all too often, I have to run goalseek/solver on a few lines, to quickly get a result.

However, every time I use goalseek/solver, the entire spreadsheet recalculates repeatedly (including areas which are not relevant to the particular solution).

Do we have a work-around this? Say I could define that goalseek should only re-calc rows 1 and 2, while solving for J2 equal to 0, by changing B2.

Thanks a lot!
Aj

 

[lhkittle]

I have a few calculation intensive spreadsheets, which take a long time to calculate completely. But all too often, I have to run goalseek/solver on a few lines, to quickly get a result.



However, every time I use goalseek/solver, the entire spreadsheet recalculates repeatedly (including areas which are not relevant to the particular solution).



Do we have a work-around this? Say I could define that goalseek should only re-calc rows 1 and 2, while solving for J2 equal to 0, by changing B2.



Thanks a lot!

Aj

Maybe at the start:

Application.ScreenUpdating = False

and at the end:

Application.ScreenUpdating = True

Regars,
Howard

 

[ajay.bhosale]

That will help with the screen updating, but still the whole sheet will be calculated on each iteration. The spreadsheet will be frozen for a while.

No way to limit calculations to specific ranges?

 

[lhkittle]

That will help with the screen updating, but still the whole sheet will be calculated on each iteration. The spreadsheet will be frozen for a while.



No way to limit calculations to specific ranges?

Hi ajay,

From a Google search.


Here's some sample code that shows how and what to shut off while your code runs. Doing this should help improve the performance of your code:


'Get current state of various Excel settings; put this at the beginning of your code

screenUpdateState = Application.ScreenUpdating

statusBarState = Application.DisplayStatusBar

calcState = Application.Calculation

eventsState = Application.EnableEvents

displayPageBreakState = ActiveSheet.DisplayPageBreaks 'note this is a sheet-level setting

'turn off some Excel functionality so your code runs faster

Application.ScreenUpdating = False

Application.DisplayStatusBar = False

Application.Calculation = xlCalculationManual

Application.EnableEvents = False

ActiveSheet.DisplayPageBreaks = False 'note this is a sheet-level setting

'>>your code goes here<<

'after your code runs, restore state; put this at the end of your code

Application.ScreenUpdating = screenUpdateState

Application.DisplayStatusBar = statusBarState

Application.Calculation = calcState

Application.EnableEvents = eventsState

ActiveSheet.DisplayPageBreaks = displayPageBreaksState 'note this is a sheet-level setting

Regards,
Howard

 

[lhkittle]

That will help with the screen updating, but still the whole sheet will be calculated on each iteration. The spreadsheet will be frozen for a while.



No way to limit calculations to specific ranges?

Here is the link if you want to read a bit more on it.

http://blogs.office.com/b/microsoft...el-vba-performance-coding-best-practices.aspx

Howard

 

[GS]

Here's how I do this sort of thing...

Option Explicit

Type AppSettings
CalcMode As Variant
DisplayAlerts As Boolean
EnableEvents As Boolean
ScreenUpdate As Boolean
ShowStatusBar As Boolean
ShowFormulaBar As Boolean
End Type
Public XLSettings As AppSettings

Sub DoGoalSeek()
With Application
'Store current settings
With XLSettings
.CalcMode = .Calculation
.DisplayAlerts = .DisplayAlerts
.EnableEvents = .EnableEvents
.ScreenUpdate = .ScreenUpdating
.ShowFormulaBar = .DisplayFormulaBar
.ShowStatusBar = .DisplayStatusBar
End With 'XLSettings

'Reset stored settings
.Calculation = xlCalculationManual
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
.DisplayFormulaBar = False
.DisplayStatusBar = False

'Run GoalSeek
On Error GoTo errexit
Call RunGoalSeek '//edit to your procedure name

errexit:
.Calculation = XLSettings.CalcMode
.DisplayAlerts = XLSettings.DisplayAlerts
.EnableEvents = XLSettings.EnableEvents
.ScreenUpdating = XLSettings.ScreenUpdate
.DisplayFormulaBar = XLSettings.ShowFormulaBar
.DisplayStatusBar = XLSettings.ShowStatusBar
End With 'Application
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion