EXCEL 数据单元格的实时动态处理的有关问题
EXCEL 数据单元格的实时动态处理的问题
小弟是VBA新手，最近碰到一个问题，想和大家讨论下，看看有没有什么更好的解决方案。谢谢大家！
问题：数据提供商动态提供交易价格到我们一个Excel 的单元表格里面，我需要基于这个动态数据，给出动态分析的结果（一分钟里面的最大最小值）。具体问题描述如下：
'问题描述：Excel表格里面有单元格E9，和数据提供商动态连线，动态(秒量级)给出每笔交易的价格
' 暂时不考虑一秒钟里面有超过一笔且价格不同的交易
’ B9 = 初始值（没分钟里面第一笔交易价格，无交易则去上一分钟最后一笔交易价格）, C9 = 价格动态最大值, D9 = 价格动态最小值
'程序用途：给出动态交易一分钟里面（##：00～##：59）的交易数据的动态最大最小值
' 动态的意思是：E9更新时， B9, C9 和 D9 同时更新，分别给出所需要的开始值，最大值和最小值
'解决办法：
'用arrD(i)数组存储每一个当前屏闪更新的(E9)里面的数据，i = second(now())+1, arrD(i) = sheet1.range("E9")
'用arrT(i)数组存储每一个当前屏闪更新的(E9)里面的数据时候发生的时间，用 minute(arrT(j))来判定同一分钟里面的数据 j = 1 to 60
'(1)在每一个屏闪更新E9时，保存E9的值到arrD(i)，屏闪时间到arrT(i)
'(2)初始 High = E9， 顺序扫描j = 1 to 60，if arrD(j)>High and minute(arrT(j) = minute(now())), then High = arrD(j) end if
'(3)初始 Low = E9， 顺序扫描j = 1 to 60，if arrD(j)<High and minute(arrT(j) = minute(now())), then Low = arrD(j) end if
'(4)初始 Open = E9, 顺序扫描j = 1 to 60，bFlag = (minute(arrT(j) = minute(now())). if bFlag = true, then Open = arrD(j) Exit for end if
'(5)如果bFlag = False, then Open = E8 (上一分钟的最后一笔交易价格)
'////////////////// 程序代码 //////////////////////////////
Dim arrD(60) As Double ' assume price length is no longer than 60
Dim arrT(60) As Date ' save shot time in arrT, and use minute(arrT(j)) the same as within 1 minute
Public Function HighPrice() As Double
'1 save this shot price and time to arrD(i) and arrT(i)
'2 i = second(now())+1
Application.Volatile
Dim i As Integer, j As Integer, shot_time As Date, shot_price As Double
Dim open_temp As Double, high_temp As Double, low_temp As Double
Dim nMinute As Integer 'minute of this shot
Dim bMinute As Boolean
shot_time = Now() 'record shot time
shot_price = Sheet2.Range("E9") 'record shot price
i = Second(time_now) + 1 '+1 because the array index in VBA starts from 1, not 0
nMinute = Minute(shot_time)
arrD(i) = shot_price
arrT(i) = shot_time
'find the hightest price and lowest price in nMinute
high_temp = shot_price
low_temp = shot_price
open_temp = shot_price
For j = 1 To 60
bMinute = (Minute(arrT(j)) = nMinute)
If (bMinute) And (arrD(j) > high_temp) Then 'only compare the price within the same minute
high_temp = arrD(j)
End If
If (bMinute) And (arrD(j) < low_temp) Then 'only compare the price within the same minute
low_temp = arrD(j)
End If
Next j
'give the open price = the first price in nMinute
For j = 1 To 60
bMinute = (Minute(arrT(j)) = nMinute)
If bMinute = True Then
open_temp = arrD(j) 'OpenPrice = the first price in nMinute
Exit For
End If
Next j
If bMinute = False Then '一分钟里面都没有shot数据
open_temp = Sheet2.Range("E8") 'no trade in 1 minute: OpenPrice = last end price
End If
'show the open, high and low price
Sheet2.Range("B9") = open_temp 'show the dynamic low price
Sheet2.Range("C9") = high_temp 'show the dynamic high price
Sheet2.Range("D9") = low_temp 'show the dynamic low price
HighPrice = high_temp
End Function
------解决思路----------------------
整那么复杂啊，来个简单点的，看看是否满足要求。
这个图，咋那么难呢？
小弟是VBA新手，最近碰到一个问题，想和大家讨论下，看看有没有什么更好的解决方案。谢谢大家！
问题：数据提供商动态提供交易价格到我们一个Excel 的单元表格里面，我需要基于这个动态数据，给出动态分析的结果（一分钟里面的最大最小值）。具体问题描述如下：
'问题描述：Excel表格里面有单元格E9，和数据提供商动态连线，动态(秒量级)给出每笔交易的价格
' 暂时不考虑一秒钟里面有超过一笔且价格不同的交易
’ B9 = 初始值（没分钟里面第一笔交易价格，无交易则去上一分钟最后一笔交易价格）, C9 = 价格动态最大值, D9 = 价格动态最小值
'程序用途：给出动态交易一分钟里面（##：00～##：59）的交易数据的动态最大最小值
' 动态的意思是：E9更新时， B9, C9 和 D9 同时更新，分别给出所需要的开始值，最大值和最小值
'解决办法：
'用arrD(i)数组存储每一个当前屏闪更新的(E9)里面的数据，i = second(now())+1, arrD(i) = sheet1.range("E9")
'用arrT(i)数组存储每一个当前屏闪更新的(E9)里面的数据时候发生的时间，用 minute(arrT(j))来判定同一分钟里面的数据 j = 1 to 60
'(1)在每一个屏闪更新E9时，保存E9的值到arrD(i)，屏闪时间到arrT(i)
'(2)初始 High = E9， 顺序扫描j = 1 to 60，if arrD(j)>High and minute(arrT(j) = minute(now())), then High = arrD(j) end if
'(3)初始 Low = E9， 顺序扫描j = 1 to 60，if arrD(j)<High and minute(arrT(j) = minute(now())), then Low = arrD(j) end if
'(4)初始 Open = E9, 顺序扫描j = 1 to 60，bFlag = (minute(arrT(j) = minute(now())). if bFlag = true, then Open = arrD(j) Exit for end if
'(5)如果bFlag = False, then Open = E8 (上一分钟的最后一笔交易价格)
'////////////////// 程序代码 //////////////////////////////
Dim arrD(60) As Double ' assume price length is no longer than 60
Dim arrT(60) As Date ' save shot time in arrT, and use minute(arrT(j)) the same as within 1 minute
Public Function HighPrice() As Double
'1 save this shot price and time to arrD(i) and arrT(i)
'2 i = second(now())+1
Application.Volatile
Dim i As Integer, j As Integer, shot_time As Date, shot_price As Double
Dim open_temp As Double, high_temp As Double, low_temp As Double
Dim nMinute As Integer 'minute of this shot
Dim bMinute As Boolean
shot_time = Now() 'record shot time
shot_price = Sheet2.Range("E9") 'record shot price
i = Second(time_now) + 1 '+1 because the array index in VBA starts from 1, not 0
nMinute = Minute(shot_time)
arrD(i) = shot_price
arrT(i) = shot_time
'find the hightest price and lowest price in nMinute
high_temp = shot_price
low_temp = shot_price
open_temp = shot_price
For j = 1 To 60
bMinute = (Minute(arrT(j)) = nMinute)
If (bMinute) And (arrD(j) > high_temp) Then 'only compare the price within the same minute
high_temp = arrD(j)
End If
If (bMinute) And (arrD(j) < low_temp) Then 'only compare the price within the same minute
low_temp = arrD(j)
End If
Next j
'give the open price = the first price in nMinute
For j = 1 To 60
bMinute = (Minute(arrT(j)) = nMinute)
If bMinute = True Then
open_temp = arrD(j) 'OpenPrice = the first price in nMinute
Exit For
End If
Next j
If bMinute = False Then '一分钟里面都没有shot数据
open_temp = Sheet2.Range("E8") 'no trade in 1 minute: OpenPrice = last end price
End If
'show the open, high and low price
Sheet2.Range("B9") = open_temp 'show the dynamic low price
Sheet2.Range("C9") = high_temp 'show the dynamic high price
Sheet2.Range("D9") = low_temp 'show the dynamic low price
HighPrice = high_temp
End Function
------解决思路----------------------
整那么复杂啊，来个简单点的，看看是否满足要求。
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "E9" Then
''只有这个单元格内容发生变化时才响应
If Second(Now()) = 0 Then ''暂不考虑1s内超过1笔
''恰好在0秒时发生
[a9] = Minute(Now()) ''写入当前的分钟数
''其它几项都和e9相同
[b9] = [e9] ''初始值
[c9] = [e9] ''最大值
[d9] = [d9] ''最小值
[f9] = [f9] ''最后发生
Else
''需要判断分钟数
If Minute(Now()) = [a9] Then
''如果在相同的分钟数内发生,判断大小
[f9] = [e9] ''最后发生
If [e9] > [c9] Then [c9] = [e9] ''更换最大值
If [e9] < [d9] Then [d9] = [e9] ''更换最小值
Else
[a9] = Minute(Now()) ''更新分钟数
''分钟数发生改变了
[b9] = [f9] ''初始值用上一次的最后值
[c9] = IIf([e9] > [f9], [e9], [f9]) ''最大值
[d9] = IIf([e9] > [f9], [f9], [e9]) ''最小值
[f9] = [e9]
End If
End If
End If
End Sub
这个图，咋那么难呢？