グッドデザイン賞を受賞したウォーターサーバー >>

下記のプロシージャを実行すると実行速度が異常に遅くなることがあります。色々原因を調べたところ、一度印刷を実行したシートで再度下記のプロシージャを実行するとこの現象が再現できることがわかりました。(一度ブックを閉じて、再度立ち上げなおせば実行速度も元のスピードに回復しますが、また印刷をかけると遅くなります)
再現性があるのでどなたか原因を教えていただけないでしょうか?
-------------------------------------------------
Sub 非表示_0()

Application.ScreenUpdating = False
Dim i As Integer

For i = 1 To 100
  If Cells(i, 1).Value = 0 Then
Cells(i, 1).EntireRow.Hidden = True
End If
Next i

End Sub
-------------------------------------------------

このQ&Aに関連する最新のQ&A

A 回答 (7件)

こんにちは。

Wendy02です。

#6のKenKen_SPさんの回答の補足に対するものですが、

>xlbの拡張子はツールバーかなにかの情報をもったものだったと思うのですが、これって消しても大丈夫でしょうか?

これを消すと、メニューがデフォルトに戻ります。通常、3点セットで、Personal.xls, *.xlb, *.pip を削除すると、完全なデフォルトに戻ります。なお、*.pip は、メニューの操作の記憶ですが、これを削除すると、修復とか言ってきたと思います。Personal.xls は、言うまでもなく、個人用マクロブックですから、削除したら、その中身は失ってしまいます。

さて、私の方のExcelですが、

#2のテストで、何をしなくても、何度やってみても、
3 秒を越えますね。
その理由は、システムから情報を取得する、いくつかの複雑なユーザー定義関数を通ってきているからです。

私の知っている範疇で、変則的な方法ですが、実用度はあると思います。
いろいろ実験したわけではないのですが、昔、ある有名な方が作ったもののコードのイメージが、こんな風だったかなと思って作ってみました。(時間はかなり短縮されます。)
本来は、もう少し手直しする必要があるのですが、今は、ここでとどめました。

'Declare Function timeGetTime Lib "winmm.dll" () As Long '既に標準モジュールにある場合は不要

