InputBoxメソッドを使ってワークシート名を特定する

年末のイベント年賀状のデザインを考えるのがたるいあてくしです。

こんちくわ壁|ω・)ﾉ

さて今日の記事は、他所から送られて来るワークブックに含まれる数も名前もわからないワークシートの中から、目的の情報が詰まっているワークシート名を特定しよう～なオハナシです。

でわいきます￣▽￣)ﾉ

ある外部事業者さんとの仕事で、送られてきた情報をもとに解析した結果を同じファイルに書き込んで先方に返すということを定期的にやっています。ルーチンワークですので、ここぞとばかりマクロ化して作業効率化しています。

f:id:FukuCyndiP:20191222001934p:plain:w350

ところが！　ところがですよ！！

今月の初め頃から、先方から送られてくるエクセルファイルに変化が・・・

なんと、オートフィルターやら、条件付き書式などを付けてくるようになったのです。さらに、ワークシートの数や名前、表の列位置やTitle表記も変わってて..orz..。

つまり、情報の書き方がガラリと変わってしまったのですね -ω-)

これまで３年ほどはただの味気ない一覧で扱いやすかったのに(・д・)ﾁｯ

いあ、見た目きれいなんだけど、前より見やすくなったんだけど、こちとら自動で情報を抜き差しするだけなので、むしろ味気ない一覧の方がよかったわけで。。

てなことで、手元のマクロの修正を迫られている今日この頃なのです。

それで、いろいろある要対応事項の中で少々手強いと思ったことについて、コレいけるんじゃね 的ひらめきがあったので、恥ずかしげもなく紹介したいと思います。

手強い要対応事項とは

現在手元の解析マクロの処理の流れとしては

送られてきたブックを選択して開く
ワークシートから情報を読み込む
解析
新しい情報を開いたブックに書き込む

となっておりますです。

で、上の 2. の処理なのですが、今後も起こりえる変化を考えると コード上でワークシートが特定できなくなるコトに気が付きました。またその対処に普段から使っている王道的な方法が使えないことにも同時に気が付いたのですね。

ワークシート名を使う：名前が何度も変更されると ✖
インデックス番号を使う：数が~~ムダに~~増減すると ✖
オブジェクト名を使う：ブックを新しく作り直されると △～✖

(すでにワークシート名が "格納情報の名称" → "Sheet1" (ブックを作り直したようだw)になっていて、処理が絶賛止まっておりますw)

というわけで

手強い対応事項：
数や名前に依存せずに目的のワークシートを特定する

思いついたアイデアとは

先日開催されたノンプロ研もくもく会でコードの修正作業をしていたのですが、良さげなアルゴリズムとして以下のようなことを思いつきました。

ワークシートをカウント
＜条件分岐＞
1. シート数が1つ → インデックス番号で特定
2. シートが複数 → マクロを使うユーザーに特定させる

(数には若干依存したｗ)

ブックに含まれるワークシートが複数あった場合に(名前は突然変更される可能性あり)どうやって必要なワークシートを特定するかが特に問題だったのですが、マクロを使うユーザーに特定してもらえばいいのではないか。さらに、それを実現する手段として InputBoxメソッドが使えそうなことに気が付いたわけですね。

InputBoxメソッドってナニ？

ユーザーに入力してもらった文字列を変数に代入するInputBox関数は使ったことがある方も多いかと思います。
ココで紹介するInputBoxメソッドでは戻り値として数式やRangeオブジェクトなどを取得できる点で大きく異なります。

InputBoxメソッドの記述

Application.InputBox(Prompt as string, [Title], [default], [Left]. [Top], [HelpFile], [HelpContextID], [Type])

[引数]は省略可能

なお、Applicationを省略すると自動的にInputBox関数と認識されるので、ご注意ください。

たくさんの引数がありますが、このメソッドを使う上で重要なのは [Type] で、数値で戻り値の種類を指定します。

Type引数値とその対応関係

値	説明
１	数式
２	文字列（引数省略時の規定値)
４	ブール値(True/False)
８	セル参照(Rangeオブジェクト)
１６	エラー値(#N/Aなど)
６４	値の配列

そう、ワークシートをユーザーに特定させるとわ、

必要な情報が格納されたワークシートの任意のセルをクリックするようユーザーを促す
ダイアログの"OK"ボタンを押した瞬間にクリックしたRangeオブジェクトを取得
それに紐づいたワークシート名を引っぱり出す

という魂胆なのです(`ω´)ｸﾞﾌﾌ

コード例

Option Explicit
Sub クリックしたセルのワークシート名を特定()
  Dim target As Range
  Set target = Application.InputBox(" ""こんちくわ"" を選択してください", Type:=8)
  
  Dim targetWsName As String
  targetWsName = target.Worksheet.Name
  
  MsgBox "アナタが選んだ ""こんちくわ"" は、" & targetWsName & "シートにあります"

End Sub

変数targetにユーザーが選択したRangeオブジェクト情報が格納されます。
TypeはInputBoxメソッドの最終引数ですが、ここでは第1引数(Prompt)から間の引数を省略しているので、名前付き引数とする点にご注意ください。
名前付きにしないと、"8"が第２引数とみなされて、コンパイルエラーとなります。

そしてtarget.Worksheet.Name で、targetに格納されているワークシート名を引っぱり出しています。

まとめて下のように記述するのもアリですね。

Option Explicit
Sub クリックしたセルのワークシート名を特定()

  Dim targetWsName As String
  targetWsName = Application.InputBox(" ""こんちくわ"" を選択してください", Type:=8).Worksheet.Name
  
  MsgBox "アナタが選んだ ""こんちくわ"" は、" & targetWsName & "シートにあります"

End Sub

尚、InputBoxメソッドの戻り値を格納する変数宣言は理解しやすさを重視して Range または String としましたが、実用を考えるなならば、Variant型として宣言する方が良いかと思います（ユーザーがダイアログのOKではなくキャンセルボタンをクリックした場合も想定）。

　
動作検証
f:id:FukuCyndiP:20191222000714g:plain:w350

というわけでうまくいきましたねｗ

まとめ

今回の記事ではInputBoxメソッドと、その特徴を利用した事例としてワークシートの内容が固定されないブックを対象にしたワークシート特定方法について紹介しました。

このような事例が皆様のお役に立てるかどうかはわかりませんが、InputBoxメソッドの使い方を学ぶ上で少しでも参考になればと思います。

InputBoxメソッドは数値や文字列、オブジェクトだけでなく、ワークシート上の数値(文字列)群を配列として取得することができることも併せて覚えておくとアルゴリズムを考える上での引き出しが増えて良いと思いますね（ってか配列取り込みは使ってみたい）。

でわまた～￣▽￣)ﾉｼ

それわVBA案件ですね

エクセルVBAネタを書いています

InputBoxメソッドを使ってワークシート名を特定する

目次

背景

手強い要対応事項とは

思いついたアイデアとは

InputBoxメソッドってナニ？

コード例

まとめ