Posts Tagged With: VBA

[Excel]白做工了,規劃求解與 VBA


很久沒有做分析了,都在 code …

上個月老闆交代我做個分析,想來想去,想要最佳化還是用數學規劃,線性規劃是個快速解決問題的方法,只要問題不複雜,直接用線性規劃求解就可以了。

問題是離開學校後,沒有好的軟體可以算線性規劃,想起來還有 Excel 的規劃求解可以用,就翻開 Excel 玩。

話說 2005 年在新加坡 MVP亞洲年會中,跟當時負責中文化的洪士吉老師與這個巨集的澳洲原作者聊過,當時聽洪士吉老師有原始碼跟密碼,我那時自己也用 VBScript 寫一個線性規劃的 ASP 線上網頁,所以主要討論的是卡曼卡法的加速解跟 Lindo,但是我論文不是搞這方向,離開學校後就甩到邊邊角角了。

我平常為了加速 Excel 開啟,所有的增益集都關閉,Office 2003 年代短暫的玩過規劃求解,但主要還是玩 Lindo ,開啟規劃求解增益集後,發覺設定方式完全不一樣了,看了一下線上手冊,上面寫 Excel 2010 後改成這樣,只好重新學習。

首先,先把自己寫的線性規劃翻出來,用相同的模型建構 Excel ,自己會比較容易上手:

線性規劃命題範例

按下 [進行最佳化分析] 後,可得到計算結果:

線性規劃計算結果

這個命題是在做教材範例,假定一個有閘門的滯洪池或水庫,在已知預報進水量下,受到滯洪池蓄洪量、放水路放水等限制,目標是穩定洩水減低洪害,各限制式的依據參閱上面網頁擷圖右側文字說明。

下面是我喜歡的規劃求解表格樣式,規劃求解仍然使用 Excel 公式,所以並沒有需要排得整整齊齊,但是為了人類容易閱讀,還是排成表格方式比較不會錯,詳細個位置說明,放到最後,先看操作步驟:

在 Sheet 上建立規劃求解命題

從 選單 點選 [資料] > [規劃求解] ,逐項設定:

規劃求解設定

規劃求解計算約 1 秒內可完成:

規劃求解完成

計算結果與設定對話盒連結註記說明:

規劃求解計算完成
  1. 圖上數字對應到 [規劃求解參數] 對話盒的數字,並不對應到這邊說明的項次。
  2. 變數名我選擇放在第一列,C1 ~ J1 ,這種架構大家都容易看得懂。
  3. 變數計算結果我選擇放在第二列,C2 ~ J2,預設可以不輸入,參見前面建立命題的圖,不輸入,就代表起始解為 {0} 集合。
  4. 目標函數的係數我放在 C3 ~ J3,也就是 MIN C。
  5. 限制式左側的係數我放在 C4 ~ J19 ,不等號我放在 M4 ~ M19,常數項我放在 N4 ~ N19。
  6. 最佳化可以在 [規劃求解參數] 輸入 Excel 公式,但這樣會造成每次設定的複雜化,比如說目標函數一變,或是限制式調整時, [規劃求解參數] 的設定就要跟著變,所以為了方便起見,建議像我這樣做,在 K3 輸入陣列公式,讓係數乘變數,就可以降低 [規劃求解參數] 的設定問題,K3 正確設定完成後,複製到 K4 ~ K19 ,讓限制式的係數一樣可以乘變數。注意,陣列公式輸入完成後,要按下 {Shift}+{Ctrl}+{Enter} 三個鍵,才能正確變成陣列公式。
  7. 最後在 [規劃求解參數] 對話盒 [載入/儲存] 按鈕,點下 [儲存] ,選擇 N2 。

這個計算結果,可以透過 K/L/M 三欄檢查限制式是否成立,或是直接觀看 N 欄,限制式成立的邏輯公式就是使用 K/L/M 三欄。

