Same code produces two different sets of results

[austenr]

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.

[vba]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)

[/vba]

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:

[vba]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
[/vba]

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?

[austenr]

Does anyone know if Bill and Co. change the fubctioning of add-ins from release to release? The only thing I can think is that there was some sort of mnor change in the calculations (maybe some sort of rounding) from one release to the other. I can't come to any other conclusion since the code is exactly the same and the data is as well.

[tpoynton]

I read your post earlier and was wondering the same thing. Perhaps you can install solver.xla and use that instead of solver.xlam in 2007 and see if the results vary?

I read quite a while ago that earlier versions of the analysis toolpak did not produce accurate stats (still dont in some ways), but that there were some improvements in later versions. so my understanding (and hope!) is that there are 'improvements' over time to the add ins. Sorry for the fuzziness...

[austenr]

Hey,

That is the only thing I can come up with as well. What the whole thing boils down to is that I am migrating code from 2003 to 2007 it uses Solver. This particular workbook has been in use for a few years and is used to teach MBA students. I finished the migration with a lot of help from this board (Thanks Bob and others). I was QA'ing it and noticed the discrepancy. Im affraid they may have to live with this fact although they won't like it. Not about to go trying to delve into the add-in code. I would be totally lost.

[tpoynton]

well, if they only use one or the other, they wont know the discrepancy exists . My suggestion to try running the xla in 2007 was simply to validate your intuition that something changed in the solver add in, and not somewhere else.

I developed an add in that does a lot of what the analysis toolpak does, but with a point and click interface (no range selections mostly). I ended up not relying on the toolpak for all but one of the of the procedures (regression) because of such inconsistencies.

[austenr]

Yea. It only differs by hundreths of a percentage and a few cents either way. "You siad your software worked Michael". Michael, "It does work, I must have put the decimal in the wrong place, Im always doing something mundane like that" Quote from Office Space Classic stuff

[Bob Phillips]

Spinach got its reputation as a strong-man's food because of a publication by Dr. E. von Wolf in 1870, where a misplaced decimal point led to an iron-content figure that was ten times too high.

[tpoynton]

Not that I didnt believe you Bob, but I had to look it up...

[Bob Phillips]

This cropped up in a TV programme over here only this week where a lady called Rachel de Thame was looking into whether these so-called super-foods (imported fruits/vegetables like kiwi, blueberries) were better than our home-growns. She mentioned Dr Wolf in the programme.

Whilst it may not be as rich in iron as once thought, it is an extremely good vegatble, especialli in Indian food (uumh ... sag aloo).

[rory]

They do sometimes change the add-ins from version to version - there is passing mention here that they "didn't change much" but clearly they did make changes while taking care of the new Excel specs and what you're seeing is possibly a side-effect!