Excel多工作表相同VBA宏（含命令按钮）运行结果不一致问题

I’ve dealt with this exact Solver + multi-sheet VBA issue before, so let’s break down what’s happening and how to fix it quickly.

The Root Cause #

Your current macro uses hardcoded cell references like $L$7 but doesn’t explicitly tie them to a specific worksheet. When you click the button on Sheet2, if the macro lives in a standard module (not the sheet’s own code module), Solver might still be targeting cells on Sheet1—especially if Sheet1 was the active sheet when you clicked the button. Even if the button is on Sheet2, VBA defaults to the active worksheet for unqualified range references, which throws everything off.

A secondary check: Make sure the G7 cell on Sheet2/3 has the correct formula (=SUM(G4:G6)). If that formula’s broken, the Solver constraint G7=1 will never work as expected.

The Fixes #

Option 1: Bind the Macro to the Sheet (Cleanest Approach) #

Move the macro into the worksheet’s own code module instead of a standard module. This lets you use Me to reference the sheet that holds the button, guaranteeing Solver targets the right cells:

Sub Macro1() ' Solver macro tied to the sheet
    Dim targetSheet As Worksheet
    Set targetSheet = Me ' "Me" = the sheet with the button you clicked
    
    SolverReset
    SolverOk SetCell:=targetSheet.Range("$L$7"), _
        MaxMinVal:=2, _
        ValueOf:="0", _
        ByChange:=targetSheet.Range("$G$4:$G$6")
    SolverAdd CellRef:=targetSheet.Range("$G$7"), Relation:=2, FormulaText:="1"
    SolverSolve userFinish:=True
End Sub

How to set this up:

• Right-click the button on Sheet2 → Select "View Code" (this opens Sheet2’s code module automatically)
• Paste the code above, replacing any existing code
• Repeat this for Sheet3’s button

Option 2: Universal Macro for All Sheets #

If you want to keep the macro in a standard module (so you don’t duplicate code), use Application.Caller to detect which sheet’s button was clicked:

Sub Macro1() ' Universal Solver macro for multiple sheets
    Dim targetSheet As Worksheet
    
    ' Figure out which sheet triggered the macro
    If TypeName(Application.Caller) = "Button" Then
        Set targetSheet = Application.Caller.Parent
    Else
        ' Fallback to Sheet1 if something goes wrong
        Set targetSheet = Sheet1
    End If
    
    SolverReset
    SolverOk SetCell:=targetSheet.Range("$L$7"), _
        MaxMinVal:=2, _
        ValueOf:="0", _
        ByChange:=targetSheet.Range("$G$4:$G$6")
    SolverAdd CellRef:=targetSheet.Range("$G$7"), Relation:=2, FormulaText:="1"
    SolverSolve userFinish:=True
End Sub

Quick Checks Before Testing #

• Double-check that G7 on Sheet2/Sheet3 uses =SUM(G4:G6) to calculate the weight sum
• After clicking the button, open the Solver dialog (Data tab → Solver) to confirm the constraint $G$7=1 is applied to the current sheet’s cells

Once you make these changes, Sheet2 and Sheet3 should behave exactly like Sheet1—weights will sum to 1 while minimizing the error in L7.

内容的提问来源于stack exchange，提问作者Red Eye