ExcelVBA(2003)でドロップダウンリストを設定できる数に制限ありますか？

質問

はじめまして、yasu_131と申します。
掲題について質問させてください。（長文失礼します）

やりたいことは、「Excel VBAを使って、ある固定列（例えばB列）に対してドロップダウンリストによる入力規則を適用したい」ということです。
諸事情により、ExcelVBAを直接扱うのではなく、JScriptを使ってExcelVBAを操作するという制約がありますが、
結局、書きたいコードや実行したい処理内容はExcel VBAそのものです。
開発環境、実行環境は Excel 2003 です。

困っていること：
実際に、10列×10行程度のデータを用意して、B列へドロップダウンによる入力規則を適用することはできています。
しかし、実際の運用で用いるデータは23列×2500行程度あり、このデータに対してExcelVBAを実行すると
なぜかB列の1023行目までしかドロップダウンリストが適用されません。1024行目以降は普通の（入力規則が適用されてない）
セルなのです。。

セルの中身のデータに問題があるかもしれないと思い、ためしに1～100までの数値だけが入力された列（E列）に対して
実行してみましたが、やはり1023行目までしかドロップダウンリストが適用されませんでした。
なにやら、1023（1024）という数値にExcelの内部的な制約を感じていますが、MS社のサイト等では該当する説明は
ありませんでした。

この現象について、原因の糸口や解決策（あるいは回避策、代替策）を模索しています。
皆様のお知恵をご教授いただければと思います。

※追加検証１：ドロップダウンリストを適用する列を増やす（例えばB列とE列を同時に適用する）と、
　B列は1023行目まで、E列は202行目までしかドロップダウンリストになりませんでした。

※追加検証２：Excel 2007 環境では、2列（B列とE列）へドロップダウンリストを適用しても
　末尾行（2500行目）まで正常に入力規則が設定されました。

以下はその際のコードです。

// 初期化
this._objExcelApp = new ActiveXObject("Excel.Application");
this._objWorkbook = this._objExcelApp.Workbooks.Add();
this._objExcelApp.Selection.Style = "Normal";
this._activeSheet = this._objWorkbook.ActiveSheet;

// 外部データのインポート
//（実際のコードではCSVから必要なデータを抜き出してセルに埋めています。）
for (i = 1; i < 2500; i++) {
for (j = 1; j < 23; j++) {
with(this._activeSheet) {
Cells(i,j) = 'AAAAA';
}
}
}

// 入力規則の適用（B列に対するドロップダウンリストの設定）
var _row;
var _xlDown = -4121;
var _xlValidateList = 3;
var _xlValidAlertStop = 1;
var _xlBetween = 1;
var _maxRow = this._activeSheet.Range("A1").End(_xlDown).Row;
for (_row = 1 ; _row <= _maxRow ; _row++) {
// B列に対するドロップダウンリストの設定
with(this._activeSheet.Range("B" + _row)){
with(Validation) {
Delete();
Add(this._xlValidateList,this._xlValidAlertStop,this._xlBetween,"AAA,BBB,CCC,DDD,EEE");
IgnoreBlank = true;
InCellDropDown = true;
}
Locked = false;
}
// E列に対するドロップダウンリストの設定
with(this._activeSheet.Range("E" + _row)){
with(Validation) {
Delete();
Add(this._xlValidateList,this._xlValidAlertStop,this._xlBetween,"111,222,333,444,555");
IgnoreBlank = true;
InCellDropDown = true;
}
Locked = false;
}
}

// 実際のコードではここで保存処理はしていませんが、投稿のためにここで終了させています。
var fname;
fname = this._objExcelApp.GetSaveAsFilename()
this._objWorkbook.SaveAs(fname);
this._objWorkbook.Close();

回答No.2

onlyrom · ベストアンサー

下記マイクロソフトのサイトに、入力規則、２０４５、という数字があります。

このサイトでは、不連続セル、となってますが。。
一応の覗いてみてください。

http://support.microsoft.com/kb/436987/JA/

試してみましたが、どうやら、一気に設定するその制限に引っかかるようです。
で、例えば、６０００セルセットしたければ、
１～１０００セットして、保存
１００１～２０００セットして、保存
・・・・・・・・・
５００１～６０００セットして、保存
面倒でも制限範囲個数でセット、保存を繰り返してはどうでしょうか。

当方で試してところこれで上手くいきました。

お礼

MS社サイト情報ありがとうございます。覗いてみました。
また、回避策も提示して頂いて、大変勉強になりました。

実は、onlyromさんの「一気に設定する」というキーワードがひっかかって、私なりに色々と泥臭いコードを試してみました。
すると、以下のようなコードにしてみたところ、無事に2500行目（最下行）まで入力規則のプルダウンを適用することができました。

for (_row = 1 ; _row <= _maxRow ; _row++) {
// B列に対するドロップダウンリストの設定
with(this._activeSheet.Range("B" + _row)){
with(Validation) {
Delete();
Add(this._xlValidateList,this._xlValidAlertStop,this._xlBetween,"AAA,BBB,CCC,DDD,EEE");
IgnoreBlank = true;
InCellDropDown = true;
ShowError =false; ←ココでエラー通知をひとまず無効化して入力規則を適用
}
Locked = false;
}
with(Validation) {
ShowError = true; ←適用が済んだあとで、エラー通知だけ有効に戻す
}
}

Excelはデフォルトでいろいろな機能を自動的に有効にしていますので、明示的に機能を無効化するロジックを付け加えてみた次第です。

onlyromさんのレスが解決に向けたアプローチのきっかけになりました。
ありがとうございました。

回答No.1

imogasi

Sub test01()
On Error GoTo p1
Worksheets("sheet1").ComboBox1.Clear
' Exit Sub
For i = 1 To 10000
Worksheets("sheet1").ComboBox1.AddItem i
Next i
p1:
MsgBox i
End Sub
のようなのを１００００の部分を増やしてやってみたら（まず論より証拠）。

お礼

助言いただき、ありがとうございます。
お示しいただいたような処理を試してみましたが、2500行すべてにコンボボックスをつけることはできました。

その後、いろいろ試行錯誤した結果、onlyronさんへの回答に書いたように無事うまく入力規則を適用することに成功しました。

論より証拠。壁にぶつかってネットに答えを求め始めると、つい忘れがちです。（＾＾；
ありがとうございました。