Excel规划求解VBA [英] Excel Solver VBA

问题描述

我在Excel VBA宏中创建的求解器模型中的一个方程如下:

One of the equations in a Solver model that I am creating in an Excel VBA macro is as follows:

SolverAdd CellRef:="$M$18:$M$30", Relation:=2, FormulaText:="$N$18:$N$30"

我如何实现它，以使行号"18"是可以从另一个单元格读取的值?例如，单元格A1的值可能为18，这是我要在此等式中使用的值.如果A1中的值为10，则等式应为:

How could I implement it so that the row number "18" is a value that can be read as an input from another cell? For example, cell A1 may have the value 18, which is what I want used in this equation. If the value in A1 was 10, then the equation should read as:

SolverAdd CellRef:="$M$10:$M$30", Relation:=2, FormulaText:="$N$10:$N$30"

类似地，变量定义为:

SolverOk SetCell:="$H$47", MaxMinVal:=2, ValueOf:=0, ByChange:="$H$18:$J$30,$R$18:$R$30", _
    Engine:=1, EngineDesc:="Simplex LP"

同样，当单元格A1中的数字值从18变为10时，变量应从"$ H $ 18:$ J $ 30，$ R $ 18:$ R $ 30" 更改为"$ H $ 10:$ J $ 30，$ R $ 10:$ R $ 30" .

Again, when the value of the number in cell A1 changes from, say 18 to 10, the variables should change from "$H$18:$J$30,$R$18:$R$30" to "$H$10:$J$30,$R$10:$R$30".

我将不胜感激任何建议.

I will greatly appreciate any suggestions.

非常感谢.

推荐答案

• 要使用变量作为输入，请使用以下代码: input_variable = .Cells(1,1)或 input_variable = Range("A1")并更改为"$ M"& input_variable&":$ M $ 30
• To have a variable as input use this: input_variable = .Cells(1,1) or input_variable = Range("A1") and change to "$M"&input_variable&":$M$30

有关其他方法，请参考此问题: VBA:按变量选择范围或范围为可变VBA

Refer to this question for further ways: VBA: Selecting range by variables or Range as a Variable VBA

• 另一种替代方法是使用 SolverStudio ，它非常有用，因为您使用几乎所有著名的优化在Excel中进行建模库.
• Another alternative is to use SolverStudio, it is really useful, because you model in Excel using almost all famous optmisation libraries.

随着问题的加剧，Excel似乎无法解决某些问题，并且还有许多其他专业软件使用这些优化建模语言.

As problems grows, Excel seems to be unable to solve some problems and there are many other professional softwares that uses those optimisation modelling languages.

如果您尝试安装某些问题，请输入以下链接来解决:帮助页面我的问题有所不同，并已解决

If you try to install some problems can happen, enter this link to solve: Help Page My problem was different and was solved here.

在我的课程中，我是用AMPL授课的，所以SolverStudio的编程要比Excel VBA容易，因为SolverStudio的语言专注于优化问题.

In my classes I was taught in AMPL, so programming with SolverStudio was easier than Excel VBA, because SolverStudio's languages are focused on optimisation problems.

这篇关于Excel规划求解VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！