Access VBAのコンボボックスのリストを動的に設定する

## 主旨

Accessのコンボボックスで、**AddItemメソッド**を使用しようとして若干つまづいたので、メモとして残しておきます。
わかれば簡単な内容です。

記事の末尾に、参考として「Excelファイルを開いてコンボボックスからシートを選択するソースコード」も貼っておきます。

## テスト用フォームの作成

とりあえず、フォームにコンボボックスを配置します。
![2021-01-24 151705.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/551412/fe7a9c2b-5f32-dd60-c315-35d4cc44d46d.png)
フォームの名前は`フォーム1`のままで、コンボボックスの名前は、`CmbBox`としておきました。

## VBAの記述

そして、Excel VBA の感覚で次のように記載します（フォーム1のクラスモジュールに記載します）。

```vb
Private Sub Form_Load()
    CmbBox.AddItem "東京"
    CmbBox.AddItem "神奈川"
    CmbBox.AddItem "千葉"
    CmbBox.AddItem "埼玉"
End Sub
```

フォームの立ち上げ時に処理する内容は、Excel VBAでは`Private Sub UserForm_Initialize()`に書きますが、Access VBAでは`Private Sub Form_Load`に書いておきます。

しかし、このコードでは、フォームを立ち上げると次のエラーが出てきます。

```
実行時エラー '6014':
このメソッドを使用するには、"RowSourceType/値集合タイプ" プロパティに値リストを設定する必要があります。
```
![2021-01-24 150655.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/551412/77a24e13-3e8e-72fd-3942-0e376297b38e.png)
ということで、エラーで指示があるように、`値集合タイプ（RowSourceType）`を`値リスト`に指定します。

```vb
Private Sub Form_Load()
    CmbBox.RowSourceType = "Value List"
    CmbBox.AddItem "東京"
    CmbBox.AddItem "神奈川"
    CmbBox.AddItem "千葉"
    CmbBox.AddItem "埼玉"
End Sub
```
1行目に`CmbBox.RowSourceType = "Value List"`を加えただけです。
フォームを立ち上げ直すと、AddItemメソッドが正常に実行されて、次のようにリストが登録されています（めでたし）。
![2021-01-24 152740.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/551412/26e3f258-c967-115b-3393-34d2728f4e69.png)

