2022/09/27

ListObjectの絞り込みデータの配列化

ListObjectについて、下記のようなシリーズで説明しています。
 ・ListObjectの作成と概要
 ・ListObjectの並べ替え
 ・ListObjectの絞り込み
 ・ListObjectの絞り込み解除
 ・ListObjectの絞り込みデータの配列化  ←今回
 ・ListObjectのデータ追加
 ・ListObjectのデータ変更
 ・ListObjectのデータ削除

今回は、ListObjectオブジェクトを絞り込んだ後の「データ配列化」について説明します。

1.絞り込まれたテーブルのデータ処理(手動)

何らかの条件で絞り込んだ状態のテーブルが図1の右側だとします。タイトル行は1行目で、データ行は3~4行目、および6行目と不連続行が抽出されています。
テーブルを絞り込んだ状態
図1


この絞り込んだデータを手動で活用するのであれば、「絞り込んだデータのみの状態」にした方が使い易いはずです。そのために通常は、図2のように「テーブル範囲を選択」→「コピー」→「(他のシート等に)貼り付け」することで、「抽出された部分だけ」を貼り付けてから活用することになると思います。
テーブルを絞り込んだ状態
図2


寄り道
この、単純に「コピーしただけで、絞り込んだデータのみ」がコピーされるという特徴は、テーブルでは無く「オートフィルター」自体が持つ機能のようです。実際にテーブルでは無く、オートフィルターだけを適用したセル範囲でも、絞り込んだデータのみがコピーされます。

しかしフィルターでもテーブルでも無い状態では、「非表示の行(≒ 除外した行)」を含めて選択・コピーすると、「非表示のデータもコピー」されてしまうことになります。
もし「非表示行をコピー対象外」にしたいのなら、非表示行を含めたセル範囲を選択後、Excel上部のリボンの「ホーム」タブ→「編集」グループ→「検索と選択」の中の「条件を選択してジャンプ」をクリックして表示される「選択オプション」で、「可視セル」のオプションボタンを選択し「OKボタン」をクリックする必要があります。

但し「非表示行を対象外」としてコピーできる条件が存在します。条件としては以下の通りです。
 ・同じシート内にオートフィルターを適用したセル範囲がある。
 ・そのオートフィルターで、現在絞り込みをしている状態である。
 ・テーブル化している範囲は、絞り込み有無に関わらず条件の範囲外。
この現象の説明はどこのサイトにも見当たらないので、仕様では無くバグかもしれません。ですので、非表示行を対象外にしたいときには「選択オプションで可視セルを選択」という手順を省かないようにした方が良いと思います。

2.絞り込まれたテーブルのデータ処理(マクロ)

今回は絞り込まれたデータを「配列」に取り込む方法を考えます。必要なデータが配列化できれば、その後自由にプログラム内で活用できます。なお、今回はタイトル行もデータ内に含めることにします。

配列化する手段として、まず「手動で他シートにコピペ」したデータをそのまま配列にする方法(下記のNo.1)を思いつくと思いますが、他にも色々考えられます。
 1.絞り込まれたテーブルをコピー→他シートにペーストし、セル範囲を配列化
 2.テーブルの可視セルのデータを配列化
 3.テーブルをコピーし、クリップボード上のデータをDataObjectで配列化
以下で1つずつ実コードを示しながら説明していきます。

2-1.絞り込まれたテーブルをコピー→他シートにペーストし、セル範囲を配列化 その1

