【VBA備忘録】初心者脱却～コピペは配列を使え！～

2024年10月16日2024年10月29日

VBAで1番最初に使うであろうコピー＆ペースト

最初のとっかかりに最適なコピペ、範囲をCopyで選択→PasteSpecialで貼り付ける。
これをVBAでやると

Range(“A:A").Copy
Cells(1, 2).PasteSpecial

A列をコピーしてB1に貼り付けています。
コピペは超簡単なんですが範囲が広くなったり、関数を使っていたりすると（データが大きくなると）時間がかかるのがネック。
これにはいろんな高速化の方法がありますが、私は配列が最強だと思うので、やり方を紹介します。

別ブックを開いてコピペする場合は高速で開く方法があります！
こちらも併せてお読みください。

【VBA備忘録】爆速！別ブックを開かずに値を取得する

別ブックを開くのって遅いな～画面もチラチラするし～別ブックを開いてそこにある値を拾ってくるマク ...

https://indoor-nekura.work/vba_otherbook_open_super/

他にもVBAのやり方あり〼

配列とはなんぞや

配列とは仕切りのついた箱です

配列は簡単に言えば均等な仕切りのついた箱です。
まずは一次元と二次元配列を説明します。
ここでは任意の配列名『Box』を使用します。

一次元配列

Dim Box(10)
という配列を10個の仕切りで区切るという宣言をします。
そしてそれぞれ区切られた1部屋ずつに0～10の数字がつき、それぞれに値を格納することができます。
一次元配列は一方向にだけ区切る配列です。

二次元配列

Dim Box( 10 , 2 )
という配列を縦に10個、横に2個の仕切りで区切るという宣言をします。
そしてそれぞれ区切られた1部屋ずつには縦の番号0～10と横の番号0～2をカンマで区切った名前がつき、それぞれに値を格納することができます。
二次元配列は二方向に区切る配列です。
マンションのポストをイメージしていただくとわかりやすいと思います。
縦が階数、横が部屋番号です。例えばBox(9,1)は9階の1号室Box(9,2)は9階の2号室です。

Option Base 1

配列の要素は0から始まるので、Dim Box(10,2)と宣言すると縦11個/横3個の部屋ができることになります。
要素数が数字より1個ずつ多く（慣れれば平気だったりしますが）わかりづらいので、
Subの前にOption Base 1と入力しDim Box(10)と宣言すると1～10の10個の部屋にすることができます。

Dim Box(10,2)が縦1～10、横1～2の要素に変更されます。

Shironeco(嫁)

好みだけどね…

配列への格納の仕方

配列には一次元配列と二次元配列それぞれ値を入れることができます。
マンションのポストで各部屋に郵便物を届けるイメージです。
部屋番号を指定してそこに郵便物(値)を入れていきます。

格納する

Box（1）= Cells(1,1)

Box( 0 , 2 )="箱の中身は何かな？"

配列のインデックス番号を指定して直接セルを格納したり、文字列を格納することも可能です。

配列でコピペするには

配列だと速くなるのはなぜか

なぜ配列にすると速くなるか。それはExcelの仕様が関係してきます。
Excelは1動作ごとに再計算し画面表示を更新しています。
少ないデータでは気になりませんが、関数が入っていて行数も多いデータの場合、
そのデータをコピーし、ペーストすると再計算して画面表示するのに時間がかかります。
もう一つVBAのCopy/PasteSpecialはセルへのアクセスが遅いためセルをインプットする/アウトプットするに時間がかかるのです。
配列はデータを箱に入れる時に計算した値を入れることも、そのままの値を入れることも容易です。
箱に入れたデータもしくは箱に入れるときに計算したデータをExcelという箱の集まりにそのまま入れ直すので、
貼り付けた後に起こる遅い処理が1回ですべておわることと、Copy/PasteSpecialを使用しないでもいいので速くなるのです。

配列を宣言する

配列を作るよ！と宣言します。
不変(Dim)と可変(ReDim)で選べますが、コピペの時は可変の二次元配列で行きます。
可変は配列の部屋数を変えることができます。便利なのは配列の要素数が毎回変わるとき。
今回のようにコピペだとコピーする数は毎回変わってきます。
計算したり数えたりして部屋数を変えたいので可変にするのです。

配列の宣言

ReDim Box[配列名]([コピーする行数] ,[コピーする列数] )

[コピーする行数]は
Cells(Rows.Count, 1).End(xlUp).Row[最後の行数] – [最初の行数] で求めることができます。

順番に配列に入れていく（For ～ Next）

順番に配列へデータを入れてくにはどうしたらいいか、私はFor～Nextをまず使うことをオススメします。
For～Nextを使うと1セルずつ処理をLoopして配列に格納していくことができるため、計算した値を格納するのにも重宝します。

For ~ Nextで配列に格納

n=0
For i = [最初の行] To [最後の行]
Box( n , 0) = Cells(i, 1) '1列目のセルをそのまま格納
Box( n , 1) = Cells(i, 2) '2列目のセルをそのまま格納
Box( n , 2) = Box(n , 0)*Box(n , 1) '1列目×2列目の積を格納
n = n + 1 '配列(Box)のインデックス番号(階数)を増やしていきます
Next i

配列の中身をセルに貼り付ける

配列に格納された値をセルに貼り付けていきます。
このとき注意が必要なのが、貼り付ける先は配列の中身と同じ大きさ（範囲）にしないといけません。
つまり配列のインデックス数＝セル数にしなければいけません。
インデックス数がセル範囲より小さいときは問題ないのですが、セル範囲の方が小さい場合は、はみ出てしまった部分はペーストされません。なので同じ数にしとく癖をつけた方がいいです。

UBound関数を使う

UBound関数は配列のインデックスの最大値を取得できます。
よってUBoundで得たインデックス数は貼り付け先のセルの数なのです。

UBound関数

UBound( [配列名] , 引数 )

引数（ひきすう）は何次元の最大値なのかを指定します。
1次元であれば1、2次元であれば2、…と入力します。
インデックスの最大値がわかることで貼り付け先のセルの範囲指定できるようになります。

配列を貼り付けるセルを指定する

UBound関数を使って配列の大きさ分のセルを指定し、イコールで配列を繋げます。

配列をセルに貼り付け

a=[貼り付けるセル範囲の左上の行]
b=[貼り付けるセル範囲の左上の列]
Range(Cells( a , b ),Cells( a + Ubound([配列名],1), b + Ubound([配列名],2)))=[配列名]

配列でコピペするプログラム

Sub 配列でコピペ()

'コピーする方のシート
Dim this As Worksheet
Set this = Worksheets("コピー")

'貼り付ける方のシート
Dim that As Worksheet
Set that = Worksheets("ペースト")

'配列
ReDim box(10, 2)

'コピー処理＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

n = 0

With this
For i = 1 To 11
box(n, 0) = .Cells(i, 1)   '1列目のセルをそのまま格納
box(n, 1) = .Cells(i, 2)   '2列目のセルをそのまま格納
box(n, 2) = box(n, 0) & box(n, 1) '1列目×2列目の積を格納
n = n + 1 '配列(Box)のインデックス番号(階数)を増やしていきます
Next i
End With

'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

'ペースト処理＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

a = 3
b = 1
that.Range(that.Cells(a, b), that.Cells(a + UBound(box, 1), b + UBound(box, 2))) = box

'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

End Sub