ExcelマクロVBA技術解説 | 遅い文字列結合を最速処理する方法について | Excelマクロの問題点と解決策、エクセルVBAの技術的解説



最終更新日:2016-08-29

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

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

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


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

String宣言した変数は、格納するたびにアドレスが変わります。
つまり=で値を入れたびにメモリ領域の割り当てが行われます。
Dim str As String
str = "AAA"
str = str
この時、
str= str
の時点で、新たにメモリ領域が確保されて、
元の値("AAA")を、新たなメモリ領域へ格納します。

細かい話は抜きにしても、どう考えても遅そうだと分かります。


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



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

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


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

Sub test()
  Dim i As Integer
  For i = 1 To 10
    iCol = i + 7
    If i <= 3 Then
      Call test1
    End If
    Call test2
    Call test3
    Call test4
    Call test5
    Call test6
    Call test7
    Call test8
    DoEvents
  Next
End Sub

test1は処理時間が長いので、3回だけ実行しています。
各マクロは、処理時間をH列以降に書き出します。



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回の実測平均で、
270秒


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

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

文字列結合を高速化する手段として、
・StringBuilderのような機能を作成する
・配列を使う

この二つの方法について色々なパターンで検証してみます。


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

Sub test3()
  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(4, iCol) = Timer - sTimer
End Sub

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

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


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

Sub test4()
  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(5, iCol) = Timer - sTimer
End Sub

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

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


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

Sub test2()
  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(3, iCol) = Timer - sTimer
End Sub

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

これが意外に速いのですよ。
この記事を書こうと思ったのは、この手法が紹介されている記事が見当たらなかったからでもあります。


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

Sub test6()
  Dim i As Long
  Dim j As Long
  Dim str As String
  Dim sBuf As String
  Dim iBuf As Long
  Dim ary
  Dim ary2() As String
  Dim sTimer As Single
  ary = Range("A1:E100000")
  sTimer = Timer
  
  iBuf = 0
  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(7, iCol) = Timer - sTimer
End Sub

5.配列のJoin関数を使う1では、1次元配列にする部分で時間がかかっています。
そこを工夫して、
2次元配列→1次元配列
ここを、改良して速度アップしています。
10回の実測から最速3回の平均で、
0.104秒

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


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

Sub test5()
  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(6, iCol) = Timer - sTimer
End Sub

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

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


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

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


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


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

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

Private sBuf As String
Private iBuf As Long
Private pIniLen As Long
Private pIncPer As Single

Public Property Get IniLen() As Long
  IniLen = pIniLen
End Property
Public Property Let IniLen(ByVal argIniLen As Long)
  pIniLen = argIniLen
End Property

Public Property Get IncPer() As Single
  IncPer = pIncPer
End Property
Public Property Let IncPer(ByVal argIncPer As Single)
  pIncPer = argIncPer
End Property

Private Sub Class_Initialize()
  Call Clear
End Sub

Public Sub Clear()
  pIniLen = 32768
  pIncPer = 1.5
  sBuf = String$(pIniLen, 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) * pIncPer) + Len(sValue), vbNullChar)
  End If
  Mid(sBuf, iBuf + 1) = sValue
  iBuf = iBuf + Len(sValue)
End Sub

Friend 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回の実測から最速3回の平均で、
0.169秒

汎用コードにしていますので、3.Midステートメントを使う2よりは若干落ちるのは仕方ないでしょう。


8.配列Join関数汎用

標準モジュールに
Function Join2(ByRef argArray As Variant) As String
  Dim i As Long
  Dim j As Long
  Dim ary() As String
  
  ReDim ary(1 To UBound(argArray, 1) * UBound(argArray, 2))
  For i = LBound(argArray, 1) To UBound(argArray, 1)
    For j = LBound(argArray, 2) To UBound(argArray, 2)
      ary((i - 1) * UBound(argArray, 2) + j) = argArray(i, j)
    Next
  Next
  Join2 = Join(ary, "")
End Function

使い方
Sub test8()
  Dim str As String
  Dim ary
  Dim sTimer As Single
  ary = Range("A1:E100000")
  sTimer = Timer
  
  str = Join2(ary)
  Cells(9, iCol) = Timer - sTimer
End Sub

10回の実測から最速3回の平均で、
0.128秒

汎用コードとして、別プロシージャーにした事による時間がかかっていますね。



結果の一覧

種別 1回 2回 3回 4回 5回 6回 7回 8回 9回 10回 最速3回平均
1.普通に&で結合 272.742 267.719 269.563 270.008
2.Midステートメントを使う1 0.203 0.203 0.219 0.219 0.219 0.203 0.203 0.219 0.219 0.219 0.203
3.Midステートメントを使う2 0.203 0.188 0.188 0.141 0.148 0.203 0.203 0.203 0.203 0.188 0.159
4.配列のJoin関数を使う1 0.414 0.367 0.391 0.375 0.359 0.375 0.375 0.375 0.359 0.375 0.362
5.配列のJoin関数を使う2 0.102 0.109 0.109 0.109 0.102 0.125 0.109 0.109 0.109 0.109 0.104
6.Midステートメント+配列 0.133 0.156 0.141 0.125 0.141 0.141 0.141 0.141 0.141 0.141 0.133
7.Midステートメント汎用 0.164 0.172 0.172 0.172 0.172 0.172 0.172 0.172 0.172 0.172 0.169
8.配列Join関数汎用 0.148 0.141 0.141 0.125 0.133 0.141 0.141 0.156 0.125 0.141 0.128


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

最後に総まとめとして

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


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


VBAの基本的な速度対策については、以下を参照してください。
速度比較決定版【Range,Cells,Do,For,For Each】
エクセルVBAのパフォーマンス・処理速度に関するレポート
マクロVBAの高速化・速度対策の具体的手順と検証
大量データで処理時間がかかる関数の対処方法(WorksheetFunction)
大量データにおける処理方法の速度王決定戦




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

配列の使い方について
最終行の判定、Rangeオブジェクトと配列、高速化の為に
記述による処理速度の違い
速度比較決定版【Range,Cells,Do,For,ForEach】
エクセルVBAのパフォーマンス・処理速度に関するレポート
VBAのFindメソッドの使い方には注意が必要です
WorksheetFunction.Matchで配列を指定した場合の制限について

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

SUMIFの間違いによるパフォーマンスの低下について|エクセル関数超技(6月17日)
条件式のいろいろな書き方:TrueとFalseの判定とは|ExcelマクロVBA技術解説(6月15日)
空白セルを正しく判定する方法2|ExcelマクロVBA技術解説(5月6日)
フルパスをディレクトリ、ファイル名、拡張子に分ける|ExcelマクロVBA技術解説(4月15日)
テキストボックスの各種イベント|Excelユーザーフォーム入門(4月9日)
フォルダ(サブフォルダも全て)削除する、Optionでファイルのみ削除|ExcelマクロVBAサンプル集(4月4日)
最後の空白(や指定文字)以降の文字を取り出す|エクセル関数超技(3月26日)
先頭の数値、最後の数値を取り出す|エクセル関数超技(3月26日)
Excelファイルを開かずにシート名をチェック|ExcelマクロVBAサンプル集(3月23日)
数式の参照しているセルを取得する|ExcelマクロVBAサンプル集(3月18日)

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

1.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
2.RangeとCellsの使い方|ExcelマクロVBA入門
3.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
4.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
5.変数とデータ型(Dim)|ExcelマクロVBA入門
6.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
7.セルの参照範囲を可変にする(OFFSET,COUNTA,MATCH)|エクセル関数超技
8.ひらがな⇔カタカナの変換|エクセル基本操作
9.定数と型宣言文字(Const)|ExcelマクロVBA入門
10.CSVの読み込み方法|ExcelマクロVBAサンプル集



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

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


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

    ↑ PAGE TOP