在說明項目 6 中,使用了陣列公式,實務上會增加計算量,也就是係數 0 x 變數,但卻可以讓設定參數簡易許多,就如同本例。

做到這,可以發現 L 欄用不到,因為在 [規劃求解參數] 對話盒 [新增] 限制式時,都要手動輸入,我的目的是要解決老闆交付的任務,所以針對此命題範例建立了測試的 VBA 程式碼,細節到正式命題再修改:

用 VBA 建立規劃求解參數

為了方便起見,我在 VBA 內直接建立陣列公式,也就是說上面建立規劃求解的途中,K 欄不用輸入,其他 [規劃求解參數] 對話盒的輸入,都用 VBA 自動產生。

規劃求解不支援直接輸入不等式,所以用 GetRelationIndex 函數將不等式轉成索引值輸入。

如果不想自己打太多程式碼,可以用錄製的再進行修改,不過這張圖上的程式碼已經很精簡了,錄製會很亂,不如直接打,再改欄位值即可。

規劃求解的函數使用說明參考微軟官方網址:https://docs.microsoft.com/zh-tw/office/vba/excel/concepts/functions/using-the-solver-vba-functions

好了,終於開始建立正式命題,先把正式命題縮小為一天24小時的時資料進行連續量分析,建個小小的模型,測試看看是不是能正常運作:

規劃求解超過上限

不得不爆粗口…

「規劃求解限定為 200 個變數儲存格和 100 個限制式,加上變數儲存格的限值。」

100 個限制式?這個規格限制下,根本是玩具吧… 規劃求解,我只好繼續把你放生了…

[2019/06/05]

花了點時間,把網頁版 ASP 的 VBScript 轉成 VBNET 語法,順便把命題轉成類別,測試還算成功。

VBNET WinForm 輸入命題
VBNET WinForm 輸入命題

輸入完線性規劃命題後,點選 [限制式] 頁籤 下方工具列那個圖示 (測試程式沒有管 Icon 的美觀度) ,即可得到輸出結果,輸出改用 StringBuilder 產生 HTML 語法用 WebBrowser Control 輸出,這樣之後若有需要,可以在工具列放預覽列印的按鈕,呼叫 WebBrowser Control 的預覽列印。

VBNET WinForm 輸出結果
VBNET WinForm 輸出結果

不過裡面還有一些 Bug ,大型命題解不出來,這就造成除錯困難,大概還要加一頁來輸出線性規劃各步驟階段的計算表格,才好除錯。

廣告
Categories: Office, 工作點滴, 技術分享 | 標籤: | 發表留言

[VBA] Office 2013 x64 以後版本呼叫 Windows API 要加關鍵字 PtrSafe


工作上有一個很少用的工具 Excel 巨集,裡面放了一堆 API ,是以前寫的。

這兩天臨時要用到,用 Excel 開起來後,一片滿江紅,下面的圖檔是我處理完後加個測試行做出來的,檔案就跟第一行一樣,宣告區全是紅的。

Office 2013 VBA 版本

Office 2013 VBA 版本

從版權畫面可以看到,VBA 7.1 的大版本懸掛在那。

滿江紅是看不出來要改啥,所以上網查半天,詳細說明請參考 MSDN 線上手冊:

Delare 陳述式 https://msdn.microsoft.com/zh-tw/library/office/gg278581.aspx

搞定後才發現,重新輸入程式碼,換行時,VBA 程式碼自動檢查就會提醒你,在 Office x64 下,要加 PtrSafe :

自動提醒要加 PtrSafe

自動提醒要加 PtrSafe

可以比對 [錯誤] / [視窗] 兩行宣告,就只差在 PtrSafe ,錯誤那行沒有輸入 PtrSafe 就會跳錯。

所以也可以推知,舊版本的 VBA 在 x64 下,若有呼叫到 Windows API 都須修改宣告,否則根本不能跑,可能非微軟的協力廠商,例如 AutoCAD 也受此影響。

