Recorded Macro used to work great, but now?? 1

[SteveGregory]

This spreadsheet started out as a Quattro Pro (DOS) sheet and I brought it in to Excel some time ago. Recently, I discovered a quirk while editing a formula. In Excel 2007, I went to Options> Advanced > Lotus Compatibility Options and unchecked a couple of options called Transition Formula Evaluation and Transition Formula Entry. Then I used "Apply Names" to make all of the formulas work properly.

Originally, I used Excel 2000 to record the macro. Since I made the changes noted above, the constraints stored in the range named "OPT" don't seem to work with named cells like "Variable" but do work with "$B$80". So, I changed those refences.

But now after the macro runs, the current cell selected is now the cell holding the solver solution. Before, the macro used to run without moving the current cell selection. This is driving me crazy. Here is my simple code. I assigned the macro to a control button as well as the shortcut below.

Sub Maximum_Moment()
'
' Maximum_Moment Macro
' Macro recorded 10/1/2007 by Steve Gregory For Maximum moment or using the solver
'
' Keyboard Shortcut: Ctrl+m
'
SolverLoad loadArea:=Range("OPT")
SolverOptions Iterations:=200
SolverSolve UserFinish:=True
End Sub

 

[IRstuff]

"This spreadsheet" ?? Did you intend to include it?

TTFN

FAQ731-376
Chinese prisoner wins Nobel Peace Prize

 

[SteveGregory]

The "Find Maximum Moment" button runs it.

Another macro does a similar thing with the currently selected cell. It runs from the "Find Max Moment, X for Wheel Loads".

 

[daviv]

I believe you get this error message "Compile Error: Can't Find Project or Library when you try to find the maximum moment. If so, the solution is enable the Solver add-in in the Excel Options dialog box.

1) Tools>>Options>> Select Solver Add-in. Afterwards, you will see the Solver Coomand under the Tools Menu. And you will need to select Solver. Otherwise, it will still not work. Close Solver.

2) Open Visual Basic Editor. Tools >> Reference>> Select Solver. There might be another solver.xlm. Unselect that.

I don't think there is anything to do with named ranges. Anyways, I attached the file that I changed.

Also, can you explain what P1C, W1C, EMOM, TRAP, LOP, RCP represents? Can I use the spreadsheet?

 

[SteveGregory]

The only error that I received was about the variable "constraints". Solver was loaded and running or else the "constraints error" would not pop up. I changed the cell references from named ranges to the default cell names such as "$B$20". My errors diappeared.

My current dilema is that after the macro runs, the current cell selected is now the cell holding the solver solution. Before, the macro used to run without moving the current cell selection. This is driving me crazy.

 

[daviv]

Add another line in the end of Maximum_Moment()Procedure to select the cell you want. I am assuming you do not want to be taken all the way down to row 80 after you run the macro. Range ("a1").select will keep you at the top.

 

[SteveGregory]

Thanks,Daviv That's a start in the right direction! I was googling VBA to find a solution. What if I stored my current position in a cell (such as M20) at the beginning of my macro and then set the current position back to the original at the end of the macro? Here is my clueless attempt:

Range("m20").value = ActiveCell.Address
...
..
ActiveCell.FormulaR1C1 = m20

p.s. Using this spreadsheet might be hazardess to your career. There are some nasty bugs that are difficult to find and correct. That was part of the reason that I was working on it in my spare time. Most of the time, the analysis is correct, but once in a while the answers are not right and it is not very obvious that they are incorrect.

 

[daviv]

x = activecell.address

...
...

range(x).select

This will work.

 

[SteveGregory]

Thanks daviv, That works.

One more thing, It makes me a little dizzy, so I tried to turn off the screen movements at the beginning and end of the macro with:

Application.ScreenUpdating = False
.
.
Application.ScreenUpdating = True

The activecell was correct but it was not visible because the screen was scrolled down to row 80. How do I keep the original screen location?

 

[daviv]

Try

Application.ScreenUpdating = False
x = activecell.address
.
.
Application.ScreenUpdating = True
range(x).select

This gets you back to the activecell. It still gets flickering but less than before and about the same when running the max moment with wheel load. I don't why there are still flicker even with the updating disabled and why it does not return the activecell with the way you set it up.

By the way, 2 questions about the max function and count function in the range ("OPT"). Is the max function necessary if the argument is only cell A80 and count function necessary if the argument is only cell B80?

 

[SteveGregory]

The range OPT holds all of the items tracked by the Solver plugin. Since my spreadsheet has different uses for the Solver, I chose to save and then load the entire list of items each time the Solver is used in the VBA macros.

Thanks for your help. I am still not sure what exactly caused my macro routine to stop working.

 

[Denial]

Regarding the preference to return the screen to the way it looked before the macro was run, and also the various speed-increasing tricks, over the years I have developed the following "matched pair" of subroutines. I call the first one before launching a slab of code, and I call the second one after the slab of code has completed.

Option Explicit
'
'  Module-wide variables to record the screen state etc.
'
Dim OldRow As Long, OldCol As Long, OldSheet As String
Dim OldCalcState, OldUpdateState, OldCell

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub RecordScreenState()
'
'  Records various aspects of the screen state so that the state
'  can later be restored (by Sub RestoreScreenState), then
'  temporarily changes some things for quicker processing.
'
OldSheet = ActiveSheet.Name
OldRow = ActiveWindow.ScrollRow
OldCol = ActiveWindow.ScrollColumn
Set OldCell = ActiveCell
OldUpdateState = Application.ScreenUpdating
OldCalcState = Application.Calculation
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub RestoreScreenState()
'
'  Restore the screen state to as it was when it was recorded
'  by Sub RecordSceeenState().
'
Application.ScreenUpdating = True
'  ScreenUpdating should have been set back to OldUpdateState but
'  on rare occasions (due to untrapped erros of some sort) it
'  could end up locked into False.
Application.Calculation = OldCalcState
Worksheets(OldSheet).Select  '********   ADDED IN NOVEMBER 2009   *******
ActiveWindow.ScrollRow = OldRow
ActiveWindow.ScrollColumn = OldCol
On Error Resume Next
OldCell.Activate
On Error GoTo 0
'
End Sub