法蘭西斯的學習筆記

我有一個需求是將某欄的資料複製到其他地方, 但是希望能夠是按照大小排列, 並且過濾掉重複的資料 我先做一個測試, 如果我在A1輸入自訂函數(MyCopy), 去改變B1儲存格的內容 : Function MyCopy() As Variant Cells(1, 2).Value = "AA" MyCopy = "OK" End Function 結果是 #VALUE! (失敗), 再將程式修改一下, 在A1中輸入 =MyCopy(B1), 把B1當參數傳進去會怎樣 Function MyCopy(myRange As Range) As Variant myRange.Value = "AA" MyCopy = "OK" End Function 結果依然是 #VALUE! (失敗), 只好爬文看看別人的作法, 爬了很久直到這篇文章( How to Change Another Cell with a VBA Function ), 作者的技巧是用 Evaluate 函數來叫另一個自訂函數來幫忙修改其他儲存格的資料, 看來自訂函數果真無法直接修改其他儲存格的資料了!(如果有其他作法也歡迎留言告知喔), 程式再改一下測測看 Function MyCopy() As Variant Evaluate "MyChange(B1,100)" MyCopy = "OK" End Function Function MyChange(myRange As Range, newData As Variant) myRange.Value = newData End Function 我在A1儲存格中輸入 =MyCopy(), 結果對了!!(附上圖慶祝一下) 再來是排序問題, 網路上作法很多, 最後我挑了 System.Collections.SortedList 這個物件, 程式如下 : Function MyCopy(copyFrom As Range, copyTo As Range) As Variant Dim mList As Object D...

如果有用自訂函數來完成工作的人, 可能會發現有的自訂函數當你改變要計算區域的值時, 結果會跟著改變, 有的則不會跟著變, 如 Excel VBA (一) : 自訂函數之自動加總 會跟著變, 而 Excel VBA (二) : 自訂函數之自動加總(續) 則不會改變, 而這個答案就是參數 如果想要計算範圍內, 只要資料有異動, 則會自動更新計算結果, 就必須將該範圍納入自訂函數的參數( 參考微軟的文章 ), 參數裡的儲存格, 不論多少, 都是Excel監控的對象, 就如同 Excel 中的標準函數, 我只要輸入範圍, 那就會自動更新, 如果沒參數或是輸入資料而已, 如 SIN(100) 就不會有自動更新的功能, 因為沒監控對象 !! 如下圖中A欄黃色儲存格輸入的是 我的加總2() 無參數函數, 而橘色儲存格輸入的是 我的加總(A1:A9) 有參數函數, A欄的結果都是15, 我再將A欄複製到B欄後, 結果當然也是15,但是當我將B1儲存格從1改成2之後, 黃色無參數的結果沒變, 而橘色有參數的結果自動變成16了, 這是有參數的好處 測試時間: 2020年07月29日 測試環境: Windows 10 家用版, Excel 2019 家用版

繼續上篇的 Excel VBA (一) : 自訂函數之自動加總 來修改程式 加總時如果要將非數字的全部濾掉, 我利用 TypeName 這個函數來完成, 再將Excel的C欄資料除了文字型別以外, 加上日期以及布林型別, 測試結果如下 Function 我的加總(加總區域 As Range) As Double Dim data As Variant 我的加總 = 0 For Each data In 加總區域 If TypeName(data.Value) = "Double" Then 我的加總 = 我的加總 + data End If Next End Function 如果我現在很懶, 懶得選擇加總區域, 讓它自動將它上方的資料自動加總起來, 例如在A10中輸入自訂函數, 則會自動將A1到A9的儲存格都加總起來, 我的懶人程式如下 : Function 我的加總2() As Double Dim 目前儲存格 As Range Dim 目前列 As Integer Dim i As Integer Set 目前儲存格 = Application.Caller 目前列 = 目前儲存格.Row 我的加總2 = 0 For i = 1 To 目前列 - 1 Set 目前儲存格 = 目前儲存格.Offset(-1, 0) If TypeName(目前儲存格.Value) = "Double" Then 我的加總2 = 我的加總2 + 目前儲存格 End If Next End Function Application.Caller : 會取得函數計算的儲存格, 它的行跟列可以透過Column跟Row來取得 ( 參考文章 ) 加總則是從同欄的第一列開始加, 一直加到輸入的前一列, 也就是 Application.Caller.Row - 1 offset(-1,0) : 則表示要位移的位置, -1 表示垂直往上一格, 0 表示水平維持不變 我在A10輸入自...