當然又回到那句老話,這樣看來,微軟十年前根本是騙大家的,VB6 要上到 x64 還是有很多解法,就看微軟願不願意而已,因為在 Office 中已經解決了阿~

微軟不再支援 VB6 的理由,又消滅了一個…

Categories: Office, 技術分享 | 標籤: | 發表留言

[VBA] 回傳到 Sheet 的陣列索引值下限會自動從 0 變更為 1


這篇跟前篇有點關係:[VBA] LongLong 超長整數 我打算將監測數據依據是否超越門閥值來轉換成旗標變數。 由於 Excel 內沒有 Array 這個函數,所以我在 VBA 裡面加了一個 VbaArray 函數來呼叫 Array 給 Excel 呼叫。

Function VbaArray(ParamArray arrayData())
  ReDim rtnArray(0 To UBound(arrayData))
  For ibd = 0 To UBound(arrayData)
     rtnArray(ibd) = arrayData(ibd).Value
  Next
  VbaArray = rtnArray
End Function

也就是直接包裝 Array 而已,沒啥特別的。 透過這個函數,我在 Sheet 內呼叫我的旗標轉換函數,由於 VB6 以後,未明確宣告陣列索引下標者,均為 0 ,VBScript / VBNET 則強制為 0 ,沒有陣列索引下標可以不為 0 ,因此習慣這個體驗,直接用:

For i = 0 To UBound(arrData)
   ' 略
Next

然後 Excel 回傳 #VALUE … 用除錯模式追蹤,居然問題出現在 arrData(0) 陣列索引錯誤?立刻在立即除錯測試 LBound(arrData)、UBound(arrData) 與我的認知完全不同,所有索引值都增加了 1 ,也就是說從 Base 0 變成 Base 1 。 寫了小的測試 Excel 2013 (VBA 7.1) 的檔案如下圖:

Excel 2013 經過 Sheet 陣列索引值變更了

Excel 2013 經過 Sheet 陣列索引值變更了

GetSecond 變成取第一個。 我以為是 Excel 2013 的 bug ,找台 Excel 2003  (VBA 6.5) 測試,也是相同狀況:

Excel 2003 經過 Sheet 陣列索引值變更了

Excel 2003 經過 Sheet 陣列索引值變更了

所以此行為是設計行為,透過 Google 搜尋微軟站台相關說明。 Support 那邊有相關的,但並沒有很明確提醒這個狀況:https://support.microsoft.com/zh-tw/kb/213798

論壇則有人因為跟 VBNET 慣例不同,提出疑問:https://social.msdn.microsoft.com/Forums/en-US/8155cfa8-7b9f-4a55-ba46-45bddcb77779/array-dimensions-mismatch-between-excel-and-net-vb-2010?forum=vbinterop

我猜可能是為了相容 Excel 5.0 預設採用 Base 1 吧?不然也沒得猜了。 做個紀錄,以後不會再犯相同狀況。

Categories: 工作點滴, 技術分享 | 標籤: | 發表留言

[VBA] LongLong 超長整數


話說,公司的專案支援 VBScript 外掛,這幾天在用 Excel 2013 的 VBA 7.1 寫 VBScript 。

因為裡面要用到位元旗標,我就很順手的打了:
2^i

想說,VBA 原始碼自動感知會幫我加空白,就繼續往下寫。

寫完以後用 Excel 測試,變成 #VALUE ,就用追蹤方式追查,才知道這行有問題。

手動改成 2 ^ i 就沒問題,好奇之下另外開個小專案測試:

誤使用到超長整數符號

誤使用到超長整數符號

用除錯模式看,型別居然是 LongLong 。

按下 F2 查物件類別庫,果然有 LongLong ,用 HEX(-1) 測試,是 8 bytes 的超長整數:

LongLong 為 8 bytes 超長整數

LongLong 為 8 bytes 超長整數

