PDA

View Full Version : Same code produces two different sets of results



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:

austenr
08-17-2007, 05:57 PM
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. :wot

tpoynton
08-17-2007, 07:22 PM
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
08-17-2007, 08:40 PM
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
08-18-2007, 07:00 AM
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
08-18-2007, 08:15 AM
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 :rotlaugh: Classic stuff

Bob Phillips
08-18-2007, 08:22 AM
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
08-18-2007, 11:17 AM
:rotlaugh:

Not that I didnt believe you Bob, but I had to look it up... (http://en.wikipedia.org/wiki/Spinach#Nutrition)

Bob Phillips
08-18-2007, 11:33 AM
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
08-19-2007, 03:23 PM
They do sometimes change the add-ins from version to version - there is passing mention here (http://blogs.msdn.com/excel/archive/2006/09/06/743902.aspx) 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!