xlam外掛製作實驗手冊

大家來和筆者一起做一個xlam外掛吧。很簡單，很詳細。

楔子

• excel支援自定義選單欄，但是我在搜尋如何製作選單欄，以及如何製作addin檔案的時候，即使是最好的例子，也只是點到為止，做了一個按鈕就結束了。想要再進一步，竟然就沒有合適的二手資料了。所以自然筆者就決定自己補充這樣一篇文章。並且以實驗手冊的形式，並且把自己的方法論也一併分享出來。

方法論

• 清單
  寫程式碼的時候，筆者一般是先寫草稿和註釋。然後把後續的一些困難也列進清單中。
  清單的作用一個是因為如果是比較基礎的工具，工程規模不大，很可能是自己一個人完成的任務，但是沒有一件事情是一條直線走到底的。所以為了執行，需要把任務扁平化，即使不能從最優的順序去執行，也不至於在執行的時候，被自己的其他思路干擾。而且困難可以暫時放著，甚至到後面選擇其他的方案。
• 單元測試
  由於excel的vba工程，既包含了介面，又包含程式碼，而且vba語言不是我們的主要工作程式碼，所以程式碼出現錯誤是很正常的。但是也沒必要去寫測試程式碼，根據筆者自己簡單有用4個字的原則，是邊寫邊測，測過的程式碼就認為是沒問題的。除錯起來bug也很少，能夠很快定位問題，bug一般在新寫出來的程式碼中。這樣就不需要到處找bug，也不會不知道用什麼測試用例來尋找bug。每一段新寫的程式碼就代表一個用例，這樣寫出來的程式碼，從結構上來說也方便維護，不太需要大幅的重構。
• 為什麼需要方法論
  做這樣的一次性的付出就可以得到成果的工作，過程和結果都是未知的。和重複勞動對應的穩定相比，哪個可以帶來快樂，哪個帶來焦慮。取決於知識和方法，零散的知識能支撐方法。實際還是要在實踐中採用正確的方法，得到未知的成果。

前提

基礎知識

• excel資料結構
  • vba的學習，直接看別人的程式碼和例子是不夠的。因為資料結構操作和vba語法混雜在一起，會讓人誤認為，這就是vba。
    excel的資料結構也是非常的簡單，Application -> Workbook -> WorkSheet -> Range，這樣的4層結構。所有要把這個資料結構操作的程式碼刨除出去，才是vba原來的語法的樣子。所以說對這些資料結構對應的類的操作的程式碼和vba的其他程式碼需要分開來看和理解學習。
  • aplication這個概念就相當於一個啟動的excel的程序。然後還有一些物件，其實都屬於application的下面的，比如Selection，ActiveSheet, ActiveWorkbook, ActiveWindow，方便我們和介面進行互動。

excel應用安裝

• 必須是安裝了excel，wps不能進行實驗。

需求

• 由於excel並不提供對文字框圖形中的文字進行檢索，（可能是因為其他種類的物件比如圖片等，型別太多了，沒有辦法全部開發出來，乾脆就不開發了。）所以就總感覺，我搜尋關鍵字的時候，文字框裡的內容搜不到，
  本來文字框可以自由的進行一些標註和說明的，搜尋不到，只能靠肉眼找非常累，降低效率成為了閱讀文件的阻礙。
  （實際上，筆者後面在開發的時候也遇到了一些問題，比如圖形還分為group物件，或者隱藏物件，或者是沒有文字的，沒有完全統一的資料結構，搜尋的時候直接報錯，或者搜尋到了不存在的東西，透過筆者的一些If判斷程式碼，也成功的迴避了這個問題）

實驗步驟

步驟概要

• 生成一個帶有選單欄的xlsm檔案（主要是編輯xlsm檔案中的custom/customUI.xml的內容）
• 把xlsm檔案另存為xlam，並載入進來。
• 開發xlam的vba的程式碼
• （如果需要重新調整選單欄的專案，取消xlam的載入，把xlam作為zip檔案解壓，重新修改選單欄內容後，再次壓縮成zip檔案，修改檔案字尾為xlam，重新載入xlam）

customUI.xml製作選單欄介面

• 由於有手工編寫程式碼的部分，筆者直接找了xlwings.xlam拿過來作為模版，這個在github/xlwings/xlwings中有。見本文【參考連結】

  • 第二種方法是自定義選單欄，然後匯出自定義的ribbon，結果筆者的電腦系統是mac系統，只能自定義，但是不能匯出。所以還是決定用xlwings.xlam作為模版，複製過來。內容都是一樣的，殊途同歸。
  • 第三種方法是比較正式的方法，是下載customUIeditor, 先建立一個xlam或者xlsm檔案，然後用這個editor開啟，然後在裡面先生成一個模版。然後編輯xml，然後儲存，然後把這個xlam載入進來，選單欄就出現了。然後開始開發vba程式碼。
    （但是這個editor是exe的，算了，而且模版的內容過於簡單，沒有xlwings模版豐富。這可能也是導致別人的文章裡面只有一個按鈕就點到為止的原因。因為editor裡面的模版就一個笑臉按鈕，一個按鈕真的，等於沒有用。但是編輯customUI.xml不用每次解壓和壓縮檔案了）
    下載連結見本文【參考連結】

• 首先生成一個新的xlsm檔案，然後修改名稱，從【圖形檢索.xlsm】，改為【圖形檢索.zip】，然後解壓成資料夾

• 解壓後的目錄結構如下（, 注意如果是在mac中，解壓工具裡面的配置是不是有隱藏檔案不解壓的設定，需要把所有內容完整的解壓出來。筆者直接用自帶工具解壓失敗。用其他壓縮工具能夠解壓。）

- rel
	- .rels
- docProps
- xl
- [Content_Types].xml

• （稍後，在後面的步驟之後，需要重新把這個資料夾壓縮成zip，然後修改檔案字尾為xlsm，然後開啟，看看是不是能夠正常開啟。）（筆者的檔案開啟後有修復資訊，然後重新儲存了一下）
• 然後我們把下載好的xlwings.xlam檔案也是修改字尾為zip,解壓開來，解壓後的檔案結構是這樣的。

- rel
	- .rels
- customUI
	- customUI.xml
- docProps
- xl
- [Content_Types].xml

• 對我們有用的是.rels 和customUI.xml。（如果看不見隱藏檔案.rels，mac系統請按shift + command + . 來顯示隱藏檔案。)
• 我們開啟.rels和customUI.xml （這裡如果沒有下載好xlwings.xlam檔案也沒有關係，因為我會給出所有需要的程式碼）
  • 在.rels檔案中新增一個relationship元素 <Relationship Id="customUItuxingjiansuo" Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="customUI/customUI.xml"/>
  • 整個檔案長這樣

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>
<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
<Relationship Id="customUItuxingjiansuo" Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="customUI/customUI.xml"/>
</Relationships>

• 然後我們按照.rels檔案中的customUI/customUI.xml的配置，建立一個資料夾customUI和檔案customUI.xml。檔案內容如下

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
	<ribbon startFromScratch="false">
		<tabs>
			<tab id="tuxingjiansuo" label="圖形檢索">
				<group id="udfGroup" label="檢索">
					<editBox id="keyword" label="關鍵字(key1;key2;key3)" onChange="setKeywords" sizeString="AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA" screentip="多個關鍵字用分號; 隔開。匹配方式為*?，非正規表示式匹配"/>
					<button id="search" label="檢索" imageMso="MacroPlay" size="large" onAction="searchShapeWithKeywordstuxingjiansuo" screentip="開始檢索下一個，只能按順序往後檢索"/>
					<checkBox id="book" label="檢索範圍：工作簿" onAction="pressBook" screentip="預設檢索當前工作表"/>
					<checkBox id="entire" label="完整匹配（開頭到結尾" onAction="pressEntire" screentip="每一個關鍵字需要完整匹配，而不是包含關係"/>
					<checkBox id="col" label="滾動到列" onAction="pressScrollToCol" screentip="檢索匹配到之後預設會跳轉到圖形的行"/>
					<checkBox id="case" label="大小寫敏感" onAction="pressCaseSensitive" screentip="預設大小寫不敏感"/>
					<checkBox id="nextReverse" label="上一個" onAction="pressPrevious" screentip="檢索當前結果的上一個"/>
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

• 這樣我們就完成了選單欄的編輯，這裡提一下，如果是2010版本，.rels檔案中新增的是<Relationship Id="customUItuxingjiansuo" Type="http://schemas.microsoft.com/office/2007/relationships/ui/extensibility" Target="customUI/customUI14.xml"/>
  • customUI14.xml檔案中的開頭用 <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
  • 這裡做這個實驗，這兩個版本都是可以的，沒有什麼影響。只是因為xlwings.xlam用的是舊版，所以直接複製過來，避免出錯排查問題。因為重點是先看一下自己定義的選單欄是否能正常顯示。
• 然後我們把所有的檔案重新壓縮成zip包，修改檔名稱字尾為xlsm。開啟檔案，檢視選單欄是否已經正常顯示。
  
• 如果選單欄【圖形檢索】可以正常顯示了。那麼我們的選單欄介面做完了。那麼接下來可以開發vba的程式碼。
  • 有兩個選擇，一個是繼續在xlsm上面繼續開發，那麼這個選單欄和程式碼都是專屬於這個檔案的，可以在這個xlsm檔案裡面測試功能。
  • 另一個選擇就是直接做成xlam檔案，然後作為addin載入進來，vba程式碼依然在原來的xlam檔案對應的vba工程中編輯，但是功能是在其他excel中進行測試。由於我們開發的這個功能是搜尋圖形，一定是搜尋的是每一個開啟的excel檔案自己的圖形包含的文字關鍵字，可以一步到位，另存為xlam檔案，然後載入外掛，因為外掛的程式碼其實是可以在載入之後隨時修改。
  • 這裡提示一下。xlam載入檔案需要放到一個比較穩定的檔案目錄下，因為檔案一旦刪除或者移動，下次開啟excel，就會載入失敗。

另存為xlam

• 另存為的方法不是直接修改檔名稱字尾的方法了。需要先開啟xlsm檔案，然後點選另存為，選擇xlam格式，然後儲存在一個長期穩定的資料夾下。
  
• 然後我們把原來的xlsm檔案關閉之後，開啟另外一個新建的excel檔案，這個時候選單欄【圖形檢索】就不見了。然後把這個xlam檔案載入進來。比如我這裡的檔名為圖形檢索v1.01.xlam。載入方法如下。
  • 點選工具 -> excel載入項， 然後點選瀏覽，開啟檔案 圖形檢索v1.01.xlam 並且顯示已經勾選上了。
  • 然後我們看一下，選單欄【圖形檢索】重新出現了。那麼就完成了本小結的步驟。然後進入下一節的vba程式碼開發。
    

vba程式碼開發

• 現在我們開啟著一個普通的xlsx檔案，選單欄【圖形檢索】已經顯示了。需要開發載入項xlam檔案的功能，我們首先進入vba工程介面
  • 按下 alt + F11 （如果您的筆記本的F11功能鍵，需要按下Fn + F11才能生效。那麼您需要按下 alt + Fn + F11)
    
• 然後我們看到了我們的xlam檔案，右鍵插入模組。然後把下面的【模組1】程式碼複製進去
  
• 然後再建立一個類模組，修改類的名稱為CLCond，如下圖，然後把【class ClCond】程式碼複製進去。然後command + s儲存工程檔案。切回excel介面就可以進行最終的使用了。（這裡先把筆者的程式碼直接複製進去，讓實驗進行下去）
  

' 模組1程式碼
Private conditions As Collection
Sub clearFound()
    getCond().found = False
    getCond().lastMatchIndex = 0
    getCond().lastShapeIndex = 0
End Sub
Function isCondiExists(coll As Collection, key As String) As Boolean
    On Error GoTo EH
    IsObject (coll.Item(key))
    isCondiExists = True
EH:
End Function
Function getCond() As ClCond
    If conditions Is Nothing Then
        Set conditions = New Collection
    End If
    If isCondiExists(conditions, Selection.Application.ActiveWorkbook.Name) Then
        Set getCond = conditions.Item(Selection.Application.ActiveWorkbook.Name)
    Else
        Set getCond = New ClCond
        conditions.Add Item:=getCond, key:=Selection.Application.ActiveWorkbook.Name
    End If

End Function
Sub setKeywords(control As IRibbonControl, text As String)
    '輸入完必須按下回車或者tab
    getCond().keywords = text
    Call clearFound
    MsgBox "關鍵字修改成功為: " & text
End Sub
Sub pressBook(control As IRibbonControl, pressed As Boolean)
    getCond().bookPressed = pressed
    Call clearFound
End Sub
Sub pressEntire(control As IRibbonControl, pressed As Boolean)
    getCond().entirePressed = pressed
    Call clearFound
End Sub
Sub pressScrollToCol(control As IRibbonControl, pressed As Boolean)
    getCond().colPressed = pressed
    Call clearFound
End Sub
Sub pressCaseSensitive(control As IRibbonControl, pressed As Boolean)
    getCond().casePressed = pressed
    Call clearFound
End Sub
Sub pressPrevious(control As IRibbonControl, pressed As Boolean)
    getCond().previousPressed = pressed
End Sub
Sub searchShapeWithKeywordstuxingjiansuo(control As IRibbonControl)
    Dim kws As String
    kws = getCond().keywords
    If kws = "" Then
        MsgBox "keywords empty, please change it" & Chr(13) & _
        "press Tab or Enter, keyword will be changed sucessfully"
        Exit Sub
    End If
    Dim wpSheet As Worksheet
    Dim hasResult As Boolean
    hasResult = False
    If getCond().bookPressed Then
        shSt = 1
        shEd = Selection.Application.ActiveWorkbook.Sheets.Count
        shSp = 1
        If getCond().previousPressed Then
            shSt = Selection.Application.ActiveWorkbook.Sheets.Count
            shEd = 1
            shSp = -1
        End If
        If getCond().found Then
            shSt = getCond().lastMatchIndex
        End If
        
        For j = shSt To shEd Step shSp
            Set wpSheet = Selection.Application.ActiveWorkbook.Sheets(j)
            hasResult = searchInSheets(wpSheet, kws)
            If hasResult Then
                getCond().lastMatchIndex = j
                Exit For
            End If
        Next j
    Else
        Set wpSheet = Selection.Application.ActiveSheet
        hasResult = searchInSheets(wpSheet, kws)
    End If
    If hasResult Then
    Else
        MsgBox kws & " not founed, or reach end" & Chr(13) & "please search again"
        Call clearFound
    End If
End Sub
Function searchInSheets(ByRef wpSheet As Worksheet, kws As String)
    searchInSheets = False
    Dim tShape As Shape
    spSt = 1
    spEd = wpSheet.Shapes.Count
    spSp = 1
    If getCond().previousPressed Then
        spSt = wpSheet.Shapes.Count
        spEd = 1
        spSp = -1
    End If
    If getCond().found Then
        If getCond().previousPressed Then
            spSt = getCond().lastShapeIndex - 1
            If spSt < 1 Then
                Call clearFound
                Exit Function
            End If
        Else
            spSt = getCond().lastShapeIndex + 1
            If spSt > wpSheet.Shapes.Count Then
                Call clearFound
                Exit Function
            End If
        End If
        Call clearFound
    End If
    
    For i = spSt To spEd Step spSp
        Set tShape = wpSheet.Shapes(i)
        If tShape.Visible = msoFalse Then
        Else
            If tShape.AutoShapeType = msoShapeMixed And tShape.Type = msoGroup Then
            Else
                If tShape.TextFrame2.HasText <> -2 And tShape.TextFrame2.HasText <> 0 Then
                If tShape.TextFrame2.TextRange.text <> "" And _
                isMatchingKws(tShape.TextFrame2.TextRange.text, kws) Then
                    wpSheet.Activate
                    tShape.TopLeftCell.Select
                    If getCond().colPressed Then
                        ActiveWindow.ScrollColumn = Selection.Column
                    End If
                    ActiveWindow.ScrollRow = Selection.Row
                    tShape.Select
                    searchInSheets = True
                    getCond().lastShapeIndex = i
                    getCond().found = True
                    Exit Function
                End If
                End If
            End If
        End If
    Next i
End Function
Function isMatchingKws(content, kws)
isMatchingKws = False
    Dim kwArr() As String
    kwArr = Split(kws, ";")
    For Each kw In kwArr
        If kw = "" Then
        Else
            matchKw = kw
            If getCond().entirePressed Then
                matchKw = kw
            Else
                matchKw = "*" & kw & "*"
            End If
            
            If getCond().casePressed Then
                If content Like matchKw Then
                    isMatchingKws = True
                    Exit For
                End If
            Else
                If UCase(content) Like UCase(matchKw) Then
                    isMatchingKws = True
                    Exit For
                End If
            End If
        End If
    Next kw
End Function

' Class ClCond程式碼
Public keywords As String
Public bookPressed As Boolean
Public entirePressed As Boolean
Public colPressed As Boolean
Public casePressed As Boolean
Public previousPressed As Boolean
Public lastMatchIndex As Integer
Public lastShapeIndex As Integer
Public found As Boolean

測試和使用

• 測試效果

  • 查詢 ??cd ，並選中和滾動到這個shape
    
  • 滾動到shape所在列
    

• 由於是實驗手冊，這裡僅描述一下開發和測試的過程。最後的功能展示，可以參考筆者的抖音同名賬號和b站同名賬號的演示。

• vba開發步驟

  • 首先除錯各個custom.xml的onChange, onAction的回撥函式。編輯editbox，按下button, checkbox，會成功呼叫模組1中的回撥函式
  • 然後先開發最基本的功能，單個sheet中的shape的檢索功能，按下檢索按鈕之後，能夠找到一個shape, 選中並滾動到這個shape的位置
  • 然後開發預設的【下一個】功能，在excel檔案中畫出兩個shape，能夠記錄下上一個查詢的shapes的序號，查詢到下一個shape並且選中
  • 然後開發【工作簿檢索】功能，我們把查詢shape的function方法提取出來，迴圈sheets，然後重用查詢sheet中shape的function。其中需要記錄上次查詢結果的sheet的序號
  • 然後開發【完整匹配】功能，完整匹配的時候，不需要在like條件的前後加上*星號。
  • 然後開發【滾動到列】，checkbox勾選上時，新增滾動到列的程式碼，其實就是ActiveWindow.scrollcolumn = 對應的列
  • 然後開發【大小寫敏感】的功能，大小寫敏感時，不需要對關鍵字和文字框內容進行UCase(keywords)的操作
  • 然後開發【上一個】功能，上一個的時候，我們需要把for迴圈的開始和結束進行對換，並且如果存在上次查詢結果的時候，shapes的開始需要進行減1
  • 其實Class來存結果是最後開發的，因為每一個開啟的book都是空的。而且暫時決定先不實現customUI.xml中的onLoad方法，所以程式碼中的內容不會更新到選單欄中。那麼需要進行選擇了。選擇一個選單欄在所有的檔案中都用同一套條件，還是用不同的條件，我選擇了用不同的條件，但是不管怎麼樣，查詢結果和條件都需要按照workbook為單位進行記錄。所以就把這些查詢條件和查詢結果記錄在Collection中了。

• 遇到的坑和bug，

  • 如果上一次的檢索結果剛好是sheet中的最後一個，那麼下一次檢索應該是直接在下一個sheet進行檢索。由於沒有清除檢索結果，導致查詢下一個sheet的時候，使用了上一個sheet的shape的序號作為開始，導致找不到shape。所以跳到下一個sheet進行檢索的時候，需要清除記錄下來的shape的序號。
  • 多條件查詢的分隔符，如果鍵入了兩個;;分號。那麼會有一個條件是“”，導致所有的條件都滿足。這其實也不算問題。

鎖定外掛加密vba工程

• 在後續的使用中，加密可以避免程式碼被自己不小心修改了，出了什麼錯也不知道。看了一下其他幾個官方外掛，預設都是鎖定的。
• 加密方法，同樣是右鍵工程，點選 VBAproject屬性...
  • 然後勾選上鎖定，填入2次密碼
    

參考連結

• github/xlwings/xlwings xlwings.xlam https://github.com/xlwings/xlwings/blob/main/xlwings/addin/xlwings.xlam
• customUIeditor下載地址 http://github.com/fernandreu/office-ribbonx-editor/releases/latest 往下翻，找到selfcontained版本，exe可以直接執行，不會缺少.net元件。

類似文章

• 中文customUI的製作 https://blog.csdn.net/fan_jinyong/article/details/4617136
  • https://blog.csdn.net/stone0823/article/details/115496320
• 英文bettersolution整理的網頁 https://bettersolutions.com/vba/ribbon/editbox.htm
  • https://bettersolutions.com/vba/ribbon/document-level-notepad.htm
• 搜尋引擎的參考的關鍵字 customUI ribbon vba editbox

後記

• 本文中已經都給出了所有原始碼，完全是足夠的。xlam檔案就不提供了，不提供後期維護。而且沒有最完美的程式碼，總會有一些修修改改。文章可以修改，xlam檔案一定會過時。
• 由於mac的excel中的vba工程編輯器，寫中文錯位，非常麻煩，所以後面的彈窗訊息，都用英語寫了。
• 正規表示式的功能，由於mac不支援windows的正規表示式的庫，所以就不開發了。而且正規表示式用於分析資料比較有用，用來搜尋，有些浪費。應該先收集資料，然後再用正規表示式分析，不應該偷懶。
• 這個工程的源頭是一個需求，而且基本上在50行程式碼就能實現最基本的需求，能夠短時間解決問題。才能促使筆者一步步的行動。其他的功能是一點點的順手完善的。不是一開始就有這麼完整的功能，保持簡單有效，進退自如。現在也不到200行程式碼。
• 本文作為實驗手冊，是分享實踐經驗和行業文化，而不是分享需要維護，可能會過時的成果。
• 如果做完了實驗，可以在評論區回覆完成。實驗手冊公開也是因為搜尋資料的時候，實在沒有比較有效的完整的資料，所以把本實驗手冊放在公開部落格中。實驗手冊中如果有諸多廢話，請多見諒。