質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Office 365

Office 365は、マイクロソフトが販売している企業向けクラウドベースのグループウエアサービス。電子メールや予定表、Webサイト構築、オンラインストレージ、ビデオ会議などビジネスで必要な機能を備えています。クラウドサービスのため、自社での専用サーバーの設置の必要がないことが特徴です。

Q&A

解決済

2回答

1486閲覧

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

tttkkk

総合スコア38

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Office 365

Office 365は、マイクロソフトが販売している企業向けクラウドベースのグループウエアサービス。電子メールや予定表、Webサイト構築、オンラインストレージ、ビデオ会議などビジネスで必要な機能を備えています。クラウドサービスのため、自社での専用サーバーの設置の必要がないことが特徴です。

0グッド

1クリップ

投稿2021/04/19 12:54

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

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

VBA

1Private Sub Worksheet_Change(ByVal Target As Range) 2 3Dim foundCd As Range, foundName As Range 4Set foundCd = ActiveSheet.Cells.Find(what:="コード", lookat:=xlWhole) 5Set foundName = ActiveSheet.Cells.Find(what:="名前", lookat:=xlWhole) 6 7If (Target.Value = "") Then 8 ActiveSheet.Cells(Target.Row, foundName.column).ClearContents 9Else 10 ActiveSheet.Cells(Target.Row, foundName.column) = "〇" 11End If 12 13End Sub

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

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

VBA

1Private Sub Worksheet_Change(ByVal Target As Range) 2 3If (Target.Value = "") Then 4 ActiveSheet.Cells(Target.Row, Target.column + 1).ClearContents 5Else 6 ActiveSheet.Cells(Target.Row, Target.column + 1) = "〇" 7End If 8 9End Sub

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

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

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

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

VBA

1 2Function myFind(ws As Worksheet, strFind As String) As Range 3 For Each myFind In ws.Cells.SpecialCells(xlCellTypeConstants) 4 If myFind.Value = strFind Then Exit Function 5 Next 6End Function 7 8Private Sub Worksheet_Change(ByVal Target As Range) 9 Dim foundCd As Range, foundName As Range 10 Set foundCd = myFind(Me, "コード") 11 Set foundName = myFind(Me, "名前") 12 13 If Not foundCd Is Nothing Then Debug.Print foundCd.Address 14 If Not foundName Is Nothing Then Debug.Print foundName.Address 15 16End Sub 17

投稿2021/04/21 15:17

jinoji

総合スコア4585

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

xail2222

2021/04/21 23:33 編集

発想というか思想は素晴らしいと思いました。 私の別のやり方への逃げの案より、元の思想のままの攻めの案ということで良い気がします。 findの機能を使いたいとかfindを使ったコードを書き換えるための手段として有用ですね。 ただ、似た機能として代替という観点で考えると気になるところがあって、元のfindはRangeのメソッドなのだから、引数はWorksheetじゃなくてRangeの方がいい気がするし xlCellTypeConstants固定にしているのも、findの役割を考えると、少し気になります。 代替関数による解決法の提案という考え方の提示ということであれば 私が気になることなんて、どうでもいいことなのかもしれないけど 気になっちゃいました。 あと、関数の戻り値用の変数としての関数名を変数として使い倒している所にしびれました。 無意識的に私はデフォルト値として初めにセットして最後に確定値をセットという形でしか使ってなかったのですが、新たな境地を見せられた気分です。 長文の感想失礼しました。
jinoji

2021/04/22 02:52

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

2021/04/22 12:31

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

0

ベストアンサー

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

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

処理速度が遅いそうです。(ほんとかな?)

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

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

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

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

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

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

VBA

1Dim tRange As Range 2Dim tCol As Long 3tCol = 0 4For Each tRange In Intersect(ActiveSheet.UsedRange, ActiveSheet.Rows(1)) 5 If tRange.Value = "コード" Then 6 tCol = tRange.Column 7 End If 8Next 9

とか

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

VBA

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

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

とかあるかと思います。

投稿2021/04/19 21:38

編集2021/04/19 21:44
xail2222

総合スコア1497

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

tttkkk

2021/04/21 12:19

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問