vba操作規劃求解

• 要前期引用
• 用到的函數
• • 1，SolverReset
  • 2，SolverOk 函數
  • 3，SolverAdd 函數
  • 4，SolverFinish 函數
  • 5，SolverSolve 函數
• 一個簡單案例

要前期引用

要使用vba操作規劃求解，需要添加引用Solver
Program Files\Microsoft Office\Office14\Library\SOLVER 子文件夾中的 Solver.xlam

可以用代碼直接操作，需彈窗后點擊信任對VBA工程對象模型的訪問

Sub 用vba代碼添加模型信任和前期引用規劃求解()Dim oWshell, iSet oWshell = CreateObject("WScript.Shell")Application.ScreenUpdating = False'信任對VBA工程對象模型的訪問oWshell.RegWrite "HKEY_CURRENT_USER\Software\Microsoft\Office" & Application.Version & "\Excel\Security\AccessVBOM", 1, "REG_DWORD" '信任對 VBA 項目的訪問With Application.SendKeys "~".CommandBars.FindControl(ID:=3627).ExecuteEnd WithAddIns("規劃求解加載項").Installed = TrueWith ThisWorkbook.VBProjectFor i = 1 To .References.CountIf .References(i).Name = "Solver" ThenExit SubElseIf i = .References.Count ThenThisWorkbook.VBProject.References.AddFromFile "SOLVER.XLAM"End IfEnd IfNext iEnd WithApplication.ScreenUpdating = True End Sub

用到的函數

1，SolverReset

重置 “規劃求解參數” 對話框中的所有單元格選定區域和約束

2，SolverOk 函數

定義基本求解器模型。 相當于在"數據分析**“組中單擊**“規劃求解”，然后在"規劃求解參數” | 對話框中指定 選項。
SolverOk ( SetCell、MaxMinVal、ValueOf、ByChange、Engine、EngineDesc )

• SetCell 是 Variant 類型的可選參數(不要給單元格，給單元格地址)。 引用活動工作表中的一個單元格。 對應于" 規劃求解參數 "對話框中的"設置 目標單元格 "框。
• MaxMinVal 是 Variant 類型的可選參數。 對應于"規劃求解參數"對話框中 的"最大值"、最小值 和"值" 選項。

MaxMinVal指定

1	最大
2	最小化
3	匹配特定值

• ValueOf 是 Variant 類型的可選參數。 如果 MaxMinVal 為 3，則必須指定目標單元格匹配到的值。
• ByChange 是 Variant 類型的可選參數（不要給單元格，給單元格地址）。 將更改的單元格或單元格范圍，以便在目標單元格中獲得所需的結果。 對應于"規劃 求解參數" 對話框中的"通過 更改單元格" 框。
• Engine 是 Variant 類型的可選參數。 應用來求解問題的求解方法：2 表示單純形 LP 方法，1 表示 GRG 非線性方法，或 3 表示演進式方法。 對應于" 規劃求解參數 “對話框中的"選擇求解 方法” 下拉列表。
• EngineDesc 是 Variant 類型的可選參數。 另一種以字符串形式指定應用來求解問題的求解方法的方式：“單純形 LP”、“GRG 非線性”或“演進式”。 對應于" 規劃求解參數 “對話框中的"選擇求解 方法” 下拉列表。

3，SolverAdd 函數

向當前問題添加一個約束。 相當于在"數據分析" 組中 單擊"規劃求解"，然后單擊"規劃求解參數"對話框中 | 的"添加"。
SolverAdd ( CellRef 、Relation、FormulaText )

• CellRef 必需 Variant。 對單元格或單元格區域的引用(給地址，別直接給單元格)，該引用構成約束條件的左邊部分。
• Relation 必需 Integer。 約束左側和右側的算術關系。 如果選擇 4、5 或 6，則 CellRef 必須引用決策變量單元格，并且不應指定 FormulaText。

Relation算術關系