用 Google 搜尋 MSDN ,看起來是 x64 版本的 VBA 就加入了,應該是 Office 2010 x64 以後開始有此功能:
64 位元 Visual Basic for Applications 概觀:https://msdn.microsoft.com/zh-tw/library/office/Gg264421.aspx

順便回頭測了 Excel 2003 的 VBA 6.5:

Excel 2003 VBA 6.5 正確處理次方運算符號

Excel 2003 VBA 6.5 正確處理次方運算符號

舊版本的 VBA 就能自動把 2^2 轉成 2 ^ 2 。

從 LongLong 超長整數的 VBA 7.1 來看當初微軟在 2005 年宣告 VB6 不再支援的理由又消滅了一個,不是不能支援 64 bits 及加超長整數,而是不願意加,是不為也,非不能也。

Categories: 工作點滴, 技術分享 | 標籤: | 2 則迴響

[Office][bug]線上手冊越來越爛,這根本就又錯了嘛


話說,Office 2010 的 VBA 升級到 7.0 ,不禁讓我想到,2005/3 全球 Visual Basic 開發者在一個公開網站簽署,要求 VB6 要像 VC 一樣能被保留,弄成 VBNET 跟 B# 兩套,當時微軟的回應是窒礙難行…
事實上在 Office 2007 VB6 的核心 VBA 升級到 6.5 現在 Office 2010 VBA 達到 7.0 版,時間總能證明一切,這是微軟強制 VB6 開發者遷移到 VBNET 的手法而已。
這不是這篇的重點,話說我打開 VBA 線上手冊,要參考一些資訊時,忽然發現每個方法、屬性下面都多了一堆無意義的文字,那就是
文章
這樣非常占版面,找屬性變成需要猛翻頁,捲動滑鼠,檢視原始碼中,並沒有文章的 html 標籤跟內容,看樣子是某個白爛的 script 造成,我懷疑應該是原先要做到連結上的 title=’Document’ 被搞錯了,真是夠了。
經過檢查,Access、Excel、Outlook、PowerPoint、Publisher、Visio、Word ,都有這個情形,看來應該是共用說明文件就有這個 script 功能造成的。
Categories: Office | 標籤: | 發表留言

[VBA]自然人數位憑證簽名


今天收到經濟部工商憑證,就想說測試看看用公司的憑證簽程式碼,當然用 Excel VBA 來測試最方便,所以標題才會是 VBA ~
公司憑證很順利的完成程式碼數位簽章,想說,再測測看自然人憑證好了,以前自然人憑證簽不上去,所以重灌電腦後,自然人憑證沒有匯入電腦,這次新版的憑證匯入工具測看看。
一開始,我的憑證還是不能完成數位簽章,測娟娟的就可以正常完成數位簽章,不知道是不是我的憑證是第一代的,功能不全?
打電話到內政部的憑證管理中心電話詢問,邊做邊描述重現步驟時,忽然可以簽了… 汗…
回想一下,可能是我匯入我的自然人憑證時,Excel VBA 沒關,後來重開 Excel 就正常了。
若是不能用自然人憑證簽數位簽章的話,不妨到內政部憑證管理中心抓新版的軟體:
憑證匯入工具
MOICA_HiCOS_Setup
安裝HiCOS Clientv2.0.0.exe時,用戶不須另行下載安裝SafeSign CSP,即可於相關配合使用自然人憑證的應用軟體上具有資料加解密、認證安全的功能。
檔案下載
安裝程式若偵測到系統中已先裝有SafeSign程式時,本安裝軟體將協助解除安裝,使用者可不需自行移除SafeSign CSP程式。
※此軟體為Windows整合版本,支援Windows 98,Windows 2000,Windows XP,Windows Vista與Windows 2003。
zip 8.2 MB
在 Excel VBA 簽章過程中,內政部憑證跟一般證券、銀行、憑證認證中心的不太一樣,一般憑證選擇好就可以直接簽章,自然人憑證簽章時,必須插上讀卡機,簽章會跳出內政部的 PIN 碼輸入畫面,輸入完後,就可以正確完成簽章。
想要用自然人憑證簽章的,注意兩點:
1. 用新版的軟體匯入憑證。
2. 簽名時要插上讀卡機。
Categories: 技術分享 | 標籤: | 發表留言