図2で示した「手動でのコピペ」に相当するのが図3のコードです。別のプロシージャから呼び出す「関数プロシージャ」とし、引数として操作するテーブルを受け取り、戻り値として配列を返すものです。
  1. '========== ⇩(1) コピペ範囲を配列化1 ============
  2. Function TableArray_1(T As ListObject) As Variant
  3.  Dim TempSheet As Worksheet     '←貼り付け先のシート
  4.  Set TempSheet = Sheets("sheet2")
  5.  TempSheet.UsedRange.Clear
  6.  T.Parent.Activate
  7.  T.Range.Select
  8.  Selection.Copy
  9.  TempSheet.Range("a1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
  10.  Application.CutCopyMode = False
  11.  TableArray_1 = TempSheet.Range("a1").CurrentRegion
  12. End Function
図3


03行目「Set TempSheet = Sheets("sheet2")」では、テーブルのデータを貼り付ける先のワークシートを指定しています。システムにする場合は、貼り付け先シートは様々な場面で使用するでしょうから、重複コードとならないように「宣言部などで宣言」するものだと思います。

05行目「TempSheet.UsedRange.Clear」では、貼り付け先シートの古いデータをクリアしています。UsedRangeを使っていますので、ユーザーが貼り付け先データの一部を削除したとしても、全てのデータが削除されることになります。
またここではClearメソッドを使い、書式を含めた全てを削除しています。これは例えば、日付データを貼り付けた後にClearContentsで値だけを削除(書式はそのまま残る)し、その後「数値」を貼り付けてしまうと「日付型」として表示されてしまうのを防ぐためです。貼り付けるデータ型が一定であれば、ClearContentsで値だけを削除してもOKと思います。

07行目「T.Parent.Activate」では、コピー元であるテーブルのあるシートをアクティブにしています。
08行目「T.Range.Select」では、テーブルのタイトル行を含めた全範囲を選択しています。図2の左側の状態になります。
このSelect文を実行する時に「Selectするシートがアクティブになっていないとエラー」が発生してしまいますので、その前に07行目でシート移動をしています。
09行目「Selection.Copy」では、選択した範囲をコピーしており、これは図2の中央の状態になります。選択しているのはテーブルの全範囲ですが、Copyされるのは「可視セル」である「タイトル行+抽出された行」のみとなります。

10行目「TempSheet.Range("a1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats」では、貼り付け先の「A1セル」を左上端とし、PasteSpecialメソッドを使用してコピーしたデータの「値と数式の書式」を貼り付けています。
PasteSpecialメソッドのパラメータは以下の通り4つあり、全て省略可能なパラメータです。
パラメータデータ型内容
PasteXlPasteType(図5)貼り付けの範囲
OperationXlPasteSpecialOperation(図6)貼り付け時の演算方法
SkipBlanksVariant(True or False)空白セルの無視有無(True = 空白セルを無視。既定値=False)
TransposeVariant(True or False)行/列の転置有無(True = 行列を入れ替え。既定値=False)
図4


寄り道
なお、09~10行目を合わせて「Selection.Copy TempSheet.Range("a1")」とすると、セル範囲が簡単に貼り付けられます。しかしテーブルを貼り付ける際には注意が必要です。
コピー元のテーブルが「絞り込まれていない」状態の場合に「Selection.Copy + 貼り付け先」のコードを実行すると、貼り付け先に「テーブルとして貼り付け」がされてしまいます。テーブル名は「元のテーブル名+追番」のような名前が付くようです。

データは問題なく取得できるので配列化には問題無いのですが、05行目で旧データを削除する時には「テーブルの削除・解除」の操作が必要になってしまいます。なお絞り込まれている場合は、元のテーブルの書式貼り付け先がテーブルにはならないので「絞り込み有無により、元データクリアのコードを変える」必要が出てきてしまい、返って面倒になります。

ちなみにコピー元のテーブルが「絞り込まれている」場合に「Selection.Copy + 貼り付け先」で貼り付けをすると、可視セル行のみが貼り付きますが、一緒に「背景色や文字色の書式」も貼り付きます。こちらもデータを配列化するには問題ありませんが、データを消すときにClearContentsを使うと書式が残ってしまいます。

10行目のPasteSpecialでは、パラメータとしてPasteのみを使用しています。Pasteパラメータは図5(XlPasteType型)の中から選択することになり、今回は「xlPasteValuesAndNumberFormats(値=12)」を使用し「値と数値の書式」を貼り付けることで、日付は日付型、数値は数値型で表示されるようにしています。
XlPasteType列挙型
定数内容
xlPasteAll-4104全てを貼り付け(既定値)
xlPasteFormats-4122コピーしたソースの形式を貼り付け
xlPasteFormulas-4123数式を貼り付け
xlPasteComments-4144コメントを貼り付け
xlPasteValues-4163値を貼り付け
xlPasteValidation6入力規則を貼り付け
xlPasteAllExceptBorders7輪郭以外の全てを貼り付け
xlPasteColumnWidths8コピーした列の幅を貼り付けます。
xlPasteFormulasAndNumberFormats11数式と数値の書式を貼り付け
xlPasteValuesAndNumberFormats12値と数値の書式を貼り付け
xlPasteAllUsingSourceTheme13ソースのテーマを使用して全てを貼り付け
xlPasteAllMergingConditionalFormats14全てを貼り付け、条件付き書式をマージ
図5


なお今回は指定しませんが、「Operationパラメータ」には図6(XlPasteSpecialOperation型)の中から選択します。
XlPasteSpecialOperation列挙型
定数内容
xlPasteSpecialOperationNone(= xlNone)-4142貼り付け操作で計算は行われない(既定値)
xlPasteSpecialOperationAdd2コピーされたデータは、コピー先セルの値に追加される
xlPasteSpecialOperationSubtract3コピーされたデータは、コピー先セルの値から減算される
xlPasteSpecialOperationMultiply4コピーされたデータは、移動先セルの値に乗算される
xlPasteSpecialOperationDivide5コピーされたデータは、移動先セルの値を分割する
図6


様々な貼り付け方法は、「ホーム」タブ→「クリップボード」グループ→「貼り付け」ボタンから選択できますが、それらはPasteSpecialメソッド、Pasteメソッドを使ってマクロ側からも操作できます。一部になりますが、対応表を図7に示します。
貼り付けの種類とパラメータ設定
図7


PasteSpecialメソッドのパラメータの既定値は「Operation = xlNone(xlPasteSpecialOperationNone)」「SkipBlanks = False」「Transpose = False」です。図7でも分かるように基本的な貼り付けは、「Pasteパラメータの設定」のみでほぼ網羅できるとも言えそうです。
Excelリボンの貼り付けメニューの最下段の「形式を選択して貼り付け」をクリックすると、「形式を選択して貼り付け」ダイアログが現れます。その中のオプションボタンとチェックボックスを組み合わせる事で様々な貼り付けが可能です。このような複雑な貼り付けを行う時にPaste以外のパラメータを使う、と覚えておいてもよいと思います。

データの貼り付けは終わったので、12行目「Application.CutCopyMode = False」で、コピーモードを終了させます。
14行目「TableArray_1 = TempSheet.Range("a1").CurrentRegion」では、シートに貼り付けた範囲のデータを関数戻り値にして、関数プロシージャを終了します。
なお、セル範囲のデータを一括で配列化しているので、出来上がる配列のインデックスは行/列とも「1」スタートとなります。

2-2.絞り込まれたテーブルをコピー→他シートにペーストし、セル範囲を配列化 その2

図3のコードは「手動でのコピペ」を再現したものでした。しかし図3の07行目「T.Parent.Activate」で「テーブルの存在するシートをアクティブ」にする必要があるので、システムによってはバタバタと画面が切り替わったりしてしまいます。対策として「Application.ScreenUpdating = False」で画面更新を停止させればバタバタは無くなりますが、これでは単に「マクロ記録をVBAにしただけ」で機械に操られているようで面白くありません。

そこで改善したコードが図8です。改善点はテーブルを選択する時にコピー元を直接アクティブにするのでは無く、間接的にコピー元を指定している点です。但し直接セル選択をしていないので、テーブル全体をコピーすると「非表示の行もコピーされてしまう」ため、「可視セルのみをコピー」する必要があります。
  1. '========== ⇩(2) コピペ範囲を配列化 2 ============
  2. Function TableArray_2(T As ListObject) As Variant
  3.  Dim TempSheet As Worksheet     '←貼り付け先のシート
  4.  Set TempSheet = Sheets("sheet2")
  5.  TempSheet.Range("a1").CurrentRegion.Clear
  6.  T.Range.SpecialCells(xlCellTypeVisible).Copy
  7.  TempSheet.Range("a1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
  8.  Application.CutCopyMode = False
  9.  TableArray_2 = TempSheet.Range("a1").CurrentRegion
  10. End Function
図8


25行目「TempSheet.Range("a1").CurrentRegion.Clear」では、データの貼り付け範囲をクリアしています。図3の05行目では「UsedRange」を使いましたが、ユーザーが触れる可能性の無いシートでしたら、この「CurrentRegion」でも良いと思います。

27行目「T.Range.SpecialCells(xlCellTypeVisible).Copy」では、テーブルのタイトルを含めた範囲内の「可視セル」の部分をコピーしています。ここで使っている「SpecialCellsメソッド」には、図9のように2つのパラメータがあります。
SpecialCellsメソッド
パラメータデータ型内容
TypeXlCellType
(図10)
取得するセルの種類(必須)
ValueXlSpecialCellsValue
(図11)
Typeが「xlCellTypeConstants」「xlCellTypeFormulas」の場合に、
その種類を指定(複数の場合は加算可)
図9


27行目のコードのカッコ内が第一引数であるTypeパラメータで、今回は可視セルを表す定数「xlCellTypeVisible」を使用しています。図10のように全部で10個の内から選択します。
XlCellType列挙型
定数内容
xlCellTypeFormulas-4123数式が含まれているセル
xlCellTypeComments-4144コメントが含まれているセル
xlCellTypeAllFormatConditions-4172 表示形式が設定されているセル
xlCellTypeSameFormatConditions-4173同じ表示形式が設定されているセル
xlCellTypeAllValidation-4174条件の設定が含まれているセル
xlCellTypeSameValidation-4175同じ条件の設定が含まれているセル
xlCellTypeConstants2定数が含まれているセル
xlCellTypeBlanks4空白セル
xlCellTypeLastCell11使われたセル範囲内の最後のセル
xlCellTypeVisible12すべての可視セル
図10


なお、Typeパラメータに「xlCellTypeConstants」または「xlCellTypeFormulas」を使用した場合には、更に図11のValueパラメータで細かな種類を指定できます。複数の項目を同時に指定可能で、その場合は定数を「+」でつなげるか、値を加算して指定します。
XlSpecialCellsValue列挙型
定数内容
xlNumbers1数値のあるセル
xlTextValues2テキストのあるセル
xlLogical4論理値のあるセル
xlErrors16エラーのあるセル
図11


例えば
「セル範囲.SpecialCells(xlCellTypeConstants, 1 + 2 ).Select」
とすれば、セルに直接入力された値が数値か文字列であるセルを選択することになりますし、また
「セル範囲.SpecialCells(xlCellTypeFormulas, 4 ).Select」
とすれば、数式でその戻り値がTrueかFalseであるセルを選択することになります。
なお「xlErrors(エラーのあるセル)」のxlCellTypeConstants(直接入力値)が何に当たるのか、考えてみましたが思い当たりませんでした。

27行目で「テーブルの可視セルの部分」をコピーした後は、図3のコードと全く同じです。
28行目で貼り付け先シートに「値と数式の書式」を貼り付け、30行目でコピーモードを解除しています、
そして32行目で、貼り付けたデータを配列にし、関数プロシージャの戻り値にしています。

寄り道
なお、図3の08行目「T.Range.Select」の、Rangeの代わりに「DataBodyRange」を使って「タイトル行を含めずにデータ部分のみを配列化」する方法も考えられますが、色々なデメリットが考えられます。
1つ目は「タイトルとデータを切り離し」してしまうと、テーブルの設計を変更した際にはプログラムをすべて見直す必要が出てきます。また、絞り込んだデータをそのままワークシートに貼り付けてユーザーに見せるようなシステムでは、データ内容に対して表示タイトルが合っているかの確認が必要になります。

2つ目は、テーブルの絞り込みの結果「全データが非表示」だった場合です。テーブルを使っている場合、全データが非表示の時にタイトルを含めない「非表示部分のみ」を選択すると、何故か「全データを選択」することになってしまうのです。
普通の感覚なら、全行が非表示であれば、コピー時に「選択されていません」などと警告されたり、貼り付け時に「貼り付けるものがありません」などと怒られそうな気がするのですが、テーブルの場合は全データを取得してしまうのでエラーは出ません。この現象は、「ListObjectのデータ変更」や「ListObjectのデータ削除」の時にも悪い影響を与え、知らないでいると「全データ書き換え」や「全データ削除」と取り返しのつかない結果になってしまいます。

そのため「通常は非表示にしないタイトル行」を含めたテーブル全体を(DataBodyRange では無く Range を使って)選択する事で、間違った結果を得ないようにしています。
なお、もしデータだけを配列化したい場合には、貼り付け先からデータを取得する段階で「タイトル行を除いた処理」をすることで実現できます。

一方で図8の27行目「T.Range.SpecialCells(xlCellTypeVisible).Copy」でRangeの代わりに「DataBodyRange」を使用すると、「全データが非表示」だった場合には「SpecialCells(xlCellTypeVisible)での可視セル」が1つも無いことになってしまい、エラーが発生します。このエラーを「絞り込みデータ=ゼロ」の判断材料にして分岐処理することも1つの手法だと思います。

なお図3・図8とも、ワークシート上のセル範囲を一気に配列化していますので、出来上がる配列のインデックスは「1スタート」に固定されてしまうのも注意点の1つです。

2-3.テーブルの可視セルのデータを配列化 その1

上記のように、テーブルのデータを他のシートに貼り付けてから処理するのでは無く、直接テーブルのデータを検索し、可視セルのデータであれば配列に格納するのが図12です。
  1. '========== ⇩(3) テーブルの可視セルのデータを配列化 1 ============
  2. Function TableArray_3(T As ListObject) As Variant
  3.  Dim buf() As Variant   '←戻り値とする一時的な配列
  4.  Dim i As Long       '←カウンタ変数(配列の行位置)
  5.  Dim j As Long       '←カウンタ変数(配列の列位置)
  6.  Dim k As Long       '←テーブルのデータ行+タイトル行の全行数
  7.  Dim ColCnt As Long    '←テーブルの列数
  8.  ColCnt = T.HeaderRowRange.Count
  9.  For k = 1 To T.Range.Rows.Count
  10.   If T.Range.Rows(k).Hidden = False Then
  11.    i = i + 1
  12.    ReDim Preserve buf(1 To ColCnt, 1 To i)
  13.    For j = 1 To ColCnt
  14.     buf(j, i) = T.Range.Cells(k, j)
  15.    Next j
  16.   End If
  17.  Next k
  18.  TableArray_3 = WorksheetFunction.Transpose(buf)
  19. End Function
図12


48行目「ColCnt = T.HeaderRowRange.Count」では、テーブルの列数を取得しています。「HeaderRowRange.Count」以外に「ListColumns.Count」や「AutoFilter.Filters.Count」を使ってもOKです。

50行目「For k = 1 To T.Range.Rows.Count」では、カウンタ変数kをテーブルのタイトル行+データ行の合計行数だけ回しています。
51行目「If T.Range.Rows(k).Hidden = False Then」は、テーブルの変数kで指定した行位置(タイトル行も含んだ行位置。タイトル行が1となる)が非表示か可視行かを調べ、可視行の場合に以下の52~56行目を実行していきます。全データが非表示になってもタイトル行は可視ですので、少なくとも1回は実行されることになります。
なお、行が可視か非表示かの検査手段として、ここでは「Hiddenプロパティ」を使用しましたが、「行の高さ」を検査手段として「If Not T.Range.Rows(i).Height = 0 Then」としても同じです。

52行目「i = i + 1」は、この関数の戻り値である配列の行番号iを1つ増やしています。変数iはLong型なので初期値はゼロですので、タイトル行のところで「1」になります。
53行目「ReDim Preserve buf(1 To ColCnt, 1 To i)」は、42行目で動的配列として宣言した変数bufのサイズを設定しています。可視行があるたびに配列のサイズを増やしていく都合上、最終次元の2次元目を「データ行数」にしていますので、形としては「行列が逆転」している配列となっています。そのため配列の1次元目は、不変のテーブルの列数ColCntに当てます。
なおReDimで配列を大きくしても、配列に既に格納したデータは消したくないので「Preserve指定」をしています。

54行目「For j = 1 To ColCnt」では、カウンタ変数jをテーブルの列数分だけ回します。
55行目「buf(j, i) = T.Range.Cells(k, j)」では、可視行のテーブルのデータを配列に格納します。行列が逆転している配列bufに代入しますので、buf(i, j)では無く、buf(j, i)となります。

行列が逆転している配列へのデータ格納が完了したら、60行目「TableArray_3 = WorksheetFunction.Transpose(buf)」でTranspose関数を使って行列を戻してから、関数プロシージャの戻り値に設定します。

なお、この方式では以下のような注意点があります。
 1.絞り込み行がゼロ(=1行も抽出されず)の場合、戻り値が1次元配列になる。
 2.データが元々無いテーブルの場合は、データ値にEmptyが入る。
 3.テーブルの日付データは、戻り値の配列内では文字列型に変換されてしまう。

1つ目は、配列bufに格納されるのがタイトル行だけになり、58行目を終わった時点ではデータが存在する時と同じ2次元配列なのですが、60行目のTranspose関数により、1次元配列に変換されてしまいます。
別な説明方法をすると「縦1列の配列」→Transpose関数→「横1行の配列」=1次元配列 という感じです。
そのため関数の戻り値(配列)を処理する時に、次元数を確認してデータの有無を判断する必要が出てきます。

2つ目は、処理範囲をDataBodyRangeでは無くRangeとしているために、データが存在しないテーブルの場合はInsertRowRangeも含んでしまいます。そのため絞り込んでいない状態では、データ無し=Emptyが配列に格納されます(但し、何らかの条件で絞り込んでいる時は、タイトル行のみの配列となります)。
かと言ってDataBodyRangeを使用すると、データが無いテーブルの場合はエラーが発生しますので「On Error Resume Next」等を使った処理が必要となります。DataBodyRange・Rangeのどちらが適切かは、状況によります。

3つ目は、Transpose関数を使用しているために起こる現象のようです。ワークシート上での行列入れ替えでは、もちろんデータ型が変わることは無く、また入れ替え前の配列内では日付型と認識してくれているのですが、Transpose後は文字列に変わってしまいます。配列を1要素ずつ行列変換するのは大変なので、後処理をするしかなさそうです。

2-4.テーブルの可視セルのデータを配列化 その2

上記図12の問題点1(抽出行ゼロの時、戻り値が1次元配列)と問題点3(日付のデータ型が変わる)を解消するものが図13です。抽出された行数を取得し、事前に「戻り値と同じ状態の2次元配列」を作ってからデータを格納する方法です。
  1. '========== ⇩(4) テーブルの可視セルのデータを配列化 2 ============
  2. Function TableArray_4(T As ListObject) As Variant
  3.  Dim buf As Variant   '←戻り値とする一時的な配列
  4.  Dim i As Long      '←カウンタ変数(配列の行位置)
  5.  Dim j As Long      '←カウンタ変数(配列の列位置)
  6.  Dim k As Long      'テーブルのデータ行+タイトル行の行数
  7.  Dim CellsCnt As Long   '←絞り込みデータのセル個数
  8.  Dim ColCnt As Long   '←テーブルの列数
  9.  CellsCnt = T.Range.SpecialCells(xlCellTypeVisible).Count
  10.  ColCnt = T.HeaderRowRange.Count
  11.  ReDim buf(1 To (CellsCnt / ColCnt), 1 To ColCnt)
  12.  For k = 1 To T.Range.Rows.Count
  13.   If T.Range.Rows(k).Hidden = False Then
  14.    i = i + 1
  15.    For j = 1 To ColCnt
  16.     buf(i, j) = T.Range.Cells(k, j)
  17.    Next j
  18.   End If
  19.  Next k
  20.  TableArray_4 = buf
  21. End Function
図13


79行目「CellsCnt = T.Range.SpecialCells(xlCellTypeVisible).Count」は、テーブルで可視状態になっているセルの個数を取得しています。Rangeを使っていますので、タイトルのセル数+抽出行のセル数になります。
80行目「ColCnt = T.HeaderRowRange.Count」は、テーブルのタイトル部のセル数(=列数)を取得しています。
79~80行目のイメージは図14のようになり、可視セルの個数と横列の個数が分かりましたので、割り算をすれば「縦の数(=可視の行数)」となります。
テーブルの可視部の個数
図14


81行目「ReDim buf(1 To (CellsCnt / ColCnt), 1 To ColCnt)」では、79~80行目の結果を使って、可視の要素(タイトルを含む)の配列サイズを設定しています。図12の配列bufとは異なり、行/列はテーブルの向きと同じになっています。

83~90行目では、テーブルの全行に渡って可視行か否かを調べ、可視行のみを処理しています。
83行目「For k = 1 To T.Range.Rows.Count」で、カウンタ変数kを「テーブルの全行数(タイトル部+データ部)分だけ回します。
84行目「If T.Range.Rows(k).Hidden = False Then」では、その行が非表示か否かを確認します。非表示で無い(=可視行)時に、85~88行目を実行します。
可視行の場合は85行目「i = i + 1」で、データを格納する配列の行位置を確定します。変数iは73行目でLong型で宣言しているので、初期値はゼロです。そのため初めのデータ(=タイトル行)は配列の1行目に入ることになります。
86行目「For j = 1 To ColCnt」では、カウンタ変数jをテーブルの列数(=配列の横幅)分だけ回します。ColCntの代わりにUbound(buf, 2)を使っても同じです。
87行目「buf(i, j) = T.Range.Cells(k, j)」で、各列の値を配列に格納していきます。値は「テーブルの全データ(T.Range)」を基準とした位置のデータ「.Cells(k, j)」となります。k値はテーブルのタイトル行を1とした行位置、j値は列位置です。

全てのデータを配列bufに格納したら、92行目「TableArray_4 = buf」で、関数プロシージャの戻り値にそのまま設定します。
但し、この図13の方法でも「データが元々無い場合で、データの絞り込みをしていない状態では、データ値にEmptyが入る」という問題点は解消できません。

2-5.テーブルの可視セルのデータを配列化 その3

絞り込まれた行数が少ない場合であれば、図13のように「テーブルのデータをその都度読み取る」方式でも問題ないのですが、抽出される行数が多かったり列数が多いテーブルの場合には、セルのデータに直接アクセスする回数が増え、処理時間も多く掛かってしまいます。
そこで、一旦テーブルの全データを取り込み、セルへの直接アクセスを減らしているのが図15です。
  1. '========== ⇩(5) テーブルの可視セルのデータを配列化 3 ============
  2. Public Function TableArray_5(T As ListObject) As Variant
  3.  Dim buf1 As Variant   '←テーブル全体のデータ
  4.  Dim buf2 As Variant   '←戻り値とする一時的な配列
  5.  Dim i As Long       '←カウンタ変数(配列の行位置)
  6.  Dim j As Long       '←カウンタ変数(配列の列位置)
  7.  Dim k As Long       'テーブルのデータ行+タイトル行の行数
  8.  Dim CellsCnt As Long   '←絞り込みデータのセル個数
  9.  Dim ColCnt As Long    '←テーブルの列数
  10.  buf1 = T.Range
  11.  CellsCnt = T.Range.SpecialCells(xlCellTypeVisible).Count
  12.  ColCnt = UBound(buf1, 2)
  13.  ReDim buf2(1 To (CellsCnt / ColCnt), 1 To ColCnt)
  14.  For k = 1 To UBound(buf1, 1)
  15.   If T.Range.Rows(k).Hidden = False Then
  16.    i = i + 1
  17.    For j = 1 To ColCnt
  18.     buf2(i, j) = buf1(k, j)
  19.    Next j
  20.   End If
  21.  Next k
  22.  TableArray_5 = buf2
  23. End Function
図15


110行目「buf1 = T.Range」では、テーブルの全範囲(タイトル部+データ部)のデータを配列buf1に代入しています。以降でテーブルのデータ(値)を取得するときは、このbuf1から取り出します。
111行目「CellsCnt = T.Range.SpecialCells(xlCellTypeVisible).Count」では、テーブルの可視セル数を取得します。これは図13の79行目と同じです。
112行目「ColCnt = UBound(buf1, 2)」では、テーブルの列数を取得します。図13の80行目「ColCnt = T.HeaderRowRange.Count」では、テーブルのタイトル行のセル数から列数を求めましたが、ここでは既に取得済みのテーブル値の配列(buf1)の横幅から取得しています。どちらを使ってもOKと思います。
113行目「ReDim buf2(1 To (CellsCnt / ColCnt), 1 To ColCnt)」では、可視データを格納する配列buf2のサイズを決めています。

115行目「For k = 1 To UBound(buf1, 1)」で、カウンタ変数kをテーブルの全行数(タイトル部+データ部)分だけ回します。
116行目「If T.Range.Rows(k).Hidden = False Then」では、その行が非表示か否かを確認し、可視行の場合に117~120行目を実行します。
可視行の場合は、117行目「i = i + 1」でデータを格納する配列の行位置を指定します。
118行目「For j = 1 To ColCnt」では、カウンタ変数jをテーブルの列数分だけ回します。
119行目「buf2(i, j) = buf1(k, j)」で、配列buf2の各要素に、事前に取り込んでおいたテーブルの配列buf1から値を拾い上げて格納していきます。

最後に124行目「TableArray_5 = buf2」で、可視行の分だけの配列を関数の戻り値にします。

2-6.テーブルの可視行だけを取り出して配列化 その4

上記の方法は、テーブルのデータ行の順番(≒並び変えた順番)を守って配列化しています。そのためにFor Each~Nextでは無くFor~Nextを使っているわけですが、もしデータの並び順はそれほど重要では無い というのであれば以下のような方法も考えられます。
また「データが無いテーブル+InsertRowRangeが可視状態」の場合でも、タイトル行のみの配列を戻すような工夫も盛り込んでみましたので参考にして下さい。
  1. '========== ⇩(6) 可視行だけを取り出して配列化4 ============
  2. Public Function TableArray_6(T As ListObject) As Variant
  3.  Dim r As Range     '←絞り込まれたデータの内の1行分
  4.  Dim buf As Variant   '←戻り値とする一時的な配列
  5.  Dim i As Long     '←カウンタ変数(配列の行位置)
  6.  Dim j As Long     '←カウンタ変数(配列の列位置)
  7.  Dim CellsCnt As Long    '←絞り込みデータのセル個数
  8.  Dim ColCnt As Long    '←テーブルの列数
  9.  On Error Resume Next
  10.   CellsCnt = T.DataBodyRange.SpecialCells(xlCellTypeVisible).Count
  11.  On Error GoTo 0
  12.  ColCnt = T.HeaderRowRange.Count
  13.  ReDim buf(1 To (CellsCnt + ColCnt) / ColCnt, 1 To ColCnt)
  14.  i = 1
  15.  For j = 1 To ColCnt
  16.   buf(i, j) = T.HeaderRowRange(j)
  17.  Next j
  18.  If Not CellsCnt = 0 Then
  19.   For Each r In T.DataBodyRange.SpecialCells(xlCellTypeVisible).Rows
  20.    i = i + 1
  21.    For j = 1 To ColCnt
  22.     buf(i, j) = r.Cells(1, j)
  23.    Next j
  24.   Next r
  25.  End If
  26.  TableArray_6 = buf
  27. End Function
図16


140行目「CellsCnt = T.DataBodyRange.SpecialCells(xlCellTypeVisible).Count」では、Range(テーブル全体)では無く「DataBodyRange(テータ部分)」の可視セルのセル数を取得しています。DataBodyRangeですので、データ数がゼロの時のInsertRowRangeは含まれませんし、また絞り込みで抽出された行がゼロであればエラーが発生しますので、139行目「On Error Resume Next」で、エラーをスルー(=変数CellsCnt の値がゼロになる)させています。

143行目「ColCnt = T.HeaderRowRange.Count」は図13・図15と同じく、タイトル部のセル数(=テーブルの列数)を取得します。
144行目「ReDim buf(1 To (CellsCnt + ColCnt) / ColCnt, 1 To ColCnt)」は、可視セルのデータを格納する配列のサイズを決めていますが、今回は「CellsCnt」がタイトル行を含まない値ですので、図13・図15とは少しだけ式が異なります。
縦方向は「 (CellsCnt + ColCnt) / ColCnt」と、(可視状態のデータセル数+タイトルセル数) ÷ タイトルセル数 という数式になります。横方向は、変わらずに「ColCnt」とテーブル列数そのものです。

データの格納も、タイトル部とデータ部は分けて入れていく必要があります。
146行目「i = 1」は、格納する配列bufの1行目にデータを入れる事を表しています。
147行目「For j = 1 To ColCnt」は、カウンタ変数jをテーブルの列数分だけ回します。ColCntの代わりに「UBound(buf, 2)」を使っても同じです。
148行目「buf(i, j) = T.HeaderRowRange(j)」で、タイトル行の値を1要素ずつ配列に格納しています。

タイトル部のデータ格納が完了したら、151~158行目でデータ部の値を格納していきます。
なお可視状態のDataBodyRangeが存在しない(抽出された行がゼロ、またはデータが全く無いテーブル)場合には、データを入れる必要がありません。と言うより、格納する配列bufに「データ用の場所が無い」ので入れることができません。
ですので151行目「If Not CellsCnt = 0 Then」で、140行目がエラーの場合(=可視データが無い)は、152~157行目を実行せずに、160行目でタイトルデータだけの配列を戻すことになります。

可視状態のデータ部が存在する場合は、152行目「For Each r In T.DataBodyRange.SpecialCells(xlCellTypeVisible).Rows」で、可視状態のデータ領域から1行ずつ取り出します。For Eachですから取り出す順番は保障されていません。
寄り道
146~158行目のコード(タイトル部の配列化とデータ部の配列化)を「まとめて配列化」しようとして「DataBodyRangeの代わりにRange」を使う方法を思いつく方もいるかもしれません。しかしFor Eachでは取り出される順番が保障されていないために、例えば「タイトル行が配列の先頭にならなかったり、InsertRowRangeを処理した結果のEmptyが入ってしまったり」する可能性が出てきてしまいます。
そのため「タイトル行を含めた範囲に対する処理」にFor Each~Nextを使用することは危険です。

153行目「i = i + 1」では、格納する配列bufの行位置を指定しています。146行目で変数iに「1」を代入してタイトルデータを格納していますので、153行目では変数iは「2」からスタートします。

154行目「For j = 1 To ColCnt」では、カウンタ変数jをテーブルの列数分だけ回します。
155行目「buf(i, j) = r.Cells(1, j)」では、取り出した可視データ行(Rangeオブジェクト変数r)の範囲内の、「1行目×j列目」の位置のデータを、配列bufの要素に格納します。

配列へのデータ格納が完了したら、160行目「TableArray_6 = buf」で、関数プロシージャの戻り値に設定します。

2-7.テーブルをコピーし、クリップボード上のデータをDataObjectで配列化

図8では「テーブルの可視セルをコピーし、別シートに貼り付け」て、貼り付けたデータに対して処理を行っていました。
今回は、コピーするところまでは同じですが、クリップボードに入ったデータを直接取り出して処理をしています。

まず、クリップボードからデータを取り出すために、今回はDataObjectオブジェクトというものを使用します。DataObjectには図17のようなメソッドがあり、クリップボードとのデータやり取りが可能です。
DataObjectのメソッド
メソッド内容
ClearDataObjectの全データを削除
GetFormat(データ形式)DataObject上の形式の有無 → 出力
GetFromClipboardClipboardのデータ → コピー → DataObject
GetText([データ形式])DataObjectのデータ → テキスト文字を出力
PutInClipboardDataObjectのデータ → 移動 → Clipboard
SetText(格納データ,[データ形式])テキスト文字列 → コピー → DataObject
StartDrag(操作の効果)DataObjectのドラッグ&ドロップを開始
図17


図18は、今回の流れです。VBAはクリップボードに直接アクセスできない為、DataObjectを通して操作します。
クリップボードとDataObjectの関係
図18


まずテーブルの範囲をコピーすると、そのデータ(可視セルのみ)はクリップボードに入ります。図8の時には「他のシートにデータを貼り付け(Paste)」し、その貼り付けたデータを使って処理を行ってきました。(図18の右上側)
今回は、クリップボードに入ったデータをDataObjectオブジェクトにコピーし、そこからテキストデータとして取り出してから処理(今回は配列化)をしています。(図18の右下側)

実行コードは、図19のようになります。
  1. '========== ⇩(7) クリップボードからデータ取り出し配列化1 ============
  2. Function TableArray_7(T As ListObject) As Variant
  3.  Dim Dobj As Object   '←DataObjectオブジェクト
  4.  Dim buf1 As String   '←クリップボードのデータ
  5.  Dim buf2 As Variant   '←行ごとに分割した配列データ
  6.  Dim buf3 As Variant   '←1行データを列に分割した配列データ
  7.  Dim buf4 As Variant   '←戻り値となる配列
  8.  Dim AllStr As Long    '←クリップボードのデータの文字数
  9.  Dim CrLfCnt As Long   '←その内のCrLfの数(=行数)
  10.  Dim TabCnt As Long   '←その内のTabの数(=列数ー1)
  11.  Dim i As Long      '←配列の行位置
  12.  Dim j As Long      '←配列の列位置
  13.  Set Dobj = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
  14.  T.Range.SpecialCells(xlCellTypeVisible).Copy
  15.  Dobj.GetFromClipboard
  16.  On Error Resume Next
  17.   buf1 = Dobj.GetText
  18.   Application.CutCopyMode = False
  19.   AllStr = Len(buf1)
  20.   CrLfCnt = (AllStr - Len(Replace(buf1, vbCrLf, ""))) / 2
  21.   TabCnt = AllStr - Len(Replace(buf1, vbTab, ""))
  22.   ReDim buf4(1 To CrLfCnt, 1 To (TabCnt + CrLfCnt) / CrLfCnt)
  23.   buf2 = Split(buf1, vbCrLf)
  24.   For i = 1 To UBound(buf4, 1)
  25.    buf3 = Split(buf2(i - 1), vbTab)
  26.    For j = 1 To UBound(buf4, 2)
  27.     buf4(i, j) = buf3(j - 1)
  28.    Next j
  29.   Next i
  30.   If Not Err.Number = 0 Then Exit Function
  31.  On Error GoTo 0
  32.  TableArray_7 = buf4
  33.  Set Dobj = Nothing
  34. End Function
図19


183行目「Set Dobj = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")」では、実行時バインディングで「MSFormsのDataObjectオブジェクト」を生成し、変数Dobj(DataObjectの略のつもり)としています。
長い記号でのCreateObjectなので、もっと簡単に「参照設定をして事前バインディング」を使うやり方を下の方で紹介します。

185行目「T.Range.SpecialCells(xlCellTypeVisible).Copy」では、テーブルの可視セル範囲をコピーしています。これは図8の27行目と同一で、直接セル選択していないため「SpecialCellsを使って可視セルに絞る」必要があります。
186行目「Dobj.GetFromClipboard」では、クリップボードに入ったデータをDataObjectにコピーしています。

188行目「On Error Resume Next」については、205行目のところで説明します。
189行目「buf1 = Dobj.GetText」は、DataObjectから「テキストデータ」を取り出し、変数buf1に代入をします。テーブルのような二次元の表をコピーしていますが、この段階では配列では無く単純な「文字列」です。
但し表の形を文字列で表すため、図20の右側のように「タブ(vbTab)」が次の列に移る印、また「改行(vbCrLf)」が次の行に移る印になっています(セル内の改行は、vbLf になります)。
(図20の右側は、テーブルをコピーした後、Microsoft Wordに「テキスト貼り付け」したものです。オプションの表示の設定で、タブと段落記号(=改行)も表示させています。)
取り出したテキストデータの形
図20


テキストデータの取得が完了したら、190行目「Application.CutCopyMode = False」でコピーモードを解除します。

192~195行目では、コピーしたセル範囲の縦×横のサイズを計算し、値を格納する配列サイズを指定します。その手順を図21に示します。
データ格納する配列のサイズ計算
図21


コピーしたテーブルの可視セル範囲は図21の左側の2行3列とし、それぞれのセルには値が入っているとします。GetTextメソッドで得られるテキストデータは、セル値を「列の移動はTab」「行の移動はCrLf」で繋いだものになります。そのテキストデータの中の「Tab」と「CrLf」の数を数えることにより、元のセル範囲のサイズ(=データを格納する配列サイズ)を計算することが可能です。

まず、取得したテキストデータの全体の「文字数」を192行目「AllStr = Len(buf1)」で取得します。
193行目「CrLfCnt = (AllStr - Len(Replace(buf1, vbCrLf, ""))) / 2」は、CrLfの数を調べています。元のテキストデータのCrLfの部分を「""(長さゼロの文字列)」で置き換え、元の長さから引き算するのですが、CrLfは「CrとLfの2つの文字」ですので「2」で割ることでCrLfの数を求める事が出来ます。
194行目「TabCnt = AllStr - Len(Replace(buf1, vbTab, ""))」は、同様にTabの数を調べています。Tabは1つの文字なのでそのままでOKです。

195行目「ReDim buf4(1 To CrLfCnt, 1 To (TabCnt + CrLfCnt) / CrLfCnt)」では、CrLfの数とTabの数から行列の数を計算し、データを最終的に格納する配列buf4のサイズを指定しています。

197行目「buf2 = Split(buf1, vbCrLf)」では、GetTextで得たテキストデータを「CrLf」で分割して配列buf2に代入しています。図21の左図が元のテーブルだとすれば、図22のようになります。

CrLfで文字列を分割
図22


図22では変数buf1の文字列は「A Tab B Tab C CrLf D Tab E Tab F CrLf」となっていますので、文字列「CrLf」で分割し、配列buf2の各要素に代入します。buf1には最後にもCrLfがありますので、3番目(インデックス=2)の要素まで作られますが、中に入る値は「""(長さゼロの文字列)」となります。

198~199行目では、197行目で分割し配列buf2の各要素のテキストデータを「Tab」で更に分割し配列buf3に代入します。図22の続きとすると図23のようになります。

更にTabで文字列を分割
図23


197行目で得られた配列buf2の各要素(最下段は除く)は、最終的な配列(buf4)の1つの行データを集めたものになっていて、複数の各要素は「Tab」でつながっている形です。ですので、要素を切り離すためには「Tab」で分割していきます。
198行目「For i = 1 To UBound(buf4, 1)」では、最終的な格納配列(buf4)の行数分だけカウンタ変数iを回し、1行分ずつ処理をしていきます。配列buf2の要素は「最終的な配列buf4の行数よりも1つ多い」ので、buf2の最後の要素(長さゼロの文字列しか入っていない)は処理されないことになります。
199行目「buf3 = Split(buf2(i - 1), vbTab)」は、1行分のデータを「Tab」で分割し、配列buf3に代入します。

buf3まで分割したら、その配列buf3の各要素の値を「最終的な配列buf4に格納」していきます。流れとしては図24のようになります。
最終的配列に値を格納
図24


まず199行目で「1行分の配列buf3」を作ったら、200行目「For j = 1 To UBound(buf4, 2)」でカウンタ変数jを列数分だけ回します。列数は「最終的な配列buf4の列数」であり、また「配列buf3の要素数」でもあります。
201行目「buf4(i, j) = buf3(j - 1)」では、配列buf3の各要素の値を、最終的な配列buf4の要素に格納していきます。

説明を先延ばしにしていた188行目「On Error Resume Next」の説明をしていきます。
今回のプログラムは、テーブルの範囲をコピーし、クリップボードに入ったデータを取り出して配列化の処理を行うという流れです。
しかしWindows上には、このマクロを動かしているExcel以外にも複数のアプリ(異なるExcelも含む)が同時に動いています。またクリップボードはWindowsの全てのアプリが共有できるものです。

例えば今回の185行目の「テーブルをCopy」した直後に、他のアプリが割り込んできて「クリップボードに関わる操作」をしてしまったとします。
GetTextメソッドでテキストデータを取り出す前なので、取得するデータは「目的のテーブルのデータでは無い」ことは確かですが、そのデータを処理する段階でエラーが出る場合があります。
図25では、クリップボードに「どんな操作をされたら、どんなエラーになるか」をまとめてみました。文字列の組み合わせは無限にあるので、全てが網羅されているかは分かりません。
Noクリップボードへの操作エラー内容
1コピーモード解除buf1=0となり、195行目でゼロ除算エラー
2オブジェクト(例えば図形)をCopy189行目のGetTextでエラー
3Text

Copy
テーブル状のデータ問題無く処理
4CrLf・Tabの各数が、テーブル状では無いデータ列数計算に小数点が発生し、195行目でエラー
5TabもCrLfも無いTextCrLfCnt=0となり、195行目でゼロ除算エラー
6先頭にCrLfがあるText199行目でbuf2(0)に値が入らず、201行目でエラー
7テーブル状データの後ろにも文字列があるText問題無く処理(後ろの文字列は無視)
図25


結論から言うと、処理コードの中の色々な部分でエラーが発生する可能性があるため、188行目「On Error Resume Next」以降でエラーをスルーさせ、処理が終わった205行目「If Not Err.Number = 0 Then Exit Function」でエラー番号を調べ、ゼロ以外の場合は関数プロシージャを抜け出します。まだ関数プロシージャの戻り値設定をしていない状態なので、Variant型の初期値Emptyが戻ることになります。

最後に208行目「TableArray_7 = buf4」では、最終的な配列buf4を関数プロシージャの戻り値に設定し、終了します。
なお、この関数プロシージャから戻される配列のデータは、全要素が「文字列」ですので、用途に応じてデータ型変換が必要となることに注意が必要です。
寄り道
なお、基本的にはシングルタスクのWindows上で動いているExcelで、CopyとGetFromClipboardの間で割り込みが本当に入る可能性があるのかは良く分かりません。特段「DoEvents」を入れている訳でも無いので2つのコードが分断されて実行されることは無いような気もします。

今回は、万が一の割り込みでプログラムが停止することの無いように、このような構成にしましたが、しかし本当に怖いのは、図25のNo.3やNo.7のように、割り込みをされたのに「正常に処理されて、あたかも正しいような配列データ」が戻ってしまった時だと思います。プログラムはその間違ったデータを元に処理を続けていき、元の大切なデータを書き換えてしまうかもしれないからです。
その意味でも、戻ってきたデータが「本当に正しいか」のチェックに、力を入れる必要があると思います。少なくとも「戻ってきた配列1行目が、引数として渡したテーブルのタイトル行と同じか」くらいは、重要なシステムなら行うべきかもしれません。

図19では「実行時バインディング」を使ったために「CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")」のように長くて無機質な文字列の並びをCreateObjectしないといけませんでした。
これを「事前バインディング」で、必要なライブラリを参照設定しておけば、もっと分かりやすい設定となります。

事前バインディングは、図26のようにVBE画面の「ツール」→「参照設定」で表示される「参照設定ダイアログ」にライブラリを登録する必要があります。
ライブラリの登録方法
図26


リストの中から探し出してチェックをONにすればよいのですが、なぜか今回必要な「Microsoft Forms 2.0 Object Library」は見当たりません(見つかる環境もあるのかもしれません)。その場合はダイアログの横の「参照」ボタンから、ライブラリを直接するのが正式なやり方のようです。

なお、もっと簡単に登録できる方法があります。図27のように「シート上に、ActiveXコントロールを配置(A)」または「ユーザーフォームを挿入(B)」すれば、自動的にライブラリが登録されます。もし作ったActiveXコントロールやユーザーフォームが不要なら、すぐに削除しても問題ありません。ライブラリは消えずにBookに残ってくれます。(逆に一旦ActiveXやUserFormでライブラリを追加した時の、解除方法が分かりませんでした。Book内のどこかに情報が残ってしまうようです。) ActiveXコントロールやUserFormを作ることで登録可
図27


上記のどの方法かでライブラリを登録した状態で、図28のようなコードにします。図19と異なる部分を見え消しと赤字で表します。
  1. '========== ⇩(8) クリップボードからデータ取り出し配列化2 ============
  2. Function TableArray_8(T As ListObject) As Variant
  3. ' Dim Dobj As Object  
  4.  Dim Dobj As MSForms.DataObject   '←DataObjectオブジェクト
  5.  Dim buf1 As String   '←クリップボードのデータ
  6.  Dim buf2 As Variant   '←行ごとに分割した配列データ
  7.  Dim buf3 As Variant   '←1行データを列に分割した配列データ
  8.  Dim buf4 As Variant   '←戻り値となる配列
  9.  Dim AllStr As Long    '←クリップボードのデータの文字数
  10.  Dim CrLfCnt As Long   '←その内のCrLfの数(=行数)
  11.  Dim TabCnt As Long   '←その内のTabの数(=列数ー1)
  12.  Dim i As Long      '←配列の行位置
  13.  Dim j As Long      '←配列の列位置
  14. ' Set Dobj = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
  15.  Set Dobj = New MSForms.DataObject
  16.  T.Range.SpecialCells(xlCellTypeVisible).Copy
  17.  Dobj.GetFromClipboard
  18.  On Error Resume Next
  19.   buf1 = Dobj.GetText
  20.   Application.CutCopyMode = False
  21.   AllStr = Len(buf1)
  22.   CrLfCnt = (AllStr - Len(Replace(buf1, vbCrLf, ""))) / 2
  23.   TabCnt = AllStr - Len(Replace(buf1, vbTab, ""))
  24.   ReDim buf4(1 To CrLfCnt, 1 To (TabCnt + CrLfCnt) / CrLfCnt)
  25.   buf2 = Split(buf1, vbCrLf)
  26.   For i = 1 To UBound(buf4, 1)
  27.    buf3 = Split(buf2(i - 1), vbTab)
  28.    For j = 1 To UBound(buf4, 2)
  29.     buf4(i, j) = buf3(j - 1)
  30.    Next j
  31.   Next i
  32.   If Not Err.Number = 0 Then Exit Function
  33.  On Error GoTo 0
  34.  TableArray_8 = buf4
  35.  Set Dobj = Nothing
  36. End Function
図28


なお、223行目と235行目を一緒にし、図29のようにしてもOKです。
  1. Dim Dobj As New MSForms.DataObject
図29


2-8.各方法のまとめ

テーブルのデータを配列化する方法を、上記で8種類紹介しました。しかし「どれを使えば良いんだ?」と思われると思いますので、各方法での処理スピード等を比較してみました(図30)。
なお、試したテーブルは「10列」で、1000行のデータがあるものと、タイトルのみでデータがゼロ(InsertRowRangeのみ)の状態のものです。PCはギリギリでWindows11にアップグレード出来ないi5です。環境によって全く違う値になると思うので、相対的な参考値として捉えてください。
プロシージャ処理速度(ms)戻される配列の
データ型再現性
データ:1000行データ:ゼロ行
全行可視10行に絞込そのまま文字列数値日付
TableArray_1777756
TableArray_2726852
TableArray_377180×(文字列)
TableArray_466200
TableArray_526182
TableArray_64530
TableArray_7372427×
(文字列)
×
(文字列)
TableArray_8412127
図30


処理速度の方は、10回動かしてみての平均値(単位ms:1/1000秒)です。またテーブルのデータには「文字列」「数値」「日付」の値を入れ、戻されてきた配列内のデータの型を調べたものが図30の右側3列になります。個別の説明でも記しましたが、文字列として返してしまうものがあるので注意が必要です。

TableArray_1とTableArray_2は、別シートにデータを貼り付けているために、ある程度の時間がかかってしまいます。また、別シートを準備する必要もあります。

TableArray_3~TableArray_5では、Hiddenプロパティ値を調べるのにはそれほど時間が掛からないようです。但しTableArray_3はTranspose関数で行/列をひっくり返す時に「日付型→文字列型」になってしまうデメリットがあります。また、一旦テーブルをメモリ(配列)に取り込んでから処理をするTableArray_5は、抽出行が多い場合は効果が出ています。

TableArray_6はFor Each~Nextで、可視行のみを取り出して処理するので、抽出行の数に比例して時間が掛かります。並び順をあまり気にしないシステムでは有効と思います。

TableArray_7とTableArray_8は、バインディングが実行時か事前かの違いですが、処理時間には違いが見られませんでした。これは、プロシージャを頻繁に呼び出さないテストのためと思います。
また、クリップボード経由のデータ処理にはある程度の時間がかかるようです。それに何と言ってもデータ型が全て文字列になってしまうため、配列を受け取った側での処理に気を遣う必要があります。

アプリ実例

DVD等の内容・保管場所等管理システム
先行予約可能な備品予約・貸出システム
ToDoリストで個人タスク管理
会社番号検索システム