エクセルでVBAで範囲に対してTRIM関数を適用したい

質問

他のソフトから取り込んだデータなどに、各データの前後に不要なスペースがついている場合があります。
これをワークシート関数のTRIM同様前後のスペースだけ（文字列中のスペースは残す）削除したいのですが、データが多いのでFor～Nextは避けたいと思っています。
何かよい方法はありますか？

回答No.3

KenKen_SP · ベストアンサー

こんにちは。KenKen_SP です。

ループ処理は必須ですね。ただ、時間を短縮させることは可能です。

【POINT】

1.　UsedRange や CurrentRegion、SpecialCells などで処理対象のセルを
　　限定させ、ループ数を減らす

　　全てのセルが選択された状態で考えてみます（Selection が Cells のとき）

　　For Each c in Selection ...といったコードは良く見かけますが、この
　　ままでは、65536×256 のループ処理になります。通常、処理が必要なのは、
　　何らかのデータが存在するセルのみなので、

　　For Each c in Intersect(Selection,Activesheet.UsedRange)

　　のようにすると、ループ回数が激減するはずです。同様に、置換処理などでは
　　数式のあるセルでは置換処理は無意味ですから、

　　For Each c In Selection.SpecialCells(xlCellTypeConstants, xlNumbers Or xlTextValues)

　　として定数のセルのみループさせます。数式の置換なら数式のあるセル
　　だけですね。

　　ループ回数を減らすだけで、随分と処理速度が改善します。

2.　オブジェクトへの参照を減らす

　　Excel VBA で処理速度を落とす要因のひとつに、オブジェクトへの参照
　　があります。これをなるべく減らす様にコーディングすると良いでしょう。

　　具体的には配列を使用します。

　　For Each c in Selection で Range コレクションをループさせた場合、
　　ループの度にセルへの参照が行われます。これが非常に遅い。

　　オブジェクトの参照が遅いのは、数多くのプロパティーを同時に取得する
　　からですね。例えば、セルのフォントや罫線、色、書式等々。

　　全セル選択時だとフリーズ、、または非常に長い時間がかかります。

　　大半の処理で必要なのは Value プロパティーだけだったりします。
　　では、配列を使用してみます。

　　Buffer = Selection.Value
　　For Each vntElement in Buffer
　　Next

　　この場合、セルの参照は1回だけで、取得するプロパティーは Value のみ
　　です。

　　全てのケースで配列が有効なわけではありません。特に、セルの選択範囲
　　が飛び飛びの場合だと Areas コレクションでブロック毎に切り分け処理が
　　必要になりますし。

以上を検証するために、7万セル（10,000行×7列）に "aaa " のデータをセットし、
実際に計測してみました。

左が Test1 で、右が Test2 の結果です。単位はミリ秒。ロジックの違いで
10倍以上の差が発生します。

　　　　　　Test1　　Test2
1回目:=　1038　　10427
2回目:=　1039　　10427
3回目:=　1043　　10439
4回目:=　1034　　10424
5回目:=　1041　　10456

Sub Test1()

　　Dim rngTarget As Range
　　Dim Buffer　　As Variant
　　Dim lngRowCnt As Long
　　Dim lngColCnt As Long
　　Dim i As Long, j As Long

　　Set rngTarget = Range("A1").CurrentRegion

　　Buffer = rngTarget.Value
　　lngRowCnt = UBound(Buffer)
　　lngColCnt = UBound(Buffer, 2)

　　For i = 1 To lngRowCnt
　　　　For j = 1 To lngColCnt
　　　　　　Buffer(i, j) = Trim$(Buffer(i, j))
　　　　Next j
　　Next i
　　rngTarget.Value = Buffer
　　Set rngTarget = Nothing

End Sub

Sub Test2()

　　Dim rngTarget As Range
　　Dim C As Range

　　Set rngTarget = Range("A1").CurrentRegion

　　For Each C In rngTarget
　　　　C.Value = Trim$(C.Value)
　　Next

　　Set rngTarget = Nothing

End Sub

お礼

KenKen_SPさん、いつもありがとうございます。
配列ですか。すごい方法があるんですね！驚きました。
早いです！ありがとうございました。助かりました。

ひとつだけいいですか？
C.Value = Trim$(C.Value)の＄は何でしょうか？
Stringかなとも思いましたが、結果は数値ででますし、これを取っても結果は変わらなかったので不思議に思いました。

回答No.5

KenKen_SP

> 結果は数値ででますし、これを取っても結果は変わらなかったので不思議に
> 思いました。

C.Value = Trim$(C.Value)

セルの値が数値なら、Trim 関数の結果が String 型で返され、その文字列の
数字をセルへ代入しているわけですが、その時に Excel が数字なので、数値
に再び変換しています。

...なんて無意味な...(|||´Д｀)

詳しくは #4 に書いてます。

お礼

なんどもありがとうございます。
これからもよろしくお願いいたします。

回答No.4

KenKen_SP

> C.Value = Trim$(C.Value)の＄は何でしょうか？

あら？ セルへのデータセットなので、この場合は逆効果ですね、すいません。
それは C.Value = Trim(C.Value) の方が良いです。

＄ の意味ですが、Trim 関数をヘルプで調べると、

　[Excel VBA ヘルプ引用]
　指定した文字列から...（略）...先頭と末尾の両方のスペース (Trim) を削除
　した文字列を表すバリアント型 (内部処理形式 String の Variant) の値を返
　します。

とあるように、Trim 関数の戻り値は Variant 型です。これに ＄ をつけて Trim$
とすると、戻り値の型は String 型になります。この意義ですが、

　　Trim 関数の戻り値を String 型の変数に代入するときは、Trim$ 関数の
　　方が高速に動作する

です。同様に Left$、Right$、Mid$、Replace$、String$ ...等々もあります。

VB（VBA）は、プログラムの敷居を低くするためデータ型を意識しなくとも、
バックグラウンドで適切な型に変換する機能（自動キャスト）が備わっています。

　# ゆえに、「VB にはデータ型がない、変数を宣言しなくても良い」という
　# 誤解を生んでいますが。

しかし、VB もプログラム言語ですから内部的には厳密なデータ型が存在します。
データ型を意識しないコードを書いても、それが動作するのは、

　　「VB が裏で必死で頑張ってくれるから」

に過ぎません。例えば、次のようなコード。

　　Dim strDATA As String '<-- String 型で宣言
　　strDATA = Trim("あああああ")

Trim 関数の戻り値は Variant 型なので、String 型の変数に直接代入できません。
そこで、VB は頑張ります。

　　1.　どうも Variant のままでは代入できないらしい
　　2.　では、どの型に変換したら良いか調べよう
　　2.　どうも String 型がよいらしい
　　3.　では Variant --＞ String のデータ型変換を行おう
　　4.　変数へ代入

なんて作業が裏では発生しています。

確かに、プログラムの敷居は低くなりますが、これでは余計な型変換の作業が
発生する分、実行速度は低下しますね。これを回避するなら、

　　Dim strDATA As String '<-- String 型で宣言
　　strDATA = Cstr(Trim("あああああ"))

とします。これで、VB が「どの型に変換すべきか？」という作業から開放され、
実行速度が向上します。

さらに、Trim には Trim$ 関数という結果を String 型で返す専用関数が用意
されてます。

　　Dim strDATA As String '<-- String 型で宣言
　　strDATA = Trim$("あああああ")

これで、「適切な型に変換する」という作業から開放されました。結果として
実行速度は向上します。なお、

　　vntDATA = Trim(Range("A1").Value)

のように、セルに何型のデータが入力されるか、固定できない場合は、逆効果
になるので、$ はつけません。

上記のように「ごく短い」コードでは、このような細かい注意で体感できるほど
の処理速度の向上はありませんが、例えば次のコードではどうでしょうか？

　　Dim strDATA As String '<-- String 型で宣言
　　Dim i As Long, j As Long, k As Long

　　For i=1 to 10000
　　　　'処理1
　　　　For j=1 to 5000
　　　　　　'処理2
　　　　　　For k=1 to 30
　　　　　　　　'処理3
　　　　　　　　strDATA = Trim("あああああ") '※
　　　　　　　　strDATA = Replace　～
　　　　　　Next k
　　　　Next j
　　Next i

ごく普通にでてきそうなコードですね。。。

注意してほしいのは、※記号の部分が 10000 × 5000 × 30 回実行される点です。
上述の「VBが裏で頑張る余計な作業」がこれだけ発生することになるんです。

これでは、遅くなるのは「当たり前」です。VB が「遅い」という前にコードが
悪いといった方が良いでしょう。

長々と書きましたが、以上の訳で、Trim は文字列を扱う場合がほとんどですから、
$ を癖みたいにつけてしまうんです。

今回はセルへの代入なので、逆効果ですが。

　　変数の宣言をしないと速度が落ちる、、だから、ちゃんと宣言しなさい、、

と良く言われる理由のひとつです。

お礼

詳しい解説をありがとうございました。
とても勉強になりました。

回答No.2

imogasi

Sub test01()
Worksheets("Sheet1").Range("A1:A5") = Trim(Worksheets("Sheet1").Range("A1:A5"))
End Sub
ができないので
ForNextかForEachNextでセルごとに繰り回しをせざるを得ないと思います。

お礼

ありがとうございます。
Sub test01()
Worksheets("Sheet1").Range("A1:A5") = Trim(Worksheets("Sheet1").Range("A1:A5"))
End Sub
が出来ればいいんですけどねえ。

回答No.1

GreatDragon

下記の例のようなコードで時間が掛かりすぎるということでしたら、
１．他のソフトで前後のスペースを取り除いてから Excel に取り込む
２．外部からデータを取り組む際に何らかの細工をする
のどちらかになると思います。

（例）
Public Sub Test1()
　　Dim MyRange As Range
　　For Each MyRange In Range("A1").CurrentRegion
　　　　MyRange.Value = Trim(MyRange.Value)
　　Next
　　Set MyRange = Nothing
End Sub

お礼

ありがとうございます。
データ量が多いもので少しでも高速化をと思い、質問させていただきました。