私が業務でよく行っているダウンロードデータ等を規定フォームに転記する際に、複数セルの値を１つのセルにカンマ区切りで代入する方法のまとめ記事です。
イメージとしては動画のような形です↓
サンプルコード
Sub SampleCode3()
Dim Num() As String
Dim i As Long, j As Long, max As Long
Dim n As Long
max = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To max
j = 2
n = 0
Do While Cells(i, j) <> ""
ReDim Preserve Num(n)
Num(n) = Cells(i, j)
j = j + 1
n = n + 1
Loop
Cells(i, "j") = Join(Num, ",")
Next i
End Sub
コードのポイント
ポイントとしてはたった２つです。
- 動的配列にデータを格納（Preserve)
- Join関数でセルにカンマ区切りで代入
まずは、上記ポイントについて解説していきます。
既に理解されている方はこの記事下にデータ転記の記事リンクを貼っていますので、読み飛ばしてください。
配列変数
上記コードで使っている配列について解説します。※配列変数と配列は同義です。
変数とは？
配列の説明の前に、そもそも変数とは何か？
一時的にデータを保存しておく箱のようなものです。
変数には１つしか値を入れておくことができません。
変数についての詳細はこちら↓
配列とは？
変数を理解した上で、配列のポイントは以下の通りです。
- 通常の変数は１つの変数に１つしか値が入りませんが、複数の値が入る変数
- 複数の値を整理するために、要素数が振られている※要素数は通常「0」から始まる
配列を使うメリットとして、コードの処理速度が速くなります。
配列を使う時のルール
Sub SampleCode()
Dim Ward(3) As String
Ward(0) = "荻原"
Ward(1) = "萩原"
Ward(2) = "荻本"
Ward(3) = "萩本"
MsgBox Ward(3)
End Sub
配列変数の宣言
Dim 配列名（3）
配列を使う場合、通常の変数と同じようにDimで宣言します。
その時に配列をいくつ作るのかを括弧の中に指定します。上記の場合３つです。
※変数の型は格納する値によって変更します。(例)はStringの文字列型です。
配列に値を格納
配列名(要素数) = 値
格納する要素数を指定して値を代入します。
Ward(0) = "荻原"
Ward(1) = "萩原"
Ward(2) = "荻本"
Ward(3) = "萩本"
配列を取り出す場合
Debug.print 配列名(3)
配列名と要素数を指定します。
動的配列
Dim Num() As String
上記の配列（静的配列）の場合、変数宣言の時点で要素数を決めました。
しかし、サンプルコードのような動的配列の場合は配列宣言時には要素数は指定せず、空の（）にします。そして、実行途中で要素数を決めます。
どのような場合に使用するか？
単純な話で宣言時点では配列に格納する値がいくつになるかわからない場合です。
Redim
ReDim Preserve Num(n)
ReDim Num(要素数)
コード途中で要素数が決定した時点でReDimを使って要素数をいれるというルールです。
Preserve
Preserveを付けることで、それまで配列に入っていた値は保持されます。
逆にPreserveが無くRedimのみの場合それまで配列に入っていた値は失われます。
Preserveが無い場合
Do While Cells(i, j) <> ""
ReDim Num(n)
Num(n) = Cells(i, j)
j = j + 1
n = n + 1
Loop
Cells(i, "j") = Join(Num, ",")
Next i
新たな値が入った時点で、これまで配列に格納されていた値は消えます。
Preserveがある場合
Do While Cells(i, j) <> ""
ReDim Preserve Num(n)
Num(n) = Cells(i, j)
j = j + 1
n = n + 1
Loop
Cells(i, "j") = Join(Num, ",")
Next i
新たな値が入っても、これまで配列に格納されていた値は消えません。
配列とPreserveを覚えるメリット
- 配列：値を配列に格納することで、バラバラの値を１つの変数として扱うことができる。
- Preserve:宣言時点で要素数を確定させる必要がないので、入力してある値の数によって要素数を決めたいなどの場合は非常に便利です。
VBA初心者の方でもこの２つを使うとできることが増えます。そしてコードを短く書けます。
Join関数
Join関数・・Join(文字列型配列変数、区切り文字）
Join関数は、配列に入っている値を指定の区切り文字で結合します。
関連記事リンク
まとめ
本記事での転記のポイントは以下です。
- 動的配列にデータを格納（Preserve)
- Join関数でセルにカンマ区切りで代入
その他の方法は関連記事で解説しています。
コメント