エクセル2010、VBAや関数について

質問

Private Sub CommandButton1_Click()
Worksheets("商品マスタ").Activate
Application.Calculation = xlCalculationManual

If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
ActiveWindow.ScrollRow = 3
Range("AI1:AQ4").ClearContents
Range("AI1:AQ4").NumberFormatLocal = "@"
Range("AJ2:AK2").Value = Range("B2:C2").Value
Range("AL2:AM2").Value = Range("D2").Value
Range("AO2").Value = Range("E2").Value
Range("AP2").Value = Range("V2").Value
Range("AQ2").Value = Range("W2").Value
Range("AN2").Value = Range("D2").Value
If Me.TextBox1.Value <> "" Then ' コード
Range("AK3").Value = "*" & Me.TextBox1.Value
End If
If Me.TextBox2.Value <> "" Then ' メーカー
Range("AL3").Value = "*" & Me.TextBox2.Value & "*"
End If
If Me.TextBox3.Value <> "" Then ' <--シリーズ
Range("AM3").Value = "*" & Me.TextBox3.Value & "*"
End If
If Me.TextBox4.Value <> "" Then ' <--サイズ
Range("AN3").Value = "*" & Me.TextBox4.Value & "*"
End If
If Me.TextBox5.Value <> "" Then ' 入荷日
Range("AJ3").Value = Me.TextBox5.Value
End If
If Me.TextBox9.Value <> "" Then ' 仕入れ先
Range("AP3").Value = Me.TextBox9.Value
End If
If Me.TextBox12.Value <> "" Then ' 単体価格
Range("AQ3").Value = Me.TextBox12.Value
End If
If Me.TextBox6.Value <> "" Then ' 在庫数
Range("AO3").Value = Me.TextBox6.Value
End If
If Cells(3, Columns.Count).End(xlToLeft).Column > 34 Then
Range("A2:W" & Rows.Count).AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Range("AI2").CurrentRegion, Unique:=False
End If
Range("AI1:AQ4").ClearContents
ActiveWindow.ScrollColumn = 4
Range("A2").Activate
Application.Calculation = xlCalculationAutomatic

End Sub

このようなマクロを組んでいるのですが、とても反応が遅いのですが、
シートにはA4-AG2000にデータが入っていて、
G4-U2000には

=SUMIFS('[在庫.xlsx]02'!$AD:$AD,'[在庫.xlsx]02'!$AQ:$AQ,$A421,'[在庫.xlsx]02'!$AS:$AS,$F$2,'[在庫.xlsx]02'!$AT:$AT,G$2)

このような関数が入っております。

これが原因で、動作が遅くなっているのでしょうか？

行の挿入等もとても遅いのですが、
G-U列の関数をやめてVBAで転記してから、検索をかけたら、早くなるのでしょうか？

G-U列には関数での表記しかわからなかったため、関数をいれております。

解決法があれば教えてください。

回答No.2

sora1515 · ベストアンサー

>>回答１補足
>>・[在庫.xlsx]指定抜き
ブック名指定を外しついでにSheet名指定も未指定にします。（自分のSheetを見るようになります）実行中簡易版で走らせる為に変えてます。
=SUMIFS('[在庫.xlsx]02'!$AD:$AD,'[在庫.xlsx]02'!$AQ:$AQ,$A421・・
↓
=SUMIFS($AD:$AD,$AQ:$AQ,$A421・・

>>・TextBoxをセルに変更
テキストボックスを作るのがめんどうだったので実行中A1に置き換えました。これも簡易措置。
If Me.TextBox1.Value <> "" Then
↓
If Range("A1").Value <> "" Then

ここで思ったのですが自分で提示したものだしもう少し理解してから質問した方がいいのではないでしょうか・・
ブック指定とか値があるかのIF文とか基本中の基本なので・・・
まずはどこが遅いかの特定が先です。

全ての行にデバッグ行を入れてどの処理が時間が掛かったか見てください。
-最初の切り分け-（デバッグ）
　　　・全体的に遅い→自動計算を疑う。（自動計算をやめて実行してみる）（搭載メモリなど環境も疑う）
　　　・特定行が遅い→対策が絞りやすい。

-ブック読み込みが遅ければ-
ps.のように指定をやめたり変えてみる（ネットワーク遅延や参照ブックに疑いをかける）
-利用しているTextBoxが遅ければ-
ps.のように参照を変えてみる
-全体的に遅ければ-
psのように関数を疑うorメモリや参照範囲も疑う
・
・

補足

説明不足のようでした。

在庫ブックにしか乗っていない情報を読み取っているため、この関数を入れないと不可能だと以前教えて頂きました。

VBAに関しても組んで頂いたため、あまり理解しておりませんでした。

デバッグと言うのもあまりわかっていないため、

これでは解決してないため、質問を変えて投稿してみようと思います

ありがとうございました

回答No.1

sora1515

関数の自動計算をしてますが必要のないタイミングを見極めてこまめに止めてみてはどうでしょうか？
Application.Calculation = xlCalculationManual

あとどこで遅いかはステップ実行なりデバッグ入れたりすればどの処理が遅いかすぐわかりますよ。
↓要所にデバッグ行追加の例
Range("A1").Value = "A : " & Format(Now, "hh:nn:ss")
処理A～
Range("A2").Value = "B : " & Format(Now, "hh:nn:ss")
処理B～

あと画面更新を止めておくとかもやってはどうでしょうか。
Application.ScreenUpdating = False 'マクロ開始時
Application.ScreenUpdating = True　'マクロ終了時

ps
こちらの環境では提示マクロを試したら一瞬で終わります。
・[在庫.xlsx]指定抜き
・TextBoxをセルに変更
・SUMIFSを代替SUMIFに変更
・A1～AG2000まで関数(SUMIF)あり
・End If追加

補足

ご回答ありがとうございます。psの部分について、どう変えたら早くなりますか？コードを教えて頂くことは可能でしょうか？