エクセルの処理が遅いのはPCのせいなのでしょうか？

質問

エクセルのユーザーフォームにコンボBOXを20個ほど作り、入力ボタンを押すとそれぞれのセルにデータを転記するように下記の要領でプログラムを書いたのですが、処理が驚くほど遅いのです・・・。PCはIBM,PC330でスペックはP130でメモリ80MBです、同じ物をP(3)450のPCで動かしてみた所かなりスピードUPしますが、やはり遅いような気がします、PCの性能でしたらあきらめて新型を購入したいと思うのですが、ひょっとしてプログラムが原因で処理が遅いのでしたら処理スピードの向上に良い方法を教えて下さい。
Private Sub 入力_Click()
With Worksheets("その他データ")
.Cells(celrow, 2) = ComboBox1.Text
.Cells(celrow, 3) = ComboBox12.Text
　　　　　　　　　　　　・
　　　　　　　　　　　　・　
　　　　.Cells(celrow, 18) = ComboBox10.Text
.Cells(celrow, 19) = ComboBox11.Text

With UserForm2
.ComboBox1.Text = ""
.ComboBox2.Text = ""
　 ・
　　 　・
.ComboBox18.Text = ""
.ComboBox19.Text = ""
End With
　celrow = celrow + 1
　　SpinButton1.Value = celrow
　TextBox2.Text = celrow
End With

End Sub

回答No.4

nishi6 · ベストアンサー

１データにつき同一範囲を参照する10個のVlookupがあり、
これが1日に100件分用意されており、1月分として31日分ある。合計、10×100×31＝31000個のVlookupがあります。

入力ボタンを押すことにより、Vlookupで参照するデータの11項目を書き換えにいきますが、1項目の変更で31000回再計算されています。都合、341000回Vlookupを行っています。
これが原因ですね。Vlookupは特に遅く感じられますしほとんど止まった状態になると考えられます。

その他の問題点として、
・月が変わるごとに初期化されると考えられますが、１件目の入力はダイアログボックスからはできない？
・１日の集計をしていても月末まで計算（累積）してしまっている
・苦労がExcelで『集計』することよりも『入力』になっている。

解決方法ですが、こういう日々増加していくデータをExcelで扱うのはかなりのテクニックがいります。
Accessを少し勉強すれば、苦労している入力部分、増加していくデータの集計、日計、月間計等は驚くほど簡単に実現できると思います。検索も簡単にできます。

Excelでの解決策としては、この作りのまま修正するとすれば、
日計表・集計表のＣ列にデータが入力されたら、WorkSheet_Chageイベント内で検索し日計表・集計表には検索結果を『値』として代入する方法が考えられます。

速度に関係していないと思われる箇所は余り見ていません。Vlookupを5行分くらいにしたら普通に表示されました。ザッと見た感じですが以上です。

お礼

Vlookupでしたか！構造的な欠陥があったんですね！
やっぱりアクセスを使った方が良かったんですねェー。
（良くわかつてる人にはすごくあたりまえだったり・・するんでしょうね・・タブン）
しかしnishi6さんは、すごいですね！！神様のように思えます。
『値』として代入する方法は、何とか参考書等で勉強してみます、わからない時はもう1度投稿したいと思います。
ナガナガ大変ありがとう御座いました。
以上

回答No.3

nishi6

ハフマンコード破損（異常）のメッセージが出ます。解凍できません。

補足

申し訳ありません
今度は確実に解凍できると思います（2度チエックしました）。
http://www.d2.dion.ne.jp/~souken/New.LZH
なにとぞ、よろしくお願い致します。

回答No.2

nishi6

まず、ユーザーフォームを開いた時、『celrow』がどの時点で定義（値の確定が）されているか教えて下さい。
UserForm_Initialize()などでTextBox2にデータの最終行+1を入れるのかな・・・と思いますが。