1	<=
2	=
3	>=
4	CellRef 引用的單元格必須具有整數的最終值。
5	CellRef 引用的單元格的最終值必須為 0 (0) 1。
6	CellRef 引用的單元格必須具有所有不同和整數的最終值。

• FormulaText 可選 Variant。 約束的右側。

4，SolverFinish 函數

指示 Microsoft Office Excel 如何處理結果，以及要在解決方案過程完成時生成哪種報表。
SolverFinish (KeepFinal、ReportArray、OutlineReports **** **** )

• KeepFinal 是 Variant 類型的可選參數。 可取值為 1 或 2。 如果 KeepFinal 為 1 或省略，則最終的解決方案值將保留在更改的單元格中，以替換任何以前的值。 如果 KeepFinal 值為 2，最終解決方案值遭放棄，并還原原有值。
• ReportArray 是 Variant 類型的可選參數。 Excel 在求解器完成時生成的報表種類：
  • 當使用“單工 LP”或“GRG 非線性求解”方法時，1 會創建一個“解答”報告，2 會創建一個“敏感度”報告，3 會創建一個“限制”報告。
  • 如果使用的是演進式求解方法，1 表示生成“答案”報表，2 表示生成“總體”報表。
  • 當 SolverSolve 返回 5 (規劃求解找不到可行解) ，1 創建一個"可行報告"，2 創建一個Feasibility-Bounds報告。
  • 當 SolverSolve 返回 7 時（不滿足線性條件），1 會創建“線性”報告。
• 使用 Array 函數可指定要顯示的報告，例如，ReportArray:= Array(1,3)。 OutlineReports 是 Variant 類型的可選參數。 可以是 True 或 False。 如果 OutlineReports 為 False 或省略，則報告以"常規"格式生成，而不進行分級顯示。 如果 OutlineReports 值為 True，生成的報表包含對應于你為決策變量和限制輸入的單元格范圍的大綱顯示組。

5，SolverSolve 函數

開始執行規劃求解的求解過程。 相當于單擊 “規劃求解參數” 對話框中的 “求解”。
SolverSolve ( UserFinish 、ShowRef)

• UserFinish 可選 Variant。 如果為 True，則返回結果，而不顯示“規劃求解結果”對話框。 如果為 False 或忽略，則返回結果，并顯示“規劃求解結果”對話框。 ShowRef 可選 Variant。 可以將宏的名稱作為字符串 (作為 ShowRef) 傳遞。 之后，只要規劃求解由于下列某個原因而暫停，便會調用此宏，而不是顯示“顯示試解”對話框。
  • ShowRef 宏必須具有簽名 函數 名稱 (Reason As Integer)。 參數 Reason 是 從 1 到 5 的整數值：
• 由于選中 “規劃求解選項” 對話框中的 “顯示迭代結果” 框而在每次迭代時調用的函數，或者由于用戶按 Esc 來中斷規劃求解而調用的函數。
• 由于超過 “規劃求解選項” 對話框中的 “最長運算時間” 限制而調用的函數。
• 由于超過 “規劃求解選項” 對話框中的 “迭代次數” 限制而調用的函數。
• 由于超過 “規劃求解選項” 對話框中的 “最大子問題數” 限制而調用的函數。
• 由于超過 “規劃求解選項” 對話框中的 “最大可行解數” 限制而調用的函數。

SolverSolve 返回值
如果尚未完整定義規劃求解問題，則 SolverSolve 會返回 #N/A 錯誤值。 否則，規劃求解將會運行，并且 SolverSolve 返回與“規劃求解結果”對話框中顯示的消息相對應的整數值：

返回值消息

