高速化したいのですが、私には難問です。

Question

EXCELのシートにVBAから関数を記載させてます。
SELECTしてループの繰り返しのコードを高速化する良い方法がありましたらご教示願えれば幸いです。

前置きが長くなりますが
下記のコードを別途　GetRowsStepN　関数（指定間隔で範囲指定）を使っていくらか早くしました。（他にもっと良い方法があるかもしれませんが？例えば一括で範囲指定しそれぞれに一括で書き込む等。）
同じような流れで今回の課題のコード（すいません。ずっと下の方に記入してます）を早くしたいのですが、私には思いつきませんでした。
助けて下さい！

       GetRowsStepN( _
        oRange_Target:=ActiveSheet.Range("D11:D" & SN * 2 + 10), _
        iStart:=1, iRowCount1:=1, iRowCount2:=1).FormulaR1C1 = _
        "=DATE(R6C15,R6C18,(ROW(R[-8]C[-3])-ROW(R1C1))/2)"
                 
【GetRowsStepN　関数】←省略します
内容は
N行おきの範囲を取得する関数です。(引数チェックなし)
'iStart       : 開始行
'iRowCount1   : 選択行数
'iRowCount2   : 間隔行数
としてます。

前置きが長くてすみません。
【これが本題のコードです。】←ここからが早くしたいコードです。

ｋ、BN、SNは変数（数字です）

'**1段目** 
k = 0
 For j = 1 To SN * 2 Step 2 
k = k + 1
   Range("H" & j + 10).Select
   ActiveCell.FormulaR1C1 = _
        "=IF(1<=COUNTIF('[営業所配置DATA" & BN & "月.xls]" & k & "'!R2C2:R100C30,R5C27),LARGE(INDEX(('[営業所配置DATA" & BN & "月.xls]" & k & "'!R2C2:R100C30=R5C27)*'[営業所配置DATA" & BN & "月.xls]" & k & "'!R2C9:R100C9,),IF(COUNTIF('[営業所配置DATA" & BN & "月.xls]" & k & "'!R2C2:R100C30,R5C27)=1,1,3-1)),"""")"
       
    Range("J" & j + 10).Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(RC2,'[営業所配置DATA" & BN & "月.xls]" & k & "'!R2C1:R100C30,11,0)),"""",VLOOKUP(RC2,'[営業所配置DATA" & BN & "月.xls]" & k & "'!R2C1:R100C30,11,0))"
                
Next

'**2段目**
k = 0
   For j = 1 To SN * 2 Step 2
k = k + 1
    Range("H" & j + 11).Select
    ActiveCell.FormulaR1C1 = _
        "=IF(2<=COUNTIF('[営業所配置DATA" & BN & "月.xls]" & k & "'!R2C2:R100C30,R5C27),LARGE(INDEX(('[営業所配置DATA" & BN & "月.xls]" & k & "'!R2C2:R100C30=R5C27)*'[営業所配置DATA" & BN & "月.xls]" & k & "'!R2C9:R100C9,),IF(COUNTIF('[営業所配置DATA" & BN & "月.xls]" & k & "'!R2C2:R100C30,R5C27)=1,1,3-2)),"""")"
          
    Range("J" & j + 11).Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(RC2,'[営業所配置DATA" & BN & "月.xls]" & k & "'!R2C1:R100C30,11,0)),"""",VLOOKUP(RC2,'[営業所配置DATA" & BN & "月.xls]" & k & "'!R2C1:R100C30,11,0))"
              
              
Next

以上ですが、上段と下段（奇数、偶数行）では、入力する関数が若干違うためFor j = 1 To SN * 2 Step 2でそれぞれ処理させてます。
私の悩みは変数　ｋ　の処理です。早く出来たコード（式が共通）と違い　K　の扱いは結局ループにせざるを得ないのでしょうか？（としてもどのような？）
例示では各段の各列は2列にしてますが、実際40列近くありますので（行の　SN　は30程度ありますから40×30×2のSELECT）、重いのです。（20秒位かかってしまいます）
理想は一括で範囲呼び込みして一括書き込み（配列処理？）ですが、式が列ごと、そのなかで偶数、奇数行ごとに違うので、列単位の呼び込みで処理するしかないのかな？だとしても　k　の扱いが？全くお手上げです。
読みにくいかもしれませんが、お知恵をお貸しください。
お願いします。

onlyrom · Accepted Answer

またまたまた登場、onlyromです。

＞質問文及び、前の補足に書き込んでいるように
＞式は列ごとのさらにに偶数奇数単位で一見共通ですが
＞変数ｋのせいで共通にできないのが問題なのです

もちろん、補足などはちゃんと読んでの回答です。
それでもなお、質問者が【Ｋ問題】があるので配列が使えないというその根拠がいまいち分かりません。
人並みに読解力はあるつもりですがねぇ。。。(^^;;;

質問者は配列はできないと言い、こちらはできるのではと考え、
これでは埒はあきませんので、この際ですからコードの一部だけではなく、
実際のコードを全てアップしたらどうでしょう。
そうすれば、よりよい解決策が見つかるかも知れません。

それからもひとつ疑問あり。
GetRowsStepN関数なるものを使うと実行速度が速くなるというのは本当ですか？？？
以上。

onlyrom · Answer

またまた登場、onlyromです。
＞記入時にも再計算されるとは知りませんでした
＞早速、式の記入前後にも取り入れて試してみます

マクロ実行の前に、手動計算にしてないのかも、
と推測しての回答でした。
そうしているようですので、さらに式代入の前後には不要だと考えます。

別案ですが、既出の回答にもあるように配列を使ったらどうでしょうか。
'------------------------------------------

　myArray = Range("A1:A10000").value

　　　For R = 1 to 10000 Step 2
　　　　myArray(R, 1) = 【奇数】行の式をセット
　　　Next R

　　　For R = 2 to 10000 Step 2
　　　　myArray(R, 1) = 【偶数】行の式をセット
　　　Next R

　Range("A1:A10000").Value = myArray

'---------------------------------------------------

こんなふうに。
以上。

onlyrom · Answer

コードは見てませんが。。。
遅いのは式がセットされるたびに再計算されるからでは？

●「手動計算」オプションで開始

　　Application.Calculation = xlManual

●「自動計算」オプションで終了

　　ActiveSheet.Calculate
　　Application.Calculation = xlAutomatic


あるいは、
式自体をセルに入れるのではなく
コードで計算させ結果のみセルに代入するとか。

以上。

tonton-tribo · Answer

先ほどの訂正にミスを発見しましたのでさらに訂正です。

誤：c(i, 1).FormulaR1C1 = "ここに関数"
誤：c(i, 1)= "ここに関数"

記述例として載せておきながら2回も訂正することになってすみません。

tonton-tribo · Answer

すみません、先ほどの回答で一部ミスがありましたので訂正します。

誤：Range("H" & j + 11)というセルの指定も間違いではありませんが、VBAの場合はCells(8, j + 11)という記述のほうが微妙に速いんです。
正：Range("H" & j + 11)というセルの指定も間違いではありませんが、VBAの場合はCells(j + 11, 8)という記述のほうが微妙に速いんです。

ミスの内容はCellsプロパティの座標指定のミスです。Cellsプロパティではカッコ内の数字の1つ目がY座標(縦の位置)、2つ目がX座標(横の位置)になっているのをすっかり忘れてました。

tonton-tribo · Answer

・Selectしない

質問者さんのコードでも間違いじゃではありませんがセルをSelectすると処理速度が遅いです。下記のように変更すれば10～20％ほど高速化できます。

・例
Range("H" & j + 10).Select
ActiveCell.FormulaR1C1 = 関数
↓
Range("H" & j + 10).FormulaR1C1 = 関数

・変数の型を指定する

VBAでは型を指定しない変数は全てVariant型になります。Variant型はなんでも格納できる万能の変数型ですがFor Nextなどのループ処理を行なうと遅くなります。この修正だけで30～40％ほど高速化します。k、BN、SNの最大値が32767未満ならInteger型、それ以上ならLong型で宣言しましょう。あと、For Nextで使っているjも宣言したほうが良いですね。ちなみに、宣言の仕方を間違えると効果が無いので注意しましょう。間違った記述と正しい記述は下記になります。

・例
誤：Dim k, BN, SN, j As Integer
正：Dim k As Integer, BN As Integer, SN As Integer, j As Integer

・セルを座標で指定する

Range("H" & j + 11)というセルの指定も間違いではありませんが、VBAの場合はCells(8, j + 11)という記述のほうが微妙に速いんです。効果は小さいですけどループ回数が多い場合には大きな差になるので変更したほうが良いですね。

・セルを配列に入れる

これは最初に指定範囲のセルを変数内に読み込んで処理した後に一気にセルに書き込むという方法です。これは非常に効果が大きいのでおすすめです。平均90％ほど高速化しますよ。

・例
Dim i As Integer, c As Variant
c = Range("A1:A10000") '変数cに指定範囲のセルを格納
For i = 1 to 10000
c(1, i).FormulaR1C1 = "ここに関数"
Next
Range("A1:A10000") = c 'セルに変数cに格納したデータを反映

高速化したいのですが、私には難問です。

またまたまた登場、onlyromです。

この回答への補足

またまた登場、onlyromです。

この回答への補足

コードは見てませんが。

この回答への補足

先ほどの訂正にミスを発見しましたのでさらに訂正です。

すみません、先ほどの回答で一部ミスがありましたので訂正します。

・Selectしない

この回答への補足

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング