Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


6582 / 13646 ツリー ←次へ | 前へ→

【44394】Replace関数の処理速度の変化について なば 06/11/14(火) 21:07 質問[未読]
【44395】Re:Replace関数の処理速度の変化について なば 06/11/14(火) 21:20 発言[未読]
【44406】Re:Replace関数の処理速度の変化について Jaka 06/11/15(水) 10:37 発言[未読]
【44409】Re:Replace関数の処理速度の変化について なば 06/11/15(水) 15:50 質問[未読]
【44410】Re:Replace関数の処理速度の変化について りん 06/11/15(水) 17:40 発言[未読]
【44440】Re:Replace関数の処理速度の変化について なば 06/11/16(木) 12:03 お礼[未読]

【44394】Replace関数の処理速度の変化について
質問  なば  - 06/11/14(火) 21:07 -

引用なし
パスワード
   はじめまして。
Replace関数の処理速度の変化について質問させてください。
よろしくお願いします。

今、1500行×200列ほどの範囲に、1セル10〜50文字(改行なし)のデータが入ったシートを扱っています。
このシート内の全セルに対して、半角カタカナを全角カタカナに変換する処理を行っています。
このときにReplace関数を使っています。

文字コード166〜221までが濁点、半濁点を除いた半角カタカナなので、
 Dim lngCode as Long 'ループカウンタ
 Dim rngRange as Range '処理対象範囲
 Dim strHankaku as String '半角カタカナ
 Dim strZenkaku as String '全角カタカナ

 for lngCode = 166 to 221
  strHankaku = chr(lngCode) '半角カタカナ
  strZenkaku = strConv(strZenkaku) '全角カタカナ

  '全角→半角変換
  rngRange.Replace what:=strHankaku, Replacement:=strZenkaku, _
   LookIn:=xlFormulas, MatchCase:=False, Matchbyte:=True
 next
といった具合のループを組んでいます。

この処理を行うとき、Activateを使ってシートを表示させた場合と
シートを表示させなかった場合とで処理時間に大きな差が出ます。

シートを表示させた場合は上記のループが5分もかからずに終了するのですが、
シートを表示させなかった場合は、ループ1回につき30秒、終了までに20分以上かかってしまします。

この処理時間の差の原因をご存知の方がいらっしゃいましたら、
教えていただけませんでしょうか。

ちなみに実行環境はWindows2000、Excel2000で、CPUは1GHz程度、メモリは512MBです。

よろしくお願いしますm(__)m

【44395】Re:Replace関数の処理速度の変化について
発言  なば  - 06/11/14(火) 21:20 -

引用なし
パスワード
   追記で失礼します。

上記のコードですが、Replace関数のLookInは嘘でした。。。
すいませんが、LookInだけなかったことにしてくださいm(__)m

【44406】Re:Replace関数の処理速度の変化について
発言  Jaka  - 06/11/15(水) 10:37 -

引用なし
パスワード
   人に試してほしかったら、動くコードを載せないと、
誰も返答をしてくれないと思います。

【44409】Re:Replace関数の処理速度の変化について
質問  なば  - 06/11/15(水) 15:50 -

引用なし
パスワード
   >Jaka さん
ご指摘ありがとうございます。
抜けだらけのコードですいませんでしたm(__)m
修正しましたので、再度投稿いたします。

Sub Sample1()
 Dim objFileSys   As Scripting.FileSystemObject
 Dim objFileSrch  As FileSearch
 Dim objExcelAppl  As Excel.Application
 Dim objExcelWBooks As Excel.Workbooks
 Dim objExcelWBook As Excel.Workbook
 Dim objSheet    As Worksheet '処理対象ワークシート
 
 Set objFileSys = CreateObject("Scripting.FileSystemObject")
 Set objFileSrch = Application.FileSearch
 
 Set objExcelAppl = CreateObject("Excel.Application")
 Set objExcelWBooks = objExcelAppl.Workbooks
 Set objExcelWBook = objExcelWBooks.Open(Filename:="C:\QuestionBox.xls")
 
 Set objSheet = objExcelWBook.Worksheets(1)
 
 '*****サンプルデータ作成*****
 Sample2 objSheet
 objExcelWBook.Save
 
 '*****半角カナ→全角カナ変換*****
 objExcelAppl.Visible = True '処理対象ファイルを表示
 Sample3 objSheet
 
 Set objSheet = Nothing
 objExcelWBook.Save
 objExcelWBook.Close
 objExcelAppl.Quit
 Set objExcelWBooks = Nothing
 Set objExcelAppl = Nothing
 Set objFileSys = Nothing
 Set objFileSrch = Nothing
 
 MsgBox "処理完了です"
End Sub

Sub Sample2(ByRef objSheet As Worksheet)
 objSheet.Range("A1").Value = "12アイウエオ34"
 objSheet.Range("A2").Value = "56カキクケコ78"
 
 objSheet.Range(objSheet.Range("A1"), objSheet.Range("A2")).Copy
 objSheet.Range(objSheet.Range("A1"), objSheet.Cells(1500, 200)).PasteSpecial xlPasteValues
