vba Excel复制实时数据并从中创建图表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16715285/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel to copy realtime data and create a chart out of it
提问by Chandru
I am very new to VBA programming.
我对 VBA 编程很陌生。
I have a excel sheet where real time data is populated to a cell say for example Sheet1.A6 everysecond.
我有一个 Excel 工作表，其中实时数据被填充到一个单元格中，例如 Sheet1.A6 每秒。
I am in need of a macro which will run every 1 minute to copy the Sheet1.A6 value to another sheet along with timestamp like value in Sheet2.A and timestamp in Sheet2.B .
我需要一个宏，它将每 1 分钟运行一次，以将 Sheet1.A6 值以及 Sheet2.A 中的时间戳和 Sheet2.B 中的时间戳复制到另一个工作表中。
Sheet2 Output will be like
Sheet2 输出将类似于
Value * Time
价值 * 时间
23.1 * 11:00 AM
23.1 * 11:00 上午
22.5 * 11:01 AM
22.5 * 11:01 上午
22.6 * 11:02 AM
22.6 * 11:02 上午
.......... .............
……………………
Thanks for your help.
谢谢你的帮助。
采纳答案by Our Man in Bananas
you want some code to run every minute.
您希望每分钟运行一些代码。
You should be able to do this with the Application.OnTimefunction.
您应该可以使用Application.OnTime函数执行此操作。
Here are a couple of references and examples:
以下是一些参考资料和示例：
First create your procedure which will copy the cell to the destination sheet.
首先创建将单元格复制到目标工作表的过程。
Then in a normal module you can call it like this:
然后在普通模块中，您可以这样调用它：
Sub SetTimeForCopy()
dim nextTime as date
nextTime = Now + TimeValue("00:01:00")
Application.OnTime TimeToRun, "CopyValue"
End Sub
copy the value over like this:
像这样复制值：
Sub CopyValue()
application.Calculate
worksheets("Sheet2").Range("c" & Cells(Rows.Count, 1).End(xlUp).Row+1).Value = Worksheets("Sheet1").Range("A6").Value
Call SetTimeForCopy' call the schedule code again
End Sub
NOTICEthat in CopyValuethe SetTimeForCopyprocedure is called which will schedule the next copy.
请注意，在CopyValue中调用 SetTimeForCopy过程，该过程将安排下一次复制。
回答by gssi
Why are you moving the real time data point? Use a variable to identify the computed location for each successive entry.[You can use the OFFSET and COUNTA functions to re-compute the cell location of the variable.] Use the Worksheet.Change event to generate timestamp value in an adjacent cell.
为什么要移动实时数据点？使用变量来标识每个连续条目的计算位置。[您可以使用 OFFSET 和 COUNTA 函数重新计算变量的单元格位置。] 使用 Worksheet.Change 事件在相邻单元格中生成时间戳值。
