フォームのCheck boxとOLEObjectのCheckboxのマクロの違い？

質問

エクセル2003です。
ワークシート上に複数個のチェックボックスを配置し、オンの場合、その左隣のセルの値を返すマクロを作成する場合についての質問です。
普段はフォームのCheck boxを使っています。
フォームのCheck boxなら
Sub ChkBx()
With ActiveSheet.CheckBoxes(Application.Caller)
If .Value = xlOn Then
MsgBox .TopLeftCell.Offset(0, -1).Value
End If
End With
End Sub
と、標準モジュールに一つだけプロシージャを書いて、複数個のCheck boxに同一のマクロを登録すれば簡単に出来ます。
ところがこれをOLEObjectのCheckboxでやってみようと思ったところ、フォームのように一つのプロシージャを使いまわすことができず、シートモジュールに以下のように各Checkboxごとのマクロを書かなくてはいけないようです。

Private Sub CheckBox1_Click()
With OLEObjects("CheckBox1")
If .Object.Value Then
MsgBox .TopLeftCell.Offset(0, -1).Value
End If
End With
End Sub

Private Sub CheckBox2_Click()
With OLEObjects("CheckBox2")
If .Object.Value Then
MsgBox .TopLeftCell.Offset(0, -1).Value
End If
End With
End Sub

Private Sub CheckBox3_Click()
With OLEObjects("CheckBox3")
If .Object.Value Then
MsgBox .TopLeftCell.Offset(0, -1).Value
End If
End With
End Sub

3つや4つくらいならどうってことはないのですが十数個もあるとかなり面倒です。
OLEObjectのCheckboxでももっと簡単にする方法はないのでしょうか？
それともわたしが何かOLEObjectのCheckboxの使い方について思い違いをしているのでしょうか？
ご教示をお願いいたします。

回答No.1

onlyrom · ベストアンサー

プロシージャChkBkに引数を持たしたらどうでしょうか。

'----------------------------------
Private Sub CheckBox1_Click()
Call ChkBx(1)
End Sub

Private Sub CheckBox2_Click()
Call ChkBx(2)
End Sub
'-----------------------------------

Sub ChkBx(N)
　With ActiveSheet.OLEObjects("CheckBox" & N)
　　　If .Object.Value = True Then
　　　　　MsgBox .TopLeftCell.Offset(0, -1).Value
　　　End If
　End With
End Sub

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

しかしこれでもコントロールが数十もあると大変ですので
クラスを使用するといいでしょう。

”VBA コントロール　疑似配列”などをキーにして検索すると
サンプルコードがたっぷりヒットします。

