SolverOk ByChange overwrite in Excel 2007 VBA

[DougHatfield]

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:

    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

Then, I set the SolverOk to use that Range:

SolverOk SetCell:="$U$30", MaxMinVal:=2, _ ByChange:=solverVariableRange

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

ActiveSheet.Names.Add Name:="solver_adj", RefersTo:=solverVariableRange, Visible:=False

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.