Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


7440 / 13644 ツリー ←次へ | 前へ→

【38986】処理速度を速めるには?? aaa 06/6/15(木) 16:47 質問[未読]
【39034】Re:処理速度を速めるには?? ハチ 06/6/16(金) 11:34 発言[未読]
【39143】Re:処理速度を速めるには?? aaa 06/6/19(月) 10:18 質問[未読]
【39038】Re:処理速度を速めるには?? Jaka 06/6/16(金) 12:08 発言[未読]
【39144】Re:処理速度を速めるには?? aaa 06/6/19(月) 10:21 発言[未読]
【39146】Re:処理速度を速めるには?? Jaka 06/6/19(月) 11:05 発言[未読]
【39153】Re:処理速度を速めるには?? aaa 06/6/19(月) 11:37 発言[未読]
【39166】Re:処理速度を速めるには?? Kein 06/6/19(月) 14:42 発言[未読]

【38986】処理速度を速めるには??
質問  aaa  - 06/6/15(木) 16:47 -

引用なし
パスワード
   いつもお世話になっております。
たくさんの処理をマクロで行っており、処理速度が向上せず困っています。
問題はこの以下の箇所のようです。

1.セル範囲のデータクリア
Worksheets("Sheet1").Range("A:G").Clear
Worksheets("Sheet2").Range("B:H").Clear

※本当は2行目の方の範囲はB列からH列でデータが入っている範囲全てとしたいのですが、いい方法が思いつかず上記のようになっています。
最初「Range("B2:H65535")」としていたのですが、処理が途中で止まりフリーズします。

2.セル範囲のコピー
i = Worksheets("Sheet1").Range("I1").Value
Set myRng1 = Worksheets("Sheet1").Range("A1:G" & i)
Set myRng2 = Worksheets("Sheet2").Range("B2")
myRng1.Copy
myRng2.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Set myRng1 = Nothing
Set myRng2 = Nothing

iではデータの最終行を取得したセルの値で、それを元にセル範囲を決めています。
ここがどうしても遅いのです。
コピー元のデータはその時々により変化しますが、基本的には200行以上です。
たぶんデータ量が多すぎてコピーに時間がかかるのではと思いますが、対処法はありますか?
その後H列のみ数値データに変換したいので下記のような処理を行っています。

Worksheets("Sheet2").Select
Range("H1:H" & i).Select
Selection.NumberFormatLocal = "\#,##0_);[赤](\#,##0)"

あまりいい方法ではない気がするのですが、これ以外の方法で文字列として入力された数値データを一気に変換するマクロはありますか?

どうかよろしくお願いします。

【39034】Re:処理速度を速めるには??
発言  ハチ  - 06/6/16(金) 11:34 -

引用なし
パスワード
   ▼aaa さん:
>1.セル範囲のデータクリア
>※本当は2行目の方の範囲はB列からH列でデータが入っている範囲全てとしたいのですが、いい方法が思いつかず上記のようになっています。
>最初「Range("B2:H65535")」としていたのですが、処理が途中で止まりフリーズします。

B2〜H列の最終行までなら、こんな感じで。

With Worksheets("Sheet2")
  .Range("B2", .Range("H65536").End(xlUp)).Clear
End With

>2.セル範囲のコピー
>i = Worksheets("Sheet1").Range("I1").Value
>iではデータの最終行を取得したセルの値で、それを元にセル範囲を決めています。
>ここがどうしても遅いのです。
>コピー元のデータはその時々により変化しますが、基本的には200行以上です。
>たぶんデータ量が多すぎてコピーに時間がかかるのではと思いますが、対処法はありますか?

これは、データが多いわけではなくコピーするロジックの問題だと思います。
値だけコピーでよければ、これでどうでしょうか?

i = Worksheets("Sheet1").Range("I1").Value
Worksheets("Sheet2").Range("B2:H" & i + 1).Value = _
Worksheets("Sheet1").Range("A1:G" & i).Value

>その後H列のみ数値データに変換したいので下記のような処理を行っています。
>
>Worksheets("Sheet2").Select
>Range("H1:H" & i).Select
>Selection.NumberFormatLocal = "\#,##0_);[赤](\#,##0)"
>
>あまりいい方法ではない気がするのですが、これ以外の方法で文字列として入力された数値データを一気に変換するマクロはありますか?

これは、セルの書式設定を変更しているだけでは?
.Valueの値に変化はないですよね?
見た感じ数値データと言うよりも、Currency型になりそうですが・・

【39038】Re:処理速度を速めるには??
発言  Jaka  - 06/6/16(金) 12:08 -

引用なし
パスワード
   ▼aaa さん:
>最初「Range("B2:H65535")」としていたのですが、処理が途中で止まりフリーズします。
ワークシート関数とか入っているからじゃないですか?
そうだったら、計算方法を手動にすれば大丈夫だと思います。

【39143】Re:処理速度を速めるには??
質問  aaa  - 06/6/19(月) 10:18 -

引用なし
パスワード
   ▼ハチ さん:

ハチさんからおしえていただいた通り

>With Worksheets("Sheet2")
>  .Range("B2", .Range("H65536").End(xlUp)).Clear
>End With

を使用しましたが、実はSheet2には罫線を設定したり、一行目にラベルを設定しているのでそれまでクリアされてしまいました。
この処理をしてから罫線を引く処理と値を入れる方法を取った方が早いのでしょうか?

またコピーの件ですが、

>i = Worksheets("Sheet1").Range("I1").Value
>Worksheets("Sheet2").Range("B2:H" & i + 1).Value = _
>Worksheets("Sheet1").Range("A1:G" & i).Value

ハチさんの上記の手法でやってみると確かにコピーはできたのですが、値の書式設定が変わってしまい無理でした。
そのコピーのデータには日付や時間のデータが入っていますので、それが標準になってしまいましたので、コピーした後表示形式を変更する必要があります。

また数値データの件ですが、

>>Worksheets("Sheet2").Select
>>Range("H1:H" & i).Select
>>Selection.NumberFormatLocal = "\#,##0_);[赤](\#,##0)"

ハチさんのおっしゃるとおりCurrency型に変換しています。
すいません数値データではありません。書き方を間違えました。
Currency型に変える方法は上記のやり方が通常なのでしょうか?
もうちょっとスマートなやり方はありますか?

よろしくお願いします。

【39144】Re:処理速度を速めるには??
発言  aaa  - 06/6/19(月) 10:21 -

引用なし
パスワード
   ▼Jaka さん:

色々検証した結果、Jakaさんのおっしゃるとおりワークシート関数が入っているからと言うのもあると思います。
別シートで関数を入れて計算をさせています。
手動にしてみたのですが、最後に再計算をさせるとそこでフリーズします。
計算をさせるのにも限界があるということでしょうか?
別トピで投稿させていただいているのと関係が出てくるのかもしれません。。。

【39146】Re:処理速度を速めるには??
発言  Jaka  - 06/6/19(月) 11:05 -

引用なし
パスワード
   ▼aaa さん:
>色々検証した結果、Jakaさんのおっしゃるとおりワークシート関数が入っているからと言うのもあると思います。
あると思う出なく、それが原因です。
これに関係しているとは知らずに
SUMPRODUCTエクセル関数を別スレで書いちゃったけど.....。
こんな配列関数を書いたら余計に重くなります。

>別シートで関数を入れて計算をさせています。
>手動にしてみたのですが、最後に再計算をさせるとそこでフリーズします。
再計算させてフリーズするのに関数が原因とは考えないのでしょうか?
(フリーズでなく、1時間ぐらいほっておくと終わると思う。)

これは、セル内容を消すとフリーズするとかの分類ではないですね。
現在の状態で適当なセルに
= 1 + 1
と書いてもフリーズしたようになると思います。
この前に再計算でフリーズしたと言うようだと、開くときでさえやたらと時間がかかると思うんですけど....。

重たい関数を詰め込みすぎるのも考えた方が良いですね。
後、条件付書式に複雑な関数を使いすぎているとか....。
めんどい条件付書式で判定させて、作ったカレンダーがやたら重かったことがあります。

【39153】Re:処理速度を速めるには??
発言  aaa  - 06/6/19(月) 11:37 -

引用なし
パスワード
   ▼Jaka さん:

ひとつのマクロでたくさんの処理をさせているので、もしかしたら他にも原因があるのかと思い投稿しました。
ワークシート関数が重くなっている要因であるということは、投稿してからわかったことでした。
ブレイクポイントを設定して確かめながらデバックしていたら、確かに最初はクリアとコピーの箇所が重かったのですが、その根底にあった原因は再計算だったのですね。
勉強不足でした。
そんなに複雑な計算をさせているわけでも、関数の使用回数がそれ程多くないと私が勝手に判断していました。
それが関係しているとは思いもよらず…

>これは、セル内容を消すとフリーズするとかの分類ではないですね。
>現在の状態で適当なセルに
>= 1 + 1
>と書いてもフリーズしたようになると思います。
>この前に再計算でフリーズしたと言うようだと、開くときでさえやたらと時間がかかると思うんですけど....。

そこまでは重くなっていませんので、対処方法があるのかと思いました。

>重たい関数を詰め込みすぎるのも考えた方が良いですね。
>後、条件付書式に複雑な関数を使いすぎているとか....。
>めんどい条件付書式で判定させて、作ったカレンダーがやたら重かったことがあります。

条件付書式も使用せず、単純にカウントや集計を取っているだけです。
ワークシート関数を使用せずに、上記の作業をできるように考え直してみようと思います。

【39166】Re:処理速度を速めるには??
発言  Kein  - 06/6/19(月) 14:42 -

引用なし
パスワード
   >ワークシート関数を使用せずに
ではなく「タイミングを考えてマクロで数式を入力 & 即、値のみに変換する」
というやり方にすれば良いと思います。

7440 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free