質問者のレベルであれば当然クラスに挑戦することになるでしょう。。。(^^;;;

お礼

onlyromさま、ありがとうございます。
クラスを勉強してみるつもりではおりますが、自分でよく理解出来てないものを今、実装するわけにはいきませんので今回はプロシージャChkBkに引数を持たせることで対処しようと思います。
幸いWendy02さまのアドバイスで、シートモジュールに書くコードも簡単に出来そうです。
これからもご指導の程、よろしくお願い申し上げます。

回答No.6

Wendy02

こんにちは。

そのままになっているようですから、ちょっと割り込ませていただきます。

特に、Sub プロシージャは、標準で「参照渡し」となるので、明示的というか可読性のために、ByRef が必要な場合のみです。同じシートモジュールに書く限りは、オブジェクトを渡すのでも、あまり変わりがないように思います。確かに、オブジェクトを再取得する場合の、タイム・ロスを否定はできませんが、この程度では差が見られません。

お礼

takana_さんの、Sub ChkBx(ByRef N As MSForms.CheckBox)は、CheckBoxというオブジェクトを「参照渡し」させるために明示的に書いている。
onlyromさんの、 Sub ChkBx(N)も明示的には書いていないが「参照渡し」で「値渡し」ではないという理解でいいですね。
ありがとうございます。

回答No.5

takana_

#2です。参考までに
onlyromさんのコードを、以下のように引数でチェックボックスを渡すように変更すると
CheckBoxの名前と位置を意識しないで自由に作成することができるようになります。

'----------------------------------
Private Sub CheckBox1_Click()
　'クリックされたCheckBoxを設定
　Call ChkBx(ActiveSheet.OLEObjects("CheckBox1").Object)
End Sub

Private Sub CheckBox2_Click()
　Call ChkBx(ActiveSheet.OLEObjects("CheckBox2").Object)
End Sub
'-----------------------------------

Sub ChkBx(ByRef N As MSForms.CheckBox)
　With N
　　　If .Value = True Then
　　　　　MsgBox .TopLeftCell.Offset(0, -1).Value
　　　End If
　End With
End Sub

お礼

ご丁寧にありがとうございます。
onlyromさまのコードは省略しないで書くと
Sub ChkBx(ByVal N As Integer)
With ActiveSheet.OLEObjects("CheckBox" & N)
If .Object.Value = True Then
MsgBox .TopLeftCell.Offset(0, -1).Value
End If
End With
End Sub
と、「値渡し」していますが、それを「参照渡し」にするとサブルーチンの中にわざわざActiveSheet.OLEObjects("CheckBox" & N)と長々と書く必要がなくなるという意味に理解してよろしいでしょうか？

回答No.4

takana_

>ということは、BOOK起動時にWorkbookOpenイベントなどで必ずSub SetCheckBox()を走らせなくてはいけないということなのでしょうか？

はいその通りです。
クラスのインスタンスを作成し、それぞれのインスタンスとチェックボックスを関連付けないとクリックイベントは発生しません。

なんらかのコードで、クラスのインスタンスを作成させかつ、インスタンスを保持しなければなりません。
Private cChkbox(1 To 3) As Class1
が関数内ではなく、モジュール内変数として、宣言しているのはそのためです。

お礼

何度もありがとうございます。
初めてクラスにさわりました。
これから勉強します。

回答No.3

Wendy02

こんにちは。

以前、Class のインスタンスのお話はあまり興味を示されなかったようですので、こちらが書くのは控えておきますが、Class インスタンスのコードは、二重・三重になるので、多少、コードは複雑になるのはやむをえないです。

VBAでは一般的というか、私自身、#1さんのように、コードの共通部分だけ抜き出し、サブルーチン化してしまい、個々のオブジェクトのイベントを書くことになります。だから、コントロールツールの名称は、必ず枝番のように最後に数字をつけるわけですね。

面倒であるとかないとかは、この際はあまり重要でないというか、ある程度、複雑になり数が増えてくると、テキストエディタやワークシートの数式や関数を使い、コードを作ってしまってから、VBEditor 側に貼り付けるというのが、私のやり方です。

コントロール配列自体が、コントロールツール（OLEオブジェクト）では持たない性質上やむをえないのと、Classでは、イベント形式で、通常のワークシートなどにつけるものよりも、結果的に、内容によって煩雑というか、ややこしくなりがちなのです。また、インスタンスの恒常化も問題になります。欲を言えば、カプセル化についても考えなくてはなりません。プロの仕事（公開されたものという意味）では、アドインは別として、Class によるインスタンスを設けたものを見た記憶がありません。

お礼

Wendy02さま、いつもありがとうございます。
すみません、以前のClass のインスタンスのお話は興味がなかったのではなく不勉強なわたしには理解が出来なかったのです。
でも今回、No2のtakana_さまから具体的なコードのご教示があり、何もわからぬままにやってみたら作動したのでこれから勉強していきたいと思ってます。インスタンスの恒常化の問題？カプセル化？・・・もう何のことやら想像もつきません。すみません。

というわけで今回はクラスはほとんど理解できていませんのでNo1のonlyromさまからご教示のあったサブルーチン方式をWendy02さまの「ワークシートの数式や関数を使い、コードを作ってしまってから、VBEditorに貼り付ける」方法を使って行いたいと思います。
ありがとうございました。

回答No.2

takana_

まず、クラスモジュールを１つ作成してください。例では、Class1とします
そのクラスの中で、WithEventsをつけて、チェックボックスオブジェクトを宣言します。
そうすると、クラスの中でそのオブジェクトのイベントを記述することが出来るようになります。
中身の例です
Option Explicit
Private WithEvents objCheckBox As MsForms.CheckBox

Public Sub SetCheckBox(ByRef InCheckBox As MsForms.CheckBox)
　　Set objCheckBox = InCheckBox
End Sub

Private Sub objCheckBox_Click()
　　MsgBox objCheckBox.Name & "が" & objCheckBox.Value & "にチェックされました"
End Sub

このクラスを利用する例です
Option Explicit
Private cChkbox(1 To 3) As Class1
Sub SetCheckBox()
　　Dim i As Long

　　For i = 1 To 3
　　　　Set cChkbox(i) = New Class1
　　　　Call cChkbox(i).SetCheckBox(ActiveSheet.OLEObjects("CheckBox" & i).Object)
　　Next
End Sub

ワークシート上にCheckBox1、CheckBox2、CheckBox3という名のチェックボックスが
作成されていることが前提です。

お礼

takana_さま、ありがとうございます。
今回はNo1のonlyromさまの方法で対処することにしました。
ただ、クラスというものをやってみるのは今回が初めてですので後学のために以下の点をお教え願いたいのです。

「中身の例です」以下のコードをクラスモジュール
「このクラスを利用する例です」以下のコードを標準モジュールにコピペしました。
そのままCheckboxをクリックしましたが何も起こりませんでした。
それで標準モジュールのSub SetCheckBox()を実行させてみたところCheckboxをクリックすると作動するようになりました。
ところが一旦、BOOKを終了し、再度開いたら、また作動しません。
それでまた標準モジュールのSub SetCheckBox()を実行させてみたところ作動するようになりました。

ということは、BOOK起動時にWorkbookOpenイベントなどで必ずSub SetCheckBox()を走らせなくてはいけないということなのでしょうか？