VBAで文字列中の文字をカウントする関数を作ってみた

今回はVBAで最速で文字列に含まれる文字列の出現回数をカウントする関数を求めて色々と調査してみました。

---

きっかけ

とあるCSVパーサーのソースコードを読んでいて、以下の様な構文を見かけた。

'CSVデータの行数の最大値を取得するため、
'暫定的に入力データを改行コードごとに配列に分割
Dim iLines() As String
iLines = Split(s, vbCrLf)

dim maxLines As Long
maxLines = UBound(iLines) - LBound(iLines) + 1

私自身も今までこの方法はよく使ってきた。

一行で書けるので美味しい。

maxLines = UBound(Split(s, vbCrLf))

でも、ちょっと待った。

この構文はメモリリークが起こると、つい最近どこかで読んだ覚えがある。

そもそも行数を知りたいだけなのに、入力文字列と同等以上の配列を返すのはロスが大きすぎるのではないか？

よし、調査してやる。

調査内容

今回作成するのは、文字列に含まれる文字列の出現回数をカウントする関数　だが、私が思いついたのは次の4通り。

• Split関数で分割して配列の要素数
• Replace関数で削除した前後の文字数の差
• InStr関数で先頭から探索
• 一旦Byte配列に変換して先頭から探索

実は上記3つは検索すると普通に紹介しているサイトがいくつもある。

ただし、サロゲートペアに対応していなかったり、複数文字に対応してなかったりするので適当に私の方で書き直した。

ちなみに、この中では4つ目が私の思いついた新しい方法である。バイト配列ならきっと速いはずだ！！！

結果報告

先日、ある方に「結論から話せ」と散々注意されたので、先に結論を書いておく。

結果は

• InStrがダントツで高速
• バイト配列はダントツで低速

である。

目安としては、InStrを1倍とした時、SplitやReplaceは10倍、バイト配列は100倍となる。

必ずしもバイト配列を使えば高速になるとは限らないのである。

単位はミリセコンド、小さいほど高速ということになる。

割合は最速の結果に対してどの程度遅いかを示している。

テスト1

abc😁def😁ghi

から

😁

をカウントする処理を10万回づつ実行した結果である。

	Split	LenReplace	InStr	Byte
1回目	65	78	17	147
2回目	66	77	18	136
3回目	64	75	18	132
4回目	65	84	18	135
5回目	65	120	18	129
6回目	70	88	19	148
7回目	98	80	19	125
8回目	71	92	20	130
9回目	83	82	19	129
10回目	65	87	19	135
平均	71.2	86.3	18.5	134.6
割合	26%	21%	100%	14%

テスト2

以下は10万行、35列、合計36.7MBの巨大なCSVから、改行の個数を計測した結果である。

	Split	LenReplace	InStr	Byte
1回目	128	236	25	2581
2回目	153	253	39	2563
3回目	130	261	53	2546
4回目	131	271	34	2577
5回目	132	253	25	2592
6回目	150	241	25	2577
7回目	133	249	24	2602
8回目	136	238	23	2643
9回目	132	243	25	2674
10回目	134	244	25	2653
平均	135.9	248.9	29.8	2600.8
割合	22%	12%	100%	1.1%

ソースコード

VBEではUnicodeの文字を記入すると??に化けてしまうので、試すにはエクセルシート上に記入したデータを読み込む必要がある。

こんな感じのデータを作成して

	A	B
1	基底文字（baseStr）	照合文字（chkStr）
2	abc😁def😁ghi	😁

こんな感じのプログラムでテストする。

Sub Test1()
    Const TESTROW = 2
    Debug.Print StrCnt_Split(Cells(TESTROW, 1), Cells(TESTROW, 2))
    Debug.Print StrCnt_Len(Cells(TESTROW, 1), Cells(TESTROW, 2))
    Debug.Print StrCnt_InStr(Cells(TESTROW, 1), Cells(TESTROW, 2))
    Debug.Print StrCnt_Byte(Cells(TESTROW, 1), Cells(TESTROW, 2))
End Sub

それでは、それぞれの方式のソースコードとちょっとした解説をしていく。

Split法

最も短い文字数で書けるので、とても重宝する求め方

'文字列に含まれる文字列の出現回数をカウントする関数
'（SPLITバージョン）
Function StrCnt_Split(baseStr As String, chkStr As String) As Long
    StrCnt_Split = UBound(Split(baseStr, chkStr))
End Function

基底文字列

abc😁def😁ghi

を😁で分解すると

arr(0)=abc
arr(1)=def
arr(2)=ghi

となるため、添字の最大値＝2が、照合文字の個数である。

Len-Replace法

Excel関数でカウントするときによく使う方法

'文字列に含まれる文字列の出現回数をカウントする関数
'（LEN,REPLACEバージョン）
Function StrCnt_Len(baseStr As String, chkStr As String) As Long
    StrCnt_Len = (Len(baseStr) - Len(Replace(baseStr, chkStr, ""))) / Len(chkStr)
End Function

前提として、照合対象である😁はサロゲートペアのため、Len関数では2としてカウントされる。

?Len("😁")
2

もともとの文字数は

?Len("abc😁def😁ghi")
13

照合文字をReplace関数で空欄に置換。つまり削除した後の文字数は

?Len(Replace("abc😁def😁ghi",",",""))
9

この差は

?13-9
4

差を照合文字の文字数で割れば、出現回数が2であることが分かる

?4/2
2

※厳密にはLen関数は文字数を返しているわけでは無いが、わかりやすさのため文字数と書いた。

InStr法

ロジックが少し厄介だが、先の通り最も高速な求め方

'文字列に含まれる文字列の出現回数をカウントする関数
'（INSTRバージョン）
Function StrCnt_InStr(baseStr As String, chkStr As String) As Long
    Dim n As Long: n = 0
    Dim ret As Long: ret = 0
    Do
        n = InStr(n + 1, baseStr, chkStr)
        If n = 0 Then
            Exit Do
        Else
            ret = ret + 1
        End If
    Loop
    StrCnt_InStr = ret
End Function

下記の部分にご注目

?InStr(n + 1, baseStr, chkStr)

1回目

?InStr(0 + 1, "abc😁def😁ghi", "😁")
4

そして4=0では無いので、ret+1する。

2回目

?InStr(4 + 1, "abc😁def😁ghi", "😁")
9

そして9=0では無いので、ret+1する。

3回目

?InStr(9 + 1, "abc😁def😁ghi", "😁")
0

そして0=0なので、繰り返しを終了する。

よって、

?StrCnt_InStr("abc😁def😁ghi", "😁")
2

バイト配列法

文字列を一旦バイト配列にすることで、少なくともSplitよりは高速になるのではないかと考えた。

結果は先の通り激遅である。

'文字列に含まれる文字列の出現回数をカウントする関数
'（バイト配列バージョン）
Function StrCnt_Byte(baseStr As String, chkStr As String) As Long
    Dim b() As Byte:    b = baseStr
    Dim c() As Byte:    c = chkStr
    
    Dim i As Long, j As Long
    Dim ret As Long: ret = 0
    Dim isAllMatch As Boolean
    i = 0
    Do
        '照合文字`chkStr`のバイト配列と全てが一致しているか調べる
        isAllMatch = True
        For j = LBound(c) To UBound(c)
            isAllMatch = isAllMatch And b(i + j) = c(j)
        Next
        
        'インデックスをシフトする
        If isAllMatch Then
            '一致した場合は照合文字数分シフト
            ret = ret + 1
            i = i + (UBound(c) - LBound(c) + 1)
        Else
            '一致しない場合は2バイトシフトする
            i = i + 2
        End If
        
        '照合文字の文字数をオーバーする場合は調査終了
        If i > UBound(b) - UBound(c) Then Exit Do
    Loop
    
    StrCnt_Byte = ret
End Function

文字列をバイト型配列変数に代入すると、文字コードの入ったバイト配列が出来上がる。

Dim b() As Byte:    b = baseStr

変数	文字	10進数	16進数
b(0)	a	97	61
b(1)	a	0	0
b(2)	b	98	62
b(3)	b	0	0
b(4)	c	99	63
b(5)	c	0	0
b(6)	😁	90	5A
b(7)	😁	50	32
b(8)	d	100	64
b(9)	d	0	0
b(10)	e	101	65
b(11)	e	0	0
b(12)	f	102	66
b(13)	f	0	0
b(14)	😁	90	5A
b(15)	😁	50	32
b(16)	g	103	67
b(17)	g	0	0
b(18)	h	104	68
b(19)	h	0	0
b(20)	i	105	69
b(21)	i	0	0

照合文字😁は上表でも分かるが、以下のとおりである。

文字	10進数	16進数
😁	90	5A
😁	50	32

今にして思えば、別にバイト配列にする必要は無くて、Midで順番に調べてもよかったのではないだろうか。

まとめ

せっかく新しい関数を作ってみたが、何の役にも立たない検証結果であった。

それはさておき、

• いずれの方式でもサロゲートペアには対応できる

• InStrを使った方法がダントツで高速である

という事が確認出来たので、良かったということにして、今日はここまでにする。

以上

---

何か御座いましたらコメント欄、またはTwitterからどうぞ♪

それではまた来週♪　ちゅんちゅん(・8・)