Sub HiddenRowsPrc()
 Dim rng As Range
 Dim ar As Range
 
 '-----------------------------------
 Dim lngTime As Long 'KenKen_SP さん '#2時間測定用
 lngTime = timeGetTime()
 '-----------------------------------
 '領域設定
 Set rng = Range("A1:A100")
 Application.Calculation = xlManual
 Application.ScreenUpdating = False
 
 If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.Range.AutoFilter
 rng.AutoFilter Field:=1, Criteria1:=""
 rng.Offset(1).Resize(rng.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Select
 rng.AutoFilter
 
 For Each ar In Selection
  ar.EntireRow.Hidden = True
 Next ar
 
 Application.Goto rng.Cells(1), False
 Set rng = Nothing
 Application.Calculation = xlAutomatic
 Application.ScreenUpdating = True
 '------------------------------------------
 lngTime = timeGetTime() - lngTime '時間測定用
 MsgBox Format$(lngTime / 1000, "0.000 sec")
 
 '次のテスト用に再表示
 Call Restore
'------------------------------------------
End Sub
    • good
    • 0
この回答へのお礼

こんばんは。xlbの件ありがとうございます。やっぱりそうでしたよね。
Wendy02さんのコードで早速実験してみました。
印刷前→平均0.057sec
印刷後→平均4.705sec
でした。
もうこの辺になると私には全てのコードの意味が理解できていないのでよくわかりませんが、若干処理速度が遅くなったような気がします。いずれにしても印刷の前後の差は歴然でした(泣・・・)
でも色々勉強になります。奥が深いっ!!!

この議題も長くなってきたのでもうそろそろ一旦締め切りますね。皆さんありがとうございました。
すぐには解決できそうにもない内容なのですが、色々経験を重ねるといつかわかってくると信じて頑張ります!

お礼日時:2006/09/22 00:04

余りにも回答になってないですね~(;´・ω・`)スミマセン。



消極的ですけど、次のコードならどうなりますか?

時間計測を入れてますので、#2 のコードの末尾にでも付け足して下さい。

やや複雑なコードですが、Hidden の実行を1回だけにしています。ごく
短い処理なので、通常は shunshun-dash さんが書いたようなコードの方が
シンプルですし、より良いのですが、テストということで。

Sub TestCode()

  Dim rngTarget As Range
  Dim rngHidden As Range
  Dim rngCell  As Range
  Dim lngTime  As Long
  
  lngTime = timeGetTime()
  
  Set rngTarget = ActiveSheet.Range("A1:A100") _
          .SpecialCells(xlCellTypeConstants, xlNumbers)
  If rngTarget Is Nothing Then
    Exit Sub
  End If
  For Each rngCell In rngTarget
    If rngCell.Value Then
      If rngHidden Is Nothing Then
        Set rngHidden = rngCell
      Else
        Set rngHidden = Union(rngHidden, rngCell)
      End If
    End If
  Next rngCell
  If Not rngHidden Is Nothing Then
    rngHidden.EntireRow.Hidden = True
  End If
  Set rngTarget = Nothing
  Set rngHidden = Nothing

  lngTime = timeGetTime() - lngTime
  MsgBox Format$(lngTime / 1000, "0.000 sec")

  '次のテスト用に再表示
  Call Restore

End Sub

この回答への補足

ありがとうございます。
ご指摘のコードで再度実験してみました。今度はワークシート関数をたくさん載せた状態で。
(結果)
印刷前→0.0405sec/10回平均
印刷後→3.3735sce/10回平均
各データにはばらつきはほとんどありませんでした。

私の場合、自宅PCでも職場PCでも再現できたので皆さんも再現できると思ったのですが、不思議です・・・
念のためドライバを再インストールもしてみましたが、改善は見られませんでした。PDFへの吐き出し処理をおこなっても同じ現象が起きるので、やはりプリンタドライバへの吐き出しを行ったこと自体に原因があるということになりますでしょうか?

「Hidden」を使う時だけこの現象が起きるのですが、今後もこれと付き合っていかなければならないかと思うと「Hidden」は使いたくなくなりますね!でも非表示は絶対必要なのですが。
xlbの拡張子はツールバーかなにかの情報をもったものだったと思うのですが、これって消しても大丈夫でしょうか?(大丈夫だから教えてくださったのだと思いますが)

もう、こうなったら徹底的にということで、今職場のPC全てで試しています。今のところ全てのPCで再現性があります。(残念なことに・・・

そういえば、あともう一つの再現性があります。
というのも、印刷を実行したシート(アクティブシート)から実行する「Hidden」マクロだけが遅くなるのです。この現象が起きても、他のシートから実行するには正常速度でパパっと実行してくれます。そのシートでまた印刷をするとそのシートも死んでしまいますが・・・
ここから何か推測はできないでしょうか?

補足日時:2006/09/21 01:27
    • good
    • 0

> セルにはデータがまったくない状態で...



それだとテストの意味が薄れるので、できるだけ実際のデータをセルに配置した
上でテストした方が良いですよ。

差異は3秒ですか..微妙ですね。テスト回数を多くとれば誤差の範囲に収拾
されてしまいそうな数字ですから、コメントし難いのですが、

  「何らかの異常があるかもしれない」

という、何とも煮え切らないことぐらいしか言えません。0.3sec が 30.0sec
ぐらいに変わってしまうのであれば、原因も探りやすいのですが....

これでは、回答になってないですね。すみません。ご参考までに、私が推測し
ていた原因を述べておきます。

 ▼原因1) 実測してみればその程の差はない
  失礼ながら可能性として考慮すべきだと思いましたので、実際に
  計測をお願いしました。しかし、 #2 補足欄を拝見する限り、
  差は確かにあるようです。
  
  >印刷をかける前の実行速度で体感0.5秒、印刷実行後で体感速度
  >10秒位です。

 ▼原因2) プリンタードライバーの異常
  コードに問題があるとは考え難いのです。したがって Excel 以外
  に原因があると考えました。過去にプリンタードライバーの異常が
  原因となって、印刷時・印刷プレビュー時に Excel がフリーズ、
  あるいは可笑しな挙動を示した事例がありました。

  推測でしかありませんが、Hidden を実行するたびに PageSetup の
  印刷情報が書き換わる?ので、その際に問い合わせるドライバーに
  異常がある、例えばメモリリークなどが発生しているのだとすれば、
  実行回数に比例して処理時間が長くなる...と考えたのです。しかし、
  
  >職場のPCスペックはもっとハイパフォーマンスにもかかわらず、同様
  >の現象がおきますので、...
    
  の#2 の補足を拝見する限り、異なる環境で再現するわけですから、
  こちらも可能性は薄そうです。
  
  参考までに、その時の対処法を記載しておきます。
  
    1)プリンタードライバーの再インストール
    2)*.xlb ファイルの削除
  
  大抵のケースでは以上の手順で直りました。

長くなるので、一旦切ります。
    • good
    • 0

shunshun-dash 様


こんにちは。Wendy02です。

KenKen_SPさんとの#2の続きのお話も少し聞きたいところですが、

今、気になったので、google で、"excel slow" で検索してみました。

ここで、今までの書いていた内容について、ほとんど、出ていますね。

http://www.mvps.org/dmcritchie/excel/slowresp.htm
Slow Response, Memory Problems, and Speeding up Excel
遅い反応、メモリの問題、Excelを速くする

ここに、こんな一文があります。
 MS believes that some printer drivers are at the root of some resource issues:

(MSでは、いくつかのリソースの問題の根底に、いくつかのプリンタ・ドライバにあると考えているようです。)

http://support.microsoft.com/kb/165985
Q165985 -- Office:MS-Officeを使用しているときの 「メモリ不足」というメッセージの情報

It is also possible that the latest version of the printer driver will correct the problem.
(最新のバージョンのプリンタ・ドライバが、この問題を修正する可能性があります。)
特に、HP ドライバが問題になっている、としています。

So you might try changing print drivers.
(そんなわけで、プリンタ・ドライバを替えてもよいかもしれません。)

私は、考えてもみませんでした。もし、それが事実なら、試してみる価値はありますね。

まだ、いろんなメモリ漏れについて書かれています。全部は読んでいませんが、なかなかタメになります。
    • good
    • 0
この回答へのお礼

返事遅くなってすみません。
なんだか私の域を完全に超えているようですが、なんとなくExcelとプリンタドライバとのやり取りの中に原因があるような気がしますね。
ただ、プリンタドライバをかえても見たのですが、残念ながら改善は見られませんでした。PDFへ吐き出し処理でも同じことでしたので、やはりプリンタドライバへの出力時に何かが起きているのでしょうね!

お礼日時:2006/09/21 01:00

こんにちは。

Wendy02です。

私の場合は、1万行以上の並べ替えを頻繁に行っています。朝のスタート時などには、私のExcel 2003でも、並べ替えが目で追えるレベルで遅くなることがあります。

私は、ハード系がぜんぜん分からないのですが、キャッシュが、何かに別のものに占有されてるとき(つまり、何かを使った直後)は、異様に遅いようですね。自分のコードをもう一度見直してみないと分からないのですが、通常は問題ないので、間違いはないと思います。

その直し方というのは良く分からないのですが、Excel側で、何度か使用していると戻ります。これは、Excel2000では経験がありませんでした。ただし、私は、Excelではほとんど印刷はしませんので、印刷系でのトラブルは分かりません。

その直し方というのは、#1 で書いたように、やはり何かのツールが必要なのか、とは思いますが。

>印刷を実行するとリソースが極端に不足する
印刷自体ではないないと思います。印刷のバッファとの問題とか?

Excelのメモリそれぞれに割り振りがありますから、そこで、入れ出しになってメモリの割り振りが減ってしまっているとか、勝手な想像ですが、なかなかOffice だけでは解決できそうにもありませんね。
    • good
    • 0
この回答へのお礼

こんにちは。

>並べ替えが目で追えるレベルで遅くなることがあります。

その「目で追えるレベル」というのは私の体感しているレベルと同じだと思います。
試しに並べ替えのコードでも実験してみたのですが、残念ながら再現はできませんでしたが、原因は同じところにあるような気がします。
リソースとかキャッシュのことは全くわかりませんが、一度ブックを閉じて再度立ち上げなおすと改善されているので、リソースが開放された結果改善されるのだと勝手に想像しています。

仕事上、VBAのスキルが上達すると他の人が半日かけてする作業も30分程度でできるので非常に便利ですが、その反面、段々と難しいシステム系の問題に遭遇してくるようになってきて、こちらのほうで時間を食われています・・・

お礼日時:2006/09/20 12:21

こんばんは、shunshun-dash さん、Wendy02 さん。



KenKen_SP です。

確かに Hidden プロパティーの処理速度は決して早くはありません。しかし、
オリジナルのコードは非常にシンプルで、A1:A100 とループ回数も少なくいの
ですから、それ程時間がかかる処理とは思えません。

プログラムの実行速度について早い・遅いという点で言えば、体感速度の感じ
方には個人差があって、それが影響している可能性も否定できません。

「異常に遅い..」と言うのは、具体的にはどれぐらいの時間がかかっているの
でしょうか? これを検討するには、少なくとも

 ・PC スペック (CPU速度・メモリ搭載量・OS と Excel のバージョン)
 ・処理するデータサンプル (数件で構いません)
 ・実行するコード (これは下記コードでお願いします
 ・ベンチマークテスト結果 (10回程度)

などの材料が提示される必要があります。

私がご提示のオリジナルコードを試したら、A1:A100 とサンプル数が少ないせい
もあって、10回平均で 0.125 sec でした。計測に使っている API は 15 ミリ秒
程度の誤差がありますので、信頼できるのは 1/100 秒までです。従って、 0.12
秒程度ということになります。なお、テスト環境は

 ・P4-2.4GHz 512M / WindowsXP + Excel2002

です。もちろん、実際のものとは異なるデータ・環境でのテストですから、参考値
にもなりませんが...

実際に計って見て下さい。簡易ですが、ベンチマークは次の手順で取れます。

1. Windows の起動直後で、常駐ソフトは可能な限り終了させる
2. 起動するのは Excel のみ
3. セルに実際のデータと同様のサンプルデータを配置する
4. 下記のコードを標準モジュールにコピペする
5. 10回程度繰り返し、ベンチマークを取って下さい。

Option Explicit

' 処理時間計測用の API
Declare Function timeGetTime Lib "winmm.dll" () As Long

Sub 非表示_0()

  Dim i    As Integer
  Dim lngTime As Long
    
  lngTime = timeGetTime()
  
  Application.ScreenUpdating = False
  For i = 1 To 100
    If Cells(i, 1).Value = 0 Then
      Cells(i, 1).EntireRow.Hidden = True
    End If
  Next i
  
  lngTime = timeGetTime() - lngTime
  MsgBox Format$(lngTime / 1000, "0.000 sec")

  '次のテスト用に再表示
  Call Restore
  
End Sub

Sub Restore()
  
  '次のテスト用に再表示します
  Cells.EntireRow.Hidden = False
  
End Sub

この回答への補足

こんばんは。夜遅くにありがとうございます。
PCのスペック等簡単に記載しておきますのでアドバイスいただけるとありがたいです。
◎Windows XP
◎Celeron 2.70GHz
◎256MB+512MB(メモリ拡張)
◎Excel2003

本当に簡単に書きました。「実行速度」の体感については確かに個人差がありますが、私が最初に記載したプロシージャについて、印刷をかける前の実行速度で体感0.5秒、印刷実行後で体感速度10秒位です。
上記のPCスペックは自宅のものですが、職場のPCスペックはもっとハイパフォーマンスにもかかわらず、同様の現象がおきますので、その辺のところは関係ないのかなと思っております。
いずれにしても、他の方のPCでも再現性があるのかどうかというところからでも教えていただけると助かります。とにかく、私の突き止めた原因は「印刷前」と「印刷後」でマクロの事項速度が少なくとも10倍以上になるということで、微妙な実行速度の変化ではありません。

夜遅いので、ベンチマークは明日行ってみます。よろしくお願い致します。

補足日時:2006/09/20 04:07
    • good
    • 0
この回答へのお礼

Wendy02さん、KenKen_SPさん、こんにちは。
ベンチマークをとってみました。
Excelを新規に立ち上げて、セルにはデータがまったくない状態で、KenKen_SPさんご指摘のコードをコピペして測定しています。
(今は職場のPCで測定しています)
◎Windows XP
◎Celeron D 2.80GHz
◎256MB+512MB(拡張)
◎Excel2003
(ベンチマーク結果)
「印刷前」→0.03sec/10回平均
「印刷後」→3.805sec/10回平均

印刷実行後は100倍以上のようです。
実際に問題となっていますExcelブックにはかなり多くのExcel関数が存在しているため作業に支障をきたすくらいに遅くなってしまうのだと思います。
皆さんは、私の問題状況を再現できていますでしょうか?それとも私だけなのでしょうか???

お礼日時:2006/09/20 12:07

こんばんは。

Wendy02です。

以前書いたように、
#ひとつのセルを選択するたびに「計算しなさい(Calculate)」という指令が、Book全体に出ています。

ということに対処するなら、以下のようになります。まだ、他にも方法はありますが、これはループで行う方法です。

Sub 非表示_0()
 Dim i As Integer
 Application.Calculation = xlManual
 Application.ScreenUpdating = False
 For i = 1 To 100
  If Cells(i, 1).Value = 0 Then
   Cells(i, 1).EntireRow.Hidden = True
  End If
 Next i
 Application.ScreenUpdating = True
 Application.Calculation = xlAutomatic
End Sub
 
ScreenUpdating は、必ず、False/True ではさんでください。

また、物理的な問題がある場合、例えば、フォントやアンチウィルスソフトなどの問題は、VBAでは対処できません。一旦、リソースをフリーにするなどしてみるのも手かもしれません。

この回答への補足

こんばんは。
以前のご指摘のとおり、ブックへの計算を手動に切り替えるなど色々やってみましたが、この「表示/非表示」に関するメソッドだけが、異常に遅くなる現象が起きているように思います(現在私が記述している他のマクロについては一切このような重たくなる現象は起きないのです・・・)。
ちなみに、先ほどのご指摘のとおりのコードで実行しても、やはり改善は見られませんでした。セルに計算式が一切入っていない軽いファイルで再現しておりますので再計算の実行によるものではないと思うのです。
前回私もリソースの問題かなとは思ったのですが、印刷を実行するとリソースが極端に不足するというも変なはなしだなぁと思いました。

Wendy02さんの環境ではこのような現象は再現されませんか?私は、自宅のPCでも職場のPCでも再現性がありますので、結構困っています。

補足日時:2006/09/20 00:47
    • good
    • 0

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QExcel VBAが徐々に遅くなる

エクセルのVBAであるテキストファイルを読み込み、計算してCSVで吐き出すというプログラムを組んでいます。
質問はその計算スピードが徐々に遅くなってしまうということです。

計算し始めは大体1テキストファイルを読み込んでCSVに吐き出すのに1分くらいで済むのですが、3,4時間回してると1テキストファイル⇒CSVに2,30分かかってしまいます。

シートが増えていたりグラフを追加したりはしていません。
また、VBAの変数もできるだけ初期化するようにしてます。
読み込んでいくテキストファイルのサイズもそんなに変わらないです。

何か考えられる原因はないでしょうか?
ご教授宜しくお願いします。

Aベストアンサー

こんばんは。

>計算し始めは大体1テキストファイルを読み込んでCSVに吐き出す

「読み込んで」という、この過程に何が使われているのか分かりません。
コードをみないで、本来は、何も言うことも出来ません。しかし、通常、そのような内容の場合は、一切、ワークシートには出さないようにします。すべて、配列の中で処理していきます。

ワークシートに出せば、取り込んだものは、オブジェクトとして働いていますから、ブックの内部に積み下ろしされていないものが残しているような気がします。

QEXCEL VBA で現在開いているブックのファイル名を取得する方法

EXCEL2003 VBAで業務を簡素化するために、現在開いているブックのファイル名を取得する方法が分かりません。
作業手順をマクロを使って処理していますが、オリジナルのワークブックをファイル名を変えて保存し、以後、このワークブックを読み込んで使用しています。
このときのVBAは、オリジナルのファイル名を使っているため、ファイル名を変更するとエラーになり、以後の業務に使用できません。
常にファイル名を取得出来るVBAをどなたか、教えて下さい。

Aベストアンサー

>現在開いているブックのファイル名
 ちょっと曖昧な表現かなぁという気もいたしますが、VBAが書いてあるブックのブック名は
ThisWorkbook.Name
で、現在 "アクティブにして" 操作対象になっているブックの名前は
ActiveWorkbook.Name
ですね。

 しかし、
>VBAは、オリジナルのファイル名を使っているため、ファイル名を変更するとエラーになり
というような文脈からすると、
ThisWorkbook.Name
の方ですかね。

QExcelが急に遅くなりました。解決策を教えてください

Excelがある日から急に遅くなってしまいました。
サイズの大きいファイルや、マクロを含むファイルの操作がまともに出来なくなってしまいました。遅くなる前に何か特別な事をしたという記憶はありません。

仕事では、常にマクロを実行したりしていてExcelを常用しています。解決策をご存知の方、教えて下さい。

Excel 2000
Windows 2000
です。なお、Wordは今までと変わりなく使えています。

Aベストアンサー

こんにちは。
まずはこの辺りからでしょうか。
File opens very slowly in Excel 2000
http://support.microsoft.com/kb/299372/ja
XL2000: Workbooks with Multiple ActiveX Controls May Open Slowly
http://support.microsoft.com/kb/292298/ja

QエクセルVBAが途中で止まります

以前別のカテゴリで質問したのですが、そちらでは解決出来なかったので、こちらで改めて質問します。
下記のマクロで、一つのブックからSheet1だけをコピーして来て、少し処理をし、元のブックを閉じるというもので、ブックの数は多くて3000程、少ない時は300位です。
で、このマクロだと900位までですと最後まで行くのですが、それを超えるとリストが95位で止まってしまいます。
自宅で別データを作ってやってみるとうまくいきました。
コピー元のブックにはテキストデータのみで、200文字から500文字程度の大きさしかありません。
ファイル名も50文字程度の物を全部20文字程度まで短くしてもみましたが、ダメでした。
どうかお知恵をお貸しください。

Sub ★1★ブックの結合()
Dim sFile As String
Dim sWB As Workbook, dWB As Workbook, aWB As Workbook
Dim dSheetCount As Long
Dim i As Long
Dim SOURCE_DIR As String

'エクセルデータに変換されたファイルのあるフォルダを選択します。
MsgBox "エクセルに変換されたデータのフォルダを選択"
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show = True Then
SOURCE_DIR = .SelectedItems(1) & "\"
End If
End With

Application.ScreenUpdating = False

'指定したフォルダ内にあるブックのファイル名を取得
sFile = Dir(SOURCE_DIR & "*.xls")

'フォルダ内にブックが無ければ終了
If sFile = "" Then Exit Sub

'集約用ブックを作成
Set dWB = Workbooks.Add

'転記マクロの中のDMリストシートをコピーする
Workbooks("転記用マクロ.xlsm").Worksheets("DMリスト").Copy Before:=dWB.Worksheets("Sheet1")
Application.DisplayAlerts = False
Worksheets(Array("Sheet1", "sheet2", "sheet3")).Select
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True

'集約用ブック作成時のシート数を取得
dSheetCount = dWB.Worksheets.Count

Do
'コピー元のブックを開く
Set sWB = Workbooks.Open(Filename:=SOURCE_DIR & sFile)

'コピー元のsheet1を集約用ブックにコピー
sWB.Worksheets("sheet1").Copy After:=dWB.Worksheets(dWB.Sheets.Count)

シート転記

'コピー元ファイルを閉じる
Application.DisplayAlerts = False
sWB.Close
Application.DisplayAlerts = True

'セルA2の名前を変更する


'シート名をセルA2の値に変更
'ActiveSheet.Name = Range("A2").Value


'次のブックのファイル名を取得
sFile = Dir()
Loop While sFile <> ""

'集約用ブックを保存する
'dWB.SaveAs Filename:=DEST_FILE


Application.ScreenUpdating = False


End Sub

以前別のカテゴリで質問したのですが、そちらでは解決出来なかったので、こちらで改めて質問します。
下記のマクロで、一つのブックからSheet1だけをコピーして来て、少し処理をし、元のブックを閉じるというもので、ブックの数は多くて3000程、少ない時は300位です。
で、このマクロだと900位までですと最後まで行くのですが、それを超えるとリストが95位で止まってしまいます。
自宅で別データを作ってやってみるとうまくいきました。
コピー元のブックにはテキストデータのみで、200文字から500文字...続きを読む

Aベストアンサー

http://oshiete.goo.ne.jp/qa/8750372.html


例えば、30ファイルあっても10ファイルしか読み込まれない事があり、
エラーメッセージもない、何事もなく終了するが10ファイルしか処理されていない、
常に発生する訳ではなく、マシンが変われば同じデータでもOKだったり、
データが少し変わればOKだったりする。
なので、昨日までOKだったのに、今日データが変わった為、急にダメになったりする。
もし、10ファイル目で発生した場合、何度実行しても必ず、10ファイル目までしか処理されない。
そのファイルがおかしいかと思い、その前後の2~3ファイルを削除しても、
やっぱり、10ファイル目(さっきとは違うファイル)までしか処理されない、
という恐ろしいバグがExcel VBAにありますが、それじゃないですかね?

While文などのループの中に、ワークブックのオープンがあると、
何度目かでオープンが実行されず、エラーなしでスルンと終わります。

回避方法は、Open 文の前に DoEvets の1行を書く事。
だいたいこれで直りますが、これで直らなかったマクロもあったので、
Open 文の後ろにも DoEvets の1行を書いて、前後を DoEvets ではさむと直りました。
安全(?)の為、前後をはさんでおいた方が良いと思います。

こんな感じ:
DoEvents
Workbooks.Open aaa
DoEvents

かなり前(1年くらい?)にハマりググりまくったところ、ほとんど情報はなかったですが、
1人だけ、自分の質問に「直った」と自己回答している方がいて、Open文の前にDoEvetsをつけたら直ったそうです。
半信半疑で真似たら私も直りました。
何故、これで直るのかはわかりませんが、DoEvetsを外すと見事に再現し、DoEvetsではさむとピタッと直ります。

ググりまくった際、とても情報が少なく、こんなに顕著に再現するのに、
何故、情報が少ないのかは不思議に思いました。

http://oshiete.goo.ne.jp/qa/8750372.html


例えば、30ファイルあっても10ファイルしか読み込まれない事があり、
エラーメッセージもない、何事もなく終了するが10ファイルしか処理されていない、
常に発生する訳ではなく、マシンが変われば同じデータでもOKだったり、
データが少し変わればOKだったりする。
なので、昨日までOKだったのに、今日データが変わった為、急にダメになったりする。
もし、10ファイル目で発生した場合、何度実行しても必ず、10ファイル目までしか処理されない。
そのファイルがおかし...続きを読む

QDoEvents関数って何?

こんにちは。

VBAやプログラミングに詳しい皆様に
教えていただきたい質問があります。

cells(1,1)からcells(5000,1)までの値を消去するときに
処理の進行状況を表示するためにuserform上にプログレスバーを表示したいと思います。

そこで下記のようなコードを入力しました。

userform1.show
for i =1 to 5000
cells(i,1)=""
userform1.progressbar1.value=i/5000*100
next i
unload userform1

しかしこれだとuserformの背景が真っ白になってしまい
ラベルの文字も消えてしまいます。
そこで「EXCEL VBA パーフェクトマスター」という本を見たら

for i =1 to 5000
cells(i,1)=""
userform1.progressbar1.value=i/5000*100
DoEvents
next i
unload userform1
と入力すれば解決することがわかりました。

しかし「DoEvents」についてあまり詳しく書いていなかったのでDoEvents関数をヘルプで見ると、
「発生したイベントがオペレーティング システムによって処理されるように、プログラムで占有していた制御をオペレーティング システムに渡すフロー制御関数です。」

と書いてあるのですが正直、書いてあることがよくわかりません。

どなたかDoEvents関数について、
もう少しわかりやすく教えていただけませんか。
それから、最初に書いたコードで実行すると
ユーザーフォームの背景が真っ白になってしまう原因も
教えていただけませんか?

よろしくお願いいたします。

こんにちは。

VBAやプログラミングに詳しい皆様に
教えていただきたい質問があります。

cells(1,1)からcells(5000,1)までの値を消去するときに
処理の進行状況を表示するためにuserform上にプログレスバーを表示したいと思います。

そこで下記のようなコードを入力しました。

userform1.show
for i =1 to 5000
cells(i,1)=""
userform1.progressbar1.value=i/5000*100
next i
unload userform1

しかしこれだとuserformの背景が真っ白になってしまい
ラベルの文字も消えてしまいます。
そ...続きを読む

Aベストアンサー

簡単に言うと、
OS に制御を渡すってことです。(ヘルプそのまんま)
時間が掛かるループ処理などの場合、ループが終わるまで制御は独占されてしまいます。
ですのでループ中は OS や Excel そのものにも再描画をさせる暇さえ与えません。
途中に DoEvents を入れると制御が OS に渡るので、OS は溜まっていた処理をそこで行うことができます。
結果、フォームの再描画などが行われることになります。

注意点ですが、
Private Sub CommandButton1_Click()
  Dim i As Long

  For i = 1 To 50000
    DoEvents
    Cells(i,1) = ""
  Next i
End Sub

Private Sub CommandButton2_Click()
  MsgBox "hoge"
End Sub

っていうフォームのコードがあった場合、
DoEvents を入れることによって、ループ中にユーザーがCommandButton2 を押すことによって CommandButton2 のクリック イベントも動いちゃいます。
CommandButton1 のクリック イベントではループの前に
CommandButton1.Enabled = False
CommandButton2.Enabled = False
を書いてフォーム上の CommandButton を無効にしておき、ループが終わったら
CommandButton1.Enabled = True
CommandButton2.Enabled = True
と書いて CommandButton を有効に戻してください。

これを工夫すれば、CommandButton2 で CommandButton1 のループを途中キャンセルする処理もすることができます。

Private Canceled As Boolean

Private Sub CommandButton1_Click()

  CommandButton2.Enabled = False

  Dim i As Long
  For i = 1 To 50000
    DoEvents

    If Canceled = True Then
      MsgBox "キャンセルしました"
      Exit Sub
    End If

    Cells(i, 1).Value = ""
  Next i
End Sub

Private CommandButton2_Click()
  Canceled = True
End Sub



コードの行頭にあるスペースは見易さのために全角スペースで作成していますので、これをこのままコピペするとエラーになるかもしれません。
コピペするなら行頭の全角スペースを半角スペースに直してください。

簡単に言うと、
OS に制御を渡すってことです。(ヘルプそのまんま)
時間が掛かるループ処理などの場合、ループが終わるまで制御は独占されてしまいます。
ですのでループ中は OS や Excel そのものにも再描画をさせる暇さえ与えません。
途中に DoEvents を入れると制御が OS に渡るので、OS は溜まっていた処理をそこで行うことができます。
結果、フォームの再描画などが行われることになります。

注意点ですが、
Private Sub CommandButton1_Click()
  Dim i As Long

  For i = 1 To 50000
...続きを読む

QEXCEL VBAで計算値を四捨五入、切り上げ、切捨てする方法

ネットで探してみたのですが、計算結果を四捨五入して特定のセルを
返すにはどうしたらいいのでしょうか?

Sub hokangosa()

Dim ZPS As Double
Dim ZPOS As Double
Dim DMN As Double
MsgBox (" >>> 補間誤差自動計算 <<< ")
MsgBox (" >>> 初期値入力します <<< ")
ZPS = InputBox(">>> ステップを入力してください<<<")
ZPOS = Sheet1.Cells(22, 4).Value
DMN = ZPOS / ZPS
Sheet1.Cells(23, 6).Value = DMN
End Sub

ここでDMNの値を四捨五入したいです。

またこれとは別に切上げ、切捨ても教えていただけるとありがたいです。

Aベストアンサー

DMN = Application.WorksheetFunction.Round(ZPOS / ZPS, 0)
で、四捨五入
DMN = Application.RoundDown(ZPOS / ZPS, 0)
で切り捨て
DMN = Application.RoundUp(ZPOS / ZPS, 0)
で切り上げです。

引数で、対象桁を変更できます。

Qエクセル キャッシュメモリーの解放

エクセルで シュミレーションをしています。
パラメータは 10個 パラメーター変化数 5
5*5*---10乗 の計算
秒で 20個ほど パラメーターは進む? で まあ 1週間も有れば・・・・
所が、1日めで ハングでもない、とにかく 止まるというか
パラメータ全く動かず。
VBの実行の 中断、実行でも 動かず。
保存して

タスクマネージャーの メモリーを見ると

ハング状態          :リセットした状態のメモリー
合計     4002       : 4002
キャッシュ 2200(曖昧)   : 416 
利用可能 2000(曖昧)    : 2435
空メモリー 200(正確)    : 2461  

空メモリーが 200 と 少ない。
これが原因だろうと 思うのですが。

キャッシュを開放する方法って 有るのでしょうか

ループの中で
DIM set など 一切 ありません。
セルに値を代入。クリヤーせずに 全て上書き
自動計算 ON
相関係数算出などは 有りますが

キャッシュが 原因か それなら解放は?
それ以外の原因は?

何かご教示願えれば幸いです。
  

エクセルで シュミレーションをしています。
パラメータは 10個 パラメーター変化数 5
5*5*---10乗 の計算
秒で 20個ほど パラメーターは進む? で まあ 1週間も有れば・・・・
所が、1日めで ハングでもない、とにかく 止まるというか
パラメータ全く動かず。
VBの実行の 中断、実行でも 動かず。
保存して

タスクマネージャーの メモリーを見ると

ハング状態          :リセットした状態のメモリー
合計     4002       : 4002
キャッシュ 2200(曖昧)   ...続きを読む

Aベストアンサー

No1です。

おそらく勘違いをなさっていると思いますが、
「空きメモリー」=「電気だけ食って何の役にも立っていないメモリー」
です。
OSしか動いていない状態だと空きはかなりあるでしょうけど、プログラムがそれなりに動いている状態だと、高速化のためには空きメモリーゼロが理想的。とはいえ全くゼロだと急なメモリ使用変動に追いつけないので、空きは要りますが、数百MB空いていれば十分です。

アプリケーションプログラムが使っていないメモリーは余らせておくのは無駄なので、WindowsOSが、キャッシュやバッファに有効利用します。


普通は、必要なメモリサイズ > 物理メモリサイズ なので、足りない分をディスクを使って何とかします。とはいえ、足りなすぎるとメモリアクセスがディスクアクセスばかりになり大変遅くなる。

No1に書いたように、Windows7でOfficeをがんがん使うには4GBはやや不足です。
Excelのコミットサイズはどれだけですか???

QExel VBA 別ブックから該当データを検索し、必要なデータを取得する方法について

部品表というブックがあります
A列に商品名、B列に商品番号が入力してあります。C列のコードは未入力です。
A列     B列     C列      
商品名  商品番号  コード
モータ  U-1325-L  
ホルダ  R-134256

また、コード一覧表という別のブックには、A列に商品番号と、B列にコードが、何千件も入力されています。

やりたいことは
部品表のC列のコード欄に、コード一覧表ブックから商品番号と一致するコードを貼り付けしたいのです。

部品表は、何百種類もありますので、関数ではなく、マクロで処理を希望します。

自分では、部品表の商品番号をコピーして、コード一覧表で検索し、検索結果の右隣のセル(B列のコード)の値を部品表のC列に貼り付ければよいかと思い、書いてみたんですが…

Sub 別ブックから貼り付ける()
  Dim 検索する As Long
Windows("部品表.xls").Activate
検索する = cells(i,2).Value
Windows("コード一覧表.xls").Activate
ActiveWindow.SmallScroll Down:=-3
Selection.AutoFilter Field:=3, Criteria1:="=検索する", Operator:= xlAnd

と、してみたものの、検索しても、その検索結果の隣のセルのコードをどうやって取得すればいいのかが、わかりませんでした。

基本事項は本で学びましたが、呪文のようなコードはよく理解できません。懸命にネットで検索して、訳して理解する努力をしてはいますが。

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

部品表というブックがあります
A列に商品名、B列に商品番号が入力してあります。C列のコードは未入力です。
A列     B列     C列      
商品名  商品番号  コード
モータ  U-1325-L  
ホルダ  R-134256

また、コード一覧表という別のブックには、A列に商品番号と、B列にコードが、何千件も入力されています。

やりたいことは
部品表のC列のコード欄に、コード一覧表ブックから商品番号と一致するコードを貼り付けしたいのです。

部品表は、何百種類もありますので、関数...続きを読む

Aベストアンサー

こんにちは。
とりあえず実用性も踏まえました。
メインの動作はワークシート関数のVLOOKUPをVBA上で使用していますので理解はしやすいかと思います。
また、質問文から察するに「部品表.xls」と「コード一覧表.xls」の両方を開いて処理されていますが「コード一覧表.xls」はプログラム内で開いて閉じているので実行するときは「コード一覧表.xls」は閉じて置いてください。
Option Explicit
Sub Sample()
 Application.ScreenUpdating = False
 Dim I As Long
 Dim xlBook
 Set xlBook = Workbooks.Open("C:\★★\コード一覧表.xls") '★要変更★
 I = 2
 Do While Range("A" & I).Value <> ""
  ThisWorkbook.Worksheets("Sheet1").Range("C" & I).Value = Application.VLookup(ThisWorkbook.Worksheets("Sheet1").Range("B" & I).Value, xlBook.Worksheets("Sheet1").Range("A2:B65535"), 2, 0)
  I = I + 1
 Loop
 xlBook.Close
 Application.ScreenUpdating = True
 MsgBox ("完了")
End Sub

こんにちは。
とりあえず実用性も踏まえました。
メインの動作はワークシート関数のVLOOKUPをVBA上で使用していますので理解はしやすいかと思います。
また、質問文から察するに「部品表.xls」と「コード一覧表.xls」の両方を開いて処理されていますが「コード一覧表.xls」はプログラム内で開いて閉じているので実行するときは「コード一覧表.xls」は閉じて置いてください。
Option Explicit
Sub Sample()
 Application.ScreenUpdating = False
 Dim I As Long
 Dim xlBook
 Set xlBook = Workbooks....続きを読む

QエクセルVBAでクリップボード内容をクリア

こんにちは。
エクセルのVBAの処理の中で、ある部分をコピーしてそれを、
貼り付けする処理をしています。
処理終了後、ファイルを閉じるときに、クリップボードに
コピーの内容が残っている旨のメッセージがでてきます。
このメッセージを出さない様に、クリップボードの内容を
クリアするにはどのようにすればよろしいでしょうか?
申し訳ありませんが、お教え頂きますようお願いいたします。

Aベストアンサー

Excel.Application.CutCopyMode = False
Workbooks(fName).Close savechanges:=False

かな。1行目だけでいいかも。

Qエクセル:マクロ「Application.CutCopyMode = False」って?

エクセルのマクロを記録していると

「Application.CutCopyMode = False」

というものがよく出てきますが、これは何でしょう?
どういう意味のものかわかりません。
削除しても差し支えないのもでしょうか?

Aベストアンサー

「Application.CutCopyMode = False」の前で
セルのコピー、または切り取りを行っていると思います。
これは、その操作(セルのコピー、または切り取り)を無効にしているだけです。
------------
Range("A1").Select
Selection.Copy ← これを無効にしている
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
------------
上記の場合であれば、「Application.CutCopyMode = False」を削除しても問題ありませんが、
以下の場合、貼り付け処理でエラーになります。
------------
Range("A1").Select
Selection.Copy
Range("A2").Select
Application.CutCopyMode = False
ActiveSheet.Paste ← ココでエラー
------------
ご自分で、セルをコピーしてみると分かると思いますが、コピーした範囲が点線で点滅されます。
「Application.CutCopyMode = False」をすると、
その点滅がなくなります。

「Application.CutCopyMode = False」の前で
セルのコピー、または切り取りを行っていると思います。
これは、その操作(セルのコピー、または切り取り)を無効にしているだけです。
------------
Range("A1").Select
Selection.Copy ← これを無効にしている
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
------------
上記の場合であれば、「Application.CutCopyMode = False」を削除しても問題ありませんが、
以下の場合、貼り付け処理でエラーになります。
------------
...続きを読む


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング