別のブックでユーザーフォームを起動する

作成元以外のブックでユーザーフォームを起動する方法について

Dim WithEvents Bkob As Workbook

Sub SetBook()
If Bkob Is Nothing Then
Set Bkob = zzzワークシート取得("テストシート").Parent
Else
Set Bkob = Nothing
End If
End Sub

Private Sub Bkob_Activate()
UserForm1.Show
End Sub

Private Sub Bkob_BeforeClose(Cancel As Boolean)
Unload UserForm1
Set Bkob = Nothing
End Sub

このコードは、ユーザーフォームを作成・保存しているブックのThisWorkbookモジュールの最上部にコピペしてください
※WithEventsは標準モジュールでは利用できません(エラーが発生します)

また、このコード中にzzzワークシート取得という関数を使用しています
これは開かれているブック全てのシートから引数に指定された文字列に完全一致するシートを取得する関数です
以下の記事にて公開・解説していますので、併せてそちらも確認・コピペしてください
コピペ先は本記事コードに連続して入力してもらって問題ありません

コードのコピーペーストの例
コードのコピーペーストの例

この画像のような感じで、連続してコピペしてもらって大丈夫です
もちろん関数の方だけ、標準モジュールに記載しても構いません
同じブック(プロジェクト)内であれば、どこでもOKです

使用場面

ユーザーフォームは作成したブックに依存しています
ユーザーフォームを開いた状態で、ブックを最小化するとフォームも一緒に最小化されます
また、別のブックをアクティブ状態にしたときはそのアクティブブックより背面に表示されます

ユーザーフォームを作成したブックではなく、別のブックで利用したい場面では通常の起動の仕方では出来ません

ブックのマクロの保存状態の確認
マクロの保存状態

この画像の様に、「TestBook.xlsx」にはマクロは保存されていません
そもそもマクロが有効なファイル形式ではありません
このブックに対してユーザーフォームを依存させたい訳です
そのユーザーフォームは「Wordpress.xlsm」に作成されています

よくある方法で、ブック自体を非表示にしてフォームだけを表示させる方法がありますが、この方法はExcel自体を非表示にすることで実現されます
この場合、表示処理を行う前に処理が強制終了した場合にExcelがバックグラウンドのプロセスに残ったままになってしまいます
かといって最小化をしてもフォームも引っ張られて、一緒に非表示になります
また、ウィンドウの位置をディスプレイ画面の外側に移動させたりする方法もありますが、これも上記同様で元に戻すコードの実行が必須になります

なによりこれらすべてに共通するのが、別のブックをアクティブにした時点でフォームは背面表示となり、なんの意味もなくなってしまう点です
この場合にはWinAPIによる画面の表示順の調整が必要になります
これは現在ではBit依存による対応が必要である点とMsgBox関数等のメッセージの前に表示されていた場合は操作不能のような状態になってしまいます

そこで利用するのが、例コードの流れになります
まずWithEventsを利用して、別のブックを取得させます
そのブックのActivateイベントでフォームのShowメソッドを実行します

こうすることにより、ユーザーフォームの依存先を処理作成のブック以外のブックにすることができます
処理自体は処理ブックに保存されていますが、実際にフォームを起動して処理を行うブックが別で指定できます

依存先が対象のブックになっているので、そのブックの最小化やアクティブ状態を共有することが出来ます

コード解説

この方法はWithEventsを理解することが必要ですので、理解できていない場合は以下の記事を確認してください

Dim WithEvents Bkob As Workbook

モジュールレベルの変数宣言を行っています
ここでWithEventsを利用することで、このオブジェクトにイベント処理を作成することが出来るようになります

通常WithEventsを利用する場合はクラスを使用しますが、このコードの様に代入する対象が1つと確定している場合は特に必要ありません
ただ標準モジュールには記載することが出来ないので、ユーザーフォームやThisWorkbookなどのオブジェクトモジュール内に作成します

この1行により、後続のイベント処理が有効になります

Sub SetBook()
If Bkob Is Nothing Then
Set Bkob = zzzワークシート取得("テストシート").Parent
Else
Set Bkob = Nothing
End If
End Sub

このプロシージャは、宣言した変数への代入と解放を行う処理です
オブジェクトが取得済みかどうかで分岐処理を行っています

