エクセルＶＢＡの実行スピードが落ちます

質問

エクセルで検索を行うＶＢＡを使用していますが、エクセル立ち上げ時はサクサク動きますが、検索を繰り返し使っていくと、実行速度が落ちてしまいます。
エクセルを再起動すれば、元どおりの速さに戻ります。
何が原因でしょうか？どうすれば防ぐことはできるでしょうか？
よろしくお願い申し上げます。

実行環境
WindowsXPproSP3 Pen4 3.0Ghz メモリ1GB HDD80GB Office2003

ＶＢＡの検索部分
Function Kensaku3(Key1 As String, Range1 As String) As Long
'縦方向の検索
　　Dim myRng As Range
　　Dim Job1 As String
　　Dim Col1 As Long
　　Dim Row1 As Long
　　Col1 = Range(Range1).Column
　　Row1 = Range(Range1).row
　　Cells(Row1, Col1).Select
　　Set myRng = Range(Range1).Find(what:=Key1, _
　　　　After:=ActiveCell, LookIn:=xlValues, _
　　　　LookAt:=xlPart, SearchOrder:=xlByRows, _
　　　　SearchDirection:=xlNext, MatchCase:=False)
　　If myRng Is Nothing Then
　　　　Kensaku3 = 0
　　Else
　　　　Kensaku3 = myRng.row
　　End If
　　Set myRng = Nothing
End Function

回答No.2

Wendy02 · ベストアンサー

こんばんは。

全体が見えていないのではっきり分かりませんが、関数プロシージャではなく、値渡し型で、独立したユーザー定義関数にしてみました。目的に合うかは分かりませんが、以下は、ワークシート上でも動きます。

ただし、全て、一行または一列の中の１次元配列を検索するスタイルです。範囲の代入は、複数行や複数列でもよいのですが、縦横、長い方の一つの辺が優先されますが、縦横同じ長さの四角形なら、縦が優先されます。特に、セルの上を走っているわけではありませんし、オブジェクトを確保しているわけではないので、負担は少ないかと思います。

引数のKey1 は、数値でも、文字でも検索は可能です。
引数のRange1 は、文字型でも、Range型でも可能です。

呼び出しでなければ、これは標準モジュールになります。
'--------------------------------------------

Option Compare Text　'テキストコンペアモード
Function Kensaku4(Key1 As Variant, Range1 As Variant) As Long
　　Dim vh As Boolean
　　Dim i As Long
　　Dim ar As Variant
　　Dim rng As Range
　　If TypeName(Range1) = "Range" Then
　　　　Set rng = Range1
　　ElseIf VarType(Range1) = vbString Then
　　　　Set rng = Range(Range1)
　　End If
　　If rng.Rows.Count > rng.Columns.Count Then
　　　　Set rng = rng.Columns(1)
　　　　ar = rng.Value
　　Else
　　　　Set rng = rng.Rows(1)
　　　　ar = rng.Value
　　End If
　　If UBound(ar, 1) > UBound(ar, 2) Then
　　　　'縦方向の検索（1次元に切り出し）
　　　　ar = Application.Transpose(ar)
　　Else
　　　　'横方向の検索
　　　　ar = Application.Transpose(ar)
　　　　ar = Application.Index(ar, , 1)
　　　　ar = Application.Transpose(ar)
　　　　vh = True
　　End If
　　For i = LBound(ar) To UBound(ar)
　　If ar(i) Like "*" & Key1 & "*" Then Exit For
　　Next i
　　If i < UBound(ar) Then
　　　　If vh Then
　　　　　　Kensaku4 = rng.Cells(i).Column
　　　　Else
　　　　　　Kensaku4 = rng.Cells(i).Row
　　　　End If
　　Else
　　　　Kensaku4 = 0
　　End If
　　Set rng = Nothing
End Function

p.s.上記は、本来、配列数式でも可能ですが、VBAは、配列数式のようなメモリを再読み込みしてしまうことがありません。

>Sub化は避けたいと思っています。
Sub プロシージャは、一般的には戻り値はありませんが、仮の引数を入れてあげることで、値を返すことが可能です。あえて書きませんが、関数プロシージャも、Subプローシージャも、ほとんど変わりません。Subプロシージャの引数は、参照渡しですから、使い方によっては便利な部分もあります。

お礼

回答ありがとうございます。
教えていただいた方法を試してみましたが、結果は同様でした。
検索を強制的に繰り返すテストをしたところ、検索部分だけではスピードは落ちなかったので、どうやら他の原因のようです。
様子を見ながら確かめて行こうとおもいます。
この質問はこれで一度ｃｌｏｓｅとさせていただきます。

回答No.1

Wendy02

こんばんは。

他のコードからループにして、Key1に検索値を入れているのだと思いますが、String 型ということは、そこで、セルから、取り出しているのでしょうか。その時に、問題が発生している可能性があります。配列からなら、特に、問題はありません。

次に、サブルーチンの関数プロシージャを、SUBプロシージャ型にするか、その検索自体をひとつにしてみたらいかがでしょうか。関数プロシージャは、標準では、参照渡しではなく、値渡しですから、そこに問題が出ているように思います。関数は、ひとつのオブジェクトと同じ扱いなのです。

なお、
　　Col1 = Range(Range1).Column
　　Row1 = Range(Range1).Row
　　Cells(Row1, Col1).Select
　　Set myRng = Range(Range1)

オブジェクトを取り直したり、設定し直したりするのは無駄です。
こういう無駄をなくす作業を「オプティマイゼーション」といいます。検索すれば出てくると思います。ただし、それを直しても大きな変化はないかもしれません。

後は、VBEの設定は、

ツール--オプション--
　コンパイル
　　レ　順次コンパイル
　　レ　バックグラウンドコンパイル

としているか確認してください。

以上は、MSの教科書的な解答です。実際問題としては、難しいですね。肝心なシートのデータ量に依存しているかもしれない、ということは抜きで話をしているからです。こちらも、今、まったく思ってみないような現象が現れてきていますが、概ね、データが少なければ問題は出ないはずなのですね。

お礼

回答ありがとうございます。
Key1に入れる検索値はセルから読み取っています。
この自前の関数はプログラムの各所からCALLしています。
汎用的に使えるように検索値と検索範囲を渡しています。
検索で見つかったセルの行を返しますので、CALL元では同じ行の任意の列を操作する処理になっています。
とても重宝していますので、Sub化は避けたいと思っています。
「オブジェクトを取り直したり、設定し直したりするのは無駄」とのご指摘ですが、Rangeのハンドリング方法が良く分からないので以下のような方法でCALL元で範囲をString化して渡しています。
Adr1 = Range(Cells(i, 1), Cells(Row2, 1)).Address
もっと適切な方法をご存知でしたら教えていただけないでしょうか。

尚、VBEの設定は以下の設定になっています。
ツール--オプション--
　コンパイル
　　レ　順次コンパイル
　　レ　バックグラウンドコンパイル

よろしくお願い申し上げます。