**＜参考サイト＞**
・[コンボボックスのドロップダウンリストのデータを設定する（値リスト）](https://www.moug.net/tech/acvba/0060002.html)
・["RowSourceType/値集合タイプ" プロパティ (Access)](https://docs.microsoft.com/ja-jp/office/vba/api/access.combobox.rowsourcetype)

## プロパティシートで設定する方法

なお、`値集合タイプ（RowSourceType）`の設定はプロパティシートからもできます。
次のように、プロパティシートの「データ」タブに「値集合タイプ」の項目があります。
![2021-01-24 153549.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/551412/ccb4ad22-7b97-c2a9-e6e9-52687e4a1397.png)
このところを、ドロップダウンリストから「値リスト」に変更するだけでも同じ結果となります。
![2021-01-24 153726.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/551412/75fdb561-57aa-2b2d-b042-9cdacd09d0c5.png)

## （参考）Access VBAからExcelファイルを開いてシートを選択する方法

本来やりたかったのは、Accessから「任意のExcelファイル」を開いて、「任意のシート」を選択してもらいデータを取り込むということです。
Excel VBAとは、異なるところがいくつか出てきます。

以下、Access VBAでExcelを開き、コンボボックスにシートを表示するまでのサンプルコードを貼っておきます。
個々の説明は、コメント部分を参照してください。

**＜フォーム画面＞**
![2021-01-24 154549.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/551412/91b65066-c548-648a-c92a-51e9afd55d92.png)

**＜各コントロールの名称＞**

|場所|コントロール名|コントロールの種類|
|---|---|---|
|ファイル選択ボタン|FileSelectBtn|コマンドボタン|
|クリアボタン|ClearBtn|コマンドボタン|
|ファイルパス（右の四角部分）|FileNameText|テキストボックス|
|シート選択（右の四角部分）|SheetCmb|コンボボックス|

**＜ソースコード＞**

```vb
Option Compare Database
Option Explicit

'フォームオープン時の処理
Private Sub Form_Load()
    SheetCmb.RowSourceType = "Value List" '値集合タイプを「値リスト」に設定
    FileNameText.Locked = True 'ファイルパス表示のテキストボックスを編集不可にする
End Sub

'ファイル選択ボタンのクリック時の処理
Private Sub FileSelectBtn_Click()
    'FileDialogオブジェクトでファイルを開く
    With Application.FileDialog(msoFileDialogOpen)
        .Filters.Clear 'Filtersコレクションはファイルの種類を指定（Clearで一旦全部解除）
        .Filters.Add "Excelファイル", "*.xls*" 'Excelファイルを指定（前半は表示名, 後半は拡張子指定）
        .AllowMultiSelect = False '複数選択を許可するか否か
        .InitialFileName = "C:\VBA" '最初に開くディレクトリを指定
        If .Show = True Then 'Showメソッドでダイアログボックスを表示（戻り値：開くボタン=-1, キャンセルボタン=0）
            FileNameText.Value = .SelectedItems(1) 'SelectedItemsプロパティはユーザーが選択したファイルパス一覧を返す
        Else
            Call FormReset 'フォーム内容を初期化（サブプロシージャ呼び出し）
            Exit Sub
        End If
    End With
    
    'Excelオブジェクトを取得
    Dim xlApp As Object: Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True 'Excelを表示
    
    '対象ファイルのオブジェクトを取得
    Dim wb As Object
    If IsFileOpened(FileNameText.Value) Then 'ファイルが開いているか否かを確認（関数を呼び出し）
        Set wb = GetObject(FileNameText.Value) 'ファイルのオブジェクトを取得（ファイルが開いている場合）
    Else
        Set wb = xlApp.Workbooks.Open(FileNameText.Value) 'ファイルを開いてオブジェクトを取得（ファイルが閉じている場合）
    End If
    
    Call ClearSheetCmb '一旦コンボボックスのリストを全部削除する（サブプロシージャ呼び出し）
    Dim ws As Object
    For Each ws In wb.WorkSheets 'wbオブジェクトの各シートを取得
        SheetCmb.AddItem ws.Name 'シート名をコンボボックスに追加
    Next
    SheetCmb.Value = SheetCmb.ItemData(0) '１つ目のシートを初期値に設定
    
    Set xlApp = Nothing 'Excelオブジェクトの開放
End Sub

'コンボボックスのリストを全部削除
Private Sub ClearSheetCmb()
    Dim i As Long
    For i = SheetCmb.ListCount - 1 To 0 Step -1
        SheetCmb.RemoveItem Index:=i 'RemoveItemメソッドで１つずつ削除（AccessではClearメソッドは使用できない）
    Next i
    SheetCmb.Value = "" '表示している値も削除
End Sub

'クリアボタンクリック時の処理
Private Sub ClearBtn_Click()
    Call FormReset 'フォームの内容を初期化（サブプロシージャ呼び出し）
End Sub

'フォーム内容を初期化する処理
Private Sub FormReset()
    FileNameText.Value = "" 'テキストボックスのファイルパスを削除
    Call ClearSheetCmb 'コンボボックスのリストを全部削除（サブプロシージャ呼び出し）
End Sub

'ブックが開かれているかチェックする(https://vbabeginner.net/check-if-the-book-is-open/)から拝借
Private Function IsFileOpened(fullPath As String) As Boolean
    On Error Resume Next 'エラーが生じても停止せず次の行から続行
    Open fullPath For Append As #1 'ファイルを開いてみてエラーが発生するか確認
    Close #1
    If Err.Number > 0 Then 'Err.Number→70:ファイルが開いている, 75:ファイルが存在しない（要注意）
        IsFileOpened = True
    Else
        IsFileOpened = False
    End If
End Function
```
結果として、ファイル名は`FileNameText.Value`で取得され、シート名は`SheetCmb.Value`で取得されるので、それを元に処理を続ければよいことになります。

**＜参考サイト＞**
・[エクセルの神髄 : ファイルダイアログ（FileDialog）](https://excel-ubara.com/excelvba1/EXCELVBA376.html)
・[ComboBox の removeitem メソッド (Access)](https://docs.microsoft.com/ja-jp/office/vba/api/access.combobox.removeitem)
・[フォームを開くときに、コンボボックスの値を選択（アクセスVBA)](https://oshiete.goo.ne.jp/qa/2999631.html)
・[Excel作業をVBAで効率化 : ブックが開かれているかチェックする](https://vbabeginner.net/check-if-the-book-is-open/)
・[アクセスVBAで既に開いているエクセルを閉じたい](https://oshiete.goo.ne.jp/qa/11079471.html)

以上