Excel（エクセル）活用コミュの（質問）VBAでテキストボックスを作成する場合の作成数の制限について

こんにちは。
ExcelでVBAを使ってテキストボックスを作成する場合の作成数の制限について質問させて下さい。
ちなみに以下の事象が発生したのは、Excel 2000、Excel 2003です。（OSは、XPです。）
ーーーーーー
VBAを使って処理結果をテキストボックスで表示するマクロを書いています。
（処理の流れ）
　（１）既存の結果表示（テキストボックス）を削除する
　　　　ActiveSheet.TextBoxes(i).Delete　（iは、テキストボックス数だけ回しています）
　（２）計算処理を行う
　（３）新しい計算結果をテキストボックスを作成して表示する
　　　　ActiveSheet.TextBoxes.Add(Y,X,SY,SX).Select　（Addの中は位置、サイズ情報）
　　　　（この後、作成したテキストボックスの中に値を設定）
　　　　（結果の数だけ（５００個くらい）ループで回して作成、値設定します）

１回の処理で５００個くらいのテキストボックスの削除、作成を行っています。
しばらくは正常に動いていたのですが、ActiveSheet.TextBoxes.Add(Y,X,SY,SX).Selectのところで、「実行時エラー1004　TextBoxクラスのSelectメソッドが失敗しました」というエラーが出て処理が止まるようになりました。

調べてみると新規で作成されるテキストボックス（一つだけ生成されてSelectエラーで止まったテキストボックス）の名前が「Text 67505」となっていました。（エラーが出たあと数回エラー検証のため実行した後の情報です。）

推察するところ、テキストボックスの生成に65536個（16bit）あたりで制限があるのかなぁと思うのですが、ネットでいろいろ調べてもわかりませんでした。

ちなみに、エラーが出たシート（テキストボックスは（１）の処理で全部削除された状態）をコピーして実行すると正常に動作しました。どうもテキストボックスのカウント情報がリセットされたような気がします。
ーーーーーーー
ということで、上記のエラーの原因、回避策などをご存知の方がおられましたら、ご教示願えれば助かります。
よろしくお願いします。

コメント(15)

最初
全て
最新の40件

[1] mixiユーザー 10月10日 01:06

こんばんは。
昨日コミュニティ覗きにきたのにコメント付いていないトピがあるのを見逃していました。

現象、再現できるか試してみますね！

[2] mixiユーザー 10月10日 01:26

うーん。
残念ながら、ちょっと再現できませんでした。

シートモジュールに下記のコードを書いて実行しました。
10万回、テキストボックスを追加・削除を繰り返すマクロです。

Sub Sample()
　　Dim i As Long
　　Dim txt As TextBox

　　On Error GoTo ErrHandle

　　Application.ScreenUpdating = False
　　For i = 1 To 100000 Step 1
　　　　Set txt = Me.TextBoxes.Add(10, 10, 10, 10)
　　　　txt.Delete
　　Next i
　　Application.ScreenUpdating = True

　　Stop
　　Set txt = Nothing
　　Exit Sub

ErrHandle:
　　Stop
　　Resume
End Sub

検証で500個ずつ置くことに意味はないと思ったので1つずつにしています。

65536回目あたりで2つ目の STOP に飛びかと期待していたら、
10万回でエラー起きずに1つ目の STOP に来てしまいました。

下記はMicrosoftサイトのExcel2003の仕様と制限のページです。
特にオートシェイプ等の制限については書かれていません。
http://office.microsoft.com/ja-jp/excel-help/HP005199291.aspx?CTT=1

テキストボックスのNameプロパティで、毎回名前を振っていってあげても実行時エラーが出ますか？

[3] mixiユーザー 10月10日 01:33

ウッカリさんだったかもしれません(私のことです)

エラー内容、「実行時エラー1004　TextBoxクラスのSelectメソッドが失敗しました」ですね。

内容の通りならテキストボックスの生成ではなく、Addメソッドに続けて記載されているSelectメソッドが失敗したようです。

詳細な原因はわかりませんが、コードで書かれているSelectメソッドは必要なのでしょうか？

>　（３）新しい計算結果をテキストボックスを作成して表示する
が、書いてある通りなら
ActiveSheet.TextBoxes.Add(Y,X,SY,SX).Select　ではなく
ActiveSheet.TextBoxes.Add(Y,X,SY,SX)　で良いと思うのですが・・・

[4] mixiユーザー 10月10日 10:39

似たような状況を再現するようなマクロを作って試してみたら、確かに、65536辺りまで作ったテキストボックスをSelectしようするとエラーになってしまいますね。
これって、自動的に付けられるオブジェクト名とかに問題が生じてるのかなぁ……。なんなんだろ。

で、おそらく、テキストボックス内のテキストを設定するために、Selectしていらっしゃるのかもしれませんが、よっちゃんさんのおっしゃる通り、Selectしないで、Setで、

　Set txtBox = ActiveSheet.TextBoxes.Add(Y,X,SY,SX)

みたいにしておいて、

　txtBox.Characters.Text = Range("A1").Value

とすれば、テキストも代入できますよね。

しかし、この問題、根本的な回避方法はないのかなぁ。気をつけなければならない問題であることは確かだと思う。

[5] mixiユーザー 10月10日 20:51

>>[2]

コメントをありがとうございます。
いろいろ検証していただき、本当にありがとうございます。
ご指摘いただいたように、Setを使った構文に変えたところ、エラーがでなくなりました。
大変助かりました。

Selectを使ったマクロは、元々、マクロの記憶を使って「テキストボックスを作りテキストを代入する」という記録をさせ、これをもとに修正して作ったものです。

よっちゃんさんがご指摘の
＞ActiveSheet.TextBoxes.Add(Y,X,SY,SX).Select　ではなく
＞ActiveSheet.TextBoxes.Add(Y,X,SY,SX)　で良いと思うのですが・・・
も試したのですが、構文エラーになってしまいました。

65536辺りでエラーが出たことから、16bitの限界があるのでは？と勝手に思い込んでいました。

エラーの原因がいまいちはっきり理解できていないのは、ちょっと気になるのですが、今回の件では、今までSetをあまり使っていなかったので、大変勉強になりました。

[6] mixiユーザー 10月11日 00:50

>>[5]

とりあえずエラーは回避できたようでよかったです。
根本的な原因は私もわかっておらず、モヤモヤしています。

> よっちゃんさんがご指摘の
> ＞ActiveSheet.TextBoxes.Add(Y,X,SY,SX).Select　ではなく
> ＞ActiveSheet.TextBoxes.Add(Y,X,SY,SX)　で良いと思うのですが・・・
> も試したのですが、構文エラーになってしまいました。

すみません、これは私の書き方が不親切だったようですm(_ _ )m

ごめんなさいついでに補足しつつ説明します。
(飲み会の後ですので変な日本語があってもご容赦)

引数を受け取るたいていのAddメソッド(Addに限らず、関数とかもですが)は返り値を返します。
ただ実行するだけなら引数をカッコでくくる必要はない場合が多いです。
また、返り値がある場合はしっかり受け止めてあげないといけない場合があります。

ActiveSheet.TextBoxes.Addは、生成されるテキストボックスへの参照を返します。

そして、引数をカッコ付きで書く場合には Set xx = ・・・のように
きちんと返り値を受け止めてあげる必要があげなければなりません。

私が回答した意図としては、返り値は必要ないので
ActiveSheet.TextBoxes.Add Y,X,SY,SX　　のようにカッコを付けない必要があります。

もしくは、「ただ呼ぶ(実行する)だけだよ」ということで
Call ActiveSheet.TextBoxes.Add(Y,X,SY,SX)　　のようにしてあげなければなりません。

もうちょっと掘り下げてみましょうか。

カッコ付きの関数やメソッドは常に結果を受け止めてあげなければならないのか？
　↑
そうでもないのです。

Workbooks.Open ("開きたいワークブックへのパス")
これはエラーになりません。
参照は返すので、
Set xxx = Workbooks.Open ("開きたいワークブックへのパス")
で xxx(Workbook型変数が望ましい) にはちゃんと開いたワークブックへの参照がセットされますし
MsgBox Workbooks.Open ("開きたいワークブックへのパス").Name
のようなコードを書いてもちゃんと動きます(開いたブックのファイル名が表示されます)。

一概に「こういうメソッドや関数の場合はこうだ」とは言えないのですが、
「カッコ付き引数の場合は何かを返す」と覚えておけばよいと思います。

引数をカッコ付きで渡さない場合は関数でも Call や = が要らない場合もあります。
Dir "C:\"
Left "abc", 1
LCase "abc"
どれも結果を返すのにエラーになりません(結果的に何も起こらないので意味ないですけど)。

でも関数ではカッコ付きで引数を渡さないといけない場合が多いです。
Len "abc"
CLng "1"
どちらもカッコ付きで引数を渡す必要があり、結果は = で受け取ってあげないといけない。

誰もが使ったことあるであろうMsgbox関数は？
MsgBox "a"　　　　　　　←エラーにならない
MsgBox ("a")　　　　　　←値(vbOk)を返すがエラーにならない
MsgBox ("a", vbYesNo)　←値(vbYesかvbNo)を返す。結果を受けてあげないとコンパイルエラー。
MsgBox "a", vbYesNo　　←値(vbYesかvbNo)を返すがエラーにならない

こうやって例を挙げていると、適当な言語だと改めて実感します・・・

[7] mixiユーザー 10月11日 12:34

>>[6]

詳しい解説をありがとうございました。m(_ _ )m
カッコを外して呼び出すということには思い至りませんでした。

そういえば、引数が２つ以上ある Subプロシージャを呼び出すとき、カッコをつけて呼び出していてエラーで動かず、困惑したことがありました。（Callは使っていませんでした。）
ネットで調べて、カッコを外して呼び出せばよいということを知り一応解決したのですが、引数が１つの場合と２つ以上で呼び出し方が違うのは、ちょっと違和感がありました。

VBAの世界も奥が深いですね。
というか、ちゃんと体系的に勉強していない私の知識不足なのですが...(汗）

ところで、
ActiveSheet.TextBoxes.Add Y,X,SY,SX　でテキストボックを生成した後、
そのテキストボックスに文字を設定するためには、テキストボックスをSelectするか、参照を設定しなければいけないと思うのですが、簡単なやり方が思いあたりません。
（Setで参照を得るというやり方は別にしてという話です。）
なにかいい方法があるのでしょうか？（例えば作るときに合わせてテキストを設定するとか。）

余談ですが、実は、今回のマクロは、原案を１０年以上前、Excel95の時代に作ったもので、その後、97以降のVBA仕様の見直しで一部見直しましたが、概ね、そのままのコードで使ってきていたものです。
今回、結果のテキストボックスを多量に表示するようにしたのですが、これまで使ってきたものは、多くても５０程度の表示（結果の数はユーザーが求める解の数になっており自由に変更可能な仕様になっています）でしたので、これまで問題が表面化しなかったようです。
とんだ時限爆弾マクロだったというわけでした（苦笑）

[8] mixiユーザー 10月13日 15:25

aozoramameさん

> なにかいい方法があるのでしょうか？

いくつか方法は考えられますが、回答するのが複雑な心境です。

TextBoxes.Add(引数省略).Characters.Text = "おすすめできない"
のようにTextBox追加してText設定するだけしかできないコードは書けますが
私としては拡張性を犠牲にする上記のようなコードは好きじゃなかったりするので
Setして参照を自由に扱う以上の良い方法は知らない＆思いつかないです。

ところで、下記の発言に興味があるので教えてください。
　　　　　　↓
> Setで参照を得るというやり方は別にしてという話です

なぜ私が提示した方法は別にして他でいい方法がないのか知りたくなったのでしょう。

[9] mixiユーザー 10月13日 19:38

>>[8]

変な質問をしまして、すいませんでした。

投稿した時点で、TextBoxes.Addでテキストボックスを作った後、どのようにして作ったテキストボックスにアクセスするのかなと、やり方が思いつかなかっただけなのです。
深い意味はありませんでした。

投稿した後に、最後に作ったテキストボックスなので、TextBoxes.Countで最後のテキストボックスにアクセスできるとは思ったのですが、作りっぱなしで後から探すというのは、合理的なやり方ではなく、ご指摘のとおり、あまり意味がない質問でした。

[10] mixiユーザー 10月13日 20:40

>>[9]

意味がない質問ではないと思いますよ。

作成済みの「任意のテキストボックス」のプロパティを変更することだってあると思います。
その場合は、TextBoxをAddする際に、Nameプロパティで、特定の名前を設定しておけばいいのでは？
そうすれば、

ActiveSheet.TextBoxes("任意の名前").Characters.Text = "文字列"

というようにいつでも変更することができますよね。

[11] mixiユーザー 10月13日 21:40

>>[10]

コメントありがとうございます。

＞その場合は、TextBoxをAddする際に、Nameプロパティで、特定の名前を設定...

話がそれて申し訳ないのですが、複数のTextBoxに同じ名前が設定できるよう
ですが、名前で特定した場合にどのような動きになるのか以下のコードで
実験してみました。

結果、エラーは出ずに、最初に作ったTextBoxに値が設定されました。

ActiveSheet.TextBoxes.Add(100, 100, 100, 100).Name = "P"
ActiveSheet.TextBoxes.Add(200, 200, 100, 100).Name = "P"
ActiveSheet.TextBoxes("P").Characters.Text = "ABC"

特定の名前というのがポイントですね。

[12] mixiユーザー 10月13日 21:59

>>[11]

そうなんですよ。そこら辺についても言及しようかと思ったのですが、自分でもきちんと試してみていないので、省略しました。

で、同じ名前のTextBoxは、そういう動きになるんですね。なるほど。
Excel 2003では試してないですが、Excel 2007以降でテキストボックスを作成すると「テキストボックス 1」のような名前が「名前ボックス」に表示されるのですが、Nameプロパティで確認すると「TextBox 1 」になってたりするんですよね。
ここら辺のナゾも自分もまだ未解決のままです。

[13] mixiユーザー 10月13日 22:29

オブジェクトのNameはユニークに扱うべき代物で、重複を許すべきでは無いと思います。
Excel2003で試したところ、TextBoxesプロパティを利用せず、ShapesプロパティからアクセスしてAddTextBoxメソッドでテキストボックスを追加する分には、Nameの重複設定時にはちゃんとエラーが起きてくれてNameのユニークさが保証されるようですね。

直感的に記述できるように色々と増築工事された結果、振舞いがちぐはぐになっている
気がします。

[14] mixiユーザー 10月14日 17:05

aozoramameさん

ご回答ありがとうございました。

> 名前で特定した場合にどのような動きになるのか以下のコードで実験してみました。
> 結果、エラーは出ずに、最初に作ったTextBoxに値が設定されました。

なるほど・・・。
Excel2003・2007・2010・2013全てバージョンで同様の結果となりました。

今回の件で、例えばアクティブなシートにあるテキストボックスにループでアクセスする際にはこれまで下記の Sample1() のように書いていましたが、どうやら Sample2() のように書いたほうが良さそうです。

Sub Sample1()
　　Dim t As TextBox

　　For Each t In ActiveSheet.TextBoxes
　　　　'何かの処理
　　Next
End Sub

Sub Sample2()
　　Dim i As Long
　　Dim t As TextBox

　　For i = 1 To ActiveSheet.TextBoxes.Count Step 1
　　　　Set t = ActiveSheet.TextBoxes.Item(i)
　　　　'何かの処理
　　Next i
End Sub

[15] mixiユーザー 10月14日 20:54

>>[13]

コメントありがとうございます。
>オブジェクトのNameはユニークに扱うべき代物で、重複を許すべきでは無いと思います。
まさにそうですね。普通に考えておかしな振る舞いですよね。
バグ発生の元だと思います。
ちなみに手作業で名前を付ける場合は、同じ名前は付けられないようになっていますね。

ログインすると、みんなのコメントがもっと見れるよ