人に聞けない痔の悩み、これでスッキリ >>

早速ですが、
シート1 行 3.000から4000.列 セル式 H-AM まで
シート2 行 3.000から4000.列 セル式 H-W まで
シート3 行 3.000から4000.列 セル式 H-AO まで
このような、BOOKが有ります。
データの追加(各シートのA-G 1行追加)に伴い シート1から 3まで セル式の計算が行われます。

で、計算が済んだあと、セルの変化をとらえて、次のアクションを起こすのですが、
セル式が完了するまで、時間を稼ぎたいのですが、
今は、タイマーで
act時刻 = Now
act時刻 = DateAdd("n", 2, act時刻 )
待ち時間 = DateAdd("s", 20, act時刻 )
Application.OnTime act時刻 , "next-job", 待ち時間

このタイマーが不正確(動作したりしなかったり)で 上の式は 2分待ちですが 1分以下だと 100%起動せず、
1分40秒以上でどうやら・・・

で、タイマー以外で 
シートにデータ送り込むVBA(15分毎のタイマー)の続きに 計算完了、もしくは、セル式の仕事を止めないで、セル式計算完了を まって、”next-job” の 仕事をさせたいのですが

セル式の仕事を止めない時間稼ぎは無いでしょうか 

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

A 回答 (4件)

#2-3、cjです。

#2お礼欄拝見しました。

> アドオンで、設定セル(5個ほど)にネット経由でデータの変更が有るたびに送り込まれます。
お返事頂いてありがとうございます。
そういうことなら、理解できます。
ただ、それはそれで、"普通"ではない"特殊"な条件な訳ですから、
"セル式の計算"という簡単な説明で済まされるものではないですし、
当初の質問文から、明示しておいた方が、より有用な回答が得られ易かったのだと思います。

まぁ、なんとなくDDE絡みの話かも?とか想像できなくもなかったのですが。
とりあえず、
Worksheet_Calculate と Application_AfterCalculate との違いは
ヘルプで確認してみてください。
これが役に立つかどうか、こちらでは未確認ですが、
#2-3で、役に立つことがもしあるとすれば、_AfterCalculate とリンクを張ったページぐらい
だと思います。

テスト環境を整えて、きちんとレスしようと思ったのですが、
株価RSSは私の環境(Excel2010x64)で使えるものがない(あるにはあるけど他の理由でNG)
ので、断念しました。

1、2年前にRSSに同期をとるVBAサンプルコードが紹介された書籍が出ていたように思いますが、書名が思い出せません。

たぶん、情報をお持ちの方は多数いらっしゃる筈ですから、
要件を整理して、質問建て直した方が、解決が近づくかも知れませんね。
RSSの具体名をタイトルにも掲げるとかして、重要度の高い情報(条件)が
目立つように質問すれば、いいことあるかも、です。

OnTime メソッドについての現状説明については、正直、よく解りませんでした。
あらためて考えてみましたが、
OnTime メソッドって、
1.予定した時刻に指定のプロシージャを実行する。
2.待機中のキューを先に実行させる。(#2での説明主旨)
3.非同期処理との連携で時刻が過ぎたら処理をキャンセルする
という使い方がメインなんだと思います。
この内、関連ありそうなのは2.3.ですが、
非同期・同期を問わず、外部オブジェクトを扱う際には、決定的な解決策になる場合も多いので、
この際、色々試してみるのも、無駄になることはないと思います。
ただ、RSSの処理の完了のタイミングをとったり、ワークシートの再計算終了のタイミングをとったり、
ということには、やはり役には立たないかな、とは思います。

あらためて、返信ありがとうございました。
解決に届くことを、そして、ご健闘をお祈りいたします。

この回答への補足

取り急ぎ

計算完了の件。
お説の通り、当方は セル式の計算完了を意識せず、コードを書いて良さそうです。

あと、実行速度を上げる件、挑戦してみます

補足日時:2014/09/16 14:16
    • good
    • 0
この回答へのお礼

たびたびのご教示ありがとうございました。取り急ぎお礼まで

お礼日時:2014/09/15 14:51

(2/2)



ここからは、提案、のようなものです。

> 1分40秒
えっと、再計算されるセルの数を心得た上での所見ですが、
ちょっと遅過ぎるように思います。
最適化を究めたとしてもそれ位掛かる複雑な計算なのかも知れませんが、
いずれにしても重い計算になっていることは確かなので、
手当て出来ることは色々ありますし、まだ手を付けていないこともあるかも知れませんから、
再計算に要する(正確には再計算が終了して次のVBAステートメントに進む迄に要する)時間
を短縮することについて、優先度を高めた方が、全体として苦労を減らせるような気がします。

まずVBA側単独で出来ることとして、例えば、
(#1さんご紹介の前スレでも示唆されている)
  Application.Calculation = xlCalculationManual
  処理
  Application.Calculation = xlCalculationAutomatic
とか、
  Application.EnableEvents = False
  処理
  Application.EnableEvents = True
(AfterCalculate() イベントを使う場合は×。その他のイベントだけをキャンセルするように工夫)
単セル毎に値の変更をして(その度に再計算して)いるのを止めて
代りに.Calculationを一時中止した上で、矩形範囲毎に纏めて配列出力して
すべての参照先の値が確定してから再計算させるように替えるとか、
  Application.Calculation = xlCalculationManual
  処理
  Range(再計算が必要な範囲に限定).Calculate
  Application.Calculation = xlCalculationAutomatic
とか、
マルチスレッドじゃない場合で、その他の条件があえば、
  Application.Calculation = xlCalculationManual
  処理
  Range(再計算が必要な範囲に限定).CalculateRowMajorOrder
  Application.Calculation = xlCalculationAutomatic
とか。

Excel数式側で出来ることとして、例えば、
マルチスレッド計算のオプションが適正に設定されているか確認する。
数珠つなぎ的な(チェーン)参照(A1=const B1=A1+1 C1=B1+1 D1=C1+1 ... みたいな)を減らす。
INDIRECT()関数は止め、なるべくINDEX()関数に(次点としてOFFSET()関数)に振り返る。
【マルチスレッド再計算、適用外の関数】
| PHONETIC
| CELL(format引数またはaddress引数を使用する場合)
| INDIRECT
| GETPIVOTDATA
| CUBE関連の各関数
| ADDRESS(5番目のパラメーsheet_namが提供されている場合)
| PivotTableを参照する任意のデータベース関(DSUM、DAVERAGE など)
| ERROR.TYPE
| HYPERLINK
| VBAおよびCOMアドインによるユーザー定義関数
以上はマルチスレッドによる再計算の恩恵を受け易くする為の話です。
数式の書き方として、参照する回数を減らす。
(同じ数式を書くのにも、同じセル範囲への参照を減らす書き方を心掛ける)
参照先、が、上、左にある(下、右にない)ようにシートデザインする。
COUNTIF SUMIF SUMPRODUCT 単セルで定義した配列数式 など、いわゆる重い数式を、
他の関数に振り返る。
フィルドラッグ等でコピー可能な連続した数式を、単セル毎に設定している代りに、
複数セル範囲を選択した上でCtrl+Shift+Enterで確定する配列数式(FormulaArray)にする。

とか。
もっと出来ること色々ありますが、条件によっては劇的に処理が速くなる、
なんてことも、経験された方多い、というか、ここで回答付けてる方の殆どが、
一度経験されたことと思います。
実物を見た訳じゃないので、こちらが想像するより遥かに最適化が進んでいるのかも知れませんが、
やり残したことがあれば、参考になれば嬉しいです。

また、前スレでは完全に否定されて一蹴されていますが、
再計算のすべてをVBAで処理する、というのも、(再計算が必要なセル数を心得た上で)
処理を速める正しい方向である場合が多いです。
(シートに数式を置かない(減らす)設計って、直接的な処理速度以外の面でも幾つか恩恵がありますね。)
VBAの記述ひとつひとつにも遅い速いある訳で、下手すれば、ワークシートの再計算の方がマシ、
うまくすれば、あっという間、なんて、色々な状況や環境変数的なものを考える必要はありますが。

参考URLは、私が最近よく参照(VBAというよりシート設計の最適化に役立つ)ぺージです。
『Excel 2010 におけるパフォーマンス: パフォーマンスの向上と制限の改善』
このページで、以下のキーワード検索すると役にたつことあるかも?です。
マルチスレッド計算 INDIRECT Workbook.ForceFullCalculation 名前付きテーブルと構造化参照 Range.CalculateRowMajorOrder

大変恐縮ながら、私の疑問にもお応え頂けると有難いです。
お願いしますm(__)m

長、失礼しました。

(以上です)

参考URL:http://msdn.microsoft.com/ja-jp/library/office/f …(v=office.14).aspx
    • good
    • 0

(1/2)


こんにちは。お邪魔します。
すみませんが、長くなるので連投します。

ニーズを正確に理解出来ている訳ではないので、それぞれのパーツの話、、、です。

> で、時間を稼ぎたいのですが、
> 今は、タイマーで
> act時刻 = Now
> act時刻 = DateAdd("n", 2, act時刻 )
> 待ち時間 = DateAdd("s", 20, act時刻 )
> Application.OnTime act時刻 , "next-job", 待ち時間

> このタイマーが不正確(動作したりしなかったり)で 上の式は 2分待ちですが 1分以下だと 100%起動せず、
> 1分40秒以上でどうやら・・・

本題とは関係ないですが、OnTime メソッドの話から。
考え方として、
 ×| EarliestTime | ★ | LatestTime |×
EarliestTime と LatestTime の間★の期間に、【タイミング】が合えば、Procedureは実行されます。
i)「OnTime メソッドを実行したプロシージャ」 を抜け(の処理が終り)、
ii)「OnTime メソッドを実行したプロシージャ、を呼び出した大元を含む実行中のすべてプロシージャ」 を抜け(の処理が終り)、
iii)「他に実行されるべきアプリケーションのタスク」 の処理が済んだ後
この【タイミング】が★の期間内に収まっていれば、Procedureが実行されます。
ですから、EarliestTimeを「早め」に設定し、LatestTime「遅め」に設定し、★の期間を「長く」取った方が、
Procedureは実行され易い、ということになります。
そういう意味では
 Application.OnTime Now, "proc", DateAdd("n", 20, Now)
「今、から、20分後、まで」みたいに書いておけば、【タイミング】が来次第、Procedureは実行されます。
しかし、今回の課題の意味からすると、引数LatestTimeは指定しないで、
 Application.OnTime Now, "proc"
のようなことがなさりたいのではないでしょうか?
引数LatestTimeの意味、としては、指定した時刻を過ぎたら、Procedureを実行しない、です。
もし、この点で不明瞭なのでしたら、今一度、VBAのヘルプ等で確認してみて下さい・
Procedureが実行可能になる【タイミング】について補足しますが、
例えば、ActiveXのコマンドボタンで呼び出すような場合、
1: コマンドボタン押下げ
2: Private Sub CommandButton1_Click()
3:   SubProc
8: End Sub
4: Sub SubProc()
5:   Application.OnTime Now, "proc"
6:   時間の掛かる処理
7: End Sub
9:コマンドボタンの押下げ表示を元に戻す既定の処理
10:OnTime メソッドで設定したProcedureが実行可能になる
11: Sub Proc()
12:   処理
13: End Sub
のような順番に処理されることになります。
直接的には、この順番を替えたり、割り込んだりすることは出来ません。
また、上の順番の中で8:9:を4:より前に済ませておく必要があるような場面など
ある程度処理の順番を操作したい場合に、
有効な方法として用意されているのがOnTime メソッドでもあり、
この例では、3:の呼び出し部分をOnTime メソッドに書き換えるような使い方
が、重要度の高いOnTime メソッドの代表的な使用例でもあります。
たぶん、今回の課題は直接的にはOnTime メソッドとは無縁だと思います。
(5:6:の順が逆になるように書くことはあると思いますが、、、。)
ただ、処理の流れを時系列で把握することは重要ですから、
概念提示という意味では、比較的解り易い例だったと思います。
どのタイミングで End Sub を通るのか、ぐらいは把握していないと書けないものも多いですしね。

> シートにデータ送り込むVBA(15分毎のタイマー)の続きに 
> 計算完了、もしくは、セル式の仕事を止めないで、
> セル式計算完了を まって、”next-job” の 仕事をさせたいのですが

> セル式の計算
これって「ワークシート(セル範囲)の再計算」のことでいいしょうか?

> セル式の仕事を止めない時間稼ぎは無いでしょうか
"セルの仕事を止め"る、とは、再計算を中断または終了させる?という意味に読めますけど、
何故そんなことが問題になるのか私には理解できないので教えて頂けませんか。
例えば、セルA1の値を変更(追加)するとして、
値を入力し、Enterキーを押しても、
直接・間接にA1を参照先に持つワークシート上のすべての再計算が終了するまで、
A1セルは確定しませんよね。
VBAでも同じで、
 Cells(1, 1) = "hoge"
に追従する再計算が終了するまでは次の行の処理に進めません。
なので、次の行を実行する時には、前の行の処理に連動した再計算は終了しているのでは?
これが普通で、それ以外は特殊、というのが、これまでの私の常識でしたので、
何か自信無くしそうですが、少し補足説明を戴けたら助かります。
Sleep関数を使っていることも気になっているのですが、
再計算を非同期で実行させるような状況なのでしょうか?
私はマルチスレッドの再計算関連に詳しくないので、私が知らない何か、なのでしょうけれども、、、。

不躾ながら、
Application.CalculationState が使える、というのも、私の理解の外ですが、
それなら、Application_AfterCalculate イベントを使って、
VBAの処理に割り込ませることが有効なケースもあろうかと。
これは本題に近い話だと思いますが、、、。
/// ThisWorkbook モジュール
Option Explicit
Private WithEvents xlApp As Application
Private Sub Workbook_Open()
  Set xlApp = Application
End Sub
Private Sub xlApp_AfterCalculate()
  DoEvents
End Sub
///(Workbook_Open()を(が)実行すれ(されれ)ばxlApp_AfterCalculate()が有効に)
仮に、DoEventsだけを処理させていますが、
これは再計算時に、ひとつの参照チェーン毎に計算が終了する度に発生するイベントですので、
"セル式の仕事を止めない時間稼ぎ"という言葉の指す意味合いによっては有効な手段かも、です。
(再計算に掛かる時間が長過ぎて、PCがスリープしちゃうとか?)
実際には、Publicなフラグ変数を用意しておいて、セル値の変更処理の度に
 フラグ=n → セル値変更 → 再計算
 → _AfterCalculate() → フラグ>0ならば、n番めの再計算終了と判断して 必要な処理 → フラグ=0
のようにしてみれば使い道は色々ありそうですけれど。
 フラグ = n
 Cells(1, 1) = "hoge"
  ↓
 再計算
  ↓ ←ここに_AfterCalculate()が割り込みます。
 フラグ = n + 1
 次の処理

(次の投稿に続きます)

参考URL:http://msdn.microsoft.com/ja-jp/library/office/f …(v=office.15).aspx
    • good
    • 0
この回答へのお礼

恐縮です。
我流でコマンド書いている身としましては、赤面の限りです
さて、
 Application.OnTime Now, "proc", DateAdd("n", 20, Now)
「今、から、20分後、まで」みたいに書いておけば、【タイミング】が来次第、Procedureは実行されます。
しかし、今回の課題の意味からすると、引数LatestTimeは指定しないで、
 Application.OnTime Now, "proc"
のようなことがなさりたいのではないでしょうか?

当方の理解では、
タイマー指定時間に実行できる環境でなければ、スルーされ、実行されない。その為のLatestTimeで もう一度この時間にトライされる。よって、トライは2回。
開始時間から、LatestTimeまで パルス的にトライしてくれるかどうか、調べたのですが、探せませんでした。
よって
Application.OnTime Now, "proc" パルス的にトライして可能な時間に成ったら・・・
この概念は有りませんでした。これが可能なら、最速の可能な時間に処理が行われることに成り、当方としては、これで願ったり・・・です。

基本なロジックは
アドオンで、設定セル(5個ほど)にネット経由でデータの変更が有るたびに送り込まれます。設定セルだけですから、全て上書きです。
それを、Worksheet_Calculate()(これ以外不可)で変化を捉え、時系列に記録を伸ばします。
早い話、株価ですが、以上はコントロール不可能。まったく、相手任せ。いつ仕事がされるか不明(変化したら・・・ですから)

現状は

朝の起動時から9:00 から 15分ピッチで
反復時刻 = 反復時刻 + インターバル
待ち時間 = DateAdd("s", 20, 反復時刻)
Application.OnTime 反復時刻, "orgdata.15job", 待ち時間
タイマーを予約し、その時間に 集まったデータを15分データとして、集約します。集約の後、シートにデータを 飛ばします。

15job()
次の15分タイマーを予約
2分後に、セル計算後の”評価JOB”をタイマー予約
コピー開始
orgdata.Range("a4:b4").Copy Destination:=fun15.Range("A" & endsisu)
orgdata.Range("c4:f4").Copy Destination:=fun15.Range("d" & endsisu)
fun15.Range("c" & 100 & ":c" & 100).Copy
fun15.Range("c" & endsisu & ":c" & endsisu).PasteSpecial Paste:=xlPasteFormulas
fun15.Range("H" & 100 & ":AZ" & 100).Copy
fun15.Range("H"&endsisu&":AZ" & endsisu).PasteSpecial Paste:=xlPasteFormulas

fun60.Cells(end60, 1) = fun15.Cells(st15, 1)
省略
fun60.Cells(end60, 7) = fun15.Cells(end15, 7)

fun60.Range("c" & 100 & ":c" & 100).Copy
fun60.Range("c"&end60& ":c" & end60).PasteSpecial Paste:=xlPasteFormulas
fun60.Range("H" & 100 & ":Z" & 100).Copy
fun60.Range("H"&end60&":Z" & end60).PasteSpecial Paste:=xlPasteFormulas
15job end

2分後の 評価JOB
シートfun15の特定の(4個ほど)セル変化をとらえてアクションを起こす、起こさないを決定します。セル変化を単純に IF文で 比較するだけです。
その結果がTRUEなら フォーム および IEを起動して、証券会社にアクションが起きます。

ここが、お説のようなら(セル式の計算の完了を待って次のコードを実行する) 待ち時間など考慮せず(2分後のタイマー予約なしで)、15job end の続きに、当方は IFの比較文(タイマー中身)を記述すればいいことに成ります。

タイマー有りの時が、時間を短く予約すると タイマー不発
90秒でもやや不安定(アドオンのデータ更新が影響か)。2分でどうにか・・・

で、次のテストが 2分後タイマーを止めて
コピーのあと
Do
Sleep (10)
DoEvents
xxx = xxx + 1
If xxx > 10000 Then ’ CalculationState = xlDone使ったことないので、念のため
yy = orgdata.Cells(1000, 14).End(xlUp).Row + 1
orgdata.Cells(yy, 14) = "ループ-out" & Now
Exit Do
End If
If Application.CalculationState = xlDone Then
yy = orgdata.Cells(1000, 14).End(xlUp).Row + 1
orgdata.Cells(yy, 14) = "State = xlDone-out" & Now
しまった、ここに xxx の数値を 記録すれば、このループが必要か判明する(来週の話)
Exit Do
End If
Loop

Call 評価JOB

これで、不発は無いようですが
マクロin2014/09/13 1:00:12
マクロcopy-in2014/09/13 1:00:35
マクロfun copy-in2014/09/13 1:00:41
マクロ 60fun copy-out2014/09/13 1:00:51
nothing2014/09/13 1:00:52
State = xlDone-out2014/09/13 1:00:53
注文確認-in2014/09/13 1:00:54
注文確認-suru-out2014/09/13 1:00:55
マクロin2014/09/13 1:15:13
マクロcopy-in2014/09/13 1:15:36
マクロfun copy-in2014/09/13 1:15:41
マクロ 60fun copy-out2014/09/13 1:15:49
変化
買いから 売り
State = xlDone-out2014/09/13 1:15:52
注文確認-in2014/09/13 1:15:53
2014/09/13 1:16:00返済 doTradeAfterNoon 
2014/09/13 1:17:01新規売り 
2014/09/13 1:17:51返済注文 Morning2 

マクロin2014/09/13 1:00:12 のスタートから(12秒は無視してください)
State = xlDone-out2014/09/13 1:15:52 まで 40秒掛かっている計算。
(それ以降の時間はスルーしてください、IEが絡みますので)

さて、頂いたご提案
xlApp_AfterCalculate() について、
話がかなり戻るのですが、本来なら、Worksheet_Change(ByVal Target As Range)で 計算式完了後の変化を捉えることが出来るはずなんですが、これも、不発で
むしろ、これの方が 良いかも・・・テストさせていただきます。

時間短縮、あれや、これや、ご教示ありがとうございました。時間がかかりそうですが、検証させていただきたく。 感謝

お礼日時:2014/09/13 21:55

application.CalculationState の xlCalculating 、xlDone 、 xlPending


使えませんかね?
半年前のあなたとワタシ。
http://oshiete.goo.ne.jp/qa/8523503.html
    • good
    • 0
この回答へのお礼

再度、恐縮です。

今、タイマーやめて それで 実験中。うまくゆきそうです。
Do
Sleep (10)
DoEvents
x = x + 1
If x > 10000 Then
  yy = orgdata.Cells(1000, 14).End(xlUp).Row + 1
     orgdata.Cells(yy, 14) = "loop-out" & Now
Exit Do
End If
If Application.CalculationState = xlDone Then
  yy = orgdata.Cells(1000, 14).End(xlUp).Row + 1
    orgdata.Cells(yy, 14) = "State = xlDone-out" & Now
Exit Do
End If
Loop
Call nextjob

データ転送から LOOP 抜ける時間差は 50秒以下なのですが、

分からないのが、タイマー使って 時間差(90秒前後)で 仕事をさせると、タイマーが動作しない。
時間を120秒くらい遅らせて タイマー使うと何とか・・・
それでも、動作し無い時も有り、不安定。

タイマーは仕事が忙しいとサボるらしいのですが、(その為に次の時間があるはずなんだけど)
どうも、このああたりが・・・・

お礼日時:2014/09/13 00:13

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

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

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

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

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

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
...続きを読む

Qエクセル 再計算とVBA の優先順位??

シュミレーションの結果が合わないのですが

エクセル 列 約70 1パターン6列 の10個と 合計列
行は 約300 300*70= 21.000セル?
セルには 何らかの 式が埋め込まれています。
1パターンの中の列に 手間のかかりそうなのは 1列だけ18行の移動平均があります。他の列は、隣の足し算とか、比較してどうこう・・・(よって 移動平均は 合計10列)

で、このワンパターン に 各パラメーターが有って これを変化させて 再計算して 合計の変化でMAXを 取り出す

プログラムは
c4=0 i4=0 ・・・・10個のパラメーターに初期値代入 
この時点で セルの再計算
do
if max<合計セル then
  do
   Call Sleep(5)
  DoEvents
   z = z + 1
   If z > 5 Then
    Exit Do
   End If
loop
ここに 入れる? 合計セルが遅れて パラメーターが先行してたら意味ないような??
  max=合計セル 
  記録(max、c4.i4・・・・・・)

endif

c4=c4+1 この時再計算
sleepがないと 秒 10回くらい変化する
21.000セルが 10回計算してるとは 思えないのですが?
if c4>6 then
c4=0
i4=i4+1  この時再計算

if i4>6 then
i4=0
o4=o4+1  この時再計算
10個まで同じロジックが続く

ここに sleep を 入れる??

loop

で、問題は sleep を 入れないとき、計算が進んで、別の保存プログラムにパラメータを入れて合計を見るのですが、記録されたパラメーターと合計が合わないのです。
しかし、
MAXが更新されたときの中にブレークポイントを付けて、ストップ状態で(別のことしてたので、かなり止まってた)、保存プログラムにパラメーター代入、合計確認すると合っていました。

で、知りたいのは
再計算とVBAの整合性は エクセルは 取ってくれないのか?
再計算が完了しようが 途中であろうと、VBAだけ お構いなしに ループするのか

はっきりしてるのは パラメータを弄った 後に Sleep入れるのが 最適なのでしょうが、6*6*6*・・・
の仕事量ですから、ここで 0.5秒取られても 合計時間がかかる。

記録の所だけなら、MAXが更新した時だけですから 時間が早いのですが・・・

この辺の状況、ご存知の方が いらっしゃればご教示ください。

シュミレーションの結果が合わないのですが

エクセル 列 約70 1パターン6列 の10個と 合計列
行は 約300 300*70= 21.000セル?
セルには 何らかの 式が埋め込まれています。
1パターンの中の列に 手間のかかりそうなのは 1列だけ18行の移動平均があります。他の列は、隣の足し算とか、比較してどうこう・・・(よって 移動平均は 合計10列)

で、このワンパターン に 各パラメーターが有って これを変化させて 再計算して 合計の変化でMAXを 取り出す

プログラムは
c4=...続きを読む

Aベストアンサー

試したわけではありませんが
http://msdn.microsoft.com/ja-jp/library/ff196047.aspx

セルに値代入後かな?に
(コードを端折られているので特定できません。)
そちらでお考えになってください。
do while Application.CalculationState <> xlDone
sleep 200
loop
で待機

http://msdn.microsoft.com/ja-jp/library/ff821508(v=office.15).aspx
を余計なイベントが発生しないように適当なところに挟み込む
(必要があるかどうかは、そちらで確認してください)

あと、位取りに「.」を使うのは止めてください。 21,000
余計なところで思考がストップしてしまいました (^_^;)
日本語環境でないPCでしたらご容赦。

QEXCELファイルのカレントフォルダを取得するには?

EXCELファイルのカレントフォルダを取得するには?

C:\経理\予算.xls

D:\2005年度\予算.xls

EXCEL97ファイルがあります。

VBAで
  カレントフォルダ名
(C:\経理\,D:\2005年度\)
を取得する事は可能でしょうか?

CURDIRでは上手い方法が見つかりませんでした。

Aベストアンサー

こんばんは。
Excel97 でも、同じですね。以下で試してみてください。

Sub test()
'このブックのパス
a = ThisWorkbook.Path
'アクティブブックのパス
b = ActiveWorkbook.Path
'Excelで設定されたデフォルトパス
c = Application.DefaultFilePath
'カレントディレクトリ
d = CurDir
MsgBox "このブックのパス   : " & a & Chr(13) & _
   "アクティブブックのパス: " & b & Chr(13) & _
   "デフォルトパス    : " & c & Chr(13) & _
   "カレントディレクトリ : " & d & Chr(13)
End Sub

Qエクセル マクロで指定フォルダを開く

エクセルにて
指定フォルダを開く、マクロがあれば教えて頂けないでしょうか。
よろしくお願いいたします。

Aベストアンサー

こんにちは。

こういうものですか?
開くフォルダを変えたいときは targ に与えるパスを変更します。

Sub OpenFolders()
Dim targ As String
targ = "C:\"
Shell "C:\Windows\Explorer.exe " & targ, vbNormalFocus
End Sub

QExcel VBAにて2つの処理を同時実行可能?

是非お力をお貸し下さい。
よろしくお願いします。
Windows XP
Excel2003 (VB6.0)

メイン処理が非常に時間がかかるため、フォームを表示させ
文字(Label)を点滅させて「動いている(ハングアップしていない)」ことを
使用者に伝えようとしています。

--- Form1内のコード(メイン) ---
Sub Main()

Form2.Show vbModeless

[ ~メイン処理~ ]

End Sub

--- Form2内のコード ---
Sub UserForm_Initialize()

Call Blink

End Sub

--- 標準モジュール内のコード ---
Sub Blink()

If Form2.Label1.Visible = True Then
Form2.Label1.Visible = False
Else
Form2.Label1.Visible = True
End If

Form2.Repaint

DoEvents

Application.OnTime Now + TimeValue("00:00:01"), "Blink"

End Sub


これを実行すると、メイン処理が終了した後にForm2内の文字が
点滅します。

実現したいのは「使用者が動いていることを(ハングアップしていない
ことを)確認出来る」という点です。

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

是非お力をお貸し下さい。
よろしくお願いします。
Windows XP
Excel2003 (VB6.0)

メイン処理が非常に時間がかかるため、フォームを表示させ
文字(Label)を点滅させて「動いている(ハングアップしていない)」ことを
使用者に伝えようとしています。

--- Form1内のコード(メイン) ---
Sub Main()

Form2.Show vbModeless

[ ~メイン処理~ ]

End Sub

--- Form2内のコード ---
Sub UserForm_Initialize()

Call Blink

End Sub

--- 標準モジュール内のコード ---
Sub Blink()

If Form2.L...続きを読む

Aベストアンサー

単純に、HTMLファイルを表示するとかいうのではだめでしょうか?VBAに影響を与えることなくブラウザ側が勝手にやってくれるので、負荷は少ないように思うのですが。
的を外した回答でしたらすみません。

QEXCELのVBAでのSelectとActivateの違い

VBAの初心者です。
ExcelのVBAでメッセージを表示してシートを切換えるというのを作りたいのです。
見真似で作成したのが↓です。


Dim rtn As String
rtn = MsgBox("シートを切換えますか", vbYesNo, "シートの切替")
If rtn = vbYes Then
Worksheets("送付先一覧").Activate
Range("a1").Select

Else
Exit Sub
End If

動作確認はできましたが、上記の「Activate」を「Select」に変更しても特に動作異常がありません。
そこで、疑問ですが、「Activate」と「Select」ってどうやって使い分けるのでしょうか?

Aベストアンサー

こんにちは。

通常は、シートもセルも Select でよいと思います。
選択して、扱えるようにするということだと思います。

#2さんも述べておりますが、Activate って、ひとつを選ぶことですね。でも、なぜか、Activate は、ほとんど使いません。

たぶん、Select は、選択した後に、その選択したものを、そのままオブジェクトとして確保して使えるので便利だから選ばれるのかもしれません。

Select → Selection
として使えます。

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

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

Aベストアンサー

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

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

QエクセルVBAにおけるON TIMEメソッドの解除方法について

こんにちは。現在エクセルでフォームを使用したVBAマクロを作成中です。
内容はフォーム内のテキストボックスに制限時間を設けて文字入力を行ってもらうものです。

Sub テスト()

do until
 ・
 ・
call timeup
loop

End Sub

Sub timeup()
dim timekp as integer
'テストの開始時間をキープ

'Application.OnTime timekp + TimeValue("1:00:00"),"endform"
'1時間経過後終了を促すフォームを表示する

End Sub

Sub endform()

load userform1
userform1.show
'エクセル終了のコマンドボタンがついているフォームを表示する

End Sub

マクロは大まかに記述しましたが以上のようにすると、一度はマクロの作成が成功したように終了するのですが、ブックを開けたままでいると1時間後に自動的にuserform1が表示されてしまいます。また、ブックを閉じていても自動的にオープンし、(マクロを有効にする)をONにするとデバック状態となります。
変数のtimekpを初期化する事で凌げると思ったのですが、うまくいきません。
以前マクロのヘルプを操作している時にON TIMEメソッドを解除する方法が掲載されていたように思うのですが、探し方がマズイのか見つけられませんでした。
マクロの記述方法がマズイのでしょうか?ON TIMEメソッドを解除しない限り、いつまでもこのメソッドは効力を発揮するのでしょうか?
また、ON TIMEメソッドを解除できるメソッドや良い解決方法がありましたら入門書等を片手にマクロを作成している素人にご教授の程お願い致します。

こんにちは。現在エクセルでフォームを使用したVBAマクロを作成中です。
内容はフォーム内のテキストボックスに制限時間を設けて文字入力を行ってもらうものです。

Sub テスト()

do until
 ・
 ・
call timeup
loop

End Sub

Sub timeup()
dim timekp as integer
'テストの開始時間をキープ

'Application.OnTime timekp + TimeValue("1:00:00"),"endform"
'1時間経過後終了を促すフォームを表示する

End Sub

Sub endform()

load userform1
userform1.show
'エクセル終了のコマ...続きを読む

Aベストアンサー

>ON TIMEメソッドを解除
OnTimeのヘルプはごらんになられましたか?
expression.OnTime(EarliestTime, Procedure, LatestTime, Schedule)

Schedule にfalseを設定することで、直前の実行指定を解除することができます。
具体的には
Application.OnTime timekp + TimeValue("1:00:00"),"endform",,false
とでもすればいいです。

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」を削除しても問題ありませんが、
以下の場合、貼り付け処理でエラーになります。
------------
...続きを読む

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&Aランキング