遅い文字列結合を最速処理する方法について

公開日：2016-08-29　最終更新日：2020-08-17

遅い文字列結合を最速処理する方法について

VBAは遅い・・・
よく聞くことですが、確かに普通にコード記述しているととても遅いことがあります、
その代表の一つに、文字列結合があります、
文字列結合を最速処理する方法について解説します。

そもそも文字列結合は、なぜ遅いのか、
String型（可変長文字列）についての基礎知識が必要です。

※本記事は2016/8/29に公開したものをリライトしました。

ごく一部のVBAコードは変更されています。
計測PCは、
Core i5 → Core i7
Excel2013 → Excel2019
このような処理では速度にあまり変化はありません。

String（可変長文字列）について

これにはStringのメモリ構造から話をする必要が出てきてしまいますが、
さすがに、そこから話していては、目的まで到達しませんので、
なぜStringの結合が遅いのかだけをごく簡単に説明します。

String宣言した変数は、格納するたびにアドレスが変わります。
つまり=で値を入れたびにメモリ領域の割り当てが行われます。
文字列を結合する度に、新たなメモリ領域への格納が発生します。
Stringは可変長文字列なのでメモリ確保が動的にならざるおえないということです。
細かい話は抜きにしても、どう考えてもStringの可変長文字列では遅そうだと分かります。

以下の表で、速度確認をします。

100,000行まで、ぎっしりと入っています。
これを全て文字列結合します。
つまり、結果は
100,000*5*3文字=1,500,000文字
という事です。

以下の検証では、
A1:E100000を配列に入れてから測定を開始しています。
そして、変数strに入れたところで時間測定しています。
これは、出来るだけセルとのやり取り時間を排除するためです。

以下の計測にあたって使用したVBAコード


  
    
      Option Explicit


Private iCol As Integer


Sub test()

　　Application.Calculation = xlCalculationManual

　　Dim i As Integer

　　For i = 1 To 10

　　　　iCol = i + 7

　　　　If i <= 3 Then

　　　　　　Call test1

　　　　End If

　　　　DoEvents

　　　　Call test2

　　　　DoEvents

　　　　Call test3

　　　　DoEvents

　　　　Call test4

　　　　DoEvents

　　　　Call test5

　　　　DoEvents

　　　　Call test6

　　　　DoEvents

　　　　Call test7

      　　　　DoEvents

      　　　　Call test8

      　　　　DoEvents

　　Next

　　Application.Calculation = xlCalculationAutomatic

End Sub

test1は処理時間が長いので、3回だけ実行しています。
各マクロは、処理時間をH列以降に書き出します。
この10回の計測タイムら上下値2回を除いた6回の平均値を採用しました。、

1.普通に&で結合


  
    
      Sub test1()

　　Dim str As String

　　Dim i As Long

　　Dim j As Long

　　Dim ary

　　Dim sTimer As Single

　　ary = Range("A1:E100000")

　　sTimer = Timer

　

　　For i = LBound(ary, 1) To UBound(ary, 1)

　　　　For j = LBound(ary, 2) To UBound(ary, 2)

　　　　　　str = str & ary(i, j)

　　　　Next

　　Next

　　Cells(2, iCol) = Timer - sTimer

End Sub

ごく普通のVBAコードです。
2重ループしながら、&で文字列結合しています。
今回の主題である、文字列結合が遅いというのは、まさしくこのようなコードの場合です。
3回の実測平均で、
約297秒

本当に遅い・・・
ただ文字列くっつけているだけなのに・・・

では、どうするかです。
Visual Studio(.NET)では、StringBuilderと言うものが実装されています。
これを使うと、文字列結合を高速で処理できます。
しかし、VBAにはこれがありません。
.NETで文字列結合することは、そんなに多くないのですが、
まあ、SQL文の生成の時が一番多いとおもいますが、せいぜい数千から数万バイト程度なので、実際はほとんど差異がありません。
むしろ、エクセルVBAでは頻繁に文字列結合が行われます。
複数セルの文字列を結合する、至極普通の処理です。

文字列結合を高速化する手段として、
・StringBuilderのような機能を作成する
・配列を使う
・Midステートメントを使う
この3つの方法について色々なパターンで検証してみます。

2.Midステートメントを使う1


  
    
      Sub test2()

　　Dim i As Long

　　Dim j As Long

　　Dim str As String

　　Dim sBuf As String

　　Dim iBuf As Long

　　Dim ary

　　Dim sTimer As Single

　　Const IniLen As Long = 32768

　　Const IncPer As Single = 1.5

　　ary = Range("A1:E100000")

　　sTimer = Timer

　

