エクセルでマクロを自動的に実行

質問

表題の通りなんですが、エクセルで、あるセルに指定された数値（語句）が入った場合に、それを自動的に感知して実行するマクロがあればと思うのですが、そういったことってできるのでしょうか。

具体的には、
　１、バーコードリーダーで数値をセルに入力
　２、その数値に対応する名前をvlookup関数にて表示
　３、その名前が入力された時点でマクロが自動的に実行
と言うものです。　

３の部分がよく分からずに困っています。できるかどうか分かりませんが、もしできるようであるならその方法を教えていただければありがたいです。どうか、よろしくお願いします。

回答No.15

Wendy02 · ベストアンサー

こんにちは。

>それをしないようにする上で、自動的にマクロを実行させる事もできるのでしょうか。

それは以下のようになります。イベントドリブン型のマクロをアプリケーションのインスタンスにしてシートに設けます。ただし、Calculate イベントを設定してあるシートを使う時のみ、「元に戻る」が利きません。

今は、「データ」というシートのモジュールにマクロが登録してあるはずですから、そのすべてを、削除するか、コメントブロックしてしまってください。（コメントブロックというのは、先頭に「’」をつけることですが、〓のようなマークのツールボタンが出ていれば、それを使うと便利です。)

シートを「データ」シートから、一旦、別のシートにするか、または、 Workbook_Open()　を実行すれば、「データ」シートに設定されます。別のシートをアクティベートすると、そのイベントドリブン型マクロは、使えないようになります。

これでも、問題があるようでしたら、もう一度、Changeイベントの話から積み上げたほうがよいかもしれません。

'//ThisWorkbook モジュール//
'Option Explicit

Private WithEvents App As Application
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
　If Sh Is Worksheets("データ") Then
　　Set App = Application
　End If
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
　 If Sh Is Worksheets("データ") Then
　　 Set App = Nothing
　　 'MsgBox "DeSetting" '検査用
　 End If
End Sub

Private Sub App_SheetCalculate(ByVal Sh As Object)
　On Error GoTo ErrHandler
Application.EnableEvents = False
　If Range("B1").Value <> "" Then
　'実行プログラム
　出勤r
　'Beep '検査用
　End If
　Range("B1").ClearContents
ErrHandler:
　Application.EnableEvents = True
　MsgBox Sh.Name
End Sub

Private Sub 出勤r()
　Dim m As Integer
　Sheets("データ").Select
　m = Cells(1, 2).Value + 5
　Range("D3").FormulaR1C1 = "=TIME(HOUR(RC[-3]),MINUTE(RC[-3])-5,0)"
　Cells(m, 4).Value = Range("D3").Value
End Sub

Private Sub Workbook_Open()
　Worksheets("データ").Activate
　Set App = Application
End Sub

注意：#13 の件については、修正されておりません。

お礼

返信をありがとうございます。
また、こちらからの返信が遅れてしまい申し訳ありません。

試してみたんですが、なぜかは分かりませんが、マクロが実行されませんでした。
シートの方に貼り付けて試したんですが、数値を入れてもうまくいきませんでした。

少し、私自身が簡単に考えすぎだったのかもしれません。
正直、全く分からないレベルの話になってきていて、そのままコピー・ペーストで試して分からないという事で、またコードをお願いすると言う繰り返しで、かなり私自身が甘え過ぎている気がして、大変申し訳なく思っております。
とりあえず、今回の自動でマクロを実行するという事に関しては、目的が達成されているので、逆に途中で終了するのもどうかとは思いますが、一回閉めさせてもらいたいと思います。

ひょっとしたら、また再度お聞きする事があるかもしれないですし、またお世話になることがあるかもしれません。その時はどうか、よろしくお願いします。
今回は、本当にありがとうございました。

回答No.14

Wendy02

こんにちは。Wendy02です。

>バーコードの数値（あるいはVlookup関数での名前）の違いを認識する

それは、バーコードの数値の分解がよいですね。

だから、例えば、以下は、商品バーコードですが、一部分を取り出して別けます。

二つ案を考えました。

A1:

4912345678904
（書式で整えると良いです。「00 0000 000000 0」)

=IF(MID(A1,3,4)="1234",VLOOKUP(A1,Sheet2!$A$1:$B$200,2),VLOOKUP(A1,Sheet3!$A$1:$B$200,2))

ただ、これは、ひとつの式にまとめましたが、このように取り出すことも可能です。

A1:

4912345678904

C1:

=MATCH(MID(A1,3,4),{"1111","1234","3333","4444"},0)

B1:

=If(IsError(C1),"", VLOOKUP(A1,INDIRECT("Sheet"& C1 &"!A1:B100"),2))

>２、コードの終了時に削除される事については、今の所全く問題はないんですが、セルが次に行かない様にする方法はあるのでしょうか。

以下のようにしてみたらいかがですか？

If ......
・
出勤r
　'Beep '検査用
Range("A4").Select　'←If で囲まれた中に入れてみたらいかがですか？
End If

こちらで、今試してみましたが、VlookUpの値の変化がないと、これはマクロが動かないのです。たぶん、それで、不都合はないとは思いますが、もしも、気になる時は、空いたセルに、=Now() と入れると良いです。

なお、このCalculate マクロには、最大の欠点は、「元に戻す」というコマンドが、ブック全体に利かなくなってしまいます。もし、それで不便を感じられるようでしたら、今のスタイルは変えたほうがよいかもしれません。

皆さんが、もうお話を出していただいているので、後先で申し訳ないのですが、バーコードリーダー・ソフトウェアは、OLE を使っているようですね。そうすると、その設定（プロパティ)の中で、CrコードやCrLfコード（通称、改行コード)の付加を処理することも可能ではないか、と思っています。

もちろん、私のコードは、たぶん、ほとんど、出だしだけを変えるだけで済むことですが。

補足

返信をありがとうございます。また、返信が遅くなってしまい、大変申し訳ありませんでした。
本当に助かりました。
おかげで、だいたい思ったとおりの事ができるようになりました。
ありがとうございました。

今回、補足に書かせていただいたのは他でもありません。
＞なお、このCalculate マクロには、最大の欠点は、「元に戻す」というコマンドが、ブック全体に利かなくなってしまいます。もし、それで不便を感じられるようでしたら、今のスタイルは変えたほうがよいかもしれません。

この部分が気になったからです。
今の所、特に不便は感じてはおりませんし、訂正の際にEnterを押すとCalculateのマクロが貼り付けてあるシートのセルに移動すると言う事が分かったのは非常によかったんですが、それをしないようにする上で、自動的にマクロを実行させる事もできるのでしょうか。

先々のことを考え、非常にずうずうしいとは思いますが、どうか、よろしくお願いします。

回答No.13

Wendy02

補足です。

Target.Address = "$A$４"
が判定基準なら、

こちらかは、
If Range("B1").Value <> "" Then
　　↓
If Range("A4").Value <> "" Then
に換えてください。ただし、現在のコードでは、終了すると削除されます。

補足

度重なる返信を本当にありがとうございます。

試してみましたら、マクロが実行されました。
丁寧に教えていただいた上に、その結果が、私が望んでいた形になりうれしく思っております。

更に質問しても良いでしょうか？
コードに関しては完全なんですが、新たな問題が数点でてきました。
１、バーコードの入力によって、コードが実行されるようになりましたが、そのバーコードの数値（あるいはVlookup関数での名前）の違いを認識する事はできるのでしょうか？希望としては、別のシートの名前の一覧に対応したものにしたいんですが・・・

２、コードの終了時に削除される事については、今の所全く問題はないんですが、セルが次に行かない様にする方法はあるのでしょうか。私なりに、Wendy02さんが作られたコードの下の部分に、シートのセレクトとrange("a4").selectと加え、試してみたんですが、他の作業をした場合でもEnterを押したとたんに、そのシートのa4に移ってしまうんです。何か、良い方法はあるのでしょうか。