単純に、取得されていれば解放して、取得されていなければ取得を行うものです
この取得処理を行う際に、別ブックのシートを指定することで処理ブック以外をこの変数に代入させることが出来ます

なお、取得関数に関しては記事に解説がありますのでここでは割愛します
この関数はWorksheetを返す関数で、取得を行いたいのはブックなので、Parentプロパティを使用して返されたWorksheetを含むブックを取得しています

このプロシージャをボタン等によりユーザーのタイミングで実行できるようにします
と、いうのもこの処理では対象のブックが存在する必要があります
ですが、イベント等の自動化を行ってしまうと、その取得のタイミングで対象ブックを開いていない可能性があるからです

Private Sub Bkob_Activate()
UserForm1.Show
End Sub

作成した変数のActivateイベント処理です
ここで指定のフォームを起動しています

Activateイベントなので、別のブックからこのブックへフォーカスが移動してきたときに発生することになります
このことからも処理用ブックで一度ボタン等の処理実行を行うことで、自然な流れで対象ブックでフォームを起動することが出来ます

また、ここでは割愛していますが、Showメソッドがアクティブになるたびに実行されると、表示位置が初期状態の場所に移動します
入力内容が失われることは無いので、気にしなければ大したことではないのですが、表示位置がリセットされることが気になる場合は、ここの処理でフォームが起動されているかの判定処理を入れておけばいいです
判定処理に関しては以下の記事を確認してください

Private Sub Bkob_BeforeClose(Cancel As Boolean)
Unload UserForm1
Set Bkob = Nothing
End Sub

作成した変数のBeforeCloseイベントです
ブックが閉じられる前に発生するイベントですが、この対象ブックが閉じられるということはフォーム表示処理が不要になることになるので、ここでフォームの終了と変数の解放を行っています

変数の解放は、処理用ブックでも行っているので2Way仕様ということになります
この解放は、処理用ブックの変数であるため、対象ブックを終了しても自動的に解放されることはありません
必ず明示的に解放を行っておきましょう

あと、当然ですが処理用ブックを閉じた場合はフォームも強制終了して処理の実行も不能になります
処理の仕様上あまり注意が必要とは思いませんが、間違って終了してしまわないように組み上げてください

使用するメリット

マクロ処理を作成していないブックに対して、自由に処理をユーザーが任意のタイミングで実行できることが最大のメリットだと思います

例えば同じフォーマットのブックに全てにフォーム処理を作成するのではなく、処理用ブックを1つ作成してそれを開いてから作業ブックを開くようにすればメンテナンス性も大きく向上します

また、作業用ブックにはマクロを保存する必要が無くなるのでマクロ無効の通常ブックとして扱うことが出来ます
場合によってはマクロを保存してはいけないブックも会社規定により存在するかもしれません
そんな場合でも、ユーザーフォームを利用した処理が利用できます

さらに、フォーム等の処理のメンテナンスを行った際に作業用ブックを上書きする必要が無いため、データの上書きを気にする必要もなくなります
そもそも処理用ブックにはデータが存在しないからです

今回の例では特定のブックに対しての依存先変更でしたが、WithEventsの変数をシート型にして特定のシートでのみ起動されるフォームとすることも可能です

Excel間でのフォームの表示問題なら、この方法で対応可能です
別のアプリ間での表示問題になると、やはりWinAPIを利用するしかなくなりますが、それはまた別の話になります

ユーザーフォームの依存先の変更は、比較的応用の幅が広いと思います
マウスホイールが利用できない等、使い勝手が少し時代遅れになってきたユーザーフォームですが、まだまだ使える場面が多いですね

最後に実際にこれらの処理を組み込んだ場合の動きの動画になります
コード解説が行われているわけでは無いので、参考程度に確認してください

コードを適用したときの実際の動きの動画です

ユーザーフォームの起動確認

ユーザーフォームの起動(読み込み)状態を確認する方法

If UserForms.Count <> 0 Then
'読み込みされたフォームの1番目のCaptionプロパティを取得
Debug.Print UserForms(0).Caption
Else: End If

ユーザーフォームの起動確認には、UserFormsコレクションを利用します
このコレクションは読み込みが実行されているユーザーフォームのコレクションになります
読み込みとはメモリ上に読み込みをされた状態なので、Showメソッドだけではなく、Loadメソッドが実行されて読み込まれた時点でコレクションに収まります

