今だけ人気マンガ100円レンタル特集♪

商品の品番の確認作業にエクセルを使っています。
作業の効率化にエクセルVBAを使いたいのですが、うまくいきません。

具体的な使用目的は以下の通りです。
 納品された商品には製造メーカーのバーコードがついています。それをエクセルに読み込んで予定通りの商品であるか確認作業を行います。
 まず、エクセルシートのA列にメーカーコードを入力しておきます、B列のセルをアクティブにした状態でメーカーのバーコードを読み取ります。するとセルにメーカーコードが自動入力されます。この文字列がA列の文字列と一致した場合にはC列のセルにOKの文字が出るように設定しています。コードが一致しない場合はNGが出ます。
 現在はOKかNGかを目で確認しているのですが、商品数が多いので、NGの場合にBeep音が出るようになっていれば作業が早くなります。

 過去の質問例を調べて、VBAのAMI関数を使うとBeep音を鳴らせることはわかりました。たとえば、セルC1にNGと表示された場合にBeep音を鳴らす、というところまではできたのですが、列CのいずれかのセルにNGと表示された場合にBeep音を鳴らす、という設定ができません。

 わかる方アドバイスお願いします。

 ちなみに、セルC1にNGと表示された場合にBeep音を鳴らすには以下のように設定しました。

標準モジュール
Public Declare Function Beep Lib "kernel32" (ByVal dwFreq As Long, ByVal dwDuration As Long) As Long
シートのモジュール
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("C1") = "NG" Then
Call Beep(2000, 500)
End If
End Sub

このQ&Aに関連する最新のQ&A

A 回答 (6件)

こんにちは。



>VBAのAMI関数を使うとBeep音を鳴らせる 
Win32API関数です。

別に、Win32API関数を使わなくても、単に、組み込み関数のBeep だけでも良いと思います。

本来は、NGと出てくるということは、データがインポートされるのですから、それを監視したほうが早いのです。Excelには、専用コマンドもあります。しかし、それは、古いメソッドなので、監視できるかどうかは分かりません。私の知っている範囲では、半分ぐらいは成功していません。

> Worksheet_Change(ByVal Target As Range)

今のところ、バーコードの出力で、Change イベントが、発生するか分かりません。Microsoft の古いサポートでは、この方法が書いてありましたが、DDEやOLEでは、反応しなかったような気がします。Change イベントで反応するのでしたら、以下のように、の入力列の Target.Value を判定に使えばよいです。

私のコンセプトは、NGは一つとは限らない、という前提で作られています。NGという表示を置いたまま、次に移って検査しても、可能なようにできています。

もし、ひとつだけを探すなら、Match関数や、CountIf 関数自体を、マクロの中に使用しても良いと思います。


注意:以下の二つのマクロは、二者択一ですから、必ず、片方だけにしてください。
'-------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 3 Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
  If StrConv(Target.Value, vbUpperCase) Like "NG*" Then
    Call Beep(2000, 500)
  End If
End Sub

'=========================================

上記のイベント・ドリブン型のマクロでは、反応がない場合には、このようにします。

'-------------------------------------------

'標準モジュール
'Win32API関数の下に、

Public gCnt As Long 'と入れて、

'-------------------------------------------

#1さんの回答のように、該当するシートの E1 のセルに、=COUNTIF(C:C,"NG") と入れます。
そしてシートモジュールに以下のように貼りつけます。
'-------------------------------------------

Private Sub Worksheet_Calculate()
  'セル"E1" に =COUNTIF(C:C,"NG") という数式が入っていること
  Dim iCnt As Long
  iCnt = Range("E1").Value
  If iCnt > gCnt Then
    Call Beep(2000, 500)
  End If
  gCnt = iCnt
End Sub

'----------------------------------------

なお、二番目の場合は、ブックを開いて、機器を使用した時に、最初の一回目に抜けが発生するかもしれません。
    • good
    • 0
この回答へのお礼

>バーコードの出力で、Change イベントが、発生するか分かりません
手元に今バーコードリーダーがないので、試験できませんが、今度確認してみます

イベント・ドリブン型のマクロをコピペして試してみたのですが、うまく動作しません。原因不明です。

二つ目のマクロはうまくいきました。
Wendy02さんが言うように、#1さんと#2さんの方法だと、NGを保留して次に進んだ場合、OKの場合でもBeep音がなってしまいますが、このマクロだと鳴らない点がすごく優れていると思います。
ここまでくると私には複雑すぎてネットで調べても文章の意味がが理解できないのですが、#2さんのやり方のようにカウントセルをシート上におかないように修正することはできるのでしょうか。

お礼日時:2010/01/23 18:25

#5で、うっかり書き忘れましたが、


Microsoft 側が提示している (Excel 97時代)
>Sub Auto_Open()
> Worksheets("Sheet1").OnData = "SoundMessage"

の方法は、実際のデータが入ってこないと調べることが出来ません。DDEとOLEのデータが入る時に、イベントが発生すると言っていますが、私がAccess からDDEでエクスポートするマクロでは、イベントが発生していませんでした。
    • good
    • 0

こんばんは。



>手元に今バーコードリーダーがないので、試験できませんが、今度確認してみます
できたら、お願いします。英語版掲示板でも、間違った内容があったりして正確なものがないのです。それに、機種によって違うような気がします。昔は、種類がなくて、DDEのみだったので、問題はなかったのです。

以下のコードは、バーコードリーダーで、"NG"が出て反応するかどうかの問題があります。

Change イベントというのは、リターンコードなどで反応するのではないかと思うからです。そうすると、それを出力しないものは、Change イベントでは反応しません。

'-------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

>イベント・ドリブン型のマクロをコピペして試してみたのですが、うまく動作しません。原因不明です。

イベントが二重になって登録されているとか、特に、問題は発生しないように思います。

これらは、除外条件です。
If Target.Column <> 3 Then Exit Sub 'C列以外
If Target.Count > 1 Then Exit Sub '選択したセルが複数の場合
If Target.Value = "" Then Exit Sub 'セルが空の場合

チェックポイントとしては、

Private Sub Worksheet_Change(ByVal Target As Range)
この行の所にカーソルを置いて、F9 を押しますと、ブレークポイントと言って、行に色が付き、色が反転します。そこで、マクロが通ればマクロは止まり、その後に、F8を押して、ステップインで、その先に進んで行きます。そうして、マクロの反応や If分岐の状態が確認できます。

もし、まったく動かない場合は、Application.EnableEvents = False とかで抜け出たりしているは働きませんので、
Application.EnableEvents = True で、イベント可動状態にすると元に戻り動くようになります。

'-------------------------------------------

こちらのものは、バーコードリーダーで、仮に、リーターンコードが発生していなくても、反応します。DDE インポートでは、確認しています。

Private Sub Worksheet_Calculate()

>#2さんのやり方のようにカウントセルをシート上におかないように修正することはできるのでしょうか。

できるのですが、100分の1秒とかの範囲で、ワークシートの変化を監視する特殊なマクロが必要になります。人為的に無限ループを発生されるものです。今まで、このイベント・スタイルでは作ったことがありません。言い換えると、新しいイベントを作ることになるのですが、単なるワザだけで、Excelのアプリケーションとして負担が大きすぎるような気がしているのです。
'-------------------------------------------

これは、Microsoft 側が提示している方法です。しかし、これが上手く言ったという話は、今までで1件しかありません。

'標準モジュール
Sub Auto_Open()
 'シートを設定する(起動時には自動的に設定)
 Worksheets("Sheet1").OnData = "SoundMessage"
End Sub
'
Sub SoundMessage()
  If ActiveCell.Column = 3 Then 'C列に限定
    If StrConv(ActiveCell.Value, vbUpperCase) Like "NG*" Then
      Call Beep(2000, 500)
    End If
  End If
End Sub
    • good
    • 0
この回答へのお礼

返信が遅くなって申し訳ありませんでした。
自宅で手入力による作動を確認し、職場に持ち込んで試そうとしたのですが、引数の数が不正でとまってしまいました。オフィスのバージョンが違うからでしょうか。理由はわかりません。
今回、色々な方にご回答いただいたのですが、私の力不足で仕事に生かすことはできませんでした。
VBAはなかなか難しいですね。
いつか基本から学んでみたいと思います。

皆様ありがとうございました。

お礼日時:2010/03/03 10:31

VBAを今後も使うのであればと思い、下記の方法も紹介します。



Private Sub Worksheet_Change(ByVal Target As Range)
For i = 1 To Range("C1").End(xlDown).Row
If Cells(i, 3).Value = "NG" Then
Call Beep(2000, 500)
Exit For
End If
Next i
End Sub

ようは、C列の1行目から最終行までを一つずつチェックして、
「NG」の行が最初に見つかった時点で音を鳴らして、チェック終了…ということです。

ただこれは、「NG」が無いときはムダに最終行までチェックします。
つまりExcel2003までなら65536行、
Excel2007なら104万8576行を1行ずつ確認する…という時間がかかってしまいます。
「NG」があればループしている途中でチェックを切り上げてくれるので時間がかからないのですが。

なので、他のご回答のように列単位でNGがあるのかどうかを確認するのが今回は良いと思います。
    • good
    • 0
この回答へのお礼

ありがとうございます。
今後の参考にさせていただきます。

お礼日時:2010/01/23 18:10

If Range("C1") = "NG" Then


を次のようにしてはどうでしょう。
If Target.Column = 3 And WorksheetFunction.CountIf(Range("C:C"), "NG") > 0 Then
    • good
    • 0
この回答へのお礼

ありがとうございます。
ワークシートにカウント用のセルをつくる必要がないので他の人が誤ってシートを変更してしまう危険がない点が良いです。
初めに、手入力でテストしたところ音が鳴りませんでした。
バーコードが読み込まれるのはB列で(自宅でテストしているときはB列に手入力します)C列には、=IF(A1=B1,"OK","NG")という関数が入っているので、
Target. Column=3をTarget. Column=2に変更したところうまくいくようになりました。

お礼日時:2010/01/23 18:08

"どこかのセル"でC列のNGの有無をカウントし


=COUNTIF(C1:C200,"NG")

ご提示の部分を少し変えてみたらいかがでしょう。
If Range("どこかのセル").value => 1 Then
Call Beep(2000, 500)
    • good
    • 0
この回答へのお礼

大変わかりやすい回答をありがとうございます。
自宅にバーコードリーダーがないので最終確認はできませんが、手入力だと目的の動作が起きることを確認できました。
#4さんの回答を参考にC列のNGの有無は=COUNTIF(C:C,"NG")でカウントすることにしました。

お礼日時:2010/01/23 17:55

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QVBA で警告音

VBAで警告音を鳴らす際にBeepを使っています。
このたび、3種類の警告音が必要なったのですが、VBAのBEEPの場合
「一般の警告」がなるだけなので、数種類の警告音が出来ません。
何か良い方法はありませんか?

Aベストアンサー

警告音(「メッセージ (警告)」や「メッセージ (情報)」)を直接出すのでしたら、既出の質問で回答されて
いる、APIを使用します:
http://oshiete1.goo.ne.jp/qa747208.html


なお、APIに慣れていなくて敷居が高く感じられるようでしたら、SendKeys関数とMsgBoxで擬似的に
やってしまうというのもひとつの手だと思います。
試しに、現在の「Beep」の記述を、以下のコード(コメントを除けば実質2行)に置き換えてみて下さい:

 '「Enter」キーの信号(?)を送信します
 SendKeys "{Enter}", False
 '「システム エラー」の音を出します
 '(MsgBox自体は、上でのEnterで自動的に閉じられます:たまに残ることがありますが(汗))
 MsgBox "", vbCritical


・・・以上です。
なお、MsgBox関数の「vbCritical」を、「vbInformation」にすれば「メッセージ (情報)」の音に、
「vbExclamation」にすれば「メッセージ (警告)」の音に、それぞれ変えることができます。
(実際に鳴る音は、Windows側の設定に依存するので注意:
 コントロールパネルの「サウンドとオーディオデバイス」等で「システム エラー」時の音を変えると、
 Access側で鳴る音も変わります)

本来ならMsgBoxに表示するアイコンの切替用の引数ですし、SendKeysは時に予想外の
動作をすることがあるということで、あまりお勧めはできませんが、こんな方法もあるという
ことで、ご参考まで。

警告音(「メッセージ (警告)」や「メッセージ (情報)」)を直接出すのでしたら、既出の質問で回答されて
いる、APIを使用します:
http://oshiete1.goo.ne.jp/qa747208.html


なお、APIに慣れていなくて敷居が高く感じられるようでしたら、SendKeys関数とMsgBoxで擬似的に
やってしまうというのもひとつの手だと思います。
試しに、現在の「Beep」の記述を、以下のコード(コメントを除けば実質2行)に置き換えてみて下さい:

 '「Enter」キーの信号(?)を送信します
 SendKeys "{Enter}", False
 '「...続きを読む

QエクセルVBAでBeep音は?

エクセル2000、Win2000です。
VBAで音を出そうと、下記のマクロを記述しました。
ところが、「MsgBox i & "回実行しました。」の部分を、コメントブロックしたら一回しか鳴りません。ビー、ビー、ビーと三回鳴ると思ったのですが、どうしてでしょうか?

Sub sound()
Dim i As Integer
For i = 1 To 3
Beep
'MsgBox i & "回実行しました。"
Next i
End Sub

Aベストアンサー

こんにちは。maruru01です。

間隔が短すぎるからでは?

適当にWaitをかけてはどうですか?
以下では1秒間を空けて鳴らします。


Sub sound()

  Dim i As Integer
  Dim StartTime As Single

  For i = 1 To 3
    StartTime = Timer
    Do While Timer < StartTime + 1
      DoEvents
    Loop
    Beep
    'MsgBox i & "回実行しました。"
  Next i

End Sub

QエクセルのIF関数で、文字が入力されていたならば~

エクセルのIF関数で文字が入力されていたならば~、という論理式を組み立てたいと思っています。

=IF(A1="『どんな文字でも』","",+B1-C1)

A1セルに『どんな文字でも』入っていたならば、空白に。
文字が入っていなければB1セルからC1セルを引く、という状態です。

この『どんな文字でも』の部分に何を入れればいいのか教えてください。

またIF関数以外でも同様のことができれば構いません。

宜しくお願いします。

Aベストアンサー

=IF(ISTEXT(A1),"",B1-C1)

でどうでしょうか?

Q【Excel】 セルの色での判断はできますか?

使用環境:Office2003

条件付書式を使用して、セルの値がxxならばセルを着色する・フォントを変える、というのは可能ですが、

逆に

セルの色がxx(例えば赤等)ならば、隣のセルに1を代入する

ということは可能でしょうか?
※VBAを使わなければそれにこしたことはないですが、
 必要ならばコーディングも教えてください

よろしくお願いします。

Aベストアンサー

過去の質問を検索していただくと、たくさんの事例がありますが、結論から言うとVBAを使用しないとセルの色は取得できません

>セルの色がxx(例えば赤等)ならば、隣のセルに1を代入する
VBAを使用すればもちろんできますが、これは「例えば」の質問であって、このコードを書いても意味はないのでは?

ですから汎用的に使えるユーザ定義関数にしました。以下のマクロをALT+F11でVBE画面を開き、左上のVBA Projectでシート名を右クリックし「挿入」→「標準モジュール」で表示される画面に貼り付けて下さい。
ワークシート画面に戻って、適当なセルに
=CellColor(A1)
と入力してみて下さい。A1セルの色番号が表示されます。(背景色なしの場合は0が返ります)
この戻り値判定して、関数などでセルに値をセットすればよいでしょう

Function CellColor(ByVal rng As Range)
With rng.Cells(1, 1).Interior
If .ColorIndex = xlNone Then
CellColor = 0
Else
CellColor = .ColorIndex
End If
End With
End Function

ただしセルの背景色を後から変えても、ユーザ定義関数の戻り値は自動的には変わりません。式を入力した後で背景色を変更した場合は
 ALT+Ctlr+F9
で強制再計算させる必要があります。

過去の質問を検索していただくと、たくさんの事例がありますが、結論から言うとVBAを使用しないとセルの色は取得できません

>セルの色がxx(例えば赤等)ならば、隣のセルに1を代入する
VBAを使用すればもちろんできますが、これは「例えば」の質問であって、このコードを書いても意味はないのでは?

ですから汎用的に使えるユーザ定義関数にしました。以下のマクロをALT+F11でVBE画面を開き、左上のVBA Projectでシート名を右クリックし「挿入」→「標準モジュール」で表示される画面に貼り付けて...続きを読む

QExcelでセル入力後に音を鳴らす方法は?

Excel2007を使っています。

『(数値)入力が終了し、Enterキーを押して、次のセルに移動』した際と同等の処理を行った場合に音を鳴らす方法を教えて下さい。
この『 』内と同等の処理というのはある特殊な入力システムボタンを押すことで1つ押す事で、入力されます。

なお、入力は半角英数で行います。
音は、「ピッ!」という簡単な入力完了音が任意の音量で出力される事を望みます。

知見のある方よろしくお願いします。

Aベストアンサー

VBAの設定方法はどなたか近くの人に聞いてください。

2行目のBeepの4文字を書くだけです。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Beep
End Sub

QIF関数の論理式に時刻を使いたいが・・・

 例えば宅配便の配達希望時間を羅列した表があって、「20:00以降は“夜間配達”」と戻したいとき、IF関数を使用すると思います。

 解説書によると、IFの式の条件には時刻の表示形式は使えないので「”」で囲み、「1」をかける。これで時刻表示がシリアル値になるため設定が早くなる。

とあるのですが、これだけでは意味がわからず質問させていただきました。下記の2点ほどご教示いただければ助かります。

(1)どうしてIFのの条件に時刻の表示形式は使えないのか?他の関数には使えるような気がするのですが。

(2)「”」で囲むのはなんとなくわかるとして、なぜ「1」をかけるのか。

シリアル値の知識(時刻の場合24時間を1で管理する)は一応あります。

 

Aベストアンサー

(1)IFの関数というより、数式に時刻を使用することができないのではないでしょうか?
これは想像するに、時刻表示には「:」(コロン)を使いますが関数では「:」は範囲を指定する時に使用しますよね(例:A1:A10)。ですから数式に時刻の「:」を認めると範囲指定なのか、時刻なのか、収拾がつかなくなってしまいそうです。
(2)””で囲んで1を掛ける、についてですが。
””で囲むと文字列として認識されますよね。しかし””の中が本来数値ならば1を掛けることによって、文字列ではなく数値として認識されることになります。そのために1を掛けて数式として成り立つようにするのです。

想像の部分もあり間違ってたらすいません。

Qvba セルに入力した時間をマクロで受け取るには?

はじめまして、
マクロを使ってタイマーをセットしているのですが、
たとえば1行A列に9:00:00という時間をセットして、
それをマクロでうけとりmacro1というマクロを時間とおりに動かすようにタイマーにせっとします。
プログラムは以下のとおりなのですが、
TimeValue(Cells(1, 1))のところがうまくいきません。

解決方法を教えてください。
よろしくお願い思案す。


Set_timer = TimeValue(Cells(1, 1))
Cells(5, 3) = "設定完了"
MsgBox "タイマーを設定しました。"
Application.OnTime TimeValue(Set_timer), "macro1"

Aベストアンサー

こんにちは。

TimeValueの引数は文字列でなければいけません。

ところがセルに時刻を入力すると見た目は時刻ですが、実際の値は「シリアル値」と呼ばれれる値になります。

文字列のところにシリアル値を入れているので「型が一致しません」とエラーになるのです。

そこでこのシリアル値を表示と同じような文字列に変換してやります。

Set_timer = TimeValue(Cells(1, 1))
     ↓
Set_Timer = Format(Cells(1, 1).Value, "h:mm:ss")

とします。どうでしょうか。

QExcel VBAでsub,dimは何の略?

Excel VBAで
sub,dimはそれぞれ何の略ですか?

Aベストアンサー

sub subroutine(サブルーチン)
dim dimension(次元)

です。
元々はFORTRAN言語で、手続きの開始を宣言するSUBROUTINEと、配列を宣言するDIMENSIONでした。
FORTRANのDIMENSIONやN-BASIC等でのDimは、配列変数を宣言するものだったのですが、
VBになって、配列じゃない変数もDimで宣言するようになりました。

Qある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?

Excelの関数について質問します。
ある範囲のせるを検索して、その隣のセルの値を取得するという関数を探しています。
なければユーザー定義で作りたいと思っています。
VLOOKUP関数では一番左端が検索されますが、
それをある範囲まで拡張して、
その右隣の値を取得できるようにしたいのです。
どうかお知恵をお貸しください。

Aベストアンサー

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場合によっては、IFをかぶせてCOUNTIFで確認した方が良いかもしれません。
 ex. =IF(COUNTIF(A1:F200,X1)=1,【上記数式】,"えらー")

ちなみに、VBAでやるならこんな感じになるかと。

動作の概要
 【検査範囲】から【検査値】を探し、
 最初にHITしたセルについて、右隣のセルの値を返す。
 ex. =Sample(X1,A1:F200)

'--------------------------↓ココカラ↓--------------------------
Function Sample(ByVal 検査値 As Variant,ByVal 検査範囲 As Range)
 For Each セル In 検査範囲
  If セル = 検査値 Then Exit For
 Next セル
 Sample = セル.Offset(0, 1)
End Function
'--------------------------↑ココマデ↑--------------------------

いずれもExcel2003で動作確認済。
以上ご参考まで。

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場...続きを読む

Qエクセル VBA ユーザーフォームを閉じる

ユーザーフォームを開く時は
UserForm1.Showですが
閉じる時は?
UserForm1.Close
だとコンパイルエラーになります。
End
にするしかないですか?

Aベストアンサー

Unload Me とか Unload UserForm1 でユーザーフォームを閉じることができます。


人気Q&Aランキング