Excel VBA一鍵規劃求解(Solver)_以三角量水堰計算水頭(h)為例

LHB阿好伯, 2021/07/16

tags: Excel

• Excel VBA一鍵規劃求解(Solver)_以三角量水堰計算水頭(h)為例
• 量水堰功能與計算
• 設定VBA引用Solver
• 全部文章列表

量水堰功能與計算

量水堰法係以特定形狀、尺寸之堰板安裝於渠道中，測定水頭高度，進而計算出流量。

水量測定方法—量水堰法(NIEA W021.52C)
環檢所公告日:094/11/30　實施日: 094/11/30

利用其提供公式我們可以很快速的計算出流量係數(K)與流量(Q)

然而若是想利用流量回求得水頭(h)則不是簡單公式推導就能求出的
這時就可以使用Excel的規劃求解
自動填入水頭(h)、渠道寬度(B)與堰頂高度(D)求得最佳解

然而懶惰之餘也希望能分享給他人使用時能更簡單
這時一定會想到使用VBA的錄製
但若是使用後你會遇到下列錯誤

這時只需要設定好VBA的引用項目Solver
就可以順利使用錄製好的規劃求解

設定VBA引用Solver

使用規劃求解(Solver) VBA 函數

然而在Excel的規劃求解是有自動紀錄參數的功能
利用這一個特性規劃求解就只需要設定好一次參數後就可以達到一鍵執行的需求

點擊巨集並編輯可以查看相關程式碼

Sub 規劃求解巨集錄製()
'
' 規劃求解巨集錄製 巨集
'
    Range("C8").Select    '選取C8
    ActiveCell.FormulaR1C1 = "1.5"    '輸入1.5
'啟動規劃求解與設定目標值(ValueOf)為1.5
    SolverOk SetCell:="$C$7", MaxMinVal:=3, ValueOf:=1.5, ByChange:="$C$3:$C$5", _        
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverOk SetCell:="$C$7", MaxMinVal:=3, ValueOf:=1.5, ByChange:="$C$3:$C$5", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve
End Sub

這一段巨集可以稍加簡化
錄製時不清楚為何規劃求解都會被錄製到兩次
我們可以刪剪掉重複的10~11行程式
在利用VBA的Value物件取代Select
可以直接將儲存格內的數值套入VBA程式碼中
就可以修改成下面的樣子

Sub 規劃求解巨集錄製()
'
' 規劃求解巨集錄製 巨集
'
'啟動規劃求解與設定目標值(ValueOf)為C8儲存格數值
    SolverOk SetCell:="$C$7", MaxMinVal:=3, ValueOf:=Range("C8").Value, ByChange:="$C$3:$C$5", _        
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve
End Sub

之後插入按鈕選取好相對應的巨集
就完成了一鍵執行的功能

🌟全文可以至下方連結觀看或是補充
https://hackmd.io/@LHB-0222/VBArunSolver

全文分享至

https://www.facebook.com/LHB0222/

https://www.instagram.com/ahb0222/

有疑問想討論的都歡迎於下方留言

喜歡的幫我分享給所有的朋友 \o/

有所錯誤歡迎指教

全部文章列表