値の入力されたセルだけをクリアする

SpecialCellsメソッドの引数xlCellTypeConstantsを利用して値入力されたセルだけをクリアする方法について

'指定範囲内のみを対象とする
Range("A1:C3").SpecialCells(xlCellTypeConstants).ClearContents
'すべての使用済みセル範囲を対象とする
Cells.SpecialCells(xlCellTypeConstants).ClearContents

セル範囲の中で値の入力されたセルのみをクリアするには「SpecialCells」メソッドの引数「xlCellTypeConstants」を使用します

すでに関数によってある程度フォーマットが完成したシートに対して、入力値の部分だけを初期化する場合に利用します
単純にセル範囲全てをクリアしてしまうと、関数も消去されてしまうためです

なお、セル自体が数式が入力されているかを判定する方法は以下の記事で解説しています
ただ今回のような範囲に一括で処理を実行したい場合にはあまり向いていません

しかし、このメソッドもUsedRangeに影響を受けるため、既存のデータから上書き保存が許されないような処理の場合は記事コードはうまく動作しない場合がある点に注意が必要です
そのあたりに関しては以下の記事で解説していますので確認しておいてください

コード解説

値セルのみのデータの消去
値入力されたセルのみをクリアする動き

画像の動きを確認してください
前提としてC列はA列+B列の数式が入力されています
なので値入力された範囲はA2~B7となります

主にすでにあるフォーマットから入力範囲のデータを消去する場合に利用する場面が多いため記事コードでも前者の方が利用頻度は高いと思われます

'指定範囲内のみを対象とする
Range("A1:C3").SpecialCells(xlCellTypeConstants).ClearContents

このコードではA1~C3という範囲指定を行った上で、値のセルに対してClearContentsメソッドを実行して入力値を消去しています
画像ではA2~B3がその範囲に該当するため、そこが消去されています

こうして指定することでせっかく作った関数を消去せずに入力フォーマットを初期化できます

'すべての使用済みセル範囲を対象とする
Cells.SpecialCells(xlCellTypeConstants).ClearContents

こちらではシート全体のうち、使用済みセル範囲であるUsedRangeプロパティで取得される範囲の全ての値セルがクリアされます
この方法だと見出しや題目等の部分まで消去されるので、あまり利用頻度は無いかもしれません

ちなみにセル内に「=”見出し名”」というような入力を行えば、そのセルは数式と認定されるようになるので全ての範囲から値セルを消去しても問題なくなります
ただ全てを振り替えたり、修正があった場合に非常に面倒なので現実的な利用方法ではありません

この方法でセル範囲を限定すれば、ClearContentsメソッドを他のものに変更するだけで、値セルにだけ背景色を設定したりすることも簡単に行うことが出来ますので、アイデア次第で広がる汎用性のあるものだと思います
ただただUsedRangeに引っ張られる点を除けば、ですが・・・

数式の入力されたセルだけを取得する

SpecialCellsメソッドの引数xlCellTypeFormulasを利用して数式セルが入力されたセルだけを取得する

Debug.Print Cells.SpecialCells(xlCellTypeFormulas).Address

数式が入力されたセルを取得するには「SpecialCells」メソッドに引数「xlCellTypeFormulas」を使用します
この引数設定では数式が入力されたセル範囲を取得できます

このメソッドには利用時に注意点があります
それはUsedRangeプロパティが基準とされる点です
コードの解説とともに解説します

コード解説

数式が入力された範囲の取得
数式入力範囲の取得

記事コード含め合計4行を実行しています
前提として、シートの選択範囲が数式が入ったセルです
それ以外は空白セルは空白、数値が入っている部分とC列が数式です

記事コードが実行されると選択範囲と同じ範囲が取得されています
C2~C7のセル範囲が取得されます

ここで1つ注意したいのが、Cellsオブジェクトはワークシート全体のセル全てを指定しますが、内部的には自動的にUsedRangeプロパティに変換されます
ただこの引数設定ではあまり影響は無いかもしれません

ActiveCell.SpecialCells(xlCellTypeFormulas).Address
Range("A1").SpecialCells(xlCellTypeFormulas).Address
→→→ $C$2:$C$7

これら2行の指定でも同じくC列の入力範囲が取得されています
指定Rangeオブジェクトが単一の場合にはCellsオブジェクトと同様にUsedRangeプロパティに変換されて取得されます

