austenr
08-17-2007, 11:01 AM
This is very strange. I have the exact same code in Excel 2003 and Excel 2007. I checked it line for line. See code below.
Option Explicit
Public calcset As Long
Sub prova1()
'this is the same as the Sheet Macro
'was assigned to Ctrl + G
Dim i As Long
Dim eMsg As Long
Dim sb As Boolean
'errors?
On Error GoTo endo
'speed
With Application
.ScreenUpdating = False
'get statusbar status
sb = .StatusBar
'don't do calc event
.EnableEvents = False
End With
'do on data sheet
Sheets("Data").Activate
'clear previous values
'Range("B7:B47") = 0
'solver for row 7 to 47
For i = 7 To 47
SolverOk SetCell:=Range("C" & i), MaxMinVal:=3, ValueOf:="0", ByChange:=Range("B" & i)
SolverSolve UserFinish:=True
Application.StatusBar = " Solving row " & i & " of 47"
Next i
'cleanup
Sheets("Chart").Activate
'reset
With Application
.Calculate
.ScreenUpdating = True
.StatusBar = sb
.EnableEvents = True
End With
Exit Sub
'come here if errors
endo:
'cleanup
Sheets("Chart").Activate
Range("B7:B47").Clear
'reset
With Application
.Calculate
.ScreenUpdating = True
.StatusBar = sb
.EnableEvents = True
End With
eMsg = MsgBox("Error " & Err.Number & " " & Err.Description, vbCritical)
This calculates data with the SOLVER add-in. I will attach that to this thread as well.
The problem is that I cleaned up some code that was recorded and redundant which was this code:
Sub prova1()
'
' prova1 Macro
' Macro recorded 12/10/2004 by GV
'
' Keyboard Shortcut: Ctrl+g
'
Range("B7").Select
ActiveCell.FormulaR1C1 = "0"
Range("B7").Select
Selection.Copy
Range("B8:B47").Select
ActiveSheet.Paste
SolverOk SetCell:="$C$7", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$7"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$8", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$8"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$9", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$9"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$10", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$10"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$11", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$11"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$12", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$12"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$13", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$13"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$14", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$14"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$15", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$15"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$16", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$16"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$17", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$17"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$18", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$18"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$19", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$19"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$20", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$20"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$21", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$21"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$22", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$22"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$23", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$23"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$24", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$24"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$25", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$25"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$26", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$26"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$27", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$27"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$28", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$28"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$29", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$29"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$30", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$30"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$31", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$31"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$32", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$32"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$33", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$33"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$34", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$34"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$35", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$35"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$36", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$36"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$37", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$37"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$38", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$38"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$39", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$39"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$40", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$40"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$41", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$41"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$42", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$42"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$43", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$43"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$44", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$44"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$45", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$45"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$46", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$46"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$47", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$47"
SolverSolve UserFinish:=True
ActiveWindow.SmallScroll Down:=10
Range("F5").Select
End Sub
The data that is from the code at the top of the post is consistent with the data returned from the old code. However the results returned from running it in 2007 are off a few hundreths of a percent and a few cents. (See attached data sheet). Any one have any ideas? :banghead:
Option Explicit
Public calcset As Long
Sub prova1()
'this is the same as the Sheet Macro
'was assigned to Ctrl + G
Dim i As Long
Dim eMsg As Long
Dim sb As Boolean
'errors?
On Error GoTo endo
'speed
With Application
.ScreenUpdating = False
'get statusbar status
sb = .StatusBar
'don't do calc event
.EnableEvents = False
End With
'do on data sheet
Sheets("Data").Activate
'clear previous values
'Range("B7:B47") = 0
'solver for row 7 to 47
For i = 7 To 47
SolverOk SetCell:=Range("C" & i), MaxMinVal:=3, ValueOf:="0", ByChange:=Range("B" & i)
SolverSolve UserFinish:=True
Application.StatusBar = " Solving row " & i & " of 47"
Next i
'cleanup
Sheets("Chart").Activate
'reset
With Application
.Calculate
.ScreenUpdating = True
.StatusBar = sb
.EnableEvents = True
End With
Exit Sub
'come here if errors
endo:
'cleanup
Sheets("Chart").Activate
Range("B7:B47").Clear
'reset
With Application
.Calculate
.ScreenUpdating = True
.StatusBar = sb
.EnableEvents = True
End With
eMsg = MsgBox("Error " & Err.Number & " " & Err.Description, vbCritical)
This calculates data with the SOLVER add-in. I will attach that to this thread as well.
The problem is that I cleaned up some code that was recorded and redundant which was this code:
Sub prova1()
'
' prova1 Macro
' Macro recorded 12/10/2004 by GV
'
' Keyboard Shortcut: Ctrl+g
'
Range("B7").Select
ActiveCell.FormulaR1C1 = "0"
Range("B7").Select
Selection.Copy
Range("B8:B47").Select
ActiveSheet.Paste
SolverOk SetCell:="$C$7", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$7"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$8", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$8"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$9", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$9"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$10", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$10"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$11", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$11"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$12", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$12"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$13", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$13"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$14", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$14"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$15", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$15"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$16", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$16"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$17", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$17"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$18", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$18"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$19", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$19"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$20", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$20"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$21", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$21"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$22", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$22"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$23", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$23"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$24", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$24"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$25", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$25"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$26", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$26"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$27", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$27"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$28", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$28"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$29", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$29"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$30", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$30"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$31", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$31"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$32", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$32"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$33", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$33"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$34", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$34"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$35", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$35"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$36", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$36"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$37", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$37"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$38", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$38"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$39", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$39"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$40", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$40"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$41", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$41"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$42", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$42"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$43", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$43"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$44", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$44"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$45", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$45"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$46", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$46"
SolverSolve UserFinish:=True
SolverOk SetCell:="$C$47", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$47"
SolverSolve UserFinish:=True
ActiveWindow.SmallScroll Down:=10
Range("F5").Select
End Sub
The data that is from the code at the top of the post is consistent with the data returned from the old code. However the results returned from running it in 2007 are off a few hundreths of a percent and a few cents. (See attached data sheet). Any one have any ideas? :banghead: