[[20221112162152]]　『VBA 置き換え時の速度』（ららら）　ページの最後に飛ぶ

『VBA 置き換え時の速度』（ららら）

VBAで文字の置き換えをしたいのですが、行数が多いので、もっと早く出来るコードがないかと思いまして質問させていただきます。

A列の2行目から何万行も文字が入っています。
例題では、あ、い、う、え、お　の文字がランダムに入っているとします。
やりたい事は、「う」だった場合、「うう」に置き換えたいです。

考えたコードは、下記ですが、一つ一つループで見ていくのではなく、もっと一括でやる方法はあるのでしょうか。

dim 行 as long

   For 行 = 2 To Cells(Rows.Count, "A").End(xlUp).Row
      Cells(行, "A") = WorksheetFunction.Substitute(Cells(行, "A"),"う", "うう")
   Next 行

よろしくお願いします。　

＜使用 Excel：Office365、使用 OS：Windows10 ＞

 配列を使えば出来ますが、セル内の1部の文字に色がついていた場合、色が消えます。
 あとは、rangeのreplaceメソッドが使えるけど、手作業のほうが早いような
 もっと複雑な条件あるんだろうなということで、配列の例
 Sub test()
     Dim tbl As Variant
     Dim i As Long
     tbl = Range("A2",Cells(Rows.Count, "A").End(xlUp)).Value
     For i = 1 To Ubound(tbl, 1)
         tbl(i, 1) = Replace(tbl(i, 1),"う","うう")
     Next i
     Range("A2",Cells(Rows.Count, "A").End(xlUp)).Value = tbl
 End Sub
（稲葉） 2022/11/12(土) 16:42:21

 今更ですが、これ何回も実行すると、うが、ううになって、次はううううになっちゃうけど良かったのかな？
（稲葉） 2022/11/12(土) 16:59:40

稲葉様

早速ありがとうございます。
配列だと早いとネットに書いてありましたが、実際の書き方がまだよく分からずでした。
このように使用するのですね。勉強になります。。

色は使わないので大丈夫です。
また、何回も実行すると…の件ですが、長いコードの一部にこの置き換えのコードを組み込みたいと思っているので、
マクロを流しなおすと、「あ、い、う、え、お」の文字も貼りなおすので、その心配はなさそうです。
そのような使い方であることも理解しました。

大変ありがとうございます！
（ららら） 2022/11/12(土) 17:07:49

普通の置き換えではダメな理由があるのですか？
（普通） 2022/11/12(土) 21:09:17

ところで速度は改善されたの?
（***） 2022/11/12(土) 21:12:58

 速度実験です。^^;

 ＞A列の2行目から何万行も文字が入っています。						
 ＞例題では、あ、い、う、え、お　の文字がランダムに入っているとします。

 と言うことで、まずはA列にランダムに「 あ、い、う、え、お 」を作成する						
 尚、実験は 30,000行としました。

 Sub A列にデータサンプルを入力()

    Dim q&, n&						
    Dim Words As String, str As String						
    Words = "あいうえお"

    For q = 2 To 30000						
        Randomize						
        n = Int((5 - 1 + 1) * Rnd + 1)						
        str = Mid(Words, n, 1)						
        Cells(q, 1).Value = str						
    Next q

 End Sub

 そして、3つのマクロを用意して…速度検証です。

（ららら）さん作
（稲葉）さん作
（手抜き）くん

 Option Explicit						
 Dim startTime As Double						
 Dim endTime As Double						
 Dim processTime As Double

 Sub らららさん作()

    Dim 行 As Long						
    startTime = Timer						
    For 行 = 2 To Cells(Rows.Count, "A").End(xlUp).Row						
        Cells(行, "A") = WorksheetFunction.Substitute(Cells(行, "A"), "う", "うう")						
    Next 行						
    endTime = Timer						
    processTime = endTime - startTime						
    MsgBox "処理時間：" & processTime

 End Sub

 Sub 稲葉さん作()

    Dim tbl As Variant						
    Dim i As Long						
    startTime = Timer						
    tbl = Range("A2", Cells(Rows.Count, "A").End(xlUp)).Value						
    For i = 1 To UBound(tbl, 1)						
        tbl(i, 1) = Replace(tbl(i, 1), "う", "うう")						
    Next i						
    Range("A2", Cells(Rows.Count, "A").End(xlUp)).Value = tbl						
    endTime = Timer						
    processTime = endTime - startTime						
    MsgBox "処理時間：" & processTime

 End Sub

 Sub 手抜きくん()

    startTime = Timer						
    Call Range("A:A").Replace("う", "うう")						
    endTime = Timer						
    processTime = endTime - startTime						
    MsgBox "処理時間：" & processTime

 End Sub

 ◇実験結果

 [1]|            |1回目     |2回目     |3回目     |平均      					
 [2]|らららさん作|1.5312500 |1.5468750 |1.5625000 |1.5468750 					
 [3]|稲葉さん作  |0.2500000 |0.2656250 |0.2343750 |0.2500000 					
 [4]|手抜きくん  |0.1562500 |0.1406250 |0.1484375 |0.1484375

 ※配列で十分に速いのだが、手抜きも活用法によっては						
 検討の余地があるかも？

（あみな） 2022/11/12(土) 23:54:20

 >Range("A:A").Replace("う", "うう")

 なんで範囲決めないのかな？
 たいして変わらんと思うけど。

 因みに Substitute は、セル範囲で一括変換できます。
（普通） 2022/11/13(日) 01:06:57

あ、WorksheetFunction だとちと解らない。
（普通） 2022/11/13(日) 01:08:34

 やっぱ Application だけじゃないとだめだね。
（普通） 2022/11/13(日) 01:16:15

 ＞ なんで範囲決めないのかな？
 ＞ たいして変わらんと思うけど。

 なら、つまらんこと書き込むな。

（半平太） 2022/11/13(日) 06:59:21

皆様
気付いたら沢山の方からコメント頂いていたようで、ありがとうございます！
マクロ勉強始めたばかりで、皆さまのコメントの意味についていけない部分もありますが、色々とありがとうございます。

あみな様
実験までしていただいて、大変ありがとうございます。
私も実際のデータでやってみました。
気付いたのですが、最初に私が提示したコードも、気にするほど、さほど時間が掛からないのですね。。
（他の作業もマクロ化しており、置き換えのコードを追加したら時間が伸びた気がしたのですが、
もしかしたら他の部分で時間が掛かっている気がしてきました…。。ごめんなさい。。）

そして、単純に、下記一文でも動きますね。そして早いですね。
Range("A:A").Replace What:="う", Replacement:="うう"

一つ教えて下さい。
＞手抜きも活用法によっては検討の余地があるかも？

”活用法によっては”の部分が気になりますが、Range("A:A").Replaceでやって危険な所（正しく置き換わらない）等ありますか？

ネットで調べると様々なアプローチ方法があり、どれを使えば良いのかまだまだ理解が足りていません。。

（ららら） 2022/11/13(日) 12:01:05

 ＞ なんで範囲決めないのかな？
 ＞ たいして変わらんと思うけど。

 なら、つまらんこと書き込むな。

なにこの珍平太って、朝っぱらから絡んでくるなよ。
最近多いなこういう早起き爺さんのたわごとやゴタゴタ。
朝っぱらから横から出てくんな早起きカス爺さん。
（普通） 2022/11/13(日) 12:11:32

数式を使っているのなら、書き換えのたびに再計算がされます。
自動再計算を手動に切り替えることを勧めます。
（普通） 2022/11/13(日) 12:13:56

 ＞”活用法によっては”の部分が気になりますが、Range("A:A").Replaceでやって	
 ＞危険な所（正しく置き換わらない）等ありますか？

 う～ん、多分大丈夫かと思いますが	
 ↓こちらにもよるので…わかりません。

 ＞長いコードの一部にこの置き換えのコードを組み込みたいと思っているので、

 この長いコードを提示されると、他の方からもよいアドバイスがあるかもですよ。	
 元々のマクロが時間が掛かるのかもですね。

 今から、お友達と "お好み焼き" パーティーなので出かけちゃいます。	
 すいません。(o_ _)o ﾍﾟｺ

（あみな） 2022/11/13(日) 12:34:35

普通様
数式、沢山組み込んでいます。
再計算を止めて、マクロ処理をして、また再計算を自動にするイメージでしょうか。
(画面更新を止めて、戻すのと同じタイミングで使って正しく動くのでしょうか)
長いコードは会社のPC内にあるので、明日実験してみます。
ありがとうございます！

あみな様
色々ありがとうございました。
お陰で、たくさんの気付きがありました！
お好み焼きパーティー、楽しんできてください(^^)
（ららら） 2022/11/13(日) 12:50:54

 遅かったのはほかの原因でしたかー
 ＞マクロを流しなおすと、「あ、い、う、え、お」の文字も貼りなおすので、
 この部分を、配列で一気に書き込んであげるといいかもしれませんね。
 ついでに、変換した後に書き込めば、書き込み処理も一回で済みそうですので、
 差支えなければ全文載せていただければ、できる範囲で回答させてください。
（稲葉） 2022/11/13(日) 20:05:50

稲葉様
ありがとうございます！
全文を載せるのは、すみません控えさせてください。(秘匿の関係で。)
マクロは組むのも大切ですが、それ以上に構想を練る作業が一番肝になってきそうですね。
配列もこの先マスターして、効率の良いコードの表現が出来るように努力してみます。
大変ありがとうございました。
（ららら） 2022/11/13(日) 20:33:07

コメント返信：

[ 一覧(最新更新順) ]

[[20221112162152]] 『VBA 置き換え時の速度』（ららら） ページの最後に飛ぶ

[[20221112162152]]　『VBA 置き換え時の速度』（ららら）　ページの最後に飛ぶ