最近幫忙別人解決 Excel 的資料處理問題, 順便將一些使用心得做一下筆記 當我開啟Excel時, 預設是沒有 開發人員 的功能(如下圖的紅框) 要開啟就要點擊Excel選單的 檔案 (如上圖的黃框) 再點擊 選項 (如下圖的紅框) 再點擊 自訂功能區 (如下圖的紅框) 再將 開發人員 的功能打開即可(如下圖的紅框) 開啟開發人員中的 Visual Basic (如下圖的紅框)就會開啟VBA的功能視窗了 我先嘗試最簡單的程式, 看看是否成功(如下圖) Function 我的加總() As Double     我的加總 = 100 End Function 再去Excel的工作表1中, 在任一儲存格中輸入"=我的加總()", 測試是否會顯示 "100"(如下圖), 結果成功了, 代表Excel VBA中函數與變數名稱都是可以用中文 再來就是真正開始寫程式了, 如果真正要寫的完整會要寫很多例外處理, 這邊只做簡單的版本, 請大家見諒喔!以下是我完成的程式碼 Function 我的加總(加總區域 As Range) As Double     Dim data As Variant     我的加總 = 0     For Each data In 加總區域         我的加總 = 我的加總 + data     Next End Function 在Excel中輸入如下圖的資料來測試 繼續在B8儲存格中點選 "插入函數fx" (如下圖) 出現Excel的函數視窗後, 函數類別選擇 "使用者定義" 會出現 "我的加總" 函數, 按下確定後選擇A1..A5的加總區域, 結果顯示15, 代表程式初步測試OK! 再來我將 B5 故意留空白, 也讓它加總看看, 手動將B8的 "A1:A5" 改成 "A1:B5" 結果顯示29(成功)! 再來是C1我故意輸入文字 "A", 讓它加總看看, 手動將B8的 "A1:B5" 改成 "A1:C5", 結果顯示 "#VALUE!", 如果要改...

為了讓我的 Windows 10 IIS 網站在全世界都能合法認證, 方便開發測試用, 我爬網了一下, 決定嘗試申請一個台灣人都能免費申請的 *.nctu.me 網域, 以及 SSL for Free 的免費 SSL 來測試第一步當然是將 Windows 10 的 IIS 安裝好, 起碼要在瀏覽器中輸入網址 localhost 看到本機網站的首頁, 才能知道 SSL 是否設定成功 第二步則是申請一個網域, 我是去台灣交通大學的 https://nctu.me 去申請的, 只要有台灣身分證的都能申請, 網站上也有詳細的註冊說明, 如下圖: 第三步則是申請一個公證機構的 SSL 認證, 我則是去 https://www.sslforfree.com 申請一個免費的 SSL 來測試用, 申請的步驟以及IIS設定, 我則主要是參考以下幾位的做法 https://ithelp.ithome.com.tw/articles/10222231 https://it-help.tips/iis-setup-ssl-certificate/ https://benit.pixnet.net/blog/post/61838887 最終我的步驟如下: (1) 先決定好要申請 SSL 的網址是甚麼, 假如我申請的交大網域名稱是 myname.nctu.me, 則我要申請的 SSL 網址可以是 https://www.myname.nctu.me 或是 https://myssl.myname.nctu.me 或是其他我喜歡的名字, 你可能會問如果我有很多個不是就要一個一個申請, 不能是 https://*.myname.nctu.me 嗎? 因為我申請的是免費的才會有這限制, 要錢的就不用這麼麻煩了! (2) 開啟 Windows 10 的電腦管理, 點擊IIS的伺服器憑證, 畫面如下 : (3) 點擊建立憑證要求, 畫面如下: (4) 輸入資料, 其中的一般名稱就是輸入之前我要SSL的網址, 其他我則隨便填, 畫面如下: (5) 過來加密長度選 2048, 如下圖: (6) 再來就是輸入憑證要求的檔案名稱, 我們可以將它儲存在 d:\myssl.txt 的檔案中(這檔案的內容是申請 SSL for Free 要用的), 如下圖 :...