　　sBuf = String$(IniLen, vbNullChar)

　　iBuf = 0

　　For i = LBound(ary, 1) To UBound(ary, 1)

　　　　For j = LBound(ary, 2) To UBound(ary, 2)

　　　　　　If iBuf + Len(ary(i, j)) > Len(sBuf) Then

　　　　　　　　sBuf = sBuf & String$(CLng(Len(sBuf) * IncPer) + Len(ary(i, j)), vbNullChar)

　　　　　　End If

　　　　　　Mid(sBuf, iBuf + 1) = ary(i, j)

　　　　　　iBuf = iBuf + Len(ary(i, j))

　　　　Next

　　Next

　　str = Left(sBuf, iBuf)

　　Cells(3, iCol) = Timer - sTimer

End Sub

なるべく汎用性を持たせて、]無駄は少なく記述したつもりです。
その為、若干難解な部分はあるかもしれません。
初期として、32,768バイトを確保し、
Midステートメントで文字を置換して入れていきます。
長さが超えてしまう場合は、2倍の長さを再確保しています。
10回の実測から中央6回の平均で、
0.229秒

速いですね。
WEBでStringの結合を速くする方法としては、最も多く紹介されている方法だと思います。
細部のコードはそれぞれだと思いますが、概ねこのような方法になります。
この方法では、
初期確保の、IniLen=32,768バイトとncPer =2の数値によって、速度が変化します。

3.Midステートメントを使う2


  
    
      Sub test3()

　　Dim i As Long

　　Dim j As Long

　　Dim str As String

　　Dim stemp As String

　　Dim sBuf As String

　　Dim iBuf As Long

　　Dim ary

　　Dim sTimer As Single

　　ary = Range("A1:E100000")

　　sTimer = Timer

　

　　iBuf = 0

　　For i = LBound(ary, 1) To UBound(ary, 1)

　　　　For j = LBound(ary, 2) To UBound(ary, 2)

　　　　　　iBuf = iBuf + Len(ary(i, j))

　　　　Next

　　Next

　　sBuf = String$(iBuf, vbNullChar)

　　iBuf = 0

　　For i = LBound(ary, 1) To UBound(ary, 1)

　　　　For j = LBound(ary, 2) To UBound(ary, 2)

　　　　　　Mid(sBuf, iBuf + 1) = ary(i, j)

　　　　　　iBuf = iBuf + Len(ary(i, j))

　　　　Next

　　Next

　　str = Left$(sBuf, iBuf)

　　Cells(4, iCol) = Timer - sTimer

End Sub

2.Midステートメントを使う1では、必要になった時点で領域を増やしていました。
この部分が若干遅いのではと感じられますので、改良してみました。
先に全体の長さを取得して、一気に領域を確保してから、
Midステートメントで文字を置き換えています。
10回の実測から中央6回の平均で、
0.216秒

誤差範囲内程度ですが、僅かに速くはなってはいるようですが。
先に全体の長さを取得せずに、大きめに確保してしまうという手もあります。
そうすればもっと速くなるのは確実ですが、コードに汎用性が無くなってしまいますね。

4.配列のJoin関数を使う1


  
    
      Sub test4()

　　Dim str As String

　　Dim i As Long

　　Dim j As Long

　　Dim ix As Long

　　Dim ary

　　Dim ary2() As String

　　Dim sTimer As Single

　　ary = Range("A1:E100000")

　　sTimer = Timer

　

　　ix = 0

　　For i = LBound(ary, 1) To UBound(ary, 1)

　　　　For j = LBound(ary, 2) To UBound(ary, 2)

　　　　　　ReDim Preserve ary2(ix)

　　　　　　ary2(ix) = ary(i, j)

　　　　　　ix = ix + 1

　　　　Next

　　Next

　　str = Join(ary2, "")

　　Cells(5, iCol) = Timer - sTimer

End Sub

1次元の配列に入れて、
Join関数で一気に結合しています。
10回の実測から中央6回の平均で、
0.948秒

普通に&結合することに比べれば十分に速いのですが、
これまでと比べると、ちょっと遅い感じを受けてしまいます。
Join関数自体は早いはずなのですが、、、

5.配列のJoin関数を使う2


  
    
      Sub test5()
　　Dim i As Long

　　Dim j As Long

　　Dim str As String

　　Dim ary

　　Dim ary2() As String

　　Dim sTimer As Single

　　ary = Range("A1:E100000")

　　sTimer = Timer


　　ReDim ary2(1 To UBound(ary, 1) * UBound(ary, 2))

　　For i = LBound(ary, 1) To UBound(ary, 1)

　　　　For j = LBound(ary, 2) To UBound(ary, 2)

　　　　　　ary2((i - 1) * UBound(ary, 2) + j) = ary(i, j)

　　　　Next

　　Next

　　str = Join(ary2, "")

　　Cells(6, iCol) = Timer - sTimer

End Sub

5.配列のJoin関数を使う1では、1次元配列にする部分で時間がかかっています。
つまり、
ReDim Preserve
これが遅いということです。
そこを工夫して、
2次元配列→1次元配列
ここを、改良して速度アップしています。
10回の実測から中央6回の平均で、
0.133秒

もう、さすがに限界の速さではないでしょうか。

6.Midステートメント+配列


  
    
      Sub test6()

　　Dim i As Long

　　Dim j As Long

　　Dim str As String

　　Dim stemp As String

　　Dim inLen As Long

　　Dim iBuf As Long

　　Dim ary

　　Dim ary2() As String

　　Dim ix As Long

　　Const MaxLen As Long = 4096

　　Dim sTimer As Single

　　ary = Range("A1:E100000")

　　sTimer = Timer

　　

　　ix = 0

　　ReDim ary2(ix)

　　ary2(ix) = String$(Len(Cells(1, 1)) + MaxLen, vbNullChar)

　　iBuf = 0

　　For i = LBound(ary, 1) To UBound(ary, 1)

　　　　For j = LBound(ary, 2) To UBound(ary, 2)

　　　　　　inLen = Len(ary(i, j))

　　　　　　If iBuf + inLen > MaxLen Then

　　　　　　　　ary2(ix) = Left$(ary2(ix), iBuf)

　　　　　　　　ix = ix + 1

　　　　　　　　ReDim Preserve ary2(ix)

　　　　　　　　ary2(ix) = String$(MaxLen, vbNullChar)

　　　　　　　　iBuf = 0

　　　　　　　　ary2(ix) = String$(inLen + MaxLen, vbNullChar)

　　　　　　End If

　　　　　　Mid(ary2(ix), iBuf + 1) = ary(i, j)

　　　　　　iBuf = iBuf + inLen

　　　　Next

　　Next

　　ary2(ix) = Left$(ary2(ix), iBuf)

　　str = Join(ary2, "")

　　Cells(7, iCol) = Timer - sTimer

End Sub

Midステートメントと、配列のJoin関数を組み合わせてみました。
少々難解なコードになっています。
先の、Midステートメントを使う2例では、結局領域の確保が問題になっています。
そこで、この問題部分を配列にしてしまえという事です。
つまり、
Midステートメントを使う1では、都度の領域確保時に時間がかかる
Midステートメントを使う2では、全体の大きさを取得するのに時間がかかる
なので、都度領域確保するが、そこは配列を使うという事です。
10回の実測から中央6回の平均で、
0.133秒

5.配列のJoin関数を使う2よりは少し遅いといったところです。
5.配列のJoin関数を使う2は、もう無駄な部分が一切ない感じですので、これを超えることは難しいでしょう。

速度検証としては以上です。
結論としては、

配列に速やかに入れられるのなら、配列に入れてから処理しましょう。
単純に配列に入れられないような場合は、Midステートメントを上手く使いましょう。

という事になります。
以下では、使い回しの出来るように、
Midステートメントを使った、汎用のStringbuilderクラスになります。。
特に凝ったことはしていません。
必要最低限の機能実装です。

7.Midステートメント汎用

以下をクラスStringBuilderとして作成。


  
    
      Option Explicit


Private sBuf As String

Private iBuf As Long


Private Sub Class_Initialize()

　　sBuf = String$(32768, vbNullChar)

　　iBuf = 0

End Sub


      Public Sub Append(ByRef sValue As String)

　　If iBuf + Len(sValue) > Len(sBuf) Then

　　　　sBuf = sBuf & String$(CLng(Len(sBuf) * 2) + Len(sValue), vbNullChar)

　　End If

　　Mid(sBuf, iBuf + 1) = sValue

　　iBuf = iBuf + Len(sValue)

End Sub


      Public Function ToString() As String

　　ToString = Left$(sBuf, iBuf)

      End Function

使い方


  
    
      Sub test7()

　　Dim i As Long

　　Dim j As Long

　　Dim str As String

　　Dim ary

　　Dim sTimer As Single

　　ary = Range("A1:E100000")

　　sTimer = Timer

　　

      　　Dim sb As New StringBuilder

　　For i = LBound(ary, 1) To UBound(ary, 1)

　　　　For j = LBound(ary, 2) To UBound(ary, 2)

　　　　　　sb.Append CStr(ary(i, j))

　　　　Next

　　Next

　　str = sb.ToString

