VBAデータ転記方法-関数・配列編-

VBAを使った別ブックからのデータ転記の際に使えるステートメント、関数、配列について紹介します。

前回記事はこちら↓

VBAデータ転記方法-列挙型Enum-

データを転記する際に必須となる列挙型Enumの使い方をダウンロードデータを使って紹介しています。

今回は、私が実務でよく行っているGoogleフォームのダウンロードデータの転記作業を事例に下記のうち配列、関数を使ってデータを加工して、結合したデータをカンマ区切りでセルに代入する方法を解説していきます。

■データ転記に使用するステートメント・関数・配列

LEFT関数、Mid関数
InStr関数
Split関数、Join関数
配列
Enumステートメント

サンプルデータを使ってコードを確認したい方はこちら

作業の概要
VBAの動作内容
上記要件を操作するコード
まとめ

作業の概要

Googleフォームのダウンロードデータ（転記元ファイル）からExcelの規定フォームへの転記

VBAの動作内容

複数回答の場合、回答番号のみをカンマ区切りで結合して、１つのセルに入力する。
「その他」は番号を別途入れて、「その他」欄にその他回答を代入する。

上図の赤枠の転記元データを例に説明すると、転記元データは「回答番号」と「回答文言」が；（セミコロン）で区切られています。

「回答番号」のみカンマ区切りで結合する。赤枠：「1，2，3，4，5，6」
その他（成績が上がらない）は「回答番号」が無いので、「６」を付けて、列を変えてその他回答：「成績が上がらない」を転記する。

上記要件を操作するコード

Dim tmp
Dim x2 As Long
x2 = 0
'======問4======
  If DLファイル.Sheets(1).Cells(r, "E").Value <> "" Then '問4が空欄でなければ（条件）
  '=====複数回答時の処理=====
      If InStr(DLファイル.Sheets(1).Cells(r, "E"), ";") >= 1 Then
          tmp = Split(DLファイル.Sheets(1).Cells(r, "E"), ";")
                  Do While x2 <= UBound(tmp)
                      If Mid(tmp(x2), 2, 1) = "." Then  '左から2文字にピリオドがあれば（＝数字であれば）
                          tmp(x2) = Left(tmp(x2), 1)
                      Else
                          .Cells(inp行, CNo回答.問4_1) = tmp(x2)
                          tmp(x2) = 6 'その他の番号
                      End If
                      x2 = x2 + 1
                  Loop
                  .Cells(inp行, CNo回答.問4) = Join(tmp, ",") '回答欄へ転記
      Else '==単数回答時の処理==
          If Mid(DLファイル.Sheets(1).Cells(r, "E"), 2, 1) = "." Then '左から2文字にピリオドがあれば（＝数字であれば）
                  .Cells(inp行, CNo回答.問4) = Left(DLファイル.Sheets(1).Cells(r, "E"), 1)
              Else
                 .Cells(inp行, CNo回答.問4) = 6 'その他の番号
                 .Cells(inp行, CNo回答.問4_1) = DLファイル.Sheets(1).Cells(r, "E").Value
          End If
      End If
  End If

配列にデータを格納（複数回答の場合）

 If InStr(DLファイル.Sheets(1).Cells(r, "E"), ";") >= 1 Then
          tmp = Split(DLファイル.Sheets(1).Cells(r, "E"), ";")
                  Do While x2 <= UBound(tmp)
                      If Mid(tmp(x2), 2, 1) = "." Then  '左から2文字にピリオドがあれば（＝数字であれば）
                          tmp(x2) = Left(tmp(x2), 1)
                      Else
                          .Cells(inp行, CNo回答.問4_1) = tmp(x2)
                          tmp(x2) = 6 'その他の番号
                      End If
                      x2 = x2 + 1
                  Loop
                  .Cells(inp行, CNo回答.問4) = Join(tmp, ",") '回答欄へ転記

Instr関数・Split関数

Instr関数・・Instr(対象となる文字列、検索する文字列）
Instr関数は、検索する文字列が対象となる文字列の先頭から検索して、何文字目にあるかの文字位置を返す。

Split関数・・Split(文字列、区切り文字）
Spit関数は、配列を返します。よってバリアント型変数とセットで使って、文字列を区切り文字ごとに配列変数※に格納します。例）tmp = Split(文字列,区切り文字)　※tmpはバリアント型変数

※配列変数とは
・通常の変数は１つの変数に１つしか値が入りませんが、複数の値が入る変数
・複数の値を整理するために、要素数が振られる※要素数は通常「0」から始まる
先程のSplit関数を踏まえて例を挙げると、セルＡ１に「A、B、C、D」が入っていた場合、
tmp = Split(cells(1,1).value,”、”)　とすると、バリアント型変数tmpは以下のようになります。
tmp(0)：「Ａ」
tmp(1)：「Ｂ」
tmp(2)：「C」
tmp(3)：「D」

コード解説

配列変数に回答を格納します。

If InStr(DLファイル.Sheets(1).Cells(r, “E”), “;”) >= 1 Then
Cells(r, “E”)の中に；（セミコロン）が1個以上あれば（複数回答の場合）＝複数回答か単数回答かの条件分岐をします。

複数回答ならば、、、

tmp = Split(DLファイル.Sheets(1).Cells(r, “E”), “;”)
バリアント型変数Tmpに「；」で区切られた回答が格納されます。

Ubound関数・MID関数・Left関数

Ubound関数/Lbound関数・・Ubound(配列変数の名前）/Lbound(配列変数の名前）
Ubound関数・・配列で使用できる添え字（要素数）の最大値を整数値で返します。
Lbound関数・・配列で使用できる添え字（要素数）の最小値を整数値で返します。
例）tmp(5)の場合、Lbound(tmp)とすれば「５」を返します。

MID関数・・(対象文字列、文字列を抜き出す先頭位置、抜き出す文字列の長さ【省略可】）
MID関数は、対象文字列の先頭位置から指定した長さの文字列を抜き出します。
例）MID（”ABCDE”,2,3）とすれば、ABCDEの左から２文字目から3文字抜き出して”BCD”を返します。※第3引数の「３」を省略した場合は、Bから全ての文字を抜き出します。

LEFT関数・・(対象文字列、抜き出す文字数）
LEFT関数は、対象文字列の左端から指定文字数を抜き出します。
例）LEFT（”ABCDE”,2）とすれば、ABCDEの左から２文字抜き出して”AB”を返します。

コード解説

回答番号のみ配列変数に格納し直します。

ただ、先ほどのローカルウィンドウで見ていただいた通り、その他回答は番号がないので番号がある場合と無い場合（通常回答かその他回答で）条件分岐しています。

Do While x2 <= UBound(tmp)
配列変数tmpの要素数分ループします。
　If Mid(tmp(x2), 2, 1) = “.” Then
左から2文字にピリオドがあれば（＝数字であれば）
　　tmp(x2) = Left(tmp(x2), 1)
配列変数tmpに回答番号のみを格納し直します。

Else左端が数字ではなければ（その他回答の場合）
.Cells(inp行, CNo回答.問4_1) = tmp(x2)
その他回答列にtmpに格納されている値をセルに代入します。
tmp(x2) = 6 ‘その他の番号
tmpには「その他回答番号」として「６」を入れます。
End If
x2 = x2 + 1
Loop