3日がかりのその仕事、3分で終わらせる方法教えます!
パソコンスキルの心技体

【サンプルコードつき】マクロ利用者に条件入力させるには、フォームが「必要」なのか?

2019年6月14日
  • このエントリーをはてなブックマークに追加
  • follow us in feedly

先日、横浜アリーナでのイエローモンキーのライブに行ってきました。
横浜アリーナは自宅から徒歩圏内です。20分かかるか?かからないか?というくらい。

なんと、当日券でした。

いちおうアルバムツアーなのですが、懐かしい曲もいっぱい。

新横浜には、横浜アリーナだけでなく、横浜Fマリノス本拠地の日産スタジアムもあります。
こちらも、徒歩圏内。

「いいところに住んでいるなぁ」と、しみじみ ヾ(´ー`)ノ


今日は、とある受講生さんとのやりとりから。

『マクロ利用者に条件入力させるには、フォームが「必要」なのか?』というお話。

昨日、以下のようなご報告&相談メールをいただきました。

ガラパゴスタディー小川様

お世話になっております。
先ほど発展編メールセミナーの宿題を提出した○○です。

半年前までマクロという言葉すら知りませんでしたが、小川さんの講座を受講し、短期間で伝票作成マクロまで作れるようになりました。
本による独学では絶対ここまで到達できませんし、挫折していたと思います。
小川さんのおかげです。ありがとうございます。

マクロを勉強することが楽しみで、上級編も受講しようと考えております。
そこで、以下2点質問があります。

[質問1 イベントとフォーム講座を受講すると以下のことができるようになりますでしょうか]
実務でマクロを作成し、前任がアナログでやっていた仕事を10分の1でできるようになった仕事があります。
以下の内容です。

Aファイルの管理番号と、Bファイルの管理番号が一致したらCファイルに書き出す。
→前任者はプリントアウトして、検索から1つずつ番号を手入力して確認していました。

このAファイルとBファイルは毎月替わる為、今は作ったマクロのモジュール内のファイル名をコピペで差し替えています。
ですが、後任者にプログラムをいじらせるのは危険な為、フォームを作成し、ファイル名をペーストしてもらうことを考えています。

このように、フォームに入力することによりマクロのプログラムを書き換えるようなことはできるのでしょうか。

[質問2 終了証は発行してもらえるのでしょうか]
講座の受講が完了したことが分かる証書があれば、会社から多少の補助金が支給されます。
PDF等でいただくことは可能でしょうか。

以上2点、ご確認の程よろしくお願い致します。

[質問2]にある修了証の件は、もちろん発行可能です。
会社からの補助金、受け取ってください ヾ(´ー`)ノ

[質問1]の件が、今日のメインテーマ。

なお、メール本文中で受講生さんが言われている「伝票作成マクロ」というのは、「発展編1」という講座で宿題にしているもの。
以下の動画のようなマクロです。

↑このくらいできるようになると、たいていのことはマクロでなんとかなるようになります。

受講生さんから提出いただいた宿題を拝見しましたが、とてもよく書けていました。

さてさて、ということで。
当面の問題解決とさらなるスキルアップのために、「イベントとフォーム講座」の受講をご検討のようだったです。

↓「フォーム」を作れるようになると、こんなことができるようになります。

とはいえ…「いや、フォームなくてもなんとかなるだろう…」と思いましたので。

以下のようなお返事をさしあげました。
本当は一気に書いたメールなのですが、可読性のため、注釈を入れつつ、3つのセクションに分けて記載します。

【メールでのやりとりご紹介、ここから】

まずは、状況理解に間違いないことを確認すべく、ヒアリング。

小川です。

お仕事での結果につながっているようで、なによりです。

ご相談内容について、読み直してみて分からないところがあります。
以下のとおりの前提という理解でよいでしょうか。

[a] 一度の作業で作業対象とするファイルA、ファイルBはひとつしかない
[b] ただし、ファイルA、ファイルBは、毎月別の名前のもの
ファイルAには、所定のシート(シートA、とします)の所定のセル(セルA、とします)に、管理番号として使う値が記されている
[d] ファイルBには、所定のシート(シートB、とします)の所定のセル(セルB、とします)に、管理番号として使う値が記されている
[e] セルAとセルBの値を比較して、所定の条件を満たしていると判定されたならば、処理を行う(これは、○○さんがすでに実現できていること)

たぶん上記のとおりだろうと思ったのですが、, [d]については、ファイルA、ファイルBのファイル名が管理番号としての機能を担っているのかな?という可能性もあるかな、と思いまして。

たぶん上記[a]-[e]のとおりだろうと思ったのですが(実際、あとでいただいたメールによると、ほぼそうでした)。
解釈に齟齬があるとお互い手間になるので、こういうところは避けられません。

以下では、上記[a]-[e]という前提で話を進めます。

いずれにしても、この件だけで、フォームを使う必要はないだろうと思います。
マクロの入ったファイルに、シートを1枚追加します。
シート名は、「Control」というのが僕のよく使うやり方です。

以下の2つのセルに、以下のとおりに文字列を書き込みます
セルA1: ファイルAの名称を記入してください→
セルA2: ファイルBの名称を記入してください→

このようにして、ファイルA、ファイルBの名称をセルB1、セルB2に入力してもらうようにします。

別途、「伝票作成マクロ」と同じ要領で、ボタンを用意します。
このボタンを押すと、前述[e]のマクロが実行されます。

ただし、ソースにベタに書かれたファイル名の部分は、変数化しておき、シート「Control」のセルB1、セルB2から読み取るようにします。
セルB1、セルB2の値については、担当者の入力ミス、「管理番号が一致しない」といった可能性も考えられます。
必要に応じて、そういう可能性にも配慮したプログラムに書き換えます。


上記のように、マクロ利用者(プログラマーでなく)が自由に変数を指定できるようにするには、プログラムのソースをいじらせるのではなく、設定したい変数の値をセルに入力してもらうようにします。
ご相談者さんは、マクロのスキルがしっかりおありで、かつ、ご相談の文章もしっかりされていた方です。
なので、「文章得意そうだな」という前提で、僕も、文章だけで説明しきってしまいましたが…。

「エクセルファイルのシート上で、マクロ利用者に変数を指定させる」というのは、僕がよく使う方法です。

以下の無料動画でも使っているテクニックです。
(00:17くらいから、変数入力用シートの見本を見られます)


ということで、エクセル側の処理については解説しました。
続けて、マクロのほうはどう編集するのか?という件。

以下で、サンプルコードを紹介します。

ということで…以下のとおりの修正内容になるのかな、と思います。
(ファイルA、ファイルBとも、Cドライブのtempフォルダにある、という前提。そこは適当に書き直してください)

Sub macro1() 	'元のマクロ
 	Dim bkA As Workbook
 	Dim bkB As Workbook
Set bkA = Workbooks.Open(Filename:="C:\temp\ファイルA.xlsx")
Set bkB = Workbooks.Open(Filename:="C:\temp\ファイルB.xlsx")

'以下、前記[e]に相当する、本格的な処理...
 ' ...
 ' ...
 ' ...
End Sub

Sub macro2() 	'変更後のマクロ
 	Dim filenameA As String
 	Dim filenameB As String
	filenameA = ThisWorkbook.Worksheets("Control").Range("B1").Value
	filenameB = ThisWorkbook.Worksheets("Control").Range("B2").Value
	
	Dim bkA As Workbook
 	Dim bkB As Workbook

'以下、発展編1では扱っていませんが、エラー処理の基本パターンで書いてみました
	On Error Resume Next
 	Set bkA = Workbooks.Open(Filename:="C:\temp\" & filenameA)	
 	If Err.Number = 1004 Then 'Err.Numberは、問題なければ0。エラーが出ると、エラー内容により、対応した正の値。
 		Debug.Print Err.Description
 		MsgBox filenameA & "は見つかりませんでした。"
 		Exit Sub
 	End If
 	On Error GoTo 0
	On Error Resume Next

 	Set bkB = Workbooks.Open(Filename:="C:\temp\" & filenameB)
 	If Err.Number = 1004 Then
 		Debug.Print Err.Description
 		MsgBox filenameB & "は見つかりませんでした。"
 		Exit Sub
 	End If
        On Error GoTo 0

'以下、前記[e]に相当する、本格的な処理...
 ' ...
 ' ...
 ' ...

 End Sub

【メールでのやりとりご紹介、ここまで】


上記のソースについては、特にコメントありません。

その後、受講生さんからは、以下のとおりのコメントをいただきました。

おっしゃる通り、「管理番号」は「ファイル名」ではなく、「セル」に入力してある番号のことです。
AファイルとBファイルのセルの行は300行ほどあり、片方ファイルのデータを紙で出力し、もう片方のファイルを開き、検索機能を利用し300件を一つずつ手入力で確認するという、非常に手間のかかることを前任者は行っておりました。

セル自体にフォームの入力欄と同じ機能を持たせるのですね!
気付きませんでした。また、このような場合、入力者による入力ミス等を考慮するのですね。
ぜひ、メルマガで紹介してください。同じ悩みを持つ受講生の方はいらっしゃると思います。

300件の手作業がボタンひとつになったなら、削減効果、当然高いですよね ヾ(´ー`)ノ

ということで、ここまでのまとめです。

[1] マクロ利用者に条件入力させるだけなら、フォームまでは必要ではない。
エクセルシートから値を読み込むマクロを作ればたいてい解決する。
[2] ただし、マクロ利用者に値を設定させるならば、入力ミス等への配慮もしたほうがよい。

「入力ミス等への配慮」は、今回は、On Error Resume Next を使ったエラー処理だけで行いました。
ですが、参考までに追記すると、前回メルマガで紹介したような、文字列データの整形技も併用すると良いかもしれません。

たとえば「fileA.xlsx」なら存在するのに、「FILEA、xlS」みたいなものすごい打ち間違いをしてくる方もいらっしゃいます。
そういうものには、Replace関数やStrconv関数等である程度までは対応します。

それでも対応できないくらいの状況であれば、フォームを使うかもしれません。
あらかじめ候補リストを作成し、リストボックス等から利用者に選ばせる感じですね。

どこまでやるかは、利用者に期待するスキル水準や、実装にかかるコストと得られる効果との比較次第、ですね。

キーワード

コメント

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

最新の記事

人気記事

最新記事

カテゴリ

最新コメント

タグクラウド