TextBox2_Change()のエラールーティンは
　Worksheets("その他データ").Cells(celrow, 2).Activate　でcelrow=0の場合、
　　　これは、SpinButton1のMin=0のため？(Min=1で回避？)
　SpinButton1のMin、Maxの範囲外の値をTextBox2に入力した場合
に回避するためにあるようです。従って、極端に遅い原因とも考えにくいです。

次に、SpinButton1とTextBox1、TextBox2の関係が不明です。TextBox2はSpinButton1の値表示と値入力用みたいですがTextBox1はどのように関係しています？
SpinButton1_Changeとかもあれば気になります。このあたりで何か起きている？

最後に、ComboBox1の1個だけで動かした場合も時間がかかるでしょうか。他のソフトを止めて試してみてはどうでしょうか。

補足

何がなんだか解らなくなってきたので、
出来ましたら、そのものを見てみ頂きたいのですが？下記のWWWに
UPしましたよろしくお願い致します。
http://www.d2.dion.ne.jp/~souken/New1.LZH
（413KB）です。データに個人情報がありましたので200人分削除した所
さらに（再計算と表示され）ものすごく遅くなりました。もう僕には手におえません、トホホ・・。

マクロでユーザーフォーム2です。

回答No.1

nishi6

同じ（ような）フォームを作って試してみました。（20個のComboBox、SpinButton、TextBox、CommandButton各1個）
Pentium100、メモリ32MBでしたが入力ボタンを押した瞬間に処理は完了しました。最新のPCで行う必要もないような速度です。

何かコードに原因があるとすると、原因の調べ方ですが、
　（１）セルに書き込む部分 .cells(・・・ の部分をコメント行にしてみる
　（２）コンボボックスをクリアしている部分 .ComboBox・・・ の部分をコメント行にしてみる
　（３）スピンボタン値の変更の部分をコメント行にしてみる
　（４）テキストボックス値の変更の部分をコメント行にしてみる
を順に行ってみて速度を比較してみてはどうでしょうか。どこが遅いか分かるかもしれません。
ComboBox1_Change、TextBox2_Changeなどがあって、足を引っ張っている可能性があります。
処理を何回もグルグル行っていた経験は何度もあります。

また、シートの方の問題として、再計算を行っている可能性はないでしょうか。再計算の負荷は内容が分からないので何とも言えませんが・・・

一般的に、セルに書き込む時には
　Application.ScreenUpdating = False で画面更新を止めて、書き込みが終ったら
　Application.ScreenUpdating = True　で更新させれば速くなりますが、この質問には関係ないかもしれません。計算を手動にしてみると効果があるかもしれません。

コントロールのクリアについては、
.ComboBox1.Text="" より、For Each を使ってクリアした方が速いかもしれませんが劇的には速くならないでしょう。

参考に投稿します。

お礼

>TextBox2_Changeなどがあって、足を引っ張っている可能>性があります。
コレ見たいです！
教えていただいた（1）～（4）は、コメント行にして見たのですが大差なしでした。
ところが 下記の行を消すといきなりエラーになります
デイバックを押すと「.Cells(celrow, 2)＝ComboBox1.Text」の行が黄色に成ります、その都度「Private Sub TextBox2_Change()」に行く処理が足を引っ張ってるのではと思うのですが、どのようにしたら良いか解りません？僕の考えがまったく見当違いでなければ、
補足お願い致します。
Private Sub TextBox2_Change()
' On Error GoTo TRAP
celrow = TextBox2.Text
Worksheets("その他データ").Cells(celrow, 2).Activate
SpinButton1.Value = TextBox2.Text
' Exit Sub

'TRAP:
' If TextBox2.Text <> "" Then
' MsgBox "入力が間違っています。やり直してください。"
' TextBox1.Text = ""
'End If
TextBox2.SetFocus
End Sub

補足

早速ご回答いただきありがとう御座います。
やっぱり何かが、足を引っ張っているんですね！
ありがとう御座います。
今から試してみます、結果をご報告いたします。