1分ごとの数値のマクロ

質問

エクセルのVBAで、楽天RSSから1分ごとに株価が入るようにするマクロを作りたいのですが、初心者なので手が出ません。
作成したいものは以下のようなものです。

1分ごとに、1001行のA列に時間が入り、1001行のB列からIV列に、1行上の1000行の数値(この数値はあらかじめ入っていて変化します)が入るようにします。
新しい株価が1001行に入るようにして、時間が前の株価は1分ごとに1行ずつ下に下がっていくことになります。
10時に最初の株価が入ったのが例1でして、1分経ちますと例2のように1行繰り下がり、もう1分経ちますと例3のようにまた1行繰り下がります。
どうぞよろしくお願いします。

例1
　　　　　　 A 　　　　　　　 B 　　　 　　　C ・・・・

1000 　　　　　　　　　　　100 　 　　　　50
1001　　10:00:00 　　　　100 　　　　　50
1002　　　
1003

例2
　　　　　　 A 　　　　　　　 B 　　　 　　　C ・・・・

1000 　　　　　　　　　　　 105 　 　　　45
1001　　10:01:00 　　　　105 　　　　　45
1002　　10:00:00 　　　　100 　　　　　50
1003

例3
　　　　　　 A 　　　　　　　 B 　　　 　　　C ・・・・

1000 　　　　　　　　　　　 110 　 　　　40
1001　　10:02:00 　　　　110 　　　　　40
1002　　10:01:00 　　　　105 　　　　　45
1003　　10:00:00 　　　　100 　　　　　50

回答No.2

Wendy02 · ベストアンサー

こんばんは。

人によっていろいろなリクエストがありますね。人によっては、3秒以内とか、また、リアルタイムで書き換えるとか……。

'標準モジュール
'----------------------------------------

Dim myTime As Date
Dim flg As Boolean
Sub Ontime_Set()
　　'記録スタート
　　If flg = False Then
　　　　flg = True
　　　　myTime = Now + TimeSerial(0, 1, 0)　'時間設定
　　ElseIf flg = True Then
　　　　flg = False
　　Else
　　　　Exit Sub
　　End If
　　With ActiveSheet
　　　　If .Cells(1001, 1).Value = "" Then
　　　　　　.Cells(1001, 1).Value = Format(Now, "hh:mm")
　　　　　　.Range(.Cells(1000, 2), .Cells(1000, 256)).Copy .Range(.Cells(1001, 2), .Cells(1001, 256))
　　　　End If
　　End With
　　Application.OnTime EarliestTime:=myTime, _
　　Procedure:="my_Procedure", Schedule:=flg
　　If flg = False Then
　　　　myTime = 0
　　End If
End Sub

Sub Ontime_Reset()
　　'タイマー解除
　　On Error Resume Next
　　Application.OnTime EarliestTime:=myTime, _
　　Procedure:="my_Procedure", Schedule:=False
　　If Err.Number > 0 Then
　　　　MsgBox "OnTime設定はされていません。", 64
　　　　Err.Clear
　　　　flg = False
　　Else
　　　　MsgBox myTime & "の設定は解除されました。", 64
　　　　flg = False
　　　　myTime = Empty
　　End If
End Sub

Sub my_Procedure()
　　'実行マクロ
　　Application.ScreenUpdating = False
　　With ActiveSheet
　　　　.Rows(1001).Insert Shift:=xlDown
　　　　.Cells(1001, 1).Value = Format(Now, "hh:mm")
　　　　.Range(.Cells(1000, 2), .Cells(1000, 256)).Copy .Range(.Cells(1001, 2), .Cells(1001, 256))
　　End With
　　Application.ScreenUpdating = True
　　flg = False
　　myTime = 0
　　Call Ontime_Set
End Sub

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

お礼

どうもありがとうございます。
エクセルで確かめていましてお礼が遅れてしまい、すみませんでした。
長いマクロを書いて頂き感謝しています。
質問しました通りに、うまく動きました。
ただ、問題点が生じてしまいました。
1行から999行の間のセルで作業をしようと考えていまして、関数を入力したのですが、行の数字を絶対参照にしましても、マクロが実行される1分ごとに数字が1つ増えてしまうのです。
1行から999行のセルの数字が変わらないようにする方法はあるのでしょうか。
また別シートを選択しますと、マクロが停止しましてデータの更新が1分おきになされなくなってしまいます。
ですので別シートでの作業も意味が無くなってしまいます。
宜しければ、お時間のある時に解決策を頂けますと嬉しいです。
どうもありがとうございました。

回答No.3

Wendy02

こんばんは。

一応、私の想定範囲のことでしたら、以下のようになります。

>1行から999行の間のセルで作業をしようと考えていまして、関数を入力したのですが、行の数字を絶対参照にしましても、マクロが実行される1分ごとに数字が1つ増えてしまうのです。