End Sub

Sub Sample3(ByRef objSheet As Worksheet)
 Dim lngCode    As Long 'ループカウンタ
 Dim rngUsdRange  As Range '処理範囲
 Dim strHankaku   As String '半角カナ
 Dim strZenkaku   As String '全角カナ
 
 Set rngUsdRange = objSheet.UsedRange
 
 For lngCode = 166 To 221
  strHankaku = Chr$(lngCode) '半角カナ
  strZenkaku = StrConv(strHankaku, vbWide)  '全角カナ

  '半角カナ→全角カナ変換
  rngUsdRange.Replace what:=strHankaku, Replacement:=strZenkaku, _
   MatchCase:=False, Matchbyte:=True
 Next lngCode
End Sub

上記は、Cドライブ直下に「QuestionBox.xls」というファイル名の
空のExcelファイルを用意していただいてから、
Sample1を実行していただければ動きます。

QuestionBox.xlsにサンプルデータを記入(Sample2)して、
それに対して処理(Sample3)を行います。

QuestionBox.xlsを表示しないで処理するには、Sample1内の
 objExcelAppl.Visible = True
をコメントアウトしていただければOKです。

すごい面倒かとは思いますが、
お時間があるときにご検討いただければ嬉しいです。
よろしくお願いしますm(__)m

【44410】Re:Replace関数の処理速度の変化について
発言  りん  - 06/11/15(水) 17:40 -

引用なし
パスワード
   なば さん、こんばんわ。

>Sub Sample1()
> Dim objFileSys   As Scripting.FileSystemObject
> Dim objFileSrch  As FileSearch
> Dim objExcelAppl  As Excel.Application
> Dim objExcelWBooks As Excel.Workbooks
> Dim objExcelWBook As Excel.Workbook
> Dim objSheet    As Worksheet '処理対象ワークシート
> 
> Set objFileSys = CreateObject("Scripting.FileSystemObject")
> Set objFileSrch = Application.FileSearch
> 
> Set objExcelAppl = CreateObject("Excel.Application")
> Set objExcelWBooks = objExcelAppl.Workbooks

↑ここら辺になにかあるんでしょうかね。


以下のコードだと、表示時1分12秒、Application.Visible = Falseで1分9秒でした。XL2003 & WinXP

Sub Sample1()
 Dim objExcelWBook As Workbook, objSheet As Worksheet, t1 As Double
 t1 = Now
 Application.Visible = False
 Set objExcelWBook = Application.Workbooks.Add
 Set objSheet = objExcelWBook.Worksheets(1)
 '*****サンプルデータ作成*****
 Sample2 objSheet
 'objExcelWBook.Save

 '*****半角カナ→全角カナ変換*****
 Sample3 objSheet
 '
 Application.Visible = True
 MsgBox Format(Now - t1, "hh:mm:ss"), vbInformation, "処理完了です"
End Sub

Sub Sample2(ByRef objSheet As Worksheet)
 objSheet.Range("A1").Value = "12アイウエオ34"
 objSheet.Range("A2").Value = "56カキクケコ78"

 objSheet.Range(objSheet.Range("A1"), objSheet.Range("A2")).Copy
 objSheet.Range(objSheet.Range("A1"), objSheet.Cells(1500, 200)).PasteSpecial xlPasteValues
 Application.CutCopyMode = False
End Sub

Sub Sample3(ByRef objSheet As Worksheet)
 Dim lngCode    As Long 'ループカウンタ
 Dim rngUsdRange  As Range '処理範囲
 Dim strHankaku   As String '半角カナ
 Dim strZenkaku   As String '全角カナ
 Dim r1 As Range
 Set rngUsdRange = objSheet.UsedRange
 '
 Application.ScreenUpdating = False
 '
 For lngCode = 166 To 221
  strHankaku = Chr$(lngCode) '半角カナ
  strZenkaku = StrConv(strHankaku, vbWide)  '全角カナ
  Application.ScreenUpdating = False
  '処理状況をステータスバーに表示
  Application.StatusBar = strZenkaku
  'とりあえず探してみる
  On Error Resume Next
  Set r1 = rngUsdRange.Find(what:=strHankaku, LookAt:=xlPart)
  On Error GoTo 0
  '見つかった場合は変換
  If Not r1 Is Nothing Then
   '半角カナ→全角カナ変換
   rngUsdRange.Replace what:=strHankaku, Replacement:=strZenkaku, MatchCase:=False, Matchbyte:=True
  End If
  Set r1 = Nothing
 Next lngCode
 '
 Application.StatusBar = False
 Application.ScreenUpdating = True
End Sub

【44440】Re:Replace関数の処理速度の変化について
お礼  なば  - 06/11/16(木) 12:03 -

引用なし
パスワード
   >りんさん
検証ありがとうございました。
どうやら↓の1行が処理速度に影響していたようです。

 Set objExcelAppl = CreateObject("Excel.Application")

Applicationのオブジェクトを取らずにいけば大丈夫でした。
ここには注意が行ってませんでした。。。

おかげで解決策が見つかりました。
ありがとうございました。

6582 / 13646 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free