SolverOk ByChange overwrite in Excel 2007 VBA
SolverOk ByChange overwrite in Excel 2007 VBA
(OP)
The objective was to reconfigure Solver to use different sets of optimization variables (20+ of them) easily using different sets of cell background colours.
While trying this I encountered an interesting 'feature' (aka bug) in VBA Solver. I created a Range by searching for a given cell background format:
Then, I set the SolverOk to use that Range:
SolverOk pasted the range *as text* into each cell of the given range. Seriously. My spreadsheet already has a number of named ranges, so maybe Excel ran out of bits.
The solution was given by Joerd at http://www.eng-tips.com/viewthread.cfm?qid=199259
Excel has graciously agreed to no longer overwrite my hard won starting values. I thought this info could be of use to others and I'm infinitely grateful to SteveGregory for the question and Joerd for the solution.
While trying this I encountered an interesting 'feature' (aka bug) in VBA Solver. I created a Range by searching for a given cell background format:
CODE
first = True
Set globalRange = Range("A2:AH100")
colourSpec = Range("A1").Interior.ColorIndex
For Each localCell In globalRange
If localCell.Interior.ColorIndex = colourSpec Then
If first Then
first = False
Set solverVariableRange = Range(localCell.Address)
Else
Set solverVariableRange = Union(solverVariableRange, Range(localCell.Address))
End If
End If
Next localCell
Set globalRange = Range("A2:AH100")
colourSpec = Range("A1").Interior.ColorIndex
For Each localCell In globalRange
If localCell.Interior.ColorIndex = colourSpec Then
If first Then
first = False
Set solverVariableRange = Range(localCell.Address)
Else
Set solverVariableRange = Union(solverVariableRange, Range(localCell.Address))
End If
End If
Next localCell
CODE
SolverOk SetCell:="$U$30", MaxMinVal:=2, _ ByChange:=solverVariableRange
The solution was given by Joerd at http://www.eng-tips.com/viewthread.cfm?qid=199259
CODE
ActiveSheet.Names.Add Name:="solver_adj", RefersTo:=solverVariableRange, Visible:=False




