エクセルＶＢＡでloop？？？

質問

エクセルのＶＢＡについて質問させてください。
フォームにtxt1.text～txt14.textがあります。
cmdDSをclickしたときに空値をセット（= ""）したく、今は下のようになっています。
private sub cmdDS_click()
txt1.text = ""
txt2.text = ""
…
end sub
ですが、1～14という数字以外はすべて同じ文であることから数字の部分だけを入れ替えるようにし、loopのようなものを使ってもっと簡単にできないでしょうか？

マクロを組めば必然的にファイルのサイズが大きくなってしまうと言うことはわかっていますが、サイズを抑えるためにできることやそう言ったことについてのＨＰなどがありましたら併せて教えていただきたいと思います。
よろしくお願いします。

回答No.2

Wendy02 · ベストアンサー

こんにちは。

>フォームにtxt1.text～txt14.textがあります。
txt1とかいうのは何ですか？ TextBox のことですか？
フォームとは何ですか？ UserForm ですか？
Form ツールのフォームには、TextBox とは呼びません。

Excelの場合は、できる限り標準名称を使用してください。オブジェクトの名前自体をVBのコントロールの名称にわざわざ書き換えているようですが、書き換えの手間とか面倒になると思います。なお、VBなら、コントロール配列を使えばよいと思います。また、Excelのこの種のコントロールで擬似的なコントロール配列は、あまり賛成できません。

一応、UserForm のTextBox とさせていただきます。

UserForm　場合
For i = 1 To 14
　　Me.Controls("Txt" & i).Text = ""
Next

>マクロを組めば必然的にファイルのサイズが大きくなってしまう

基本的に誤解があるように思います。
私は、Excelの使用メモリを減らすために、マクロを組みます。特に、Mode 関数や Frequency 関数のような配列を使うと、大きなファイルでは、とても、そのままでは使えませんので、その代わりに、マクロに切り替えます。また、アドインにすることにり、マクロ部とデータ部と切り離すなど、いくつかのテクニックがあります。

少し、話を膨らませて、個人的なことも書かせてもらいます。

Excelというものは、基本的にはファイルサイズの限界というものは、明確には仕様には出ていません。しかし、Excelを使用するキャッシュ・メモリがあるわけで、それは、私たちが想像するよりもはるかに小さいのです。マクロは、ファイルを開き、セキュリティ・パスが通れば、中間言語にコンパイルされます。その中間言語のファイルのストックの大きさには、当然限界はあるのですが、ファイルサイズとの直接の関係があるとはいえません。間接的には、他のメモリに押しやられることはあるかと思います。

サイズを抑えるというよりも、まずワークシート全体のメモリ管理をしなくてはならないのです。

マクロについては、「マクロの最適化」という名称で、マイクロソフトのサポートのライブラリに残されていますし、私もその内容については、ここの掲示板に書いたことがありますが、多くの方がは、それを重要視しません。しかし、ある程度のVBAに精通している方であれば、この基本的なルールをご存知ないとすれば、とても残念なことだと思います。そのルールに当てはめれば、正しいか正しくないか一目瞭然のはずなのです。

VBAコードを最適化する
http://msdn.microsoft.com/library/ja/default.asp?url=/library/ja/modcore/html/deconoptimizingvbacode.asp

詳しい内容は、『Microsoft Office プログラマーズガイド』の中にあります。（私の所有しているものは、Office 97とOffice 2000)

Office Tanaka　でも、少し書いてありますが、こちらは、内容的に、それがそのまま応用できるわけでもなければ、その実験がそのまま実務で使えるわけでもありません。この方の原則を踏襲している人もあるようですが、おかしなコードになりやすいように思います。たとえば、シートをIndex で呼び出すというのは、現実的ではありません。

Office Tanaka
http://www.officetanaka.net/excel/vba/speed/index.htm

なお、補足として、ワークシートのメモリを減らす方法として、以下があります。
[XL] メモリを節約するワークシートの作成方法
http://support.microsoft.com/kb/401704/ja

お礼

不備ばかりの質問で申し訳ありませんでしたが、回答ありがとうございました。
サイズについては理解するのにまだ時間がかかりそうですが、
マクロはお陰様で組むことができました。
本当にありがとうございました。

回答No.4

Wendy02

こんばんは。

#2の回答者です。割り込みさせていただきます。
ただし、UserFormを条件とします。

>If Left$(ctrl.name, 3) = "txt" Then ctrl.Text = ""
>If Left$(ctrl.name, 2) = "op" Then ctrl.Value = False

そういう形にするなら、普通は、オブジェクト型を取って処理すれば良いのでは？
ただし、Optionボタンを、あえてすべてFalse にするという考え方自体はヘンですが。

　 Dim ctrl As Control
　　For Each ctrl In Me.Controls
　　　　If TypeOf ctrl Is MSForms.TextBox Then
　　　　　　ctrl.Text = ""
　　　　ElseIf TypeOf ctrl Is MSForms.OptionButton Then
　　　　　　ctrl.Value = False
　　　　End If
　　Next

回答No.3

imogasi

３つの場合(（テキストボックスも、フォームも、フォームへD&Dしてから名前を変えてない場合。)
全部初期化かクリアする方法
Private Sub CommandButton1_Click()
For i = 1 To 3
’UserForm1.Controls("TextBox" & i).Text = "aa"　'初期化
UserForm1.Controls("TextBox" & i).Text = "" 'クリア
Next i
End Sub
ーー
＃１のオプションボタンの場合
Frameを普通使うと思うが、使わない場合の例
Private Sub CommandButton1_Click()
UserForm1.OptionButton2.Value = True
MsgBox "A"
For i = 1 To 3
UserForm1.Controls("OptionButton" & i).Value = False 'False
Next i
End Sub
上２行はテスト確認の為に入れている。
グループ化したオプションボタンのOnのもの１つをOffにすればよいが
無駄をして、３つともOFFにしている。
ーー
http://www.hisystec.co.jp/VBAroom/VBA08.html
を参照
Frameを普通使うと思うが（下記はFrameが２つ、オプションボタン各３つの例）
AAが表示された瞬間でOnになっていることを確認し、OKをクリックで消える（Oｆｆになる）ことを確認してください
Private Sub CommandButton1_Click()
UserForm1.Frame2.OptionButton5.Value = True
MsgBox "AA"
For i = 4 To 6
UserForm1.Frame2.Controls("OptionButton" & i) = False
Next i
End Sub

回答No.1

project-a

以下は一例です。
フォーム上のコントロールを検索して、名前の左３文字が "txt"なら内容をクリアします。
※"UserForm1"はフォーム名なので、お使いのフォーム名に直してください。

Private Sub CommandButton1_Click()
　Dim ctrl As Control

　For Each ctrl In UserForm1.Controls
　　If Left$(ctrl.Name, 3) = "txt" Then
　　　ctrl.Text = ""
　　End If
　Next
End Sub

お礼

ありがとうございました。
お礼に書こうか補則に書こうか悩んだのですが、
同じ方法でオプションボタンをfalseにできないでしょうか？
（同じコマンドボタンをクリックしたときの動作としてです。）
Dim ctrl As Control
For Each ctrl In (4)Form予定入力.Controls
If Left$(ctrl.name, 3) = "txt" Then ctrl.Text = ""
If Left$(ctrl.name, 2) = "op" Then ctrl.Value = False
End If
Next
こうかな？と思ったのですが、
テキストを空値にすることしかできませんでした。