ExcelのVBAのFindメソッドで困ったエラーが発生!

ExcelのVBAでの話になります。

RangeオブジェクトのFindメソッドを使用し、指定された値を所持するセルの行位置を取得する処理を組み入れた業務APを開発される際の注意喚起になります。

実際の業務APの内容をお見せすることは出来ないため、デフォルメ化したサンプルAPで説明させていただきます。

※サンプルExcelファイルのダウンロードはこちらからどうぞ。

<サンプルAP仕様>
シート「Sheet1」上の表より、指定された値を所持するセルを探索しその行位置を結果としてメッセージ表示する。

先ずは、シート「Sheet1」の内容になります。
excel_01_00_data

 

そして、当初開発時のソースコード(VBAマクロ)

Sub 一見問題のないように見える書き方()

 Dim l_Row As Long   '探し物の行位置

 With Sheets("Sheet1")

  '■探索範囲に存在する"くじら"を探索
  l_Row = .Range("A:A").Find("くじら").Row

  MsgBox "探し物の行は[" & Format(l_Row, "#,##0") & "]です。"

 End With

End Sub

 

これを実行すると正常に処理されます。
excel_01_01_ok1

 

しかし、下記のソースコードのように探索値を変更してみたところ・・・

Sub 問題が発生します()

 Dim l_Row As Long   '探し物の行位置
 
 With Sheets("Sheet1")

  '■探索範囲に存在しない"ことり"を探索
  l_Row = .Range("A:A").Find("ことり").Row

  MsgBox "探し物の行は[" & Format(l_Row, "#,##0") & "]です。"

 End With

End Sub

 

Findメソッドの実行行で、このように結果がエラーとなります。
excel_01_02_ng1

しかも、withブロック変数の設定が原因とのこと・・・・

見てのとおりwithブロックの書き方に間違いは見られない。

しかし、エラーはwithブロック変数・・・これは困りました。

 

諸々の情報収集と検証をしたところ、原因は本当につまらないことでした。

Findメソッドで条件該当するセルを探索することが出来ないにも関わらず、Rowプロパティを要求したため「Rowプロパティを返したいけど対象のセルがありませんよ!」というエラーだったようです。

つまり、下記のソースコードのようにオブジェクトとメソッドとプロパティの3連続ピリオド結合を止めて、正しく途中経過を判定すれば良いだけだったのです。

Sub 皆様このように書きましょう()

 Dim l_Range As Range   '探し物の結果セル

 With Sheets("Sheet1")

  '■探索範囲に存在しない"ことり"を探索
  Set l_Range = .Range("A:A").Find("ことり")

  '■探索結果を判定する。
  If (Not l_Range Is Nothing) Then
   MsgBox "探し物の行は[" & Format(l_Range.Row, "#,##0") & "]です。"
  Else
   MsgBox "探し物はありませんでした。"
  End If

 End With

End Sub

 

その結果は、このとおり正常に表示されました。
excel_01_03_ok2

 

ExcelのVBAコードを記述する場合、このようにオブジェクト.メソッド.プロパティのように連続したピリオドで結合し、ソースコードの記述を楽したいと思う心情は私も理解します。

しかし、お客様へ納品する業務APとしてVBAコードを記述する際は、このような楽をすべきではありません

今回の記事では、Findメソッドを例として記載しましたが、Findメソッド以外の他のメソッドの場合も考え方は同じです。

 

今回は、このFindメソッドの原因を究明するために、本来なら出さなくてもよいあぶら汗を約1時間も出しながら苦戦することになりました。

「作れば良い」「動けば良い」ではダメなのです。

「保守しやすいか?」「エラーが発生した場合、原因究明しやすいか?」を念頭に置いて、プログラマ諸君にはVBAコードを書いていただきたいものです。

 

(検索キーワード)
オブジェクト変数または With ブロック変数が設定されていません。

河野太郎議員に大賛成!変なExcel使いは止めましょう!

※本記事は、2016年11月4日のYahooニュース記事に関する内容になります。

【参考】2016年11月4日のYahooニュース記事
データとして役立たない「神エクセル」問題に解決の兆し 河野太郎議員が文科省へ全廃を指示

この記事でいう、河野議員の全廃指示に私は大賛成です。

今に始まったことではないですが、Excelをワープロと勘違いしているパソコンユーザが多すぎます。
※もちろん、TPOによってはExcelで文書(見積書、請求書 など)を作成することが適切な場合もあります。

Excelで作成した文書は、他の人が2次利用することを全く考えていない思いやりのない文書だと私は思います。

特に、ニュース記事に記載されているような「エクセルのワークシートを方眼紙のように整え、記入項目を1セルに1文字ずつ入力させる形式になっています」なんて使い方は驚愕でした。

また、Webサイト上で「エクセルのワークシートを方眼紙のように使えば便利です」なんて記載する方々が少なくないことも理解できません。

私は、今回の河野議員の全廃指示に胸のすく思いを感じました。(拍手!)
sukkiri

 

皆さまは、いかが思われますか?

 

追伸:

上記のYahoo記事中にこんなことも書いてありました。(以下、引用です)

このようなファイルは一部で「神エクセル」もしくは「紙(への出力しか考えていない)エクセル」と呼ばれるとともに、批判の声も多数。2013年には、神エクセルが日本の生産性を落としているのではと議論を呼んでいました。

確かに・・・生産性は落ちますよね。