EXCEL VBA不在从图表按钮调用的宏中运行SOLVER [英] EXCEL VBA does not run SOLVER inside a macro invoked from a chart button

问题描述

此问题代替了该问题:

EXCEL VBA忽略规划求解更改值

因为在原始问题中似乎还有另一个问题没有得到解决.

because there seems to be another issue not appreciated in the original question.

我有以下Excel VBA代码片段来解决最小化问题:

I have the following snippet of Excel VBA code to solve a minimisation problem:

SolverOk SetCell:="$AP$13", MaxMinVal:=2, ValueOf:=0, ByChange:="$AP$9:$AP$11", Engine:=1
SolverSolve UserFinish:=True

这在通过按工作表上的按钮触发的宏( Do_Fit )中运行.我在图表表上有另一个按钮(显示拟合)，该按钮也运行 Do_fit .SOLVER在我按下工作表按钮时运行，但在我按下图表按钮时不运行.工作表具有一个 Worksheet_change 方法，其中包含以下语句:

This runs in a macro (Do_Fit) triggered by pressing a button on a worksheet. I have another button on a chart sheet (showing the fit) which also runs Do_fit. SOLVER runs when I press the worksheet button but not when I press the chart button. The worksheet has a Worksheet_change method which contains this statement:

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

处理对工作表的特定部分所做的更改，不受 Do_fit 的影响.不知道这是否与问题有关，但认为值得一提.更新:当我禁用 Worksheet_Change 宏时，按图表按钮时SOLVER仍然无法运行

to process changes made to a specific part of the worksheet, which is not affected by Do_fit. Not sure if this has anything to do with the problem but thought it worth mentioning. Update: when I disable the Worksheet_Change macro, SOLVER still does not run when pressing the chart button

我尝试使用SolverReset重置问题，但这会产生一个错误，指出Excel已用尽.

I tried resetting the problem using SolverReset but this produces an error saying Excel has been exhausted.

当我按下工作表按钮而不是图表按钮时，我不明白为什么SOLVER运行，并且希望获得解决该问题的建议

I don't understand why SOLVER runs when I press the worksheet button but not the chart button and would appreciate any suggestions on how to solve this

谢谢！

推荐答案

按照Jon Peltier的建议，添加 Sheets("calcs").Activate ( calcs 是名称在工作表的顶部)到 SolverOk 语句之前的VBA代码即可解决问题.谢谢！

Following Jon Peltier's suggestion, adding Sheets("calcs").Activate (calcs is the name of the worksheet) to the VBA code ahead of the SolverOk statement did the trick. Thanks!

这篇关于EXCEL VBA不在从图表按钮调用的宏中运行SOLVER的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！