ほとんどそういった利用は無いとは思いますが、数式かどうかの判定には利用できないですね

Range("A1:C3").SpecialCells(xlCellTypeFormulas).Address
→→→ $C$2:$C$3

ここではRangeオブジェクトで範囲指定を行っています
この場合はその範囲内で条件を満たすものが取得されます

A1~C3までのセル範囲のうち数式が入力されたセルはC2とC3となるため、その範囲が取得されています

この様に全ての範囲から取得を行うのではなく、限定された範囲から取得する場合はその範囲を指定してあげれば良いだけです

ただ今回の引数の際にはあまり影響は無いのですが、やはりUsedRangeプロパティの影響を受けています
指定範囲内であってもUsedRangeプロパティ外は検索対象に入りません

つまりIntersectメソッドを使ったときの様に、指定範囲とUsedRangeプロパティの範囲の重複する部分のみが対象となってしまいます

このメソッドを利用する場合にはとにかくUsedRangeプロパティへの注意が必要なため、以下の記事を参考にしてこのプロパティの動きをしっかり理解しておいてください

セル内容が数式かどうかを判定する

セルの入力内容が数式かどうかを判定するためのプロパティについて

'セルが数式かどうかを判定する(Trueなら数式)
Debug.Print Range("A1").HasFormula

セル内容が数式かどうかを判定するには「HasFormula」プロパティを使用します
Trueならセル内容は数式が入力されています、値等の数式以外であればFalseが返されます

セルの入力値には大きく分けて数式と値が存在しますが、それを判定するためのプロパティです
これはIs~~関数等のものではなく、Rangeオブジェクトのプロパティである点に認識が混同しないように注意が必要です

ちなみに数式と関数はこのプロパティ上は同一で判断します

コード解説

HasFormulaプロパティの取得の動き
プロパティ取得の動き

画像の動きを確認してください

まず前提としてB1セルにはA1セルに表示された数式が入力されています
ただ空白を返しているだけの数式になります
B2セルには何も入力されていません

なので空白かどうかの判定に関しては出力されるのは「True」が出力されています

これは空白チェック時の要注意点の1つなのですが、数式の結果が空白を返している場合は、セルが空白と混同して判定されてしまいます
この場合は空白かどうかを判定するだけでは不十分です

Debug.Print Range("B1").HasFormula
→→→ True

次にプロパティを利用した判定を取得してみます
この場合はB1には数式が入力されているため、Trueが返されます

ここでさらに空白チェックを複合的に条件を立てることにより、数式で空白が返されたものは除外することが出来ます

Debug.Print Range("B2").HasFormula
→→→ False

B2に関しては何も入力されていないため数式では無いのでFalseが返されます
ここは数式でなければ入力値があったとしても値であればFalseとなります

このプロパティを利用することで、指定セルから数式の空白を含めた空白を探すような特殊な場面で活用できます

ループ処理で不要行の削除を行う

ループ処理で条件判定を行って、不要な行を削除するちょっとした処理とその際の注意点の説明

Dim zzループ用セル As Range 'Forループ用のRange型変数
Dim zz対象セル As Range  '削除対象のセルの代入先のRange型変数

'指定セル範囲を全てループする
For Each zzループ用セル In Range("A2:A16")
'セルが空白なら削除対象とする
If zzループ用セル = "" Then
'対象セル変数が取得されているかで分岐
If zz対象セル Is Nothing Then
Set zz対象セル = zzループ用セル
Else
'取得されていればUnionメソッドを使用してセル範囲を追加
Set zz対象セル = Union(zz対象セル, zzループ用セル)
End If
Else: End If
Next zzループ用セル

'ループ終了後にセルが取得されているかを判定
If Not zz対象セル Is Nothing Then
'取得されていれば対象範囲を削除する
zz対象セル.EntireRow.Delete
'↓テーブル機能利用時はテーブルの列範囲内でなければエラーとなるので以下を利用する
'Intersect(zz対象セル.EntireRow, Range("A:D")).Delete
Set zz対象セル = Nothing
Else: End If

ループ処理で判定を行いつつ不要行の削除や挿入を行う際には、実際の削除処理を実行するタイミングが処理上で注意が必要です

というのも、セルのループをする場合は基本はForEachループを利用しますが、このループ処理では最初に指定のセル範囲を代入することになります
ループ中に削除を行うと、代入したセル範囲が矛盾を起こしてしまいます
処理の組み方次第では実行時エラーとなります

ループ処理で削除や挿入を行う際には、ループ処理中にメソッドは実行せずに対象セルを取得して、ループが終了してから一括で実行することで矛盾の発生を無くす事と処理速度の向上を実現できます

その際に利用するのはRange型の変数だけです
この変数に削除対象となるセル自体を代入していきます
ただ削除対象をそのままループで代入しても、前回のセルが上書きされてしまいますのでUnionメソッドを利用してセル範囲を追加していきます

ループ判定が終了した段階で、対象セルが取得されていればその範囲に対して一括でDeleteメソッドで削除を行います

コード利用の意味について

この目的は空白行など不要な行を削除するだけの処理です
なので、特別な処理は必要なく単純にIF分岐を利用して条件に一致する行を削除してしまえば良いだけに感じると思います

実行前の状態
処理実行前

この画像は処理を実行する前の状況の画像です
このシートのテーブルデータのうち、A列のセルが空白になっている行を削除したい処理になります

コードはうまくいかないコードなので、画像内で見にくいですが確認してください
流れとしてはA列の指定セル範囲をForEachループで回しています
その際にセルが空白であれば行全体を削除しています

空白行がうまく削除できていない状態
処理を実行後の状態

こちらの画像が処理を実際に実行したあとのモノです
一見して分かるように空白行が残ってしまっています

さらに確認してもらいたいのが、画像左下のイミディエイトに出力されたループ処理で実行されたセルのAddress文字列です

ForEachループではA2~A16までを代入して実行しているのに、A11までしかループが実行されていません

これはループ中に削除を行うことにより対象のセル数が減少していること
さらに削除した時点で、ForEachループの参照するセルが存在しなくなったことで自動的に次のセルを参照してしまっているため、連続した空白行の部分がうまく処理できていない状況です

これはそもそも仕様に対処する場合には、ループ処理を上からではなく下から行うことで対処することは出来ます
ですがその場合ForEachループは利用できなくなります

コード解説

記事コードの流れで処理を組むことで、目的の達成と処理速度の向上を図れます

記事コードを実行したあとのシート状態
コードを実行した後の状態

この画像は上記のコード実行前から記事コードを実行した後の状態の画像です
今回はしっかりと空白行が無くなっていることが分かると思います

ループ処理で実行されたセルのAddress一覧
ForEachループで参照されたセルAddress一覧

この画像はイミディエイトに出力されたものです
ForEachループで指定したセル全てがしっかり処理されていることが確認できると思います

Dim zzループ用セル As Range 'Forループ用のRange型変数
Dim zz対象セル As Range  '削除対象のセルの代入先のRange型変数

最初に使用する変数の宣言です
この処理ではRange型変数を2個使用します

1つはForEachループ用の変数です
もう1つは削除対象のセルを取得する変数です
ここに代入したセル範囲に対して削除を実行します

'指定セル範囲を全てループする
For Each zzループ用セル In Range("A2:A16")

~~中略~~

Next zzループ用セル

ForEachループの個所です
ここではとりあえずとして、セル範囲は決め打ちの処理になっています
実際にはワークシートのセル範囲であればUsedRangeやCurrentRegionのプロパティや画像のようなテーブル範囲であればListObjectsオブジェクトの各プロパティ等を利用してセル範囲を取得してください

'セルが空白なら削除対象とする
If zzループ用セル = "" Then

~~中略~~

Else: End If

削除条件を設定するIF分岐個所になります
ここの条件を変更することで削除対象を変更することが出来ます
逆に空白ではない行を削除したり、特定の値を対象とすることも出来ます

'対象セル変数が取得されているかで分岐
If zz対象セル Is Nothing Then
Set zz対象セル = zzループ用セル
Else
'取得されていればUnionメソッドを使用してセル範囲を追加
Set zz対象セル = Union(zz対象セル, zzループ用セル)
End If

削除条件に一致するセルであった場合は対象セル変数に代入させます
しかし、そのまま代入すると新しいセルで上書きされていってしまいますので、すでに取得対象が存在する場合は既存のセル範囲を残して追加する必要があります
そのためUnionメソッドを利用して、取得済み範囲に追加してきます

ただ、このメソッドは2つ以上のセル範囲を追加するメソッドのため
未取得のNothingではエラーとなります
そのため、最初に取得済みかどうかの判定を行ってから、取得済みでなければループ変数のセル範囲を代入して、取得済みであれば既存の範囲に追加します

この後で行全体選択のプロパティ等を利用するので、この時点の取得範囲はセル単体の追加でも問題はありません

'ループ終了後にセルが取得されているかを判定
If Not zz対象セル Is Nothing Then
'取得されていれば対象範囲を削除する
zz対象セル.EntireRow.Delete
'↓テーブル機能利用時はテーブルの列範囲内でなければエラーとなるので以下を利用する
'Intersect(zz対象セル.EntireRow, Range("A:D")).Delete
Set zz対象セル = Nothing
Else: End If

ここはループを抜けてきた後の処理になります
ループはあくまでも削除対象のセルを判定して取得を行うためのものになります
つまり、この時点で削除対象が必ず存在しているということは分かりません

ループを抜けてきた時点で対象セル変数がNothing(初期値)となっているかどうかで存在確認が出来ます
Nothingはオブジェクトなので、比較演算子はIsを利用する必要があります
この比較演算子は反対の意味の演算子が無いので、Notを利用して逆説にすることで、Nothingではない(対象セルが存在する)という条件が成り立つときに削除処理を実行します

取得した対象セルは単一セルなので、行全体を指定するためにEntireRowプロパティを利用して削除を実行します

ただここで問題がある場面がテーブル機能の場合になります
テーブル機能範囲の行を削除する場合に連続していない行全体を削除しようとすると画像の実行時エラーが発生します

テーブル機能の行を削除できないときのエラー
実行時エラーメッセージ

このエラーが発生して、Deleteメソッドが実行できません
その場合にはコメントアウトされているコードを利用してください

'↓テーブル機能利用時はテーブルの列範囲内でなければエラーとなるので以下を利用する
'Intersect(zz対象セル.EntireRow, Range("A:D")).Delete

こちらのコードはテーブル機能を利用している場合に利用するコードになります
テーブル機能の範囲を削除を行う場合には、テーブル機能の列範囲と同じ列範囲を指定する必要があります

ここでは決め打ちの列指定ですが、実際に使用する場合はDataBodyRangeプロパティ等を利用して列範囲を取得するようにするといいです

ちなみにこのセルを変数に代入させて、あとでまとめて一括で処理を行う方法は処理向上にとても貢献します
削除だけでなく、挿入やセルの書式設定等や一括入力などにも利用できます

指定セル以下の行を全て削除する

指定セル以下の行全てを削除するコード、見出しを除外した範囲全てを消去する際に使用します

'2行目以降を削除
Range(Range("A2"), Range("A" & Cells.Rows.Count)).EntireRow.Delete

データ集計を行ったり、フィルタデータの貼り付けなどで元々あるデータは削除してデータを貼り付けする処理はよくあります
この際に見出し行は削除したくない場合があります
そんな場合に使用するコードです

指定セル以下の行全体を削除する動き
コード実行時の動き

このコードを使用すると2行目以降のすべてのセルを削除しますが、1行目はそのままなので見出しを再度作成したりする必要が無くなります

ちなみに画像ではテーブル機能のデータ範囲以下全てを削除しています
テーブル機能の場合はデータが無くても、データ範囲1行目には背景色の設定が表示されますので、実際にはちゃんと削除されています

データ範囲のみを選択する場合は以下の記事で解説しています

ですがこの選択範囲はデータ行のみになるので、もし未保存の削除セルがあった場合LastCellなどでうまく最終セルが取得できない場合があったり、コピー元のセル範囲がそういったセルを作成してしまっていたりすることがあるので、なるべく例コードの様にデータ範囲を初期化する場合は全てのセル範囲を指定する方が無難です

コード解説

Range(Range("A2"), Range("A" & Cells.Rows.Count)).EntireRow.Delete

このコードは1行ステートメントですが、複数のプロパティを使用しているのでプロパティごとに分割して解説を行います

Range(Range("A2"), Range("A" & Cells.Rows.Count)).~~.~~

まず最初にRangeオブジェクトを指定しています
Rangeオブジェクトでは引数に2つのセル範囲を指定することが出来ます
引数を2つ指定した場合は、1つ目と2つ目のセル範囲を始点と終点として範囲取得します

~~(Range("A2"), ~~).~~.~~

1つ目のセル範囲がA2セルとなります
このセルが指定範囲の始点セルとなります

つまり、このセルの行数を変更することで見出しの行数に対応することが出来ます
A2をA3とすれば3行目以降のすべてを削除するコードになります

ちなみに、この後のプロパティで行自体を取得しているため、A2をB2などの別の列を指定しても問題はありませんが意味はありません
可読性が低下するだけです

~~(~~, Range("A" & Cells.Rows.Count)).~~.~~

2つめの引数にはワークシートの最下行のセルを指定しています
「Cells.Rows.Count」により最終行数値が取得されます
現在のExcelのシートであれば104万弱ですね
その数値にAを結合することで、A列の最下行セルが指定できます

結果としてこのRangeオブジェクトではA2~A1048576までの範囲選択をしていることになります

なおここではシートの指定を行っていませんのでActivesheetが指定される形になりますが、ワークシートの行数に違いはありませんので別のシートを操作する場合でもあまり気にしなくてもいい部分です

~~(~~, ~~).EntireRow.~~

ここでは上記で取得したセル範囲の行を範囲として取得しています
EntireRowプロパティを使用すると、指定されたセル範囲の行を取得します
なので、ここでA列といった制限が無くなり、すべての列を対象とする形になります

A2~A1048576というセル範囲が2~1048576という行数の範囲になります

~~(~~, ~~).~~.Delete

最後にDeleteメソッドを使用して、その範囲を削除します
上記までで指定行範囲を取得できているので、そこに対して削除を実行します

この範囲を別のシートで関数等で参照している場合、セル参照がエラー値(#REF!)になってしまいます
その場合はDeleteメソッドではなくClearメソッドを使用することでセルの初期化を行うことが出来ます

なおブックのサイズにもよりますが、こういった削除処理を行った際は処理最後にでもブックの上書き保存を行っておいた方が良いです

なぜなら、削除したセル範囲のLastCellなどはそこでしか更新されないためです
またこれを行うことで、スクロールバーの可動域も新しいデータ範囲に調整されますので、あまりにも削除前とのデータ範囲に差がある場合にはユーザーにとっても意味のある保存と言えます

ただ保存はサーバー上であったり、ブックサイズが大きいと時間がかかってしまい
待ち時間がユーザーにとってのストレスになることもあることは認識しておきましょう

レコードの新規追加(ADODB.Recordset)

AccessデータベースにExcelのデータをレコードとして新規追加するコード

'接続済みのデータベースのテーブルへのレコードセットの接続
zzDB_Rec.Open "テーブル名", zzDB_Con, 0, 3
’レコードの新規作成
zzDB_Rec.AddNew
’新規作成したレコードへの代入
zzDB_Rec!ID = ActiveCell
’レコードのデータベースへの更新
zzDB_Rec.Update
’レコードセットの接続を解除
zzDB_Rec.Close

データベースへのレコードの新規追加にはADOオブジェクトの「AddNew」メソッドを使用します
このメソッドを実行しただけでは既定値が入力されたレコードが作成されるだけになります
その追加したレコードに代入するにはADOオブジェクトのインデックス番号か、例コードのように列名を指定して代入します
代入後に「Update」メソッドを実行することでデータベースへの登録更新が完了します
操作後はADOオブジェクトの接続を解除します

レコードの新規追加といっても、実用で考えるとこの一連の手順が最低限必要になります

また、レコードの新規追加にはSQLのINSERT文も利用可能ですが、圧倒的にこちらのメソッドの方が高速なため、基本的にはレコードの追加はこちらのコードを利用します

メソッドの書式

引数(太字は必須引数)
AddNew FieldList, Values

このメソッドには引数が2つあり、どちらも省略可能です
この引数はそれぞれが対応しています

「FieldList」は列名を指定して、「Values」はその代入値の指定です
この指定は列名を文字列として指定して値を入力させます
この引数のイメージはSQLでのINSERT文の引数と同じイメージになります

また、この引数は配列を指定する必要があるためArray関数を使用して配列として指定する必要があります

zzDB_Rec.AddNew Array("ID", "日付"), Array(ActiveCell.Value, ActiveCell.Offset(0, 1).Value)

この様に引数の2つをどちらも指定して、かつ配列として指定する必要があります
こうすることで、例コードの様に1列ずつ代入せずに一括代入が出来ます

しかし、おおよそ一括代入は処理が高速になるはずなのですがこのメソッドはその通りでは無いようです
ほんの数回程度の検証しかしていませんが、10万件2列のデータの取得を行ったところメソッドの引数を使用した場合は1.4秒前後となりました
対して、各列ごとの取得では1.1秒前後となり、0.3秒もの差がつきました

CPU性能等に非常に影響があるので、数字に意味はありませんがコードの可読性含めて考慮するとほぼ利用する意味がありません
どの列に何を代入しているかは各列入力の方が分かりやすいです

実際の使用例

シートのテーブルに入力されたデータをデータベースに登録を行います
上記の引数説明にあるように、メソッドの引数を指定する方法は利用しません

コードの動きに関しても、データベースへの登録作業のため逐一の動きが確認できませんので結果のみを画像表示します

データベースへ登録するExcelのテーブルデータ
Excelのテーブル内容

この画像はExcelに入力されたテーブル内容のデータです
今回のコードはこの画像のデータをデータベースに登録を行います

レコードが未登録の状態
登録前のデータベース

自分の環境にはAccessがありませんので、データの確認としてExcelのナビゲーター画面を利用します
この画像の右範囲を確認してもらうと分かるように、まだデータベースにデータは何も登録されていませんので、空のテーブルであることが確認できます

データベースに登録された状態
登録後のデータベース

先に結果の画像を確認してみます
先ほどは空のテーブルであった部分にデータが入力されています

Nullというのはデータベースでデータが無い場合の値です
「””」は文字列の空白を意味するので、Null=””は成り立ちません

Excelのセルの空白はデータベースではNullが代入されます
これを防ぎたい場合は、空白の判定処理を入れる必要があります
Nullが許可されていない列の場合は実行時エラーが発生します

コード解説

Sub 処理テスト()


Dim zzDB_Con As Object
Set zzDB_Con = CreateObject("ADODB.Connection")
Dim zzDB_Rec As Object
Set zzDB_Rec = CreateObject("ADODB.Recordset")

'データベースへの接続開始
zzDB_Con.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\作成DB.accdb"
zzDB_Rec.Open "テーブル名", zzDB_Con, 0, 3

Do Until ActiveCell = ""
zzDB_Rec.AddNew
zzDB_Rec!ID = ActiveCell
zzDB_Rec!日付 = ActiveCell.Offset(0, 1)
zzDB_Rec!コード = ActiveCell.Offset(0, 2)
zzDB_Rec!金額 = ActiveCell.Offset(0, 3)
ActiveCell.Offset(1, 0).Select
zzDB_Rec.Update
Loop

zzDB_Rec.Close
Set zzDB_Rec = Nothing
zzDB_Con.Close
Set zzDB_Con = Nothing


End Sub

上の処理を実際におこなったコードです

Dim zzDB_Con As Object
Set zzDB_Con = CreateObject("ADODB.Connection")
Dim zzDB_Rec As Object
Set zzDB_Rec = CreateObject("ADODB.Recordset")

ここではデータベースへの接続のConnectionオブジェクトとRecordsetオブジェクトの作成を行っています
レコード操作を行う場合はRecordsetオブジェクトを利用します

'データベースへの接続開始
zzDB_Con.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\作成DB.accdb"

データベースへの接続を開始します
ここではAccdb形式のデータベースへの接続を行っています
Providerに関してはほぼ固定なので文字列をコピペしてもらって大丈夫です

zzDB_Rec.Open "テーブル名", zzDB_Con, 0, 3

ここでRecordsetオブジェクトを接続しています
引数の1つ目はテーブル名を文字列で指定します
2つ目の引数はデータベースへの接続を指定するので、前の手順で接続したConnectionオブジェクトを指定します

3つ目と4つ目はレコードを操作する際の方法の設定になります
詳細は別の記事にて行います
ここでは、レコードを前方移動で共有ロックをかけています
ロックに関しては「3」を「2」に変えても良いです

なお、この数値は参照設定を行えば定数が利用できます
参照設定を行っていないとエラーとなるので、数値を直接指定すると安定感があります

Do Until ActiveCell = ""
zzDB_Rec.AddNew
zzDB_Rec!ID = ActiveCell
zzDB_Rec!日付 = ActiveCell.Offset(0, 1)
zzDB_Rec!コード = ActiveCell.Offset(0, 2)
zzDB_Rec!金額 = ActiveCell.Offset(0, 3)
ActiveCell.Offset(1, 0).Select
zzDB_Rec.Update
Loop

ここでループ処理により空白セルになるまで行のデータを登録していっています
新規追加して、データを各列ごとに代入させています

zzDB_Rec!ID = ActiveCell

各列に代入する場合はRecordsetオブジェクトに「!」で列名を接続して指定することで列を限定できます

AddNewメソッドの引数だと1行に全てを一気に記載する必要がありますが、例の様に各列を指定できるので、こちらのほうが見やすいです
そのうえ速いなら選択の余地がないかもしれませんね

zzDB_Rec.Update

「Update」メソッドは更新した内容をデータベースに確定させるメソッドです
この時点でデータベースが更新されます

なお、AddNewメソッドはUpdateメソッドが実行されていないレコードがあった場合に、AddNewメソッドが実行されると自動的に更新確定を行います

zzDB_Rec.Close
Set zzDB_Rec = Nothing
zzDB_Con.Close
Set zzDB_Con = Nothing

最後にRecordsetオブジェクトの接続解除、データベースへの接続解除を行います
これはお決まりの流れです

以上の様にレコードは登録処理を行います
AddNewメソッドは非常に高速です
さらに、このメソッドを最速の環境にする方法としてデータベースを接続せずにテーブルをADOオブジェクト上に作成して、レコード登録が最も早いです
およそ、この記事での例コードの倍ほどの速さを発揮します
すこしテーブル作成がやっかいなのがネックです

テーブル内にアクティブセルがあるかを調べる

テーブルがアクティブ状態かどうかをアクティブセルの存在により判定するプロパティです

'テーブル内にアクティブセルがあればTrueを返す
Debug.Print Sheet2.ListObjects(1).Active

テーブルのアクティブ状態を取得するには「Active」プロパティを使用します

テーブル機能を利用したExcelにおいて、そのテーブル内にアクティブセルが存在するかを判定基準とするプロパティです
これは単純にテーブル自体のアクティブ状態を取得することで、アクティブセルが含まれているかを判定することが出来ます

リボンにテーブル機能がのタブが表示されていれば、アクティブ状態で表示されていなければ非アクティブ状態です
リファレンスにもありますが、テーブル範囲のオブジェクトにはアクティブにするメソッドがありません
そのため、テーブルをアクティブ状態にするには範囲内セルを選択状態にする必要がありますが、そのセルが本当にテーブル範囲なのかどうかはこのプロパティを利用しないと分からないということです

Excelはユーザーが簡単に変更可能であることと、テーブル機能自体が左上のセルがA1に確定しているわけでも無いし、シート内に複数のテーブルを作成することも可能です
行列を追加挿入されてもテーブル機能としては一切問題が無いのです
そのため、状況によってはこのプロパティを使用する必要が出てくることもあります

テーブル範囲でセル選択を変更したときのプロパティの取得の動き
プロパティの取得の動き

画像の動きを確認してください
最初に取得をしているB3セルは範囲内なのでTrueを返しています

そして、2つ目の取得では見出しのセルが選択された状態です
当然見出しもテーブル範囲なのでTrueが返されます
画像にはありませんが、総計行が選択されていてもTrueが返されます
データ範囲のみだけが対象ではない点には注意が必要です

3つ目の取得は範囲外なのでFalseが返されています

また4つ目の取得でも注意が必要です
このプロパティはあくまでもアクティブ状態の判定のために、テーブル範囲内にアクティブセルがあるかどうかを判定します
つまり、選択範囲全てがテーブル範囲内にあるとは限りません

実際このプロパティの使用範囲は狭いとは思いますが、シート内に複数のテーブルを作成しているときなどに利用してください

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

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

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オブジェクトを利用する方が比較対象にならない程に早いです