今の所、考えられる質問としましては上記の二点です。私が全く分からないコードなので、ほとんどいじる事すらできません。希望としましては、バーコードの数値に対応したマクロをそれぞれ実行すると言う事になります。（バーコードリーダー＝ユーザーフォームのコマンドボタン）

それ以上のことができるのかは分かりませんし、これ以上迷惑になるかもしれませんが、もし時間を割いていただけるのであれば、回答をよろしくお願いします。

回答No.12

Wendy02

こんにちは。Wendy02です。

>Wendy02さんのコードでも試してみました。そうしたら、今度はマクロが実行したんですが、同じ作業を繰り返した後で「スタック領域が不足」というメッセージが出ました。

たぶん、何度も同じ繰り返しで、イベントの無限ループを起こしてしまったようですね。ですが、マクロは瀕死の状態でも、どうやら生きていたようですね。^^;
おそらく、その路線は使えるような気がします。

複線を張って申し訳ないのですが、私は、一応、Calculate イベントの路線で続けさせてもらいます。その前に解決したら、こちらは離脱します。

それで、私はあくまでも、VlookUp関数などがあるという条件です。ない場合は、Now や Today関数をシートにおいておけばよいです。「出勤プログラム」の内容的なものは詳しくは分かりませんが、なるべく近い姿にしました。

イベントの除外条件は、Range("B1").Valueに数字や文字列があるかどうかの判定ですから、消されては困る場合は、何か別の手段を講じなければなりません。

'なお、これは、*シートモジュール*のみです。

'Option Explicit
Private Sub Worksheet_Calculate()
On Error GoTo ErrHandler
Application.EnableEvents = False
　If Range("B1").Value <> "" Then
　'実行プログラム
　出勤r
　'Beep '検査用
　End If
　Range("B1").ClearContents
ErrHandler:
　Application.EnableEvents = True
End Sub

Private Sub 出勤r()
　Dim m As Integer
　Sheets("データ").Select
　m = Cells(1, 2).Value + 5
　Range("D3").FormulaR1C1 = "=TIME(HOUR(RC[-3]),MINUTE(RC[-3])-5,0)"
　Cells(m, 4).Value = Range("D3").Value
End Sub

回答No.11

KenKen_SP

ザッと見た限りですが...

> If Target.Address = "$a$４" Then

VBA は大文字・小文字、全角・半角を区別しますので、ここが原因です。

　IF Target.Address = "$A$4" Then

としてみては。

お礼

返事が遅れてしまいましたが、アドバイスをありがとうございました。
シートモジュール、標準モジュールの区別が今まで分かりませんでしたが、教えていただき大変勉強になりました。

まだまだ、初心者で我流でやっているのでまた質問する事があると思いますが、その時はどうか、よろしくお願いします。

回答No.10

KenKen_SP

> ちなみに、キーボードによる入力でもうまくいっておりません。

キーボードからの直接入力で Change イベントが発生しないこと事態がオカ
シイです。

基本的なことを確認したいのですが、今まで示されたコードが書かれている
場所はどこなのでしょうか？

ワークシートのイベント処理なので、シートモジュールに貼り付けなければ
ならないわけですが。

標準モジュールとかに貼ってないですか？

また、#5 で補足いただいたコードは End Sub が足りません。何かすれ違い
がある様に思います。どうも、こうした回答・補足をいくら繰り返していて
も、解決しない気がしてきました。

まず、ご自分で現在までに書いたコードを「省略しないで全て補足」して
みて下さい。

補足

返信ありがとうございます。

KenKen_SPさんの回答をいただいてすごく基本的な事が間違っている事に気がつきました。標準モジュールに貼っておりました。お恥ずかしながら、「シートモジュール」「標準モジュール」の区別が今まで全くできてなく、全て標準モジュールにて行っておりました。お手間を取らせてしまい、大変申し訳ありませんでした。
また、コードのEnd Subが抜けているご指摘の点ですが、書き込みを終わった後で気がつき、どうにもできないままご指摘を受けると言う事になってしまいました。こちらも申し訳ありませんでした。実際、後はEnd Subをつけるだけで、その他、不必要なセルの移動の部分を消しただけのコードであります。

