VBA changeイベント オートメーションエラー

Question

changeイベントを使用して、特定の値を返すマクロを組んでいるのですが、changeイベントに合わせてfindメソッドを使用すると、下記の画像のようなオートメーションエラーが出てきてしまいます。

サンプルコードを以下に記載致します。

Private Sub Worksheet_Change(ByVal Target As Range)

Dim foundCd As Range, foundName As Range
Set foundCd = ActiveSheet.Cells.Find(what:="コード", lookat:=xlWhole)
Set foundName = ActiveSheet.Cells.Find(what:="名前", lookat:=xlWhole)

If (Target.Value = "") Then
    ActiveSheet.Cells(Target.Row, foundName.column).ClearContents
Else
    ActiveSheet.Cells(Target.Row, foundName.column) = "〇"
End If

End Sub

私自身でも色々と試してみたところ、findメソッドを使用しなければ、オートメーションエラーは起こさないというのが分かりました。
ただどうしてchangeイベントにfindメソッドを組み合わせるとオートメーションエラーが出てきてしまうのか分からず、教えて頂けないでしょうか。

例えば上記のコードをfindメソッドを使用しないで書こうとすると以下のようになります。

Private Sub Worksheet_Change(ByVal Target As Range)

If (Target.Value = "") Then
    ActiveSheet.Cells(Target.Row, Target.column + 1).ClearContents
Else
    ActiveSheet.Cells(Target.Row, Target.column + 1) = "〇"
End If

End Sub

findメソッドを使用しなけば、確かにオートメーションエラーが出ずに済むのですが、表のレイアウトが変わる可能性を考えると、できるだけfindメソッドなどを用いて柔軟性のあるマクロにしたいたと思っております。

非常に簡易的なものですが、表の画像も載せましたので、参考にして頂けますと幸いです。

Answer 1

Findメソッドの代替を考えてみました。

Function myFind(ws As Worksheet, strFind As String) As Range
    For Each myFind In ws.Cells.SpecialCells(xlCellTypeConstants)
        If myFind.Value = strFind Then Exit Function
    Next
End Function

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim foundCd As Range, foundName As Range
    Set foundCd = myFind(Me, "コード")
    Set foundName = myFind(Me, "名前")
    
    If Not foundCd Is Nothing Then Debug.Print foundCd.Address
    If Not foundName Is Nothing Then Debug.Print foundName.Address
    
End Sub

Comments

[xail2222]

発想というか思想は素晴らしいと思いました。
私の別のやり方への逃げの案より、元の思想のままの攻めの案ということで良い気がします。
findの機能を使いたいとかfindを使ったコードを書き換えるための手段として有用ですね。

ただ、似た機能として代替という観点で考えると気になるところがあって、元のfindはRangeのメソッドなのだから、引数はWorksheetじゃなくてRangeの方がいい気がするし
xlCellTypeConstants固定にしているのも、findの役割を考えると、少し気になります。

代替関数による解決法の提案という考え方の提示ということであれば
私が気になることなんて、どうでもいいことなのかもしれないけど
気になっちゃいました。

あと、関数の戻り値用の変数としての関数名を変数として使い倒している所にしびれました。
無意識的に私はデフォルト値として初めにセットして最後に確定値をセットという形でしか使ってなかったのですが、新たな境地を見せられた気分です。

長文の感想失礼しました。

[jinoji]

感想ありがとうございます。励みになります。
しょせん完全な代替にはなりようがないと割り切って、引数や検索対象は一番使いそうなシーンに合わせて考えましたが、たとえば
Function myFind(rng As Range, strFind As String) As Range
For Each myFind In rng.Cells
みたいにすることはできると思います。

[tttkkk]

本質問に限らず、私の質問にご回答して頂き、ありがとうございます。
自分で関数を作成するという発想がなく、このような方法を教えて頂け、これから様々なマクロを組んでいく中で、新たな知見を得られたと思います。
是非こちらの方法も試して参りたいと思います。

もしまた機会がございましたら、教えて頂けますようお願い致します。

Answer 2

Findは確かにオートメーションエラーが発生しますね。
理由はわからなかったのでテストしてみた結果とかを合わせて回答してみます。

まず、真偽はわかりませんが、確かめても居ませんが
VBAのFindメソッドの使い方には注意が必要です

処理速度が遅いそうです。（ほんとかな？）

あとこちらは致命的な感じですが

引数LookIn、LookAt、SearchOrder、およびMatchByteの設定は、このメソッドを使用するたびに保存されます。

次にこのメソッドを使用するときにこれらの引数の指定を省略すると、保存された設定が使用されます。
これらの引数の設定を変更すると、[検索と置換]ダイアログボックスに表示される設定が変わります。

というように検索と置換のダイアログに影響を与えるのは避けた方がよさそうな気がします。

なので、諦めて他のやり方でいいんじゃないかなと思います。
例として、他のやり方を書いてみます。

Rows(1)だけを検索対象にしてよいと思うので
べたなやり方ですが

Dim tRange As Range
Dim tCol As Long
tCol = 0
For Each tRange In Intersect(ActiveSheet.UsedRange, ActiveSheet.Rows(1))
    If tRange.Value = "コード" Then
        tCol = tRange.Column
    End If
Next

とか

絶対に存在することが分っているなら

Dim tCol As Long
tCol=Application.WorksheetFunction.Match("コード", ActiveSheet.Rows(1), 0)

※これは"コード"が存在しなかったらエラーが発生します。

とかあるかと思います。

Comments

[tttkkk]

ありがとうございます！
findメソッドにそのようなデメリットがあるとは知りませんでした。
match関数なら似たようなことができ、さらにfindメソッドのデメリットもカバーできるということですね。