Excel Variant Date 在 1900/3/1 以前的基準日與 VB6 不同


這個狀況更詳細的說明可以到 http://support.microsoft.com/ 搜尋 Excel 1900 。
簡單的說,最早的試算表 Lotus 123 把 1900 視為閏年,理論上來說,逢四閏、逢百不閏、逢四百閏,所以 1900 年為非閏年,但是在 Excel 會有 1900/2/29 的存在,當時的設計是為了與 Lotus 123 相容而存在,就用到現在,所以到了 Excel 2007 還是這樣…
Excel 有兩組日期系統,以 1900/1/0 為 基準或是 1904/1/1 為基準。下面是個簡表,Variant Date 為 OleAut32.dll 裡面定義的 Variant Date ,VB6/VBA/VBScript 的內建 Date 型態,VBNET 可用 ToOADate/FromOADate 轉換,核心值域是依據 IEEE 754 的 Double 來設計,所以加上 Double 的對照:
Double Variant Date Excel Date 1904 Date
0 1899/12/30 1900/1/0 1904/1/1
1 1899/12/31 1900/1/1 1904/1/2
2 1900/1/1 1900/1/2 1904/1/3
58 1900/2/26 1900/2/27 1904/2/28
59 1900/2/27 1900/2/28 1904/2/29
60 1900/2/28 1900/2/29 1904/3/1
61 1900/3/1 1900/3/1 1904/3/2
62 1900/3/2 1900/3/2 1904/3/3
Variant Date 在 1900/3/1 以後就完全與 Excel Date 重合,基本上不建議採用 1904 為基礎的日期資料。
可以看出來,Excel Date 居然有個 1900/1/0 ,算是很有趣的東西,此外,Excel 不接受基準日以前的日期,所以不能有 1899/12/31 這類的日期出現,當然,若採用 1904 為基準,就完全不會出現 1904 年以前的日期。
因此在 1900/3/1 以前的日期若仍要使用在 Excel 內,必須改用 VBA 來處理,並運用 String <-> Date 來相互轉換。
Categories: Office | 標籤: | 發表留言

[疑似Bug] VBA 的 SOAP


早上小跟小朱透過 Live Messenger 討論 Excel 2007 內呼叫 .Net 2.0 的 Web Service ,其中有一項引數為日期變數,要查詢某日的資料,但是小朱測試的結果是會傳回早一天,例如輸入 2007/3/2 會傳回 2007/3/1 。
因為我自己做氣象系統的關係,長期必須使用 UTC 時間,直覺上是時間問題,就建議測試 +8 小時來跑看看,因為 xml 內也是用 UTC 時間。
果然,小朱改用 +8 後就正常了。
不過我們懷疑這是 SOAP 的錯誤,因為這個應該在 SOAP 自動轉換內就完成,不應該讓使用者手動處理時區,所以就由小朱回報,若有後續結果由小朱通知後,再分享給大家參考。
Categories: 更新與回報 | 標籤: | 1 則迴響

替 VBA 簽上數位憑證


http://www.tlcheng.tk/TLCheng/Basic/vba/Signature/index.htm

最近常看到有人問到如何讓 VBA 所發展的程式不要一值跳出詢問盒,這個部分主要是 Office 2000 以後,鑒於巨集病毒太過氾濫,所建立的認證制度,所以以下的方法,均可使用在相同安全控管的軟體上。這些說明在 MSDN 中均可找到,我只整理我喜歡的慣用做法,當然有其他方法,不過個人喜好不同,你也可以自己試試。…

Categories: 電腦和網際網路 | 標籤: | 發表留言

在 WordPress.com 建立免費網站或網誌.

%d 位部落客按了讚: