[Office VBA] Source Control Solution for Excel example

2014-10-26

2773
0
VBA
2015-12-28

Office VBA IDE 並沒有跟直接跟程式碼管控(TFS、SVN、git)整合，我必須要用別的方式，上網找到的解法，就是把 VBA 匯出，也是最容易的

開始前，先確認 VBA 專案有參考 Microsoft Visual Basic for Application Extensibility 5.3

在 ThisWorkbook 裡加入程式碼如下：

    Dim outputFolderPath As String, outpitFilePath As String

    outputFolderPath = ThisWorkbook.Path & "\" & EXPORT_SOURCE_CODE_PATH & "\" 
    If Dir(outputFolderPath, vbDirectory) = vbNullString Then Exit Sub

    Dim element As VBIDE.VBComponent 
    For Each element In ThisWorkbook.VBProject.VBComponents 
        Dim componentName As String 
        componentName = element.Name

        Select Case element.Type 
            Case vbext_ct_StdModule 
                outpitFilePath = outputFolderPath & componentName & ".bas"

            Case vbext_ct_ClassModule 
                outpitFilePath = outputFolderPath & componentName & ".cls"

            Case vbext_ct_MSForm 
                outpitFilePath = outputFolderPath & componentName & ".frm"

            Case vbext_ct_Document 
                outpitFilePath = outputFolderPath & componentName & ".vba" 
        End Select 
        
        '程式碼行數大於1行再匯出 
        If element.CodeModule.CountOfLines > 1 Then Call element.Export(outpitFilePath) 
    Next element 
End Sub

程式碼相當的簡單，主要是調用 VBIDE.VBComponent.Export (element.Export)方法，將 Excel 裡的 VBA 倒出來，再依照不同的 VBA 類型分類，並給予不同的副檔名，分類方式我是參考 VBA IDE 匯入對話視雙，由下圖可得知，VBA 支援三種副檔名

*.bas：Module 程式碼

*.cls：類別

*.frm：使用者表單

預設 VBA IDE 沒有匯入工作表的程式碼，*.vba 是我自己取的名字

*.vba：工作表的程式碼

接著，在存檔的時後觸發匯出動作，當然，這種做法不見得適用你的情境，你不見得要這樣做

    SaveCodeModules 
End Sub

匯出的程式碼內容如下圖：

匯出的程式碼放在 ThisWorkbook 裡

VBA 程式碼匯出之後，便可以納入版本控制了，不論你是用TFS、git、SVN

匯出程式碼後，不見得要自動匯入程式碼，但我只是懶得手動匯入程式碼，工作表的程式碼甚至不能匯入，要用貼的，所以匯入程式碼的動作，個人是覺得有必要的

匯入VBA程式碼如下：

    Dim currentProject As VBIDE.VBProject 
    Dim element As VBIDE.VBComponent 
    Dim vbaCodeModule As VBIDE.CodeModule

    Dim importFolderPath As String

    Set currentProject = ThisWorkbook.VBProject 
    'Set currentProject = ActiveWorkbook.VBProject 
    'Set currentProject = Application.VBE.ActiveVBProject 
    'Application.VBE.ActiveVBProject.VBComponents 
Debug.Print "匯入：" & vbCrLf & "專案名稱:" & currentProject.Name & vbCrLf & "檔案名稱" & currentProject.Filename & vbCrLf

    importFolderPath = ThisWorkbook.Path & "\" & EXPORT_SOURCE_CODE_PATH & "\" 
    If Dir(importFolderPath, vbDirectory) = "" Then Exit Sub

    For Each element In currentProject.VBComponents 
        Dim componentName As String 
        Dim importPathName As String 
        Dim isFileExist As Boolean

        componentName = element.Name 
        Set vbaCodeModule = element.CodeModule

        Select Case element.Type 
            Case vbext_ct_StdModule 
                importPathName = importFolderPath & componentName & ".bas" 
                If Dir(importPathName) = vbNullString Then GoTo EndLoop    'continue 
                Call currentProject.VBComponents.Remove(element) 
                Call currentProject.VBComponents.Import(importPathName)

            Case vbext_ct_ClassModule 
                importPathName = importFolderPath & componentName & ".cls" 
                If Dir(importPathName) = vbNullString Then GoTo EndLoop    'continue 
                Call currentProject.VBComponents.Remove(element) 
                Call currentProject.VBComponents.Import(importPathName)


            Case vbext_ct_MSForm 
                importPathName = importFolderPath & componentName & ".frm" 
                If Dir(importPathName) = vbNullString Then GoTo EndLoop    'continue 
                Call currentProject.VBComponents.Remove(element) 
                Call currentProject.VBComponents.Import(importPathName)

            Case vbext_ct_Document 
                '匯入Sheet程式碼， 
                'VBComponents.Import 會將Sheet程式碼轉成cls型態，所以必須要自行處理

                importPathName = importFolderPath & componentName & ".vba" 
                If componentName = "ThisWorkbook" Then GoTo EndLoop     'continue 
                If Dir(importPathName) = vbNullString Then GoTo EndLoop    'continue

                Dim importComponent As VBIDE.VBComponent 
                Dim importCodeModule As VBIDE.CodeModule 
                Dim importSourceCode As String

                Set importComponent = currentProject.VBComponents.Import(importPathName) 
                Set importCodeModule = importComponent.CodeModule

                If importCodeModule.CountOfLines <= 0 Then GoTo EndLoop    'contiune

                importSourceCode = importCodeModule.Lines(1, importCodeModule.CountOfLines) 
                Call currentProject.VBComponents.Remove(importComponent)

                If importSourceCode = vbNullString Then GoTo EndLoop  'continue

                Call vbaCodeModule.DeleteLines(1, vbaCodeModule.CountOfLines) 
                Call vbaCodeModule.InsertLines(vbaCodeModule.CountOfLines + 1, importSourceCode)

        End Select 
EndLoop: 
    Next element 
End Sub

匯入程式碼，搜尋所有專案內的程式檔案，若參考資料來源有相同的檔案，砍掉原本的程式檔(currentProject.VBComponents.Remove) ，匯入新的程式檔(currentProject.VBComponents.Import)，整個過程相當的暴力、血腥，還來不及喊痛，程式碼就被改掉了

另外，ThisWorkbook、Sheet 裡的程式碼不能直接匯入(調用 VBIDE.VBComponents.Import)，在這裡我是先把原本的程式碼全刪(vbaCodeModule.DeleteLines)，然後再寫入(vbaCodeModule.InsertLines)我想要的程式碼，程式碼已經被我匯到 importComponent 物件裡，importCodeModule.Lines 便能取得特定行號的程式碼

匯入程式碼不能處理 ThisWorkbook 裡的程式碼，程式碼能運行，目前是靠 ThisWorkbook 物件，若能它自殺後又做下一個動作，我想我應該會嚇死

匯入程式碼調用的時機是在活頁簿打開的時候，我說過了，這很暴力，請小心使用