[Office VBA] Source Control Solution for Excel example

  • 2780
  • 0
  • VBA
  • 2015-12-28

[Office VBA] Source Control Solution for Excel example

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

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

image

 

在 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:工作表的程式碼

image

 

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

    SaveCodeModules 
End Sub

 

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

image

 

匯出的程式碼放在 ThisWorkbook 裡

image

 

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 物件,若能它自殺後又做下一個動作,我想我應該會嚇死

 

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

    'ImportCodeModules 
End Sub

 


文章出自:http://www.dotblogs.com.tw/yc421206/archive/2014/10/26/147094.aspx

範例程式:https://dotblogsfile.blob.core.windows.net/user/yc421206/1410/20141027913335.zip

若有謬誤,煩請告知,新手發帖請多包涵


Microsoft MVP Award 2010~2017 C# 第四季
Microsoft MVP Award 2018~2022 .NET

Image result for microsoft+mvp+logo