Excelの個人用マクロブックをテキストファイルでアップデートするマクロ

個人用マクロブックの標準モジュールをテキストファイルでアップデートしたいと思ったので、下記参考文献を元にマクロを書きました。コードは一番下です。


# 参考文献

* [VBAでVBEを操作する](http://officetanaka.net/excel/vba/vbe/index.htm)
* [[Excel] VBAコードの Export と Import](http://ooltcloud.expressweb.jp/201310/article_17224131.html)
* [Application.OnKey メソッド (Excel)](https://msdn.microsoft.com/ja-jp/library/office/ff197461.aspx?f=255&MSPPError=-2147217396)
* [マクロにショートカットキーを割り当てる−Workbook_Open](http://www.relief.jp/itnote/archives/001492.php)

# 環境

* Windows 10
* Excel 2013


# 概要

下記の固定パスからファイルをマクロとして個人用マクロブックにインポートします。
*  C:\Users\\**ユーザー名**\AppData\Roaming\Microsoft\Excel\XLSTART\Import

下記の固定パスに個人用マクロブックのマクロをエクスポートします。
* C:\Users\\**ユーザー名**\AppData\Roaming\Microsoft\Excel\XLSTART\Export

（ユーザー名は適宜読み替えてください）

# 動作

## インポート（１回目）

標準モジュール`testA`、`testB`が存在しない状態で、`testA.bas`、`testB.bas`を`ImportModulesToPersonal`マクロを実行すると、

![image](https://qiita-image-store.s3.amazonaws.com/0/36744/8db759a2-765c-1a26-456a-df57a2b8e310.png)

![image](https://qiita-image-store.s3.amazonaws.com/0/36744/cdbe4f2c-c3ee-0ce1-39a1-08cc60149c72.png)

標準モジュール`testA`、`testB`がインポートされます。

![image](https://qiita-image-store.s3.amazonaws.com/0/36744/0dea41ce-9224-f6ed-52d8-0df2c5b8f4a5.png)

## インポート（２回目）

標準モジュール`testA`、`testB`が存在する状態で、`testA.bas`、`testB.bas`を`ImportModulesToPersonal`マクロを実行すると、エクスポート先にバックアップが作成されます。

![image](https://qiita-image-store.s3.amazonaws.com/0/36744/e5d6ffda-f6c8-c5fa-7cce-a9d092030aee.png)


## エクスポート

`ExportModulesFromPersonal`マクロを実行すると、

![image](https://qiita-image-store.s3.amazonaws.com/0/36744/77276333-0fd3-b0b8-352c-321c56592ee4.png)

![image](https://qiita-image-store.s3.amazonaws.com/0/36744/0dea41ce-9224-f6ed-52d8-0df2c5b8f4a5.png)

個人用マクロブック内のマクロが全てエクスポートされます。

![image](https://qiita-image-store.s3.amazonaws.com/0/36744/4c2497f2-c2b6-37f4-c6e2-357df43ace70.png)


# コードを動かすまでの準備

「VBAプロジェクトオブジェクトモデルへのアクセスを信頼する」のチェックなど、いくつか準備をしないと動きません。

## 開発タブの表示

`{Alt}FT`の順にキーを押下するとExcelのオプションが開かれます。

![Alt](https://qiita-image-store.s3.amazonaws.com/0/36744/a284bc3f-0725-67a5-4e21-fca9921c8851.png)

![F](https://qiita-image-store.s3.amazonaws.com/0/36744/841f412f-db83-0d29-dcae-9e44ce5b595b.png)

「リボンユーザーの設定」で、メインタブの「開発」にチェックをつけて「OK」すると開発タブが出現します。

![T](https://qiita-image-store.s3.amazonaws.com/0/36744/ef6146a5-da07-051a-6e4b-99abe8d3ae47.png)

## VBAプロジェクトオブジェクトモデルへのアクセスを信頼する

`{Alt}LAS`の順にキーを押下すると「セキュリティセンター」が開かれます。

![AltL](https://qiita-image-store.s3.amazonaws.com/0/36744/d7ea79e6-7dbc-8935-e5c9-a2d37ca2393e.png)

「マクロ設定」の「VBAプロジェクトオブジェクトモデルへのアクセスを信頼する」にチェックをつけて「OK」すると、今回のマクロコードが動かせるようになります。

![ST](https://qiita-image-store.s3.amazonaws.com/0/36744/07347caa-1e93-51f2-64c7-d4badff89914.png)

## 個人用マクロブックの作成

`{Alt}LR`の順でキーを押下すると「マクロの記録」が開きます。

![06.png](https://qiita-image-store.s3.amazonaws.com/0/36744/df39fe0d-2892-9077-4272-55b4e95b42c9.png)

保存先を「個人用マクロブック」にして「OK」します。

![07.png](https://qiita-image-store.s3.amazonaws.com/0/36744/6f54c5b1-fccf-1c0c-8d96-e6191502d2fb.png)

`{Alt}LR`の順でキーを押下すると記録が終了します。

![08.png](https://qiita-image-store.s3.amazonaws.com/0/36744/5a7f6f15-22e3-49e0-ca18-1baa09e24cfa.png)

そのまま、Excelを閉じると個人用マクロブックを保存するか聞かれるので、「保存」すれば個人用マクロブックが作成されます。

![09.png](https://qiita-image-store.s3.amazonaws.com/0/36744/8c6c5c44-19b1-1229-1575-3b8ebb4fd214.png)

## コードのインポート

記事の一番下にある[コード](#コード)を`ModuleManager.bas`のようなファイル名で保存しておきます。

Excelで`{Alt} + {F11}`を同時に押下するとVBEが開きます。そこでPERSONAL.XLSBのVBAProjectを右クリックして「ファイルのインポート」を実行します。

![10.png](https://qiita-image-store.s3.amazonaws.com/0/36744/c67899bd-b216-cd05-721f-16890e619872.png)

保存しておいた、`ModuleManager.bas`を開きます。

![11.png](https://qiita-image-store.s3.amazonaws.com/0/36744/76ec8d13-f6aa-867a-6889-9a6aa9932354.png)

標準モジュールに`ModuleManager`が追加されます。

![12.png](https://qiita-image-store.s3.amazonaws.com/0/36744/fb86a1df-01e6-f548-59ae-0fbe960bf291.png)

ついでに、ThisWorkbookに下記のコードを追加して、Excelを開きなおすとショートカットキーが使えるようになります。

```vbnet
Private Sub Workbook_Open()
  ModuleManagerOnKey
End Sub
```

![13.png](https://qiita-image-store.s3.amazonaws.com/0/36744/11e7c906-c485-698c-537a-6954ee771a32.png)

* {Ctrl} + {Shift} + {Alt} + I : モジュールのインポート
* {Ctrl} + {Shift} + {Alt} + E : モジュールのエクスポート


## インポート／エクスポート用フォルダの作成

今回のコードはインポート元のフォルダもエクスポート先のフォルダも下記のパスで固定なので、そのパスに対応するフォルダを作成しておく必要があります。（ユーザー名は適宜読み替えてください）

* C:\Users\\**ユーザー名**\AppData\Roaming\Microsoft\Excel\XLSTART\Import
* C:\Users\\**ユーザー名**\AppData\Roaming\Microsoft\Excel\XLSTART\Export



# コード

`ModuleManager.bas`の名前で保存してインポートすれば使えるようになります。ただし、`ModuleManager.bas`は手動でインポートする必要があります。マクロで`ModuleManager.bas`をインポートすることはできません。

```vbnet
Attribute VB_Name = "ModuleManager"


Sub ModuleManagerOnKey()
  Application.OnKey "^+%i", "ImportModulesToPersonal"
  Application.OnKey "^+%e", "ExportModulesFromPersonal"
End Sub


Public Sub ImportModulesToPersonal()
  Set FileSys = CreateObject("Scripting.FileSystemObject")
  Set Project = ThisWorkbook.VBProject
  
  root = Environ("APPDATA")
  root = FileSys.BuildPath(root, "Microsoft\Excel\XLSTART")
  importDir = FileSys.BuildPath(root, "Import")
  exportDir = FileSys.BuildPath(root, "Export")

  fileName = Dir(importDir & "\*")
  While fileName <> ""
    Call ImportModule(ThisWorkbook.Name, FileSys.BuildPath(importDir, fileName), exportDir)
    fileName = Dir()
  Wend
End Sub


Public Sub ExportModulesFromPersonal()
  Set FileSys = CreateObject("Scripting.FileSystemObject")
  
  root = Environ("APPDATA")
  exportDir = FileSys.BuildPath(root, "Microsoft\Excel\XLSTART\Export")
    
  For Each Module In ThisWorkbook.VBProject.VBComponents
    Path = FileSys.BuildPath(exportDir, Module.Name)
  
    Select Case Module.Type
      Case 1
        Module.Export (Path & ".bas")
      Case 2
        Module.Export (Path & ".cls")
      Case 3
        Module.Export (Path & ".frm")
      Case Else
    End Select
  Next Module
End Sub


Public Sub ImportModule(ByVal BookName As String, ByVal Path As String, Optional ByVal exportDir As String = "")
  Set FileSys = CreateObject("Scripting.FileSystemObject")
  Set Project = Application.Workbooks(BookName).VBProject
  
  ModuleName = FileSys.GetBaseName(Path)
    
  If Exist(Project, ModuleName) Then
    If exportDir <> "" Then
      exportPath = FileSys.BuildPath(exportDir, ModuleName)
      exportPath = exportPath & "." & Format(Now, "yyyymmddhhnnss")
    
      Select Case Project.VBComponents(ModuleName).Type
        Case 1
          Project.VBComponents(ModuleName).Export (exportPath & ".bas")
        Case 2
          Project.VBComponents(ModuleName).Export (exportPath & ".cls")
        Case 3
          Project.VBComponents(ModuleName).Export (exportPath & ".frm")
        Case Else
          Project.VBComponents(ModuleName).Export (exportPath & ".bk")
      End Select
    End If
    
    Project.VBComponents.Remove Project.VBComponents(ModuleName)
  End If

  Project.VBComponents.Import Path
End Sub


Private Function Exist(ByVal Project As Object, ByVal ModuleName As String) As Boolean
  On Error Resume Next
  Name = Project.VBComponents(ModuleName).Name
  Exist = Not IsEmpty(Name)
End Function
```