0	規劃求解找到解。 滿足所有約束和最優條件。
1	規劃求解已收斂到當前解。 滿足所有約束。
2	規劃求解無法改進當前解。 滿足所有約束。
3	當超過最大迭代次數限制時選擇“停止”。
4	“目標單元格”值不收斂。
5	規劃求解找不到可行解。
6	規劃求解已根據用戶的請求而停止。
7	不滿足此 LP 規劃求解需要的線性條件。
8	問題太大，規劃求解無法處理。
9	規劃求解在目標或約束單元格中遇到錯誤值。
10	當超過最長運算時間限制時選擇“停止”。
11	內存不足，無法解決問題。
13	模型出錯。 請驗證所有單元格和約束是否有效。
14	規劃求解在允許誤差范圍內找到整數解。 滿足所有約束。
15	達到最大可行 [整數] 解數時選擇“停止”。
16	達到最大可行 [整數] 子問題數時選擇“停止”。
17	規劃求解在概率上收斂于一個全局解。
18	所有變量都必須擁有上限和下限。
19	二進制或所有不同約束中的變量界限沖突。
20	變量上下限禁止全部可行解。

示例代碼

Worksheets("Sheet1").Activate SolverReset SolverOptions Precision:=0.001 SolverOK SetCell:=Range("TotalProfit"), _MaxMinVal:=1, _ByChange:=Range("C4:E6") SolverAdd CellRef:=Range("F4:F6"), _Relation:=1, _FormulaText:=100 SolverAdd CellRef:=Range("C4:E6"), _Relation:=3, _FormulaText:=0 SolverAdd CellRef:=Range("C4:E6"), _Relation:=4 SolverSolve UserFinish:=False, ShowRef:= "ShowTrial" SolverSave SaveArea:=Range("A33")Function ShowTrial(Reason As Integer)Msgbox ReasonShowTrial = 0 End Function

一個簡單案例

已知A-C列，根據F-G列客戶和金額，找到票號組合

先定義一個函數，操作規劃求解

'1參數， 目標單元格 '2參數, 目標值 '3參數, 可變的單元格 Function MySolver(targetRng As Range, _targetValue, _varRng As Range) Dim ssjg$, i targetRng.Formula = "=SUMPRODUCT(D$2:D$19*$C$2:$C$19)"SolverReset '重置規劃求解'設置基本規劃求解參數solverok SetCell:=targetRng.Address, MaxMinVal:=3, _ValueOf:=targetValue, ByChange:=varRng.Address, _Engine:=2'添加約束solveradd varRng.Address, 5'執行，但是不顯示規劃求解對話框SolverSolve UserFinish:=True'結果返回單元格SolverFinish KeepFinal:=1'判斷下規劃求解結果是否對If targetRng.Value = targetValue Then'然后在去找對應的票號For i = varRng.Row To varRng.Cells(varRng.Count, 1).RowIf Range("d" & i).Value = 1 Thenssjg = ssjg & "/" & Range("b" & i).ValueEnd IfNextEnd If'清空d列,返回結果Range("d1:d19").ClearContentsMySolver = IIf(ssjg = "", "查無", Mid(ssjg, 2)) End Function

設置主函數

Sub result()Dim r, dic, rng As Range, i, arData, ssKey$Set dic = CreateObject("Scripting.Dictionary")Application.ScreenUpdating = Falser = Range("a65536").End(xlUp).RowarData = Range("a1").Resize(r, 3).ValueFor i = 2 To r '用二級字典記錄每個客戶的單元格范圍ssKey = arData(i, 1)If dic.Exists(ssKey) = False ThenSet dic(ssKey) = CreateObject("Scripting.Dictionary")Set dic(ssKey) = Range("d" & i)ElseSet dic(ssKey) = Union(dic(ssKey), Range("d" & i))End IfNextr = Range("f65536").End(xlUp).RowFor i = 2 To rssKey = Range("f" & i).ValueIf dic.Exists(ssKey) ThenSet rng = dic(ssKey)Range("d1:d19").ClearContentsRange("h" & i).Value = MySolver([d1], Range("g" & i).Value, rng)End IfNextApplication.ScreenUpdating = True End Sub

其他規劃求解vba函數幫助可以看官方文檔
https://docs.microsoft.com/zh-cn/office/vba/excel/concepts/functions/using-the-solver-vba-functions

總結

以上是生活随笔為你收集整理的vba操作规划求解的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。