よくある起動確認でVisibleプロパティを利用することがありますが、これはあくまでも表示状態を表すプロパティですので、起動確認の目的では少しイメージが違います
なにより、ユーザーフォームのプロパティを取得しようとすると読み込みが実行されてしまいます
プロパティを取得したかっただけなのにInitializeイベントが実行され、処理を作成していた場合は実行されます
意図しないタイミングでフォームが読み込みを行う形になってしまいます

この状態でShowメソッドを実行しても、非表示状態から表示されただけの動きになってしまうので、非常に注意が必要です

そのため、フォームの起動確認にはUserFormsコレクションを利用してください
上記にあるようにこのコレクションは読み込みが実行されたものだけを対象としているので、Showメソッド含めて読み込みされていないフォーム判定が可能です

もちろん対象フォームのプロパティを取得するわけではないので、勝手に読み込みされることもありません

基本的に例コードの様にCountプロパティを利用する場面が多くなると思います
複数のフォームを同時起動して、かつその中でどのフォームだけの起動確認を行う場合は結構特殊な場面と思います

ほとんどは1つのフォームの起動確認を行う場合が多いです

コード解説

If UserForms.Count <> 0 Then

~~中略~~

Else: End If

IF文により判定を行います
Countプロパティは読み込まれたユーザーフォームの個数を返します
なのでこのプロパティが1以上を返せば、何らかのユーザーフォームが読み込まれていることが確認できます

上記にもあるように、ほとんどの場合は1つのユーザーフォームの起動確認を行う場面が多いので、このインデックス番号のフォームのプロパティを取得して判定を深堀りする必要は無いでしょう

'読み込みされたフォームの1番目のCaptionプロパティを取得
Debug.Print UserForms(0).Caption

取得が必要な場合は、ユーザーフォームの各プロパティをそのまま利用できます
インデックス番号は0から始まるので、0は確定で存在します
またCaptionプロパティや前述のVisibleプロパティも取得を行えます

実際の使用について

フォームを起動せずにコードを実行したときの動き
フォームを起動していない場合

この画像の動きはフォームを起動せずに実行した場合の動きになります
読み込みを実行していないため、Countプロパティが返す値は0となるためElseの処理が実行されます
今回は何も処理が無いので、何も起こりません

フォームを起動した状態でコードを実行したときの動き
フォームを起動した状態の場合

次にフォームを起動した状態でコードを実行しています
この状態であればCountプロパティは1を返すので、イミディエイトウィンドウに読み込みされたユーザーフォームの1番目(インデックス番号0)の表示文字列が出力されます

1つのユーザーフォームの起動確認を前提とすれば、Countプロパティが0か1かを判断すれば十分です

0なら起動されていない、1なら読み込みが実行されている
1で起動しているならVisibleプロパティで表示状態を確認しても良いでしょう
すでに読み込みされているので、プロパティを取得しても問題はありません

あとはこのIF分岐によって処理を組み込めば良いだけになります

主にこの判定処理に関しては、ユーザーフォームを自動的に起動・終了をさせるイベントや処理中にイベントの発生を容認する必要があるけど起動・終了イベントに影響が出てしまう等々の場合にユーザーフォームが起動されていれば、不要な処理は記事コードによって行わないようにする
そんな場合に使用します

最も多い場面が、自動終了させたい場合かもしれません
ExcelVBAの仕様で、Unloadステートメントを実行した場合に対象のユーザーフォームがそもそも読み込みをされていない場合Unloadという目的を達成するために、一度わざわざ読み込みを実行してからUnloadを行います

起動処理や終了処理が無い場合や、処理時間が一瞬なら大して問題は無いのですが、そうでない場合はユーザーにとっては非常に煩わしいです
注意が必要な運用方法です

データベースにSQL文を実行する(ADODB.Connection)

データベースに対してSQL文を実行するExecuteメソッドについて

'テーブルを作成するSQL文を実行する
DBCone.Execute "CREATE TABLE テーブル(ID Long,日付 Date)"

データベースにSQL文を実行するには、Executeメソッドを使用します

このメソッドを使用する場合は、レコードの取得を行わないSQL文にする必要があります
UPDATE文やINSERT INTO文などになります

レコードを取得したり、関数の結果を取得したりする場合はADODB.Recordsetオブジェクトを利用します

引数にはSQL文を1行の文字列にして指定することで、その構文が実行されます
SQLでも構文の改行がありますが、VBAから実行する場合は改行は無効となりますので1行の構文を作成するようにしてください
なお、このメソッドの実行前にデータベースへの接続を行っておく必要がありますので注意してください
接続に関しては以下の記事を確認してください

ExcelVBAとしてはメソッドの実行を行うのみになりますが、引数に指定するSQL文は別の言語なので様々な指定を行うことが出来ます
今回の例コードではテーブルの作成を行っています
その際フィールドを2つ作成しています

実際、このコードをそのままコピペして使用することはほぼ出来ません
SQL文の内容が実際の使用場面と全く違うからです

ここではSQLの実行方法の解説なので例コードにあるSQL文についての詳しい解説は割愛させてもらいます

このメソッドの大きな利点は、データベースを丸ごと処理できることにあります
UPDATE文を使用すれば、データベース全体に編集を一括で行うことが出来ます
また、テーブルやビューの作成・削除などもこのメソッドから行います

ExcelVBAでデータベースを操作する場合は、テーブルの作成が必要です
テーブルの作成を行わずにレコード操作を行うことも出来ますが、それはまた別の話になってきます

ただ、問題があるとすればリアルタイムで処理の動きを確認できないところです
VBAのデバッグの様に、1行ずつ確認しながら作業を行えませんので、SQL文がどのような結果をもたらすかを最低限の範囲でイメージできていないと、データベース操作は難しく感じます

また、AccessSQLも有名で書籍もあふれているものではありません
ほかのSQLで使用できるものが使用できなかったりすることもありますので、実際に動きを確認しながら操作を進めるようにしてください

ちなみにINSERT INTO文はVBAから行うと非常に低速なので、ほぼ使うことはありません
ADODB.Recordsetオブジェクトを利用する方が比較対象にならない程に早いです

テーブルへの配列一括出力時の注意点

テーブルへの配列データの一括出力を行う際の要注意点について

テーブル機能は範囲を指定することが容易なので、シートの最下行や指定の列を必死に探していたころより遥かにデータの挿入が楽になりました
なので、最近よく使用する機能なのですが、つい先日ハマってしまったエラーに関する内容を記事にしておきますので、同じような処理を検討している場合は注意してください

結果から言いますと、テーブル内にテーブルの列名等を使用した数式を一括出力するとエラーが発生します
これはテーブルの列名等の数式は見た目は全く同じ数式になるのですが、その内部にはテーブルの指定セルが含まれているようです
そのため、テーブルに一括出力する際にまだ行作成が行われる前にその参照をしてしまいセルの未存在の実行時エラーが発生します

テーブルにテーブルの列名を指定した数式を代入させた場合のエラーの発生時の動き
エラーが発生するときの動き

この画像の動きを確認してください
この処理では選択範囲のセルデータを配列に代入させて、その配列データをテーブルに一括出力している動きです
配列データの3列目は、1列目と2列目の値の一致判定を行う数式が入力されています
画像を確認してもらえばわかるように、出力する数式文字列は全て全く同じです

しかし、上述のようにこの数式が入力されて再計算が行われる方がテーブルの行作成より先に行われています
そのため、出力時に「実行時エラー ‘1004’:アプリケーション定義またはオブジェクト定義のエラーです。」が発生します

画像の出力後のデータを確認してもらうと分かるように2行目の数式セルまでは入力が行われています
2行目の数式セルを入力しようとしてエラーになっていることが、この結果からもわかります

さらにこのエラーの発生後に、なぞの現象として1行目のデータが無いことになってしまっています

エラー発生後のおかしな現象の状態
削除が出来ない現象

正常に出力されているはずの1行目が、なぜか存在しないことになって削除が実行できなくなっています
もちろんデータが無いことになっているので、この状態でテーブルのオブジェクトのListObject.DataBodyRangeプロパティでデータを削除しようとするとエラーが発生します
データが無いため、DataBodyRangeプロパティが存在しないためです

この場合は、このプロパティを使用せずに処理を組んでおくか、もしくは手動操作にて行の挿入を一旦行ってください
すると空白行が1行目に挿入されて表示されているデータは消滅します
とはいえこのエラー対策を行っておけば、ここは必要ありません

解決策として

この場合の解決策は非常に単純で、行を作成してから数式を出力するようにすればいいだけになります

この場合の方法としては2つあると思います
ListRowsオブジェクトのAddメソッドで必要行数の空白行を作成してから配列を出力する方法と、そもそも数式を配列に含めず後で入力する方法です

テーブルの既定の動作として、数式を1行目に入力すると列全体に反映されます
つまり1セルだけ数式を入力すれば良いだけなのです
なので、後者の方法の方が圧倒的に簡単です
前者の方法はAddメソッドがあまり早くないので、どうしても必要な場合に選択してください

テーブルにテーブルの要素名を使用する場合の処理の流れ
エラーの発生しない動き

今度は配列に代入させる選択範囲は2列目までの範囲としています
その範囲の配列データをテーブルに一括出力します

その後3列目のデータ行1行目のセルに数式を入力しています
ただこれだけで3列目は数式が列全体に反映されます

このエラーはかなり苦戦しました
なんせ、出力先のテーブルの範囲も正常にAddressプロパティで取得できるし、配列データもテーブル以外の範囲に出力させると問題なく出力されます
配列データもそれなりの列数があったので、細かく1列ごとに確認をしていない状態でとにかく出力先のセル指定が間違っているのだろうとばかり思っていました

何よりこの処理を作成する直前に、全く同じ処理の流れで別の配列データを出力させていてそちらは全く問題なく、コードの流れも同じだったのも混乱を招きました
もちろん直前の配列には数式は含まれていませんでした

原因が分かってしまえば、対策にしてもどうと言うことは無いのですが、Web検索でエラー内容の検索をしてもセルの指定間違いという内容が多かったので自分で気づいたのは翌日のことでした

数式の計算が行の作成より先というのは盲点でした
ちなみにApplication.Calculationで計算を手動にしてもエラーを回避できませんので悪しからず~

RGB関数と色の指定について

色の指定を行うためのRGB関数と色の定数について

'色数値を返す
Debug.Print RGB(111, 222, 333)

色の指定に関しては、VBAでは赤・緑・青の3原色を0~255の数値でそれぞれを指定することで表現されています
これ自体は特別なことではないとは思いますので、イメージは出来ると思います
0を含むので合計256の指定となります、内部数値ではその乗算数値になります

色の指定に関して、最も簡単なのがRGB関数を利用する形です
この関数は引数にそれぞれ上記で記載した数値の指定を行うことで色の数値を返す関数です

色の表現は整数値で表されており、赤だけなら255という数値、緑なら32768という数値で表現されます
しかし、この数値自体を理解して利用するのは現実的ではないので関数を利用します

また関数以外の指定方法として定数を指定することも出来ます
先ほどの緑なら、定数は「rgbGreen」として登録されています
定数なので微妙な色はイメージしづらいですが、緑ならGreen、赤ならRedという様に英単語を少し理解していれば比較的イメージしやすい定数となっています

定数の一覧は以下のリンク先に一覧があります
Microsoft Docs-XlRgbColor 列挙 (Excel)

固定した色なら定数の方が便利です
定数に無かったり、微妙な色合いやユーザー設定などで色の変化をさせるにはRGB関数を使用します

RGB関数自体も、その関数名の並び順に引数が指定できるので、引数を忘れてしまうことも少ない扱いやすい関数です
Red、Green、Blueの順番に引数数値を指定すればいいだけですね

Excel2007以降は色の幅が大きく広がった事もあり、原色だけを使っているフォーマットも少なくなってきました
そういった微妙な色合いを有効に活用して、見栄えの良いフォーマットやユーザーフォームを作成してみてください

GetSpecialFolderメソッド

GetSpecialFolderメソッドは特殊フォルダのパスを取得するメソッドです

'ローカルのTmepフォルダパスの取得
Debug.Print bhFSO.GetSpecialFolder(2)

特殊フォルダのパスを取得するには、「GetSpecialFolder」メソッドを使用します
引数に指定する数値によって取得できるフォルダが変わります

主な使用場面は、Tempフォルダへの一時ファイルの作成やネットワーク上サーバーへのアクセス回数を減らすために一時的にローカルにファイルを保存したりする場合にも利用できます

メソッドの書式

引数(太字は必須引数)
GetSpecialFolder (folderspec)

folderspec」は取得するフォルダの種類の指定になります
必須項目で0~2のどれかを指定します

0はWindowsフォルダ、1はSystemフォルダ、2はTempフォルダをそれぞれ取得します
このメソッドはPC環境によって、絶対パスが変化する部分に動的に対応して確定したフォルダを指定することが出来ることに優位性があります

実行時バインディング全てにおいての注意点ですが、メソッドの引数には定数は使用できません
定数の設定は事前バインディングを行ったときにしか有効ではありません

指定値2は「TemporaryFolder」という定数を指定することで表現されますが、これは実行時バインディングでは使用できないので内部数値の2で指定するようにしてください

実際の使用例

実際にこのメソッドを利用する場面が多いのはTempフォルダの利用時だと思います

Tempフォルダとは、Tempファイルを保存するための専用のフォルダです
Tempファイルとは一時ファイルという、処理中に一時的にデータを退避させたり、記録しておいたりする目的で使用されます

実際別にこのフォルダを使用しなくても、処理のあるブックのパスを利用して相対的に処理する方法でも問題は無いのですが、実務として少し難点がある場合があります
それが前述した、ネットワークサーバー上にブックがある場合です

この場合にブックの保存や、同じ場所に処理で作成したファイルを大量に保存する場合などは保存のたびにサーバーとの交信を行いますので、ネットワーク状況やファイルのサイズ次第では保存待ちにより処理の冗長化が引き起こされます

その場合の解決策として、処理中はいったんローカルに保存して処理を行い
処理が完了してからブックや、処理で作成したファイルを一括でサーバーに移動させます
こうすることでサーバーとのやり取り回数を減らすことが出来るので処理を早めることが出来ます

この方法は、保存に時間がかかればかかるほど有益なので、検討してください
ちなみに、VBAの標準メソッドのブックのCopyメソッドは開いているブックに対してはエラーが発生するので、その場合はFSOのCopyメソッドを使用します

また、これに関連していますが、開いているブックをSQLで読み込みをする場合にもこの一時ファイルの作成が必要になります
ブックでSQLを利用する場合には、開いているブックを使用するとリンク切れを起こすことが多々あります
その場合には、ブックを複製してSQLを接続する必要があります
ブックのSQL操作では、削除などが行えないので複製で十分対応できると思います

FSOでは3つのフォルダを指定することが出来ますが、WScript.Shellを利用すればデスクトップなども取得可能です

データベース取得用オブジェクトについて(ADODB.Recordset)

Accessデータベースからレコードデータを取得する際に大にゅさせるオブジェクトの宣言と使用について

'操作用オブジェクトの作成
Dim zzDB_Rec As Object
Set zzDB_Rec = CreateObject("ADODB.Recordset")

'レコード操作処理

Set zzDB_Rec = Nothing

データベースの操作にはSQLを使用する方法と、VBAのオブジェクト利用(以下、ADO)を使用する方法の2通りがあります
どちらの操作方法を使用するにしてもレコードを取得する場合はオブジェクトの作成が必要になりますので例コードを使用してください

データベース操作なので、基本的にADOで出来ることはSQLで可能です
なので操作することだけを考慮すればSQLが優位ですが、実際のVBAでのコードの分かりやすさと単体レコードでの処理速度はADOが優位です
なので、どちらが完全に優位というものではありませんので、状況に合わせて使用を切り替えるようにしてください

切り替える基準の分かりやすい点として

  • 単体のレコード操作を行う場合はADO
  • 複数のレコード操作を行う場合はSQL

と認識して使用を切り分けてください

例えば、ワークシートにあるデータをデータベースに登録する場合に一括で登録することは出来ませんので1レコードずつ登録を行います
この場合にはADOを利用してください

また、データベース上の特定の文字列を置き換えしたい(システム名を正式名称に置き換えなど)の場合には一括で操作を行えるSQLを利用します

結果的にレコードが1つだったとしてもデータベースからのSELECT文はSQLを利用する方が便利です、厳密にはデータベース全てのレコードを操作することになります

例コードはレコードを代入させるためのオブジェクトになります
つまりレコードを取得する必要のない処理であればこのオブジェクトは不要です
例えば、UPDATE文やDELETE文などデータベースのレコードを編集するだけで、そのレコードを返さないような処理の場合です
この場合は、レコードのオブジェクトは不要で、接続用オブジェクトにSQLを実行してもらう形になります

データベースにテーブルを作成する(ADODB.Connection)

作成したデータベースにテーブルを作成するコード

'テーブルの作成
zzDB_Con.Execute "CREATE TABLE テーブル名(ID Long,日付 Date,コード String(20),金額 Currency)"

接続しているデータベースにテーブルを新規作成するには、SQL文のCREATE TABLE文を使用します

この構文を実行することで、レコードを収めるためのテーブルが作成されます

このコードはデータベースへの接続が完了している必要があります
接続に関しては以下の記事を確認してください

テーブルとはExcelでいうシートのようなもので、これが無いとデータベースにレコードを登録することが出来ません

また、1度作成してしまえばそこにレコードを保存していくので処理中に頻繁に出てくるような構文ではありません

また接続しているデータベースにSQLを実行するにはExecuteメソッドを利用します
引数にSQL文を文字列で指定します

Accessの無いPCであれば実際にデータベースの中身を確認できないので、このコードを実行しても何が起きているのか分かりませんが、再度実行してみてください
実行時エラーが発生し、すでに存在していることが分かります
とりあえずはそれで確認は十分です

コード解説

zzDB_Con.Execute "CREATE TABLE テーブル名(ID Long,日付 Date,コード String(20),金額 Currency)"

CREATE TABLE文にはまず、テーブル名を指定します
ここでは「テーブル名」という名称が指定されています
テーブル名という名前のテーブルを作成する、という意味になります

次に半角カッコで挟む形で作成するフィールドの指定を行います
フィールドとはExcelでいう列のことになります

AccessデータベースはExcelとは違い列は作成しなければいけません
またExcelと大きく違うのがフィールドには入力できるデータの型を指定する必要があることです
Excelはどんなものでも入力できるセルがありますが、どんなものでも入るのは便利な反面、何が入力されているかをいちいち判定する必要があり、ユーザーの入力ミスも往々にして発生します
そういったことを回避するためにデータベースでは事前に入力できる制限を設けることが出来ます

制約というものになりますが、今回は型の紹介だけ行っておきます

(ID Long,日付 Date,コード String(20),金額 Currency)"

まず、IDという名称のフィールドを作成します
このフィールドの型はLong型なので整数型になります
実際AccessデータベースにLong型は存在しません
ですが、自動的に同意義の型に変換してくれているのであまり気にしなくても良いです
さすが同じOfficeソフトです

つまり、このフィールドは整数値のみが利用可能で文字列は登録できないことになります

次に日付というフィールド名でDate型(日付型)を作成しています
日付型なので時間等も含めて登録することが可能です

次にコードという名称で文字列型のフィールドを作成しています
フィールドを複数作成する場合は、フィールドごとに半角カンマで区切ります

これは無くても構わないのですが、(20)という文字が追記されています
これは文字数制限になります
これ以上の文字列を登録しても切り捨てられて、この文字数で登録されます

最後にCurrency型(金額型)の金額というフィールドが作成されます
金額型なのでお金のデータを登録する際に利用します
ここら辺の型の細かいところは、変数に関する記事を確認してください

ExcelVBAで使用できる変数の型はほぼ利用できます
Double型、Boolean型なども利用可能です

ここまで来たらようやくレコードの登録が可能になります
その際にテーブル名とフィールド名を使用しますので、Accessの無い方は分からなくならないようにしてください

ExcelVBAにおけるレコード操作について

ExcelVBAで利用するADOオブジェクトとSQLの併用について

ExcelVBAではデータベース操作の基本であるレコード操作がオブジェクトを利用することで編集することが出来ます
また、このオブジェクトを利用せずにSQL文を利用してレコード操作を行うことも出来ます

どちらも同じ結果を導くので、どっちゃでもええのですが操作内容によっては処理速度に大きな差が出るので、そこには注意してください

基本的にコードの実行回数が同じ回数を実行する場合は、SQLよりオブジェクトを利用した方が早いです

例えば、レコードの追加が明確に差があります
SQL文ではレコードの追加はINSERT文を使用します、これはフィールド名と入力内容を指定することでデータベースに新規レコードとして追加する構文です
それに対して、オブジェクトを利用する場合はAddnewメソッドを利用します
このメソッドはデータの入力は行いません、新規レコードを追加するだけです
このメソッドを実行した後に各フィールド名を指定して値を代入していきます

この2つの全く同じ結果を求められる処理では、後者のオブジェクト利用が圧倒的に早いです

そもそもの想定の違いかもしれませんが、SQLは基本的に細かいコードを連続して実行するものではなく、なるべく1回のコード実行で結果を出すものだからです
対してExcelVBAは、細かいコードを大量に実行します

なのでどちらが優位というものでもないのでしょうが、ExcelVBAにおいてのレコード操作という点ではExcelVBAのオブジェクト利用が優位です

そして、ここまでオブジェクトと記載してきましたが、実際にはADOオブジェクトというものを利用します
これと似たような操作のできるDAOオブジェクトもありますが古いオブジェクトとなりますのでADOオブジェクトを利用できるようになれば十分です

このADOオブジェクトは、FileSystemObjectと同じようなインスタンスを作成して利用することのできるオブジェクトになります

Accessデータベースのファイルを作成し、接続を行い、テーブルの作成を行ったら
次に行うのがこのオブジェクトの作成です
それからようやくレコード操作が可能になります
実際のコードにすれば、ほんの数行の話なのであまり気負うものでもありませんが、初めは戸惑いたっぷりになるとは思いますが、基本はセルなどのオブジェクト操作と変わりませんので、この記事を読んでいる方のレベルなら特段難しくはありません

と、ここまでExcelVBAのオブジェクト利用の優位性を言い過ぎましたが、上記でも言いましたが実行回数が同じであれば確かにオブジェクト利用が優位です
ですが、オブジェクト利用ではレコードを1つずつ操作することになります

データベース全体に対するレコード操作を行いたい場合に1000レコードのデータベースであれば1000回コードを実行することになります
ですが、SQLのUPDATE文を利用すればたった1回の構文でデータベース全体の編集を行うことが出来ます

こうなるとコードの可読性においてもこちらが優位です

つまり言いたいのは、ADOオブジェクトだけではレコード操作を有効利用できない、ということです
おすすめ書籍でもあげているSQLの基礎本などを参考にSQLもすこしずつ理解を深めていってください

フィルタやワークシート関数を多用して、かつピボットテーブルまで使ってようやく出来るような集計がSQLでは驚くほど簡単に出来ることが多いです
データベースをデータの保管庫としてだけでなく、集計処理でも有効にデータベースを利用してください

データベースに接続・切断する(ADODB.Connection)

ADODB.Connectionオブジェクトを利用してデータベースへの接続と切断を行うコード

'accdbファイルに接続する
zzDB_Con.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\作成DB.accdb"

'接続を解除(切断)する
zzDB_Con.Close

データベースの作成が完了したら、次にデータベースにOpenメソッドを使用して接続することで操作が可能となります
また、接続を行うとデータベースが接続状態となります、この状態を明示的に解除するのがCloseメソッドになります

データベースは性質上、排他的に開いたりすることが出来ます
データベースを操作する場合は、この切断処理は必ず行うようにしてください

なおコード中の「zzDB_Con」はインスタンスを作成したObject型の変数です
詳細に関しては以下の記事を確認してください

コード解説

zzDB_Con.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\作成DB.accdb"

Openメソッドを使用して、データベースに接続を開始します
引数のProviderには既定の文字列を指定します、accdbファイルでは例コードの文字列を基本的に使用します
そのあとに続くSourceにAccessデータベースファイルの絶対パスを文字列で指定します
今回の場合では、同じフォルダ内のデータを参照しています

このOpenメソッド以降で、データベースに関する操作を行うことが出来るようになります

空白のファイルを作成して、最初に接続した場合はさらにテーブルの作成を行う必要があります
このテーブルの作成まで行うと、そこにレコードを登録していくことが出来るようになるためデータベースにデータを保存していくことが出来ます

zzDB_Con.Close

このコードを実行すると接続しているデータベースを切断します
データベースの利用が終了したら確実に行います
また、なるべくデータベースは無駄に接続することの無いようにしてください

データベースから受け取ったデータの加工等に時間のかかる処理であれば、処理後にまたデータベースを利用するとしても、いったん接続は切断するようにします

ただ、処理に時間が大きくかかる可能性が無いことや、データベースの利用自体がそもそも個人レベルのものである場合はそこまで気にしなくてもいいです

なんせ、接続したら終了時には切断する
ここをしっかり行うようにしてください