&演算子は短い文字列の少ない回数の結合に向いている
VBAで文字列を結合する一番簡単な方法は&演算子を使うことだと思います。例えば、"a" & "b"
とすれば"ab"
が得られますし、メッセージを表示したいときにMsgBox DataCount & "件のデータを処理しました。"
というのもよくやると思います。
便利な&演算子なのですが、結合回数を増やしたり、結合する文字列長が大きいと処理にとっっても時間がかかります。
長さ512(1 KB)の文字列を&演算子で1000回結合するのは、0.16秒ですが、10000回結合するのは20.43秒かかります。(テスト環境 Thinkpad X230, Core i7, RAM 16 GB)
Sub TestTextJoinByAmpersand()
Dim StartTime As Single
Dim StopTime As Single
Dim TextStorage As String
Dim RepeatIndex As Long
Dim RepeatCount As Long
Dim SpaceLength As Long
RepeatCount = 10000
SpaceLength = 512
StartTime = Timer
TextStorage = ""
For RepeatIndex = 1 To RepeatCount
TextStorage = TextStorage & Space(SpaceLength)
Next
StopTime = Timer
Debug.Print "処理時間: "; Round(StopTime - StartTime, 2); " sec"
Debug.Print "結合回数: "; RepeatCount
Debug.Print "文字列長: "; Len(TextStorage)
Debug.Print "文字列サイズ: "; Round(Len(TextStorage) * 2 / 1024 ^ 2, 2); " MB"
End Sub
処理時間: 20.43 sec
結合回数: 10000
合計文字列サイズ: 9.77 MB
処理に時間がかかるのは、&演算子は文字列結合のたびにメモリの別のアドレスに変数を用意するからです。メッセージを表示するような場合は&演算子で文字列結合すればいいのですが、規模が大きいテキスト処理を行いたい場合は&演算子は使えません。
高速な文字列結合ができるMidステートメントをベースにしたクラスTextSystem
VBAは&演算子の他に、Join関数やMidステートメントのような文字列結合が高速処理できる方法を用意しています。Join関数やMidステートメント自体の説明はインターネット上の他のページにまかせて、ここではMidステートメントをベースにして作ったクラスTextSystemを説明します。
VBAのIDEのプロジェクトウィンドウで新規クラスモジュールを挿入して、次のコードを貼り付けて、クラスモジュールの名前をTextSystemとします。
Option Explicit
' Note)
' 連続して使用してメモリ使用量が1GBを超えると文字列領域不足エラーが生じる
' 使い終えたらメモリをリリースするために Set object = Nothing とする
' Space()で文字列領域を確保するのにミリ秒スケールで時間がかかるから文字列領域の確保は何度もしない方がいい
Private m_TextSpace As String ' 文字列を蓄積する変数
Private m_Pointer As Long ' m_TextSpaceのポインタ
Private m_TextSpaceLength As Long ' m_TextSpaceが確保している文字列の最大数
Private Sub Class_Initialize()
' 初期化で10KB(5120文字)の文字列領域を確保する
' クライアントはTextSpaceLengthプロパティでこの値を変更できる
TextSpaceLength = 5120
End Sub
' 蓄積したテキストを返す
' 値渡しだから参照渡しより処理が遅い
Public Property Get Text()
Text = Mid(m_TextSpace, 1, m_Pointer)
End Property
' 蓄積できる文字列の最大長を変更する
' 文字列領域を初期化してポインタをリセットする
Public Property Let TextSpaceLength(ByRef Value As Long)
If Value <= 0 Then Exit Property
m_TextSpaceLength = Value
PrepareTextSpace m_TextSpaceLength
Reset
End Property
' 確保している文字列領域の大きさ(蓄積できる文字数)
Public Property Get TextSpaceLength() As Long
TextSpaceLength = m_TextSpaceLength
End Property
' 蓄積した文字列の長さを返す
Public Property Get TextLength() As Long
TextLength = m_Pointer
End Property
' 文字列領域を確保する
' Space()は数msecの時間がかかるので頻繁にコールするとタイムロスになる
Private Sub PrepareTextSpace(ByRef TextSpaceLength As Long)
m_TextSpace = Space(TextSpaceLength)
End Sub
' ポインタを0に設定する
Public Sub Reset()
m_Pointer = 0
End Sub
' 全てのParamArray引数をm_TextSpaceに蓄積する
Public Sub Add(ParamArray Texts() As Variant)
Dim Text As Variant
Dim Length As Long
' 引数の文字列を1つずつm_TextSpaceのスペースと置換する
For Each Text In Texts
If TypeName(Text) = "Null" Then
' Nullの場合Len()でエラーになるので空の文字列に置き換える
Text = ""
End If
Length = Len(Text)
If m_Pointer + Length > m_TextSpaceLength Then
' 文字列領域の容量を超える場合はスペースを大きくする
ExtendTextSpace m_TextSpaceLength
End If
Mid(m_TextSpace, m_Pointer + 1, Length) = Text
m_Pointer = m_Pointer + Length
Next
End Sub
' 文字列領域を拡張する
' これまでに蓄積した文字列は保持する
Private Sub ExtendTextSpace(ByRef PlusLengh As Long)
Dim SavedText As String
If PlusLengh <= 0 Then Exit Sub
SavedText = Mid(m_TextSpace, 1, m_Pointer)
m_TextSpaceLength = m_TextSpaceLength + PlusLengh
m_TextSpace = Space(m_TextSpaceLength)
Mid(m_TextSpace, 1) = SavedText
End Sub
クラスの説明の前に、このTextSystemクラスを使ってさっきと同じ長さ512(1 KB)の文字列を10000回結合してみます。
Sub TestTextJoinByMidStatement()
Dim StartTime As Single
Dim StopTime As Single
Dim TextStorage As TextSystem
Dim RepeatIndex As Long
Dim RepeatCount As Long
Dim SpaceLength As Long
RepeatCount = 10000
SpaceLength = 512
StartTime = Timer
Set TextStorage = New TextSystem
TextStorage.TextSpaceLength = RepeatCount * SpaceLength
For RepeatIndex = 1 To RepeatCount
TextStorage.Add Space(SpaceLength)
Next
StopTime = Timer
Debug.Print "処理時間: "; Round(StopTime - StartTime, 2); " sec"
Debug.Print "結合回数: "; RepeatCount
Debug.Print "合計文字列サイズ: "; Round(TextStorage.TextLength * 2 / 1024 ^ 2, 2); " MB"
Set TextStorage = Nothing
End Sub
処理時間: 0.09 sec
結合回数: 10000
合計文字列サイズ: 9.77 MB
&演算子では処理に20秒かかっていたのが、TextSystemクラス(Midステートメント)を使うと0.1秒です。
なぜ高速に文字列結合できるかというと、理由はあらかじめ必要な文字列領域をメモリ上に確保しているからです。文字列領域の確保はTextSpaceLengthプロパティに結合後の文字列の長さ(文字数)を入力したときに行っています。
TextSystemクラスの説明
最初にオブジェクトを生成したとき(Set object = New TextSystem
が実行したとき)は、10 KB(5120文字)の文字列領域を確保しています。Class_Initialize()に5120と決め打ちしています。特にこの値に意味はありません。このクラスを使う場合に応じて、決め打ちで最初に確保する文字列領域の大きさを決定してもいいし、上記のテストのようにTextSpaceLengthプロパティに値を設定してもいいです。
AddメソッドはParamArray配列の引数をとるようになっていて、結合したい文字列が複数ある場合は、次のように引数の変数や値をカンマで区切ります。
Sub HowToUseTextSystem()
Dim Test As TextSystem
Dim Value As Double
Set Test = New TextSystem
Value = 1.23
Test.Add "a", " ", Value, vbLf
Debug.Print Test.Text
Set Test = Nothing
End Sub
a 1.23
Addメソッドで結合しながら蓄積した文字列を読み出すにはTextプロパティを使います。
入力した文字列が確保した文字列領域の長さ(TextSpaceLengthプロパティ値)を超えると、文字列領域を拡張します。文字列領域を拡張する時は一旦、蓄積した文字列を別の変数にコピーして、再度Space()で文字列領域を確保しているので、ミリ秒スケールのタイムロスがあります。このタイムロスが問題になる場合は、最初に十分に大きい(最大は1GBほどです)値をTextSpaceLengthプロパティに設定してください。
また、何か文字列を結合するのにTextSystemクラスを使って、その後、別の文字列を結合したいときは同じオブジェクトでReset()をしてから文字列を蓄積すればよいと思います。Reset()は内部でポインタを0に初期化しているだけなので時間がかかりません。Reset()は次のように使います。
Sub HowToUseTextSystem_Reset()
Dim Test As TextSystem
Dim Value As Double
Set Test = New TextSystem
Test.Add "とある文字列"
Debug.Print Test.Text
Test.Reset
Debug.Print "リセット"
Test.Add "別の文字列"
Debug.Print Test.Text
Set Test = Nothing
End Sub
とある文字列
リセット
別の文字列