こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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 : 間隔行数
としてます。

前置きが長くてすみません。
【これが本題のコードです。】←ここからが早くしたいコードです。

k、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 の処理です。早く出来たコード(式が共通)と違い K の扱いは結局ループにせざるを得ないのでしょうか?(としてもどのような?)
例示では各段の各列は2列にしてますが、実際40列近くありますので(行の SN は30程度ありますから40×30×2のSELECT)、重いのです。(20秒位かかってしまいます)
理想は一括で範囲呼び込みして一括書き込み(配列処理?)ですが、式が列ごと、そのなかで偶数、奇数行ごとに違うので、列単位の呼び込みで処理するしかないのかな?だとしても k の扱いが?全くお手上げです。
読みにくいかもしれませんが、お知恵をお貸しください。
お願いします。

投稿日時 - 2008-09-09 12:59:12

QNo.4315506

困ってます

質問者が選んだベストアンサー

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

>質問文及び、前の補足に書き込んでいるように
>式は列ごとのさらにに偶数奇数単位で一見共通ですが
>変数kのせいで共通にできないのが問題なのです

もちろん、補足などはちゃんと読んでの回答です。
それでもなお、質問者が【K問題】があるので配列が使えないというその根拠がいまいち分かりません。
人並みに読解力はあるつもりですがねぇ。。。(^^;;;

質問者は配列はできないと言い、こちらはできるのではと考え、
これでは埒はあきませんので、この際ですからコードの一部だけではなく、
実際のコードを全てアップしたらどうでしょう。
そうすれば、よりよい解決策が見つかるかも知れません。

それからもひとつ疑問あり。
GetRowsStepN関数なるものを使うと実行速度が速くなるというのは本当ですか???
以上。

投稿日時 - 2008-09-10 23:31:00

補足

Onlyromさんへ
GetRowsStepNは選択範囲を取得する関数にしてます。その結果に一括記入させてますので、配列と同じですので速くなると思ってましたが、実際の処理をさせると、別途 Function GetRowsStepNとの間を行き来しますので、たいして早くなりません。関数使わず素直にご教示頂いてる配列の形の方が速いですね。シンプルですのでより良いですし。
また【K問題】につきましては、気を悪くされたのならお詫びいたします。私の不注意でした。なにせ変数kが1から順次増えて行くので式を共通でとらえることが出来ないと思ってた次第です。よく考えるとFor Loop で一緒にまわせば良いだけでした。恥ずかしい限りです。先入観に捕らわれてました。onlyromさんの「出来る」という強い口調で、もう一度考えさせられたお陰で即気づきました。ボケてました。すみません。まずは実装してみます。お礼はのちほどです。

投稿日時 - 2008-09-11 08:48:47

お礼

全体では新規BOOKを作ったりして、旧BOOKと比較し、違いに対してシートを入れ替えてたりしてます。そのため時間が元々かかるのですが、本題の部分の割合も結構あるので、全体では約35秒位になり約3秒位早くなりました。今回の変更点で3秒短縮出来たことになります。今回の部分だけでの新旧比較では3分の1にすることが出来ましたので、十分です。勉強させて頂きましたし。有難うございました。印刷設定にかなりの時間を食ってますのでそちらも詰めてゆきます。
またよろしくお願いします。

投稿日時 - 2008-09-11 15:01:03

ANo.6

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(6)

ANo.5

またまた登場、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

'---------------------------------------------------

こんなふうに。
以上。

投稿日時 - 2008-09-10 17:24:27

補足

onlyromさんありがとうございます。ここの方は皆親切でありがたく思っております。
まさにそのように配列で行えればいいなと思っているのですが、質問文及び、前の補足に書き込んでいるように、式は列ごとのさらにに偶数奇数単位で一見共通ですが、変数kのせいで共通にできないのが問題なのです。助けて!

投稿日時 - 2008-09-10 17:44:15

ANo.4

コードは見てませんが。。。
遅いのは式がセットされるたびに再計算されるからでは?

●「手動計算」オプションで開始

  Application.Calculation = xlManual

●「自動計算」オプションで終了

  ActiveSheet.Calculate
  Application.Calculation = xlAutomatic


あるいは、
式自体をセルに入れるのではなく
コードで計算させ結果のみセルに代入するとか。

以上。
 

投稿日時 - 2008-09-10 10:53:01

補足

onlyromさんへ
試したところ、特に変わりませんでした。すみません。
でも記入の際も、再計算されてしまうというのであれば、常に入れておくようにします。ありがとうございます。
またどうしても関数を記入しなくてはならないので、結果をVBAでとはいかないのです。
やはり、一括もしくは分解して半一括で記入は難しいのでしょうか?

投稿日時 - 2008-09-10 14:28:41

お礼

onlyromさん ありがとうございます。
  '画面更新の抑止
Application.ScreenUpdating = False
'現在の再計算モードの取得
iOldCalculation = Application.Calculation
'再計算モードを手動に設定
Application.Calculation = xlManual
'再計算モードの復元
Application.Calculation = iOldCalculation
'画面更新
Application.ScreenUpdating = True
らは既に取り入れてます。しかし再計算モードの切り替えはコピーの前後にしか入れてませんでした。(記入時にも再計算されるとは知りませんでした。)早速、式の記入前後にも取り入れて試してみます。

投稿日時 - 2008-09-10 14:28:13

ANo.3

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

誤:c(i, 1).FormulaR1C1 = "ここに関数"
誤:c(i, 1)= "ここに関数"

記述例として載せておきながら2回も訂正することになってすみません。

投稿日時 - 2008-09-09 15:24:27

お礼

tonton-triboさん丁寧にありがとうございます。
両方誤りとなってますが、上が正しいのでしょうか?
数式はどちらでもはいりますが、FormulaR1C1を省略すると、相対参照時うまく機能しませんので、上が正しい方と理解します。よろしくお願いします。

投稿日時 - 2008-09-09 15:49:12

ANo.2

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

誤:Range("H" & j + 11)というセルの指定も間違いではありませんが、VBAの場合はCells(8, j + 11)という記述のほうが微妙に速いんです。
正:Range("H" & j + 11)というセルの指定も間違いではありませんが、VBAの場合はCells(j + 11, 8)という記述のほうが微妙に速いんです。

ミスの内容はCellsプロパティの座標指定のミスです。Cellsプロパティではカッコ内の数字の1つ目がY座標(縦の位置)、2つ目がX座標(横の位置)になっているのをすっかり忘れてました。

投稿日時 - 2008-09-09 14:31:57

お礼

tonton-triboさんへ
最終的には配列の形で解決です。変数 k を一緒にLoopで廻すことに私が気付かなかったせいで、皆さまには手間を取らせました。すみませんでした。最初にお付き合い頂きましてありがとうございました。またよろしくお願いします。
yokokama46より

投稿日時 - 2008-09-11 15:07:58

ANo.1

・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に格納したデータを反映

投稿日時 - 2008-09-09 14:19:35

補足

最初の質問が解りにくくてすみませんでした。誤入力がありまして質問の最初の
<下記のコードを別途 GetRowsStepN 関数(指定間隔で範囲指定)を使っていくらか早くしました。は正しくは
下記のコードは別途 GetRowsStepN 関数(指定間隔で範囲指定)を使っていくらか早くした分です。の誤りです。
元々For Loop Nextを使ったコード(今回の例示のような)を修正したものです。セル範囲(列の一行飛び)を取得して一括記入する。
しかし今回の例示では、セル範囲(列の一行飛び)を取得するまでは同じですが、変数 k のせいで一括記入が出来ないという問題なのです。それをなんとかしたい。ということが主題です。
またさらに、tonton-triboさんも最後に述べられていた一括範囲取得から一括記入への流れが出来ないのかな?という欲張りな質問です。
ただ、質問文にあるように、式が列ごと、そのなかで偶数、奇数行ごとに違うので一括は無理なのかなと思っている次第です。よろしくお願いします。

投稿日時 - 2008-09-09 15:37:04

お礼

tonton-triboさんご返事頂きありがとうございます。

<Range("H" & j + 10).FormulaR1C1 = 関数
そうでした。うっかりです。Selectしてたところを修正します。
いくらか早くなりそうです。
<Dim k As Integer, BN As Integer, SN As Integer, j As Integer
はOKです。例示の範囲外で宣言してます。Integerで。

Range(Cells(j + 11, 8))の方が多少とはいえ早いとは知りませんでした。取り入れます。ありがとうございます。

投稿日時 - 2008-09-09 15:18:19

あなたにオススメの質問