回答の中で教えていただきました、シートモジュールに、
Private Sub Worksheet_Change(ByVal Target As Range)
　If Target.Address = "$a$４" Then
　出勤　　　'処理
　End If
　End Sub

そして、標準モジュールの出勤のコード
Dim m As Byte
Sheets("データ").Select '出勤時間を確定させる
m = Cells(1, 2).Value + 5
Sheets("データ").Select
Range("D3").Select
ActiveCell.FormulaR1C1 = "=TIME(HOUR(RC[-3]),MINUTE(RC[-3])-5,0)"
Range("D4").Select
Range("D3").Select
Selection.Copy
Cells(m, 4).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
にて、試みてみました。しかし、結果的に実行はしませんでした。

また、Wendy02さんのコードでも試してみました。そうしたら、今度はマクロが実行したんですが、同じ作業を繰り返した後で「スタック領域が不足」というメッセージが出ました。

ほんの少しだけ、前進したような気がします。
皆様にはお手間を取らせてしまい、大変申し訳なく思いますが、どうか、よろしくお願いいたします。

回答No.9

noname#22222

s_husky です。

全く、新しいシートを用意してテストされることをお勧めします。
多分、成功すると思います。
もし、成功したのであれば、原因はVBAの誤動作です。
その場合、プロシージャを再配置（コピー＆ペースト）した可能性があります。
一度だけ他からコピーすることは許されますが、再配置は厳禁です。
再配置の後に全く予測不能の動作が始まります。

是非、全く、新しいシートで１のテストを！
成功すれば、そこに全ての処理を書けば完成です。

回答No.8

Wendy02

こんにちは。

http://barcode.technical.jp/handbook2/2-1.html
バーコードリーダーで読み取ったバーコードの値をセルに入力する

もし、このようなスタイルで、VlookUp で値が変わるなら、Calculate イベントが働くはずです。
VlookUp は、揮発性(Volatile)関数で、代入される値が変われば、イベントを生成します。

どのような値になるかは別として、簡単に書けばこのようになります。

Private Sub Worksheet_Calculate()
　If Me.Range("A4").Value <> "" Then
　'実行プログラム
　End If
End Sub

ただ、個人的には、そういう方法は、古くなってしまうと思っています。どこかで、けじめをつけていかないといけないなって思います。もちろん、VBAについてを言っているわけではありません。

補足

返信をありがとうございます。
また、バーコードリーダーに関してのHPを教えていただき、ありがとうございます。

教えていただいたコードを使い、早速試してみたんですが、なぜか実行されませんでした。changeと同様、キーボードで値を入力してみましたが、それも実行されませんでした。
なぜそうなるのか分からなかったので、他のHPに載っていたcalculateのコードでも試してみましたが、実行されず・・・

何が原因なんでしょうか。changeやcalculateと言ったものがただ単に使えないだけなんでしょうか。

度々で申し訳ありませんが、よろしくお願いします。

回答No.7

KenKen_SP

問題の切り分けが必要だと思います。２. ３．は取り合えず横においておき、
まずは 「バーコードリーダからの入力を検知する」ことから考えた方が良さ
そうです。

バーコードリーダ側でバーコードを読み取ったあとに「改行を送るかどうか」
の設定項目がないですか？ 「改行を送らない」設定になっているのでは？

Worksheet_Change イベントはあくまで、「セルの値が更新」されたとき発生
するものです。ここで、一般的なバーコードリーダの入力原理は、

　　・「キーボード入力のシミュレート」

ですから、バーコードリーダからの入力で Worksheet_Change イベントを発生
させるためには、最後に Enter キー（改行）が送信され、「値が確定」する
ことが必要です。

これが送られていないので、Worksheet_Change イベントが発生しないのでは？

バーコードリーダによっては、この設定自体がないかもしれませんが、取説を
確認してみて下さい。

　# テキストボックスなら KeyDown とか KeyPress などのイベントでも
　# 検知可能なので、改行が送信されなくても良いのですが...

補足

度々の返信をありがとうございます。

バーコードリーダーが値を読み込む際、入力モードは直接入力になっておりますので、Enterキーを押さなくても改行はされているようです。
ですが、改行されていても、マクロが起動しないんです。ちなみに、キーボードによる入力でもうまくいっておりません。

バーコードリーダーは会社のもので、古い型のようですので、取説はないんです・・・

回答No.6

noname#22222

s_husky です。

まず、99%プログラミングは完成しています。
3が自動実行されない原因は、既答の通りだと思います。

＜解決１＞

強制的にイベントを発生させる。
例えば、２でキーを送出する。（SendKeys etc.)
しかし、これは、邪道です。

＜解決２＞

バーコード処理そのものを見直す。

・バーコードはバーコードのみをセルに代入しており{ENTER}を送っていないのではないのか？

＜わざわざと書いた理由＞

私は、バーコード入力フォームを用意しています。
その小さいフォームに

・現行のセル名。
・バーコード表示テキストボックス。

を表示。
バーコードリーダは最後に{Enter}を送るように設定。
更に、このフォームから直接に２、３を実行する関数を起動。
１、２、３の処理を複数の関数に分けて書くことと一気に実行することは矛盾しないと思います。
「わざわざ」と書いた理由です。

これで、キー入力との並行処理が完成します。
なお、バーコードはキーボード部分に接続しています。
線は分岐してバーコードとキーボードを双方の信号を受け散るという訳です。

補足

返信をありがとうございます。

解決２については、入力モードが直接入力になっておりますので、Enterが押されていなくても値が入力されております。また、値をキーボードから直接入力しても、マクロが起動しないんです。

「バーコード入力フォーム」とは、どのようなものでしょうか。それを利用する事により、１→３（バーコードで数値を読み込むことで、マクロが自動的に実行）することができるのでしょうか。もし、差支えがなければ是非教えてください。どうか、よろしくお願いします。

回答No.5

KenKen_SP

こんにちは。KenKen_SP です。

> 一度試してみて、うまくいったと思ったんですが、その後はどうやっても
> うまくいかなかったです。

zap35 さんの言われている「対象セルがＣ３...」は、バーコードリーダーか
ら数値が入力されるセルのことだと思いますが、単純な勘違いがありそうなの
で確認して下さい。「対象セルがＣ３...」のセルと

> その数値に対応する名前をvlookup関数にて表示

のセルとごちゃ混ぜになってませんか？

関数による表示の変化では Change イベントは発生しません。つまり VLOOKUP
関数の結果でセルの表示が変更されても Change イベントは発生しないという
ことです。Change イベントでこのセルを指定してないですかね...

それで、1回目はうまくいくが、2回目は動作しない...となっているのでは
ないですか？

補足

返信をありがとうございます。zap35 さんのコードを少々アレンジして、
　Private Sub Worksheet_Change(ByVal Target As Range)
　If Target.Address = "$a$４" Then
　出勤　　　'処理
　End If

Sub 出勤()
Dim m As Byte
Sheets("データ").Select
m = Cells(1, 2).Value + 5
Sheets("データ").Select
Range("D3").Select
ActiveCell.FormulaR1C1 = "=TIME(HOUR(RC[-3]),MINUTE(RC[-3])-5,0)"
Range("D3").Select
Selection.Copy
Cells(m, 4).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub
と言う事で、a4にはバーコードで数値を読み込ませると、「出勤」のマクロが実行されるんじゃないかと思ったんですが、うまくいきませんでした。「出勤」のマクロ自体は単独で実行を押してもうまくいきますが、バーコードでは何度試してみてもうまくいきません。もし、原因が分かるようでしたら、よろしくお願いします。