　　Cells(8, iCol) = Timer - sTimer

End Sub

10回の実測から中央6回の平均で、
0.164秒

汎用コードにしていますので、
5.配列のJoin関数を使う2や6.Midステートメント+配列よりわずかに遅くなっています。
これらは今回の処理に特化して書いたVBAですので、さすがに仕方ありません。

結果の一覧

種別	1回	2回	3回	4回	5回	6回	7回	8回	9回	10回	中央6回平均
1.普通に&で結合	294.9	298.1	298.5								297.2
2.Midステートメントを使う1	0.227	0.227	0.297	0.219	0.336	0.234	0.234	0.227	0.273	0.297	0.229
3.Midステートメントを使う2	0.234	0.211	0.203	0.211	0.234	0.227	0.234	0.219	0.219	0.219	0.216
4.配列のJoin関数を使う1	0.945	0.938	0.930	0.922	1.016	1.016	0.961	1.000	0.984	1.016	0.948
5.配列のJoin関数を使う2	0.125	0.125	0.141	0.133	0.141	0.141	0.133	0.156	0.133	0.133	0.133
6.Midステートメント+配列	0.133	0.133	0.141	0.172	0.148	0.148	0.148	0.148	0.148	0.195	0.146
7.Stringbuilderクラス	0.164	0.172	0.156	0.219	0.172	0.188	0.164	0.227	0.180	0.211	0.169

500,000回の文字列結合で、1,500,000文字を生成して、
0.2秒以下できるのなら文句はないでしょう。
VBAという言語そのものが極端に遅いのではないという事です。
速い処理を実現するための、関数・オブジェクトの提供が、ちょっとだけ不足しているという事です。

最後に総まとめとして

配列のJoin関数
Midステートメント

これらを上手に使えば、高速で文字列結合が実現できます。

VBAの基本的な速度対策については、以下を参照してください。

速度比較決定版【Range,Cells,Do,For,For Each】

何度も言っているのですが、RangeとCellsでどっちが早いか、とか、DoとForとFor Eachでどれが早いかとか、そもそも、その議論がナンセンスなんです。以下のコードと結果を見て、各自で判断して下さい。巷の議論が、いかに無意味で、実は良く解っていないのだと言う事を、理解してもらいたい。

エクセルVBAのパフォーマンス・処理速度に関するレポート

ExcelのマクロVBAは遅い・重いと良く言われることが多いようですが、マクロVBAが遅い・重いのではなく、その書かれたVBAコードが遅いのです。正しい高速化・速度対策をしたコードなら、それほど遅くはありません。むしろ、巨大なスプレッドシートを扱っている事を考えれば、驚異的なパフォーマンスとも言えるのです。

マクロVBAの高速化・速度対策の具体的手順と検証

マクロVBAが遅い・重いという相談が非常に多いので、遅い・重いマクロVBAを高速化・速度対策する場合の具体的な手順をここに解説・検証します。マクロVBAの速度に関する記事は既にいくつか書いています。特に、以下はぜひお読みください。

大量データで処理時間がかかる関数の対処方法（WorksheetFunction）

・大量データで処理時間がかかるサンプルデータ・普通にマクロVBAコ－ドを書いた場合・指定範囲を絞ってみる・配列を使って書いてみる・アルゴリズムを考えてみる・Dictionary（連想配列）を使う・大量データで処理時間がかかる関数の対処方法の最後に

大量データにおける処理方法の速度王決定戦

同じテーマ「マクロVBA技術解説」の記事

エクセルVBAのパフォーマンス・処理速度に関するレポート
 VBAのFindメソッドの使い方には注意が必要です
 マクロVBAの高速化・速度対策の具体的手順と検証
 動的2次元配列の次元を入れ替えてシートへ出力（Transpose）
大量データで処理時間がかかる関数の対処方法（SumIf）
大量データにおける処理方法の速度王決定戦
遅い文字列結合を最速処理する方法について
大量VlookupをVBAで高速に処理する方法について
 Withステートメントの実行速度と注意点
 IfステートメントとIIF関数とMax関数の速度比較
 スピルって速いの?スピルの速度について

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.RangeとCellsの使い方｜VBA入門
5.繰り返し処理（For Next)｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.メッセージボックス(MsgBox関数)｜VBA入門
8.セルのクリア（Clear,ClearContents）｜VBA入門
9.条件分岐（Select Case）｜VBA入門
10.ブック・シートの選択（Select,Activate）｜VBA入門

ホーム
マクロVBA応用編
マクロVBA技術解説
遅い文字列結合を最速処理する方法について

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

VBA技術解説
遅い文字列結合を最速処理する方法について