[[20101104024751]]　『無限ループもどき?』（黄色いしっぽ）　ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

　

『無限ループもどき?』（黄色いしっぽ） 

 エクセル2000のVista使用してます
 Changeイベントで下記のリストをA列:B列どちらからでも入力したら自動で対応
するようにしたかったのですが・・問題なく動作しているような感じですが、
構造上ループしてると思われ、途中にMsgBoxを入れるとだめになります。
回避方法か別の方法などありましたら、お願いします。


 1    AA
 2    BB
 3    CC

Private Sub Worksheet_Change(ByVal Target As Range) 

    If Target.Column = 1 Then


          YY = Target.Value
          If YY = 1 Then XX = "AA"
          If YY = 2 Then XX = "BB"
          If YY = 3 Then XX = "CC"
          Range(Target.Address).Offset(, 1).Formula = XX
    End If


    If Target.Column = 2 Then


          YY = Target.Value
          If YY = "AA" Then XX = 1
          If YY = "BB" Then XX = 2
          If YY = "CC" Then XX = 3
          Range(Target.Address).Offset(, -1).Formula = XX
    End If


  End Sub


 >構造上ループしてると思われ
 確かにChangeイベント内でセルに値を入力するコードがあると、そのコード実行時に
 更にイベントが発生しますから、


 >構造上ループしてる


 という考え方は正しいですね！！


 これ自体は、
 Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xx As Variant
    Dim yy As Variant
    Application.EnableEvents = False   'イベントを発生させない
    If Target.Column = 1 Then
          yy = Target.Value
          If yy = 1 Then xx = "AA"
          If yy = 2 Then xx = "BB"
          If yy = 3 Then xx = "CC"
          Range(Target.Address).Offset(, 1).Formula = xx
    End If
    If Target.Column = 2 Then


          yy = Target.Value
          If yy = "AA" Then xx = 1
          If yy = "BB" Then xx = 2
          If yy = "CC" Then xx = 3
          Range(Target.Address).Offset(, -1).Formula = xx
    End If
    Application.EnableEvents = True　　'イベントの発生を有効にする
 End Sub


 上記のようにApplication.EnableEventsプロパティを使えば、イベントのループは防げます。


 が、このChangeイベントって、結構難しいんです。
 問題は、Excelのシートって、同時に複数のセルに値が入力できてしまうことです。
 一般的に使う分には、これは便利な機能ですが、ＶＢＡで制御する立場になると、
 これの扱いが厄介だし、私は、今でも悩みます。


 上記の（Application.EnanleEventsを入れたコードで）のコードにて、


 a1:a3　という複数のセル範囲を選択してください。
 この状態で、Deleteキーを押してみて下しい。
 型が一致しません。　というエラーが


 >If YY = 1 Then XX = "AA"
 ここで発生します。これは、YYが配列になっているため　このエラーが発生します。


 尚、途中でコードが止まってしまっているので、このままだと
 次のイベントが発生しません（Application.EnableEvents = False　が効いている）。


 標準モジュールに
 sub 復帰イベント()
     Application.EnableEvents = true
 end sub


 上記の 「復帰イベント」を実行して、イベントの発生を可能にしておいて下さい。
　


 このエラーの回避の考え方には、二つあります。


 一つは、同時に複数のセルへの入力には、対応しないようなコードにしてしまうこと。


 つまり、Excelの持っている機能をコードで狭めてしまう仕様にするという方針。


 もう一つは、同時に複数のセルへの入力にも対応するような仕様にするという方針。


 仕様の選択は、本来の仕様の目的やこの仕様を適用するセル範囲の広さ等
 を考慮して決めますが、難しい判断だと思います。


 検討していただき、どちらの仕様にしても結果をUPしてみてください。


 他にも　コピー&ペーストで値以外のセルのプロパティが変えられてしまったときは
 どうする？
 なんてことも本来は、考慮しなければなりません。が、とりあえず
 上記の複数の変化に対して、考えてみてください。


 ichinose


 ichinose様へ
 Private Sub はどのようにボタンを作りどのように登録するのでしょうか。
 どうしても理解できません。
 大変お手数をおかけしますが、
 幼稚園の子供に教えるつもりで詳しく詳しく教えて下さい。
 (初心者)

 ↑
 黄色いしっぽさんとは別の人ですか？


 でしたら、他人の質問に、ほとんど無関係な便乗質問をするのはお止めください。
 新規にトピックをお立てください。


 その際、なぜPrivateにこだわるのか、理由も書いてください。
 　ボタンへ登録するようなマクロは、普通、Privateにはしないものですから。。


 「だって、ここに掲載されているマクロだってPrivateがついているじゃないか」
 　と云う理由でしたら


 　　このマクロは、ボタンをクリックして動かすものではありません。
 　　シートの何処かのセルが変更になったら自動的に動くものです。
 　　いわば、シート自体がボタンと思ってください(ただし「セルが変更された」と云う条件付きです)


 　　また、このマクロは引数を渡さないと動きません。(Rangeオブジェクトを渡す必要があります）
 　　つまり、直接動かせるマクロではありません。
 　　別のマクロから、引数を渡してCallすれば動くことは動きますが、
 　　そんなことをしてまで動かす意味はないと思います。


 (半平太) 2010/11/04 10:48


 とても丁寧な説明とご指導ありがとうございました。
 おかげさまで、Application.EnableEventsを使うことで何とか思い通りに行きそうです。


 実は会計処理を簡略化しようと思っていたところでした。
 内容は下記のとおりです。


 1 運営費　　  (1)会議費
               (2)事務費
               (3)旅費


 2　専門部費   (1)総務研修部費
               (2)広報部費


 3　・・
 4　・・


     〔A〕    〔B〕   〔C〕     〔D〕
     項目1 　 項目2　項目名1  　項目名2


      1        (2)      運営費　 　事務費
　　　2　　　　(1)　　  専門部費   総務研修部費
　　　1        (3)      運営費     旅費


 入力者がセル"A"に1を入力することで"C"に項目名を表示、セル"B"の入力規制を(1)〜(3)に変更、
 セル"D"の入力規制も(会議費・事務費・旅費)に変更しリスト表示する。
 逆に、セル"C"の項目名を入力すると"A"に1を表示し、BとDの入力規制をしてリスト表示する。
 こうする事で、項目が分かればAとBに1・2と入力すればC、Dは入力しなくてよい(逆も)


 でマクロを作成しようとしていたところ表題のループ問題でつまずいたところでした。


 一応Deleteした場合や入力変更した対応も考えようと思っていました。
 上記のような仕様で考えていたので、ご指摘された範囲削除やコピー&ペーストの対応については、
 仕様上考慮しなくてもいいのではないかと考えてました??^^;


 エクセルでこのような会計処理をすること自体一般的ではないのでしょうか?(世間しらずです)
 また、エクセルでこのような処理(マクロ)は通常使わないのでしょうか??


 とりあえず教えていただいたApplication.EnableEventsプロパティを使って、作成してみたいと思います。
 本当にありがとうございました。


 ..作成したマクロ長くなりそうですがUPしたほうがいいですか?


 (黄色いしっぽ) 2010/11/4 22:15


 >作成したマクロ長くなりそうですがUPしたほうがいいですか?
 本来の目的は、わかりましたから、作成コードは、最初に提示された
 例として投稿されたコードに対しての修正コードで良いのですが・・・。


 私が知りたいのは、投稿された会計処理の科目の簡単入力が目的で


 > 一つは、同時に複数のセルへの入力には、対応しないようなコードにしてしまうこと。
 >もう一つは、同時に複数のセルへの入力にも対応するような仕様にするという方針。


 どちらの仕様で作成されるのか　ということです。
 UPされるか否かは、ここから更に不特定多数の方と勉強する気があるか否かですから、
 それは、ご本人に任せます。もちろん、私は、バグの可能性をどのようにされたのか
 ということに興味はありますけど・・・・。


 >コピー&ペーストの対応については、仕様上考慮しなくてもいいのではないかと考えてました。


 「ユーザーは、何をするか分からない」という観点からすると、


 例えば、日付の書式持ったセルをコピーし、上記のＡ列に貼り付ければ、
 貼り付けられたＡ列に　1　と入力すれば、1900/1/1　と表示されてしまいます。
 これは、一例ですが、入力規則を設定しているセルに貼付を行えば、
 その入力規則が消えてしまう可能性もあります。これらの事も承知の上で


 そこまでは、要らないだろう　という結論なら、それで良いでしょうねえ・・・。


 私の場合は、イベント内で改めて対象セルの書式を設定しなおしています。


 >エクセルでこのような会計処理をすること自体一般的ではないのでしょうか?
 >また、エクセルでこのような処理(マクロ)は通常使わないのでしょうか??
 長く使うものなら、最低でもプログラムを含んだブックとデータのみのブックというように私は、ブックを分けてしまいます。
 場合によっては、データは本物のデータベースに置きます。
 私は、↑殆どこれですねえ！！


 頑張ってください


 ichinose

　 

 初心者さんへ
 > Private Sub はどのようにボタンを作りどのように登録するのでしょうか。
 これは、この質問とは、大きくかけ離れた内容でのご質問ですか？
 そうなら、本来は、半平太さんと同様に別スレッドにした方が良いとは思います。
 それとも・・・、ということも考慮して、ここに投稿します。


 まず、このご質問とは違う意味で、
 シートに貼り付けたコマンドバー「フォーム」のボタン（仮に　ボタン 1　とする）に
 標準モジュールにある


 Private Sub sample1()
    MsgBox "sample1が作動しました"
 End Sub


 上記のsample1を 登録し、ボタン 1をクリックしたら、sample1が実行するようにする
 方法は？


 対象である「ボタン 1」にマウスを合わせて、「右クリック」---「マクロの登録」
 とクリックし、マクロの登録　ダイアログを表示させてください。
 マクロ名の一覧には、sample1は表示されていませんね！！
 でも、登録するマクロ名は、直接入力することもできます。
 登録マクロ名入力欄に　sample1と入力して、ＯＫボタンをクリックして下さい。
 これで一度、適当なセルを選択後、ボタン 1をクリックしてください。


 sample1が実行します。


 このようにPrivate宣言することでこのインターフェースを簡単に知られたくない
 という目的なら、ある程度有効です。


 このような意味ではなくて、黄色いしっぽさんが提示したコードを実行するには
 どうすればよいのか？　ということなら、次投稿に続く


 ichinose


 イベントドリブン型プログラム　というワードを検索してみてください。
 今のExcel/VBAは、このイベントドリブン型プログラムに相当します。
 簡単に言えば、ユーザーが行った操作が引き金になって、プログラムが作動する　ということです。


 このユーザーが行う操作のことをイベントと呼んでいます。
 処理可能なイベントの種類は、オブジェクトによって異なります。


 今回の黄色いしっぽさんの投稿例では、
 セルにデータを入力すると発生するイベントを使っています。


 新規ブック（Sheet1というシート名が存在する）にて、


 Sheet1をアクティブにしてください。


 Sheet1というシート名が表示されているタブにマウスを合わせて、
 「右クリック」-----「コード表示」とクリックして下さい。


 ＶＢＥが起動し、Sheet1のモジュールが表示されます。


 このモジュールに


  Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xx As Variant
    Dim yy As Variant
    Application.EnableEvents = False   'イベントを発生させない
    If Target.Column = 1 Then
          yy = Target.Value
          If yy = 1 Then xx = "AA"
          If yy = 2 Then xx = "BB"
          If yy = 3 Then xx = "CC"
          Range(Target.Address).Offset(, 1).Formula = xx
    End If
    If Target.Column = 2 Then


          yy = Target.Value
          If yy = "AA" Then xx = 1
          If yy = "BB" Then xx = 2
          If yy = "CC" Then xx = 3
          Range(Target.Address).Offset(, -1).Formula = xx
    End If
    Application.EnableEvents = True　　'イベントの発生を有効にする
 End Sub


 を記述します。


 元にSheet1に戻ってください。


 Ａ列に　1　又は、2　、又は　3　と入力してみてください。


 Ｂ列には、それぞれAA、BB、CC　と表示されます。


 これは、Ａ列にデータを入力するというイベントに対し、プログラムが実行された結果です。そのプログラムこそが上記のコードです。


 尚、イベントコードは、オブジェクトモジュールのみに記述可能です。


 ichinose


 すみません理解不足でした。


 > 一つは、同時に複数のセルへの入力には、対応しないようなコードにしてしまうこと。
 >もう一つは、同時に複数のセルへの入力にも対応するような仕様にするという方針。


 とコピー&ペーストの対応については、ご指摘どおり使用者が何をするか分からない、
 ことと操作ミスでエラーがおきた場合など、ある程度の知識がなければ
 復旧するのが、困難になると思われます。


 望むべくは、同時に複数セルの入力対応を出来ないようなコードにしたいのはやまやま
 なのですが・・・
 如何せん、自分の知識と能力では非常に困難だと思われます。


 セルの書式を設定しなおすとかなら出来そうですが、複数セルへの入力に対応できない
 コードや対応できる仕様については、どちらも構想すらできない内容のレベルと思います。
 たとえば指摘していただいた範囲選択したセルの削除などは、なにをどうすればいい
 のかも想像もつかない領域かと・・


 なんとなく思うのは、シート自体にExcelの機能を狭めるコードを記述するのかな
 という感じがするだけです。
 (いちどダブルクリックを出来ないシートの設定をしたことがあるだけ)
 それと、範囲選択したときのコピーや削除の制御も含め、ツールバーのボタンや
 マウスの右クリック、セルの移動やセルのフィルハンドル、もしかしてマウスカーソ
 ルの形状とかも?と多岐に渡った制御となるのでしょうか?


 今現在の自分ではとても手に負えないと感じています。
 これから、すこしずつでも知識を増やし努力していきたいと思います。


 ichinose様本当に丁寧な説明ありがとうございました。


 (黄色いしっぽ)


 >望むべくは、同時に複数セルの入力対応を出来ないようなコードにしたいのはやまやま
 なのですが・・・


 参考程度に提示されたコードを例としてみると・・・、


 Option Explicit
 Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xx As Variant
    Dim yy As Variant
    Application.EnableEvents = False   'イベントを発生させない
    If Target.Count = 1 Then
       If Target.Column = 1 Then
          yy = Target.Value
          If yy = 1 Then xx = "AA"
          If yy = 2 Then xx = "BB"
          If yy = 3 Then xx = "CC"
          Range(Target.Address).Offset(, 1).Formula = xx
       End If
       If Target.Column = 2 Then
          yy = Target.Value
          If yy = "AA" Then xx = 1
          If yy = "BB" Then xx = 2
          If yy = "CC" Then xx = 3
          Range(Target.Address).Offset(, -1).Formula = xx
       End If
    Else
       MsgBox "複数のセルに対しての同時入力は、禁止されています"
       Application.Undo
    End If
    Application.EnableEvents = True    'イベントの発生を有効にする
 End Sub


 このようにすると、複数セルに一括入力をさせなくすることは、できます。
 尚、結合セルはないものと仮定しています。


 このコードで


 >a1:a3　という複数のセル範囲を選択してください。
 >この状態で、Deleteキーを押してみて下しい。


 これは、エラーメッセージが表示され、元の状態に戻ります。


 実は、私はこれでも気になるところがあるんですが、これは又次回にしましょう。


 ichinose

コメント返信：

[ 一覧(最新更新順) ]

YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.