3巡目でエラーになるマクロ 間違いを教えて下さい。

Question

マクロの間違えている部分を教えて下さい。 下記のマクロは同ブックで別のマクロからcallしてます。
別のマクロでもfor nextで繰り返し処理をするのですが、2巡目のcallまで下記のマクロは機能しています。
3巡目の途中でエラー(1004 add メソッドは失敗しました。)が出ます。 worksheets.Add after:=worksheets(worksheets.count)が黄色になります。
ですが、デバッグ→継続→デバッグ→継続を繰り返すとシートは追加されます。

sub new()
Dim i ,lr as long
lr=thisworkbook.worksheets(1).cells(rows.count,1).end(xlup).row
for i = 6 to lr
worksheets.Add after:=worksheets(worksheets.count) activesheet.name = "s"&thisworkbook.worksheets(1).cells(i,1).value next i
end sub

Answer 1

エラーで止まったあと、デバッグ→継続→デバッグ→継続では動作するとのことですから、やはりシート数が多い気がします。
近下シートは削除されている、とのことですが、呼び出し元で、削除処理の後、ブックを上書き保存したり、DoEvents入れるなどしてみてはいかがでしょうか。
あと、直接は関係ないのですが、Newというプロシージャ名って使えない気がしますが、提示されたコードは実際に使われているものでしょうか？

Comments

[mavic]

ご回答ありがとうございます。
上書き保存とdoeventsの両方を試してみたいと思います。

すみません、実際にはnewではなくnewsheetです。

[mavic]

このマクロは別のマクロからcallしてるのですが、doeventsをcallする全てのマクロに追加したところ動くようになりました。
sub newsheet()だけの問題ではなかったようです。
不要なdoeventsが分からないのでそのまま使おうと思います。

Answer 2

同一ブックの別のマクロからこの sub NewをCALLしていて
２回目まではCALLできるのに３回目でエラーになるということですね。

Sub Newは１度CALLされると該当workbookの１枚目のワークシートを参照して
そのシートの６行目以降使用している行の分だけシートを追加するような仕組みのようですが、
（例えば１０５行まで使用していたら６～１０５の１００枚シートが追加される）
追加されるシートが多すぎて３巡目でマクロ実行中のPCのメモリで取り扱える範囲のシート数を
超えてしまっているということはないでしょうか？

Comments

[mavic]

ご回答ありがとうございます。
1巡目で追加したシートは2巡目の追加前に全て消し(元からある先頭2枚のみ残ります)、2巡目で追加したシートも3巡目の追加前に全て消しているので、シートが多すぎる事はないと思います、、

[beadv]

Excel.exeの内部の仕様は当然わかりませんが、シートを削除したとたんに、そのシートが使用していた
メモリが解放されるわけではなく、メモリが不要になるタイミングとメモリを開放するタイミングには
たいていタイムラグがありますので、当該処理のCALL前にいったんシートが削除されていても、メモリが
不足することはあり得ると思いますよ。当該処理を呼び出す元もマクロということですから、呼び出しと
呼び出しの間にいろいろと処理をされていると思いますので、２巡目が呼び出されるときと３巡目が
呼び出されるときで内部のメモリ使用状況が異なることは十分考えられると思います。
ただ、仮にそうだとしても、それを確認／検証する手段がちょっと思いつかないので、判断が
難しいところではあるのですが。

[mavic]

そうなのですね。マクロの組み直しを検討します、、

[jinoji]

Debug.Print Worksheets.Count
とか入れて確認してみるとか