1行～999行の中のセルの数式が、1000行以上のデータを参照しているとなると、すぐには思いつきませんので、数式の例を見せてください。一般的な回答として、データが動いても、動かさないようにするには、INDIRECT関数を使います。行が増えても減っても、同じ場所を指しています。

>また別シートを選択しますと、マクロが停止しましてデータの更新が1分おきになされなくなってしまいます。
>ですので別シートでの作業も意味が無くなってしまいます。

それは、DDE（データを送る側の仕組み)からの送信で、シートを決めているはずですから、

そのVBEditor 画面の一番上から、ActiveSheet のところにカーソルを持っていって、編集--置換で、

　ActiveSheet　'検索
　　↓
　Worksheets("Sheet1") '置換（実際にデータが入るシート)

で、データを、カレントプロジェクト--すべて置換で、そのマクロコード全体を置換してしまってください。（2箇所)そうすれば、シートを替えても、勝手にデータを取っているはずです。

お礼

Wendy02様、こんにちは。
お返事、どうもありがとうございます。

数値の更新で新たな問題点に気付きましたので、そちらからご報告いたします。
1000行目の数値が1001行目に入るようになっていますが、1000行目のA列以外のセルの関数式は、楽天RSSの関数式である"=RSS|'1334.T'!現在値"などが入っています。
1001行にコピーされる時に、その関数式がコピーされてセルに入ります。
その関数式のまま1001行以下に入っていきますので、現在値である同じ数値が更新する状態になっています。(同じ列には同じ数値が並んだ状態です)
関数式ではなく、1000行目のセルの数値自身を1001行目のセルに入れる事は出来ますでしょうか。

>数式の例を見せてください。

平均値、最大値、最小値、標準偏差、乖離率などを求める式です。
平均値の数式は、以下のようなものです。
=ROUND(AVERAGE(B1001:B1010),1)
=ROUND(AVERAGE(B1001:B1020),1)
=ROUND(AVERAGE(B1001:B1030),1)
・・・・・
平均値を求めるセルの数は、1列だけで数十個、考えていまして、A列を除く255列すべての列で、数十個の平均値を求めたいと思っています。
最大値の数式は
=MAX(B1001:B1030)でして、1列につき20個ほどを、255列で考えています。
他の数式も同様でして、1001行から先のセルを参照します。
使用したい数式(セル)の数が多いものですから、お教えいただきましたINDIRECT関数を使った方法は困難に感じています。

ある方法が思い浮かんだのですが、可能かどうか分かりません。
ご教授頂けますとありがたいです。
マクロで平均値などを計算するとしましたら、求めたい行数で計算されるのかという事です。
計算式の数が多いので、元々、セルで計算するよりも、マクロで計算した方が良いような気がしますが、マクロの式が分からず、今までセルで算出していました。

別シートを選択するとマクロが停止するのではなく、その選択したシートがActiveSheetとなって、選択したシートでマクロが実行されて、データを更新したいシートが動かなくなるのですね。
置換をしましたら、うまくいきました、ありがとうございます。

大変感謝しております。
どうぞよろしくお願いします。

補足

お礼の欄に補足して書けませんので、こちらに書きます。
解決する事が出来ました。

実際に、マクロで平均値を出してみましたら、求める行数で算出できましたが、行数の変化するシートで作業するのは大変だと感じ、別シートで作業する事にしました。
fujillin様のお考えのおかげです、どうもありがとうございました。

今回は、Wendy02様に助けて頂かなければ、自分ではどうにもなりませんでした。
心より感謝しています。
どうもありがとうございました。

回答No.1

fujillin

楽天RSSを使用したこともないし、実験できる環境にもないので…

◇別シートにデータを表示させておいて、それをマクロで1分ごとに転記するのなら、比較的簡単にできます。
かなり近い例が以下にあります。
http://kabu.muimi.com/k/rakuten_rss/2/index.html
表示するシートと記録するシートを別にしておけばほとんど同じではないでしょうか。

◇直接データを取得できるのかどうか不明ですが、以下にヒントになりそうな記述があります。
（楽天RSSのマニュアル等にもっと有効な情報があるかも知れませんが…）
http://plaza.rakuten.co.jp/acknight/diary/200711190001/

なお、1分ごと実行するのは、
VBAのOnTime メソッドなどを利用すれば実現できるでしょう。

お礼

ご回答をありがとうございます。
私は1行挿入する方法を考えたのですが、うまくいかない点に気づきました。
表示するシートと記録するシートを別にするという方法もあったのですね。
できれば、別シートを使わないで済む方法がありましたらありがたいです。
サイトのご紹介もして頂きまして、参考になりました。
どうもありがとうございました。