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

ExcelマクロVBAの問題点と解決策、VBAの技術的解説
公開日: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の可変長文字列では遅そうだと分かります。


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


VBA 参考画像

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関数の速度比較
スピルって速いの?スピルの速度について


新着記事NEW ・・・新着記事一覧を見る

VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
スピらない スピル数式 スピらせる|エクセル雑感(2023-12-06)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




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


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



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