エクセルＶＢＡ コントロールのプロパティの変更

質問

エクセル２００３でシート上に割り付けたラベルコントロールをＶＢＡでＣａｐｔｉｏｎプロパティで
変更するプログラムを作ってみました。(同じ様にボタンコントロールのＣａｐｔｉｏｎプロパティに
ついても作りました。)
ステップ実行（Ｆ８）では、思ったとおり変更されますが、普通の実行(Ｆ５)では思ったとおり
変更されず、遅れて最後のコマンド分しか変更されませんでした。
＊条件ループでセルのテキストを順番に読みコントロールのＣａｐｔｉｏｎとして変更するプログラム
　です。
　なぜ出来ないのか分かりません。
　原因又は、方法を教えていただけないでしょうか？

　ＶＢＡでは、オブジェクト名に配列を使えないらしかったのでＯＬＥObjectｓ（コントロール?、
　コレクション?）でオブジェクト名の配列のように使っています。
　あまり、詳しくないので用語が間違っていたらすいません。
　後、途中にインターバルタイマー入れたりして、動作確認しましたがやはりダメでした。

回答No.7

Wendy02

返事ありがとうございます。一応、レスする場を作っておきます。

もう少し、詳しい説明が必要かと思いましたが、触っていただければ、考え方は分かっていただけると思います。それが、そのまま、ご質問者さんのご要望とは一致しないことは、言うまでもありませんが、何かのヒントになるはずだと思っています。

ちょっと、いくつか言い訳させてください。

まず、私の知る範囲のプロは、このようなコードは書かないはずです。もっと単純なコードを書きます。知っていて書かないのがプロかもしれません。今回の書き方は、掲示板独特かもしれません。迷いましたが、ワザを見せないと、こちらの言うこと信じない人もいますからね。

よく、ここのサイトで、「もっとスマートな書き方はないですか？」と聞く方がいますが、プロは、スマートとは対極にあるのかもしれません。だいたいは、コピペで、単純なコードを書くというのが良いようです。

今回の場合は、一つずつ、コードを書いてもよいとは思います。そのほうがプロモードだということです。たぶん、ある程度読めれば、クラスのコードを展開出来るはずです。

もうひとつ、他人からツッコミを入れられる前に、
クラス側の
Public WithEvents lbl As MSForms.Label

むき出しなんですね。これをダメだしする人がいるかもしれませんが、これ以上凝った方法は、面倒くさくなってしまったからです。

それはともかく、構想することと実際にコードにするのとは、なかなか一致しないものです。VBAに限らず、いろんな同様のソフトを使ってみて、参考にしてみるのがよいかと思います。似たようなパターンが出てきます。それをExcel等で際限することを考えてみると、思ってもない以上のものが出来上がることがあります。逆に、自分の未熟さを知ることもあります。そうしたら、また、最初からやり直しです。

いろんな知識が必要ですが、最終的には、データをデータベース（mdb)や別のデータ方式で、インタフェース自体は、Excelで行うというのが良いと思います。UserFormを使う方法もあります。

こうしたものを作る時のアドバイスとしては、行き詰ってダメだと思ったら、その開発しているものに、こだわらず、それを捨ててしまうことです。それが良いものを作るコツです。

回答No.6

Wendy02

#3の補足のコードをみましたが、基本的には、こういう場合は、オプションボタン（ラジオボタン）を利用するのが一般的です。

今の状態から実現するのは、VBAのかなり高度な知識が必要が必要になります。#5のコードは見かけは簡単ですが、VBAの範囲では高度なテクニックを使っています。本来は、フォームツールのほうがよいのですが、それは、今回は割愛か後回しにします。コントロール・ツールだからこそ、こういうことになってしまいます。

まだ、完全とは言えません。実現するには、まだ足らない部分があります。ある程度、VBAマクロの経験者なら、この後は読んで書き換えられるものだと思います。

> 例えですが、ここに別のルーチンを呼出したりして入力を待ち処理を続ける。

これは不可能ではありませんが、そのタイミングは、OnTimeなどにせず、更新は「明示的に」ボタンなどにしたほうがよいです。以下では、ボタンに取り付けたらよいと思います。[DataChange]　それが、確実だと思います。

>クイズとかみたいな感じのマクロを作ろうと考えています。
>数問単位で表示・画面更新される。(この場合は3問)

「問」はよいとして、自分の選択したものをどこかに書きこむ必要があるのと、それが正解か整合しなければなりません。今は、どういうスタイルになるか、言葉ではイメージが分からないでしょうから、どのようになるのか、披露しておきます。

入力シート　（選択1-1は、オブジェクト名）
ラベル
　A　　　　 D　　　　　F　　　　　H　　　　　J
(問1)　　(選択1-1)　(選択1-2)　(選択1-3)　(選択1-4)
　　　↑
　　 選択値(1から4まで）

データシート
　　B　　C　　　D　　　E　　　F　　G
　　問　設問　設問　　設問　設問　選択値(1から4まで）
　G列に、同じ選択値が貼り付けられる

ただし、ラベルをマクロで更新すると、インスタンスは壊れてしまいますから、再び、インスタンスを生成しなければなりません。

なお、DoEvents というのは、Loop した時に、コマンドの命令のメモリが100%になっているのを、99%にして、他の命令の余裕を与えてあげるということです。正確にいうと、待ちの命令(cue)に、割り込みをし、OSに命令を与えるということます。コントロール・ツールのプロパティを書き換え中は、VBAの命令が拒否（編集）状態になりますから、マクロを終わらない限りは、復帰はしないはずです。DoEvents でした所で、現在の流れでは、Loopを止めたとしても、VBAは、二重に動くわけではありませんから、ペンディング状態にはならないはずです。#5では、cnt という所は、ページ（設問の内容）をカウントさせています。自動的に、時間でページを変える、ということも可能ですが、それは、何とも言えません。

お礼

コードまで付けていただいた上、詳しい解説をしていただきありがとうございます。

私のスキルでは、確かにこのコードは少し難しく感じました。（ _ _ ）..........o
クラスモジュール等使ったことのない手法もありましたが、手元の解説書を改めて読むとこれらのことに触れていましたので、それらも参考に何とか出来そうです。＼（＾ ＾）／

ある程度出来上がったら報告しようと思ったのですが、それでは時間がかかってしまいますので、この時点でお礼申し上げます。
有難うございました。

回答No.5

Wendy02

解説は次に続きます。

'標準モジュール
Public ctrlLabeles As New Collection
Public myClass() As Class1
Public Const N1 As String = "nyuryoku"　'シート名(入力シート)
Public Const N2 As String = "data"　'データシート
Public cnt As Integer 'ページ切り替えのためのカウント
Sub DataChange()
　Dim j As Long, i As Long, k As Long
　On Error Resume Next
　cnt = cnt + 1
　If cnt > 2 Then　'ページ数
　　 MsgBox "次のページはありません。"
　　 cnt = 0
　　 Exit Sub
　End If
　For j = 1 To 3
　　Worksheets(N1).OLEObjects("問" & j).Object.Caption = Worksheets(N2).Cells((cnt - 1) * 3 + j, 2).Value
　　For k = 1 To 4
　　　Worksheets(N1).OLEObjects("選択" & j & k).Object.Caption = Worksheets(N2).Cells((cnt - 1) * 3 + j, k + 2).Value
　Next k
Next j
Call SetInClassObject
Application.ScreenUpdating = True
Beep
End Sub
Private Sub SetInClassObject()
'クラス生成
Dim ctrl As OLEObject
Dim i As Integer
Dim nyuryoku As Worksheet
Dim data As Worksheet
Set nyuryoku = Worksheets(N1) 'Worksheets("nyuryoku")
Set data = Worksheets(N2) 'Worksheets("data")

　For Each ctrl In nyuryoku.OLEObjects
　If TypeOf ctrl.Object Is MSForms.Label Then
　　 ctrlLabeles.Add ctrl.Object
　　 ReDim Preserve myClass(i)
　　 Set myClass(i) = New Class1
　　 Set myClass(i).lbl = ctrl.Object
　　 i = i + 1
　End If
　Next ctrl
End Sub

'Class1
Public WithEvents lbl As MSForms.Label
Private Sub lbl_Click()
Dim buf As String
Dim i As Long, j As Long, n As Long
　buf = lbl.Name
　If buf Like "選*" Then
　　 i = Replace(Left(buf, Len(buf) - 1), "選択", "")
　　 n = lbl.TopLeftCell.Row
　　 Worksheets(N1).Cells(n, 2).Value = Right(buf, 1)
　　 Worksheets(N2).Cells(i, 7).Value = Right(buf, 1)
　　 Beep
　End If
End Sub

回答No.4

Azzz___

こんにちは。

質問から判断するに恐らくDoeventsを入れてあげれば問題ないと思います。
ループを使われているようなのでcaptionを変更した後に入れてあげれば大丈夫でないでしょうか。
と思っていたら質問者さんも気づいたようですが、結果いかがでしょうか？

Doeventsに関しては「おなじない」とかつて言っていた人がいましたが
制御を開放するとかいう意味だと思いますが、
イメージとしては水に潜った状態で息継ぎ(復活→また潜る)をするようなこともかもしません。

お礼

回答有難うございます。

今回は、コードとしては難しくなりますが＃６の方法で作成することにしました。
Doeventsでもやってみましたが、Doeventsだらけになったり、重い処理となると全部変わらない場合があるような感じでした。
深いところに潜る場合は・・・！？かもしれませんね。
ケースバイケースで使い分けるようにします。

回答No.3

Wendy02

#1の回答者ですが、おっしゃっていることが良く分かりません。
#2さんの回答で解決したのですか？

>この方法で試したら、標題が変更できました。

解決したのなら、以下は無視して結構です。

ただ、こちらは、「セルのテキストを順番に読み」というから、Cells(i,1).Value としたまでです。それが、変数をそのままCaptionに変えられるとか、そんなことは質問の趣旨とは違うものだと思います。

>もし、同じような構文をnextの後入れても、最初のcaptionがやはり、まずは変更されませんね。

同じようなとは、私のコードで、Captionが変わらないという意味なのですか？
Excelが壊れていなければ、こんな単純なコードでは、あり得ない話です。

>クリック入力等の繰り返しで動作するようなマクロを作ることを考えています。
>出来るかどうかわからないので試行錯誤しています。
>定石のような方法は、ありますか？

「クリック入力」とはなんですか？クリックではイベントが発生するだけだと思います。試行錯誤していることは何なのか分かりません。「定石」と言われても、やりたいことが分かりません。用語が、正確でない以上、もう少し具体的に、ご自分のしたいことを説明してください。

一度、あなたのコードを見せていただけませんか？コードを見せずに、こちらのコードでダメだされても、こちらは分かりません。

補足

こんにちは。　ありがとうございます。
やりたいことがうまく伝わらなくすいません。
コードをつけます。

Sub main()
Dim i As Integer, j As Integer, k As Integer
Dim nyuryoku As Object, data As Object
Set nyuryoku = Worksheets("nyuryoku")
Set data = Worksheets("data")
For i = 1 To 5　
For j = 1 To 3　　　　　　　　　　　　　　　　　'jは３問単位で表示するから
nyuryoku.OLEObjects("問" & j).Object.Caption = _
data.Cells((i - 1) * 3 + j, 2)　　　　　　'問はラベルコントロールの事
For k = 1 To 4 　　　　　　　　　　　　　'Kは解答枝４個にしているから
nyuryoku.OLEObjects("選択" & j & k).Object.Caption = _
data.Cells((i - 1) * 3 + j, k + 2)　　　'選択はクリックボタンの事
Next
Next
MsgBox ("答えてください") _
'例えですが、ここに別のルーチンを呼出したりして入力を待ち処理を続ける。
Next
End Sub

シート名"data"のセル
1 問1-1 選択1-1(1) 選択1-1(2) 選択1-1(3) 選択1-1(4)
1 問1-2 選択1-2(1) 選択1-2(2) 選択1-2(3) 選択1-2(4)
　　　　　　(以下データ続く)
シート名"nyuryoku"で表示変更する。

別のルーチンを呼び出される時点では、表示がまだ変わっていないです。
クイズとかみたいな感じのマクロを作ろうと考えています。
数問単位で表示・画面更新される。(この場合は3問)
解答は、ボタンクリックで行う。

追記：キーワードをいろいろ変えてネットでもう一度検索したのですが、
　　　"Doevents"の単語が出てきましたが、このような時、使うのでしょうか・・・。

回答No.2

piroin654

たとえば、シートにラベルを三つ
貼り付けたとして、

For i = 1 To 3
Worksheets("Sheet1").OLEObjects("Label" & i).Object.Caption = i
Next i

のようにすれば、標題が変更できます。

お礼

ありがとうございます。
この方法で試したら、標題が変更できました。
アレンジして使えそうです。
変数ｉについては、わざわざ文字列変換する必要はないんですね。(*^-^)

回答No.1

Wendy02

ご質問者は、VB系の経験者のようです。文章を読ませていただくと、コードには問題ないから、Excelの仕様かExcel自体のバグの問題だとおっしゃっているようです。私には、その原因については想像もつきません。それと、VBAにインターバルタイマーって使えるのでしょうか？インターバルタイマーって、そもそもコントロールですよね。

以下は、ラベルだけですが、こういうことで、Caption を変えることでしょうか？

Sub TestLabels()
Dim i As Long
　With ActiveSheet
　'A1からA10まで、文字を入れてみました。
　For i = 1 To 10
　 .OLEObjects("Label" & i).Object.Caption = .Cells(i, 1).Value
　Next i
　End With
End Sub

補足

ありがとうございます。
ご回答のような方法で、captionを変えようとしていました。

ただ、同じラベルに対してもう一度captionを変更させたいと考えています。
同じような構文をnextの後入れても、最初のcaptionがやはり、まずは変更されませんね。
（もしなったとしても、早くて分からないでしょうから、msgBoxで1テンポ入れています。

目的は、ラベルを見てクリックor選択入力をして、同じラベルの利用でcaptionを変更し、
またクリック入力等の繰り返しで動作するようなマクロを作ることを考えています。
しかし、出来るかどうかわからないので試行錯誤しています。
定石のような方法は、ありますか？

インターバルタイマーについては、合っているかどうが分かりませんがネットに
ありましたので参考にしました。:Application.Wait Now + TimeValue("00:00:01")