目次

文字列から配列へ

今回の投稿では、文字列を連結した文字列配列に変換する Excel (エクセル)マクロを作成してみました。 Trim (トリム)関数、 Split (スプリット)関数を使用して、文字列から配列を生成してみました。

連結した文字列から配列を作成してみます。 "A B C " を [ A , B , C ] の配列に変換してみます。

目次まで戻る

マクロの一例

早速、以下のマクロを作成してみました。「&」アンパサンドの記号で文字列連結、「Trim」トリム関数で先頭・末尾の空白削除、「Split」スプリット関数で配列作成という流れになっています。

Sub 文字列を連結した文字列から配列を作成するマクロ() '---(1)

    Dim s As String '---(2)

    s = "A " '---(3)

    s = s & "B " '---(4)

    s = s & "C " '---(5)

    MsgBox """" & s & """" '---(6)

    MsgBox """" & Trim(s) & """" '---(7)

    Range("A1:C1") = Split(s, " ") '---(8)

End Sub '---(9)

上のマクロを実行してみると、以下の画像のような結果になりました。途中でメッセージボックスが 2 回表示されました。

1 回目のメッセージは「"A B C "」と表示されました。末尾に「 」半角スペースが含まれています。

1 回目に表示されたメッセージボックスです。「"A B C "」と表示されました。

2 回目のメッセージでは、「"A B C"」と表示されました。 Trim 関数によって、末尾の半角スペースが削除されています。

2 回目に表示されたメッセージボックスです。「"A B C"」と表示されました。

2 回目のメッセージボックスの「OK」ボタンを押すと、ワークシートの A1 セルから右方向に A , B , C と出力されました。 Split 関数で作成された配列の値が出力されています。

配列の値をワークシートに出力しました。 A1 セルから右方向に A , B , C と出力されました。

以下に '---(1) から '---(9) の説明を記載しています。

Sub 文字列を連結した文字列から配列を作成するマクロ() '---(1)

「Sub マクロ名()」の書式になっています。 1 つのマクロの始まりになります。

Dim s As String '---(2)

変数「s」を文字列型で宣言しています。

s = "A " '---(3)

変数「s」に文字列「A 」を格納しています。「A」と「 」半角スペースです。

s = s & "B " '---(4)

変数「s」の末尾に文字列「B 」を連結しています。「A」の時と同じく、「B」と「 」半角スペースの組み合わせです。

s = s & "C " '---(5)

変数「s」に「C 」を連結しています。「C」と「 」半角スペースです。

この時点の変数「s」の値は「A B C 」になっていると思います。

MsgBox """" & s & """" '---(6)

変数「s」の値をメッセージボックスで表示しています。前後の「""""」は「"」ダブルクオーテーション 1 文字を表示するための記述になります。

メッセージボックスに「"A B C "」と出力されると思います。この時点では、末尾に半角スペースが含まれています。

MsgBox """" & Trim(s) & """" '---(7)

変数「s」に格納されている文字列の前後の空白を Trim 関数で削除しています。「C」の後の半角スペースが削除されます。

メッセージボックスに「"A B C"」と出力されると思います。次の Split 関数で「 」半角スペースを区切り文字として配列に変換するので、末尾の半角スペースを削除しておきました。

Range("A1:C1") = Split(s, " ") '---(8)

「Range("A1:C1")」は、横並びのセル 3 つになっています。「Split(s, " ")」は、 Split 関数で、変数「s」を「 」半角スペースで分割して配列を作成しています。配列の長さ(箱の数?)は 3 になります。

「セル範囲 = 配列」とすると、配列の値が一括でセルに入力できるようです。

「Range("A1:A3")」と縦並びの範囲を指定した場合、「A1」セルから下に「A」「A」「A」と出力されてしまいました。

縦並びに A , A , A が出力されました。

「Range("A1:C1") = Split(s, " ")」の行を、

Range("A1:A3") = WorksheetFunction.Transpose(Split(s, " "))

とすると、縦並びに「A」「B」「C」と出力されました。

縦並びに A , B , C が出力されました。

ワークシート関数 Transpose (トランスポーズ)の説明は以下の URL に記載されています。

TRANSPOSE 関数 - Office サポート
https://support.microsoft.com/ja-jp/office/transpose-%E9%96%A2%E6%95%B0-ed039415-ed8a-4a81-93e9-4b6dfac76027

Transpose 関数を使用すると、ワークシート上でセル範囲を「Ctrl+C」でコピーして、「形式を選択して貼り付け > 行列を入れ替える」として貼り付けた時のように、行と列が入れ替わるようです。

形式を選択して貼り付け、行列を入れ替える。

 

End Sub '---(9)

「Sub」の終了部分です。ここまでで 1 つのマクロになります。

目次まで戻る

配列の中身

Split 関数で文字列から配列を作成した時の、配列の中身を見てみるために以下のマクロを書いてみました。

文字列「A B C」を「 」半角スペースで分割して作成した配列を、変数「v」に格納しています。

Sub 配列の中身()

    Dim v As Variant

    v = Split("A B C", " ")

End Sub

上のコードの「End Sub」の行にブレークポイントを設定して、マクロの途中経過を見てみます。マクロのどこかの行にブレークポイントを設定すると、その行でマクロが一時停止するようです。ブレークポイントは、 VBE のコード編集画面で「F9」キーを押すと設定・解除が切り替わります。

ブレークポイントを設定しています。

上のマクロを実行すると、マクロが一時停止して、ローカルウィンドウに以下の内容が表示されました。「A B C」の文字列が半角スペースで分割されて、 3 つの配列要素が出来ていますね。

ローカルウィンドウに変数「v」の中身が表示されています。

とりあえず配列の中身を見ることができました。

目次まで戻る

全角スペースを Trim

上記マクロで、半角スペースを Trim 関数で削除しましたが、全角スペースの場合は、どのような結果になるでしょうか。 LTrim , Rtrim 関数のテストも併せて行ってみます。

以下のマクロを作成してみました。 LTrim , Rtrim 関数は、左側のみ、右側のみの空白を削除する関数になっています。

Sub Trimのテスト()

Debug.Print "'" & Trim(" ABC ") & "'" '「Trim」関数で「先頭」と「末尾」の半角スペースを削除

Debug.Print "'" & LTrim(" ABC ") & "'" '「LTrim」関数で「先頭」の半角スペースを削除

Debug.Print "'" & RTrim(" ABC ") & "'" '「RTrim」関数で「末尾」の半角スペースを削除

Debug.Print "'" & Trim(" ABC ") & "'" '「Trim」関数で「先頭」と「末尾」の「全角」スペースを削除

End Sub

上のマクロを実行すると、イミディエイトウィンドウに以下の内容が出力されました。

'ABC'
'ABC '
' ABC'
'ABC'

Trim 関数は、全角スペースも削除するようですね。

目次まで戻る

ループで連結した文字列から配列作成

ループで連結した文字列から配列を作成するマクロを作成してみました。偶数行の値をループして連結、Split と Trim 関数で配列作成、 B 列に配列の値を出力、という流れになっています。

Sub ループで連結した文字列から配列作成()

Dim s As String
Dim v As Variant
Dim i As Long

For i = 1 To 7

If i Mod 2 = 0 Then

s = s & Cells(i, 1).Value & " "

End If

Next i

MsgBox "ループ終了後の「s」の値は? " & """" & s & """"

v = Split(Trim(s), " ")

Range("B1").Resize(UBound(v) + 1).Value = WorksheetFunction.Transpose(v)

End Sub

上のマクロを以下の画像のワークシート上で実行してみます。偶数行なので、「B」「D」「F」の値の配列が作成されると思います。

A から G までの文字が入力されています。

マクロの実行結果は、以下の画像のようになりました。メッセージボックスが 1 回表示された後、配列の値が B 列に出力されました。

メッセージボックスに「"B D F "」が表示されました。「B D F」の後に「 」半角スペースがあります。

ループ終了後の変数「s」の値です。「"B D F "」。

ワークシートの B 列に「B」「D」「F」が出力されました。

B 列に配列の値が出力されています。

目次まで戻る

ループでカンマ区切りの文字列を生成

次は、ループでカンマ区切りの文字列を生成するマクロを作成してみました。偶数行のセルアドレスを連結して元の文字列を生成、 Split , Trim 関数で配列作成、 Join 関数でカンマ区切りの文字列を取得、 EntireRow プロパティで文字列で指定された範囲(偶数行)を削除するという流れになっています。セルのアドレスは、 Address プロパティで取得しています。

Sub ループでカンマ区切りの文字列を生成()

Dim j As String
Dim s As String
Dim v As Variant
Dim i As Long

For i = 1 To 7

If i Mod 2 = 0 Then

s = s & Cells(i, 1).Address & " "

End If

Next i

MsgBox "ループ終了後の「s」の値は? " & """" & s & """"

v = Split(Trim(s), " ")

j = Join(v, ",")

MsgBox "「j」の値は? " & """" & j & """"

Range(j).EntireRow.Delete

End Sub

上のマクロを以下のワークシート上で実行してみます。 A 列に A ~ G の文字が入力されています。偶数行には、「B」「D」「F」が入力されています。

A ~ G の文字が入力されています。

マクロを実行してみると以下の画像の結果になりました。メッセージボックスが 2 回表示された後、ワークシートの偶数行が削除されました。

For ループ終了直後の変数「s」の値は、「"$A$2 $A$4 $A$6 "」となっています。

配列作成前の元の文字列です。

Trim 関数で末尾の空白を削除、 Split 関数で配列へ変換、 Join 関数で配列をカンマ( , )で連結した文字列を取得。という流れで、カンマ区切りの文字列を生成しました。

Join 関数の結果が表示されている。

カンマ区切りの文字列「$A$2,$A$4,$A$6」の範囲を含む行を「Range(j).EntireRow.Delete」の記述で削除しました。

2 , 4 , 6 行目が削除されている。

目次まで戻る

参考資料

目次まで戻る

テスト環境

  • Windows 10
  • Microsoft Office Excel 2003

目次まで戻る

あとがき

「Range( A1:C1 ) = 配列」として、セルの値を一括入力できるのは便利な方法ですね。以前は、自分の場合は、配列にループを掛けて入力していましたが、「セル範囲=配列」の方法を知ってからマクロの記述が短く済むようになりました。

以上、閲覧ありがとうございました。

目次まで戻る

同じカテゴリの投稿( Excel VBA )

前後の投稿