Excel VBA

【柔軟】Findメソッド+EntireRowで変化に強いコードを書く方法

エクセルVBAで指定したキーワードの検索をする【Findメソッド】
プログラムを作る時に「何を処理するか」「どこを処理するか」と悩む時に【Findメソッド】が便利です。

今回は、Findメソッドを使った小技を紹介したいと思います。

この記事はこちらの方に役立つ内容です。

  • テンプレート変更が多く、VBAのコード修正が多い
  • 一度作ったら、更新がほとんど必要ないプログラムを作りたい
  • エラーを減らしたい

Cellsプロパティだけだと、融通が利かない

Findメソッドと EntireRow の組み合わせコードはとても便利です。

例えば、こちらのようなエクセル画面
1行目に様々な項目があり、データベースになっているものです。

このようなデータの中から「金額項目」を指定したい場合、Cellsプロパティを使うとこのようなコードになります。
(単に「金額」と書かれた項目セルを選択する場合です)

Sub cellsでセル指定()
  Sheet1.Cells(1, 3).Select
End Sub

「金額」項目はC列なのでCellsでいうと【3列目】ということになります。

この状態でデータベースにデータを入力するプログラムを作る場合、行番号を(iなど)の変数にすることで処理できるようになります。
(この場合、変数iの宣言、For文などが必要です)

通常だとこの方法で良いのですが、テンプレート作成者によっては「頻繁に項目の配置が変わってしまう」こともあるかもしれません。
例えば、先ほどのコードでプログラムを作った後、テンプレートの項目の配置が変わり「金額」項目が【C列】から【D列】に変わった場合。

プログラムは「金額」項目に入力したつもりですが、テンプレートの項目の配置が換わったために、別の項目にデータ入力がされてしまいます。

テンプレートの項目の配置が変わるごとに、Cellsの列番号を変更しているのは少し大変です。
(プログラムを利用する方からしても「プログラムが変になった」と困惑してしまいます)

一か所だけなら、Cells(1,3)を(1,4)に変更すれば対応可能ですが・・変更箇所がたくさんあると面倒です。

Sub cellsでセル指定()
  Sheet1.Cells(1, 4).Select
End Sub

プログラムを作る方とテンプレートの変更をする方が同じでしたら問題ないのですが、異なる場合は特に注意が必要です。

Find+EntireRowは効果的

先ほどのコードのように「毎回修正が必要なコード」だと修正が面倒。
片手間でプログラムを作っていたとしたら「仕事を楽にするプログラム」が「プログラムの修正に時間を取られる」という結果になってしまいます。

融通の利かない(テンプレートの項目の変更に弱い)コードを改善するには【 Find+EntireRow 】が効果的です。

コードでいうとこのような感じです。

Sub findの使い方()
  Dim 金額列 As Long
  金額列 = Sheet1.Range("A1").EntireRow.Find("金額").Column
  Sheet1.Cells(1, 金額列).Select
End Sub

Sheet1.Cells(1, 金額列).Select
のコードの列番号を【3】から【金額列】という変数にしてみました。

【金額列】という変数の中身は一つ上コードで指定しています。
それがこちらのコード

金額列 = Sheet1.Range("A1").EntireRow.Find("金額").Column
(金額列という変数はLong型にしています)

このコードでやっていることはこちらです。

  • A1セルのある行を検索(つまり1行目のこと)
  • 検索キーワードは【金額】という言葉
  • 金額という言葉が見つかったセルの【列番号】を取得
  • 取得した列番号は【金額列】という変数に入れる

Cellsでデータ入力をする前に、先ほどのコードを書いておくことで、このようなメリットがあります。

  • 急な項目列の位置の変更の際も、コード変更の必要がない

せっかく業務改善プログラムを作って運用しても、テンプレートの変更でプログラムが動かなくなっては問題です。
その度にプログラム利用者から「プログラムが変になった」と連絡を受けるのは大変です。

ひと手間ですがこのコードを書いておくことで、融通のきくプログラムが作れます。

あとがき

今回は、Cellsプロパティのセル操作を強固にする、Findメソッドとの組み合わせ方法を紹介しました。

ちょっとした工夫ですが、完成したプログラムが【テンプレートの変化】に強くなります。

普段の仕事で使っているデータベースのテンプレートが時々変わるという方にとって役立つワザだと思います。

気になった方は、使ってみてください。

-Excel VBA
-, , , ,