Andy2483

¦^´_ 23# ÂŤÑÄR¦À


©Ò¥HD2ªº­È¥Î =k10+L10 ­pºâ?
³o¼Ë´N³æ¯Â¦h¤F

Andy2483

¦^´_ 25# ÂŤÑÄR¦À


    ¦³³W¹º¦n¤F,©ú¤Ñ¦AÄ~Äò¹ê²{¬Ý¬Ý
ÁÙ¦³¤@ÂI·Q½Ð±ÐA2¨ìE2¥i¥H¤£­n¼g¤J,¥u¼g¤J¦b³Ì«á¦C¶Ü¡H
¦]¬°¨C¦¸¼g¤JÀx¦s®æ³£·|»Ý­n®É¶¡

§Ú·Q³]­p¥ÎVBAªº°}¦C°O¿ý10¦C¦A¼g¤JA-EÄ檺«á¤è,¾A¦X¶Ü¡H

Andy2483

¦^´_ 28# ÂŤÑÄR¦À


    ÁÂÁ½׾Â,ÁÂÁ«e½ú­@¤ßªºµ¥«Ý,«á¾ÇÂǦ¹©«¾Ç¨ì«Ü¦hª¾ÃÑ,¦ý¤£ª¾¹D¥i¤£¥i¦æ,§Ú¥Î¨â­Ó¬¡­¶Ã¯¤§¶¡ªº¤½¦¡°µ´ú¸Õ¨C¬í¥i¥H°O¿ý300¦C,DDE¨S¥Î¹L,½Ð«e½ú¸Õ¸Õ¬Ý,­pºâÅÞ¿è¥i¯à»Ý­nµø»Ý¨D½Õ¾ã,¨S¦³¬ã¨s³o¨Ç­pºâªº·N¸q
¨Ï¥Îªº¤èªk¬O¥u¯d[B1]¸Ì¦³¤½¦¡,³o¤½¦¡¥u¬O¬°¤FÅýµ{¦¡¦¬¨ìDDEÅÜ°Ê­ÈIJ°Ê Private Sub Worksheet_Calculate() ,¤½¦¡¬O©Ò¦³DDE­È¬Û¥[ªº©M¦Ó¤w ¨S¤°»ò·N¸q ¨ä¥L¥ÑVBA°õ¦æ,¦r¨åkey·íÅܼÆ,item·í­pºâ­È

¤U¸ü (157.79 KB)

2024-1-19 09:34

ÅÜ°Ê°O¿ý_20240119.zip (28.87 KB)

¤U¸ü¦¸¼Æ: 3

2024-1-19 09:28

¤u§@ªí¼Ò²Õ:
Option Explicit
Private Sub Worksheet_Calculate()
Call Price
End Sub

¤@¯ë¼Ò²Õ:
Option Explicit
Public Z, BBS(1 To 5), BB(1 To 5), BA(1 To 5), BAS(1 To 5), FBS$, FAS$, E_2$, TT$, A(1 To 1, 1 To 5), j%
Sub Price()
If Not IsObject(Z) Then'¤@¶}©lªº[B1]¤½¦¡«Ø¥ß
   Set Z = CreateObject("Scripting.Dictionary")
   BBS(1) = "=XQTISC|Quote!'FITXN*1.TF-BestBidSize1'"
   BBS(2) = "=XQTISC|Quote!'FITXN*1.TF-BestBidSize2'"
   BBS(3) = "=XQTISC|Quote!'FITXN*1.TF-BestBidSize3'"
   BBS(4) = "=XQTISC|Quote!'FITXN*1.TF-BestBidSize4'"
   BBS(5) = "=XQTISC|Quote!'FITXN*1.TF-BestBidSize5'"
   
   BB(1) = "=XQTISC|Quote!'FITXN*1.TF-BestBid1'"
   BB(2) = "=XQTISC|Quote!'FITXN*1.TF-BestBid2'"
   BB(3) = "=XQTISC|Quote!'FITXN*1.TF-BestBid3'"
   BB(4) = "=XQTISC|Quote!'FITXN*1.TF-BestBid4'"
   BB(5) = "=XQTISC|Quote!'FITXN*1.TF-BestBid5'"
   
   BA(1) = "=XQTISC|Quote!'FITXN*1.TF-BestAsk1'"
   BA(2) = "=XQTISC|Quote!'FITXN*1.TF-BestAsk2'"
   BA(3) = "=XQTISC|Quote!'FITXN*1.TF-BestAsk3'"
   BA(4) = "=XQTISC|Quote!'FITXN*1.TF-BestAsk4'"
   BA(5) = "=XQTISC|Quote!'FITXN*1.TF-BestAsk5'"
   
   BAS(1) = "=XQTISC|Quote!'FITXN*1.TF-BestAskSize1'"
   BAS(2) = "=XQTISC|Quote!'FITXN*1.TF-BestAskSize2'"
   BAS(3) = "=XQTISC|Quote!'FITXN*1.TF-BestAskSize3'"
   BAS(4) = "=XQTISC|Quote!'FITXN*1.TF-BestAskSize4'"
   BAS(5) = "=XQTISC|Quote!'FITXN*1.TF-BestAskSize5'"
   
   FBS = "=XQTISC|Quote!'FITXN*1.TF-FiveBidSize'"
   FAS = "=XQTISC|Quote!'FITXN*1.TF-FiveAskSize'"
   E_2 = "=VALUE(HOUR(NOW())&IF(LEN(MINUTE(NOW()))=1,0&MINUTE(NOW()),MINUTE(NOW()))&IF(LEN(SECOND(NOW()))=1,0&SECOND(NOW()),SECOND(NOW())))"
   TT = "=(" & Mid(FBS, 2) & "+" & Mid(FAS, 2)
   For j = 1 To 5
      TT = TT & "+" & Mid(BBS(j), 2) & "+" & Mid(BB(j), 2) & "+" & Mid(BA(j), 2) & "+" & Mid(BAS(j), 2)
   Next
   TT = TT & ")"
   ThisWorkbook.Sheets(1).[B1] = TT
End If
'=================================================================================
'¥H¤U¬O¥H¦r¨åkey­pºâDDE­È
Z("J5") = Evaluate(BBS(1)): Z("J6") = Evaluate(BBS(2)): Z("J7") = Evaluate(BBS(3)): Z("J8") = Evaluate(BBS(4)): Z("J9") = Evaluate(BBS(5))
Z("K5") = Evaluate(BB(1)): Z("K6") = Evaluate(BB(2)): Z("K7") = Evaluate(BB(3)): Z("K8") = Evaluate(BB(4)): Z("K9") = Evaluate(BB(5))
Z("L5") = Evaluate(BA(1)): Z("L6") = Evaluate(BA(2)): Z("L7") = Evaluate(BA(3)): Z("L8") = Evaluate(BA(4)): Z("L9") = Evaluate(BA(5))
Z("M5") = Evaluate(BAS(1)): Z("M6") = Evaluate(BAS(2)): Z("M7") = Evaluate(BAS(3)): Z("M8") = Evaluate(BAS(4)): Z("M9") = Evaluate(BAS(5))
Z("K10") = Evaluate(FBS): Z("L10") = Evaluate(FAS)

'¥H¤U¬O­pºâ,À³¸Ó«Ü²´¼ô,¥u¬O§âÀx¦s®æ¸m´«¦¨key¦Ó¤w
Z("K12") = Z("K10") + Z("L10")
If Z("K12") <> Z("L12") Then
   Z("I14") = 0
   If Z("K14") = Z("K5") Then
      Z("I14") = Z("J5") - Z("J14")
      ElseIf Z("K14") = Z("K6") Then
         Z("I15") = Z("J6") - Z("J14")
   End If
   Z("I15") = 0
   If Z("K15") = Z("K5") Then
      Z("I15") = Z("J5") - Z("J15")
      ElseIf Z("K15") = Z("K6") Then
         Z("I15") = Z("J6") - Z("J15")
      ElseIf Z("K15") = Z("K7") Then
         Z("I15") = Z("J7") - Z("J15")
   End If
   Z("I16") = 0
   If Z("K16") = Z("K6") Then
      Z("I16") = Z("J6") - Z("J16")
      ElseIf Z("K16") = Z("K7") Then
         Z("I16") = Z("J7") - Z("J16")
      ElseIf Z("K16") = Z("K8") Then
         Z("I16") = Z("J8") - Z("J16")
   End If
   Z("I17") = 0
   If Z("K17") = Z("K7") Then
      Z("I17") = Z("J7") - Z("J17")
      ElseIf Z("K17") = Z("K8") Then
         Z("I17") = Z("J8") - Z("J17")
      ElseIf Z("K17") = Z("K9") Then
         Z("I17") = Z("J9") - Z("J17")
   End If
   Z("I18") = 0
   If Z("K18") = Z("K8") Then
      Z("I18") = Z("J8") - Z("J18")
      ElseIf Z("K18") = Z("K9") Then
         Z("I18") = Z("J9") - Z("J18")
   End If
   Z("N14") = 0
   If Z("L14") = Z("L5") Then
      Z("N14") = Z("M5") - Z("M14")
      ElseIf Z("L14") = Z("L6") Then
         Z("N14") = Z("M6") - Z("M14")
   End If
   Z("N15") = 0
   If Z("L15") = Z("L5") Then
      Z("N15") = Z("M5") - Z("M15")
      ElseIf Z("L15") = Z("L6") Then
         Z("N15") = Z("M6") - Z("M15")
      ElseIf Z("L15") = Z("L7") Then
         Z("N15") = Z("M7") - Z("M15")
   End If
   Z("N16") = 0
   If Z("L16") = Z("L6") Then
      Z("N16") = Z("M6") - Z("M16")
      ElseIf Z("L16") = Z("L7") Then
         Z("N16") = Z("M7") - Z("M16")
      ElseIf Z("L16") = Z("L8") Then
         Z("N16") = Z("M8") - Z("M16")
   End If
   Z("N17") = 0
   If Z("L17") = Z("L7") Then
      Z("N17") = Z("M7") - Z("M17")
      ElseIf Z("L17") = Z("L8") Then
         Z("N17") = Z("M8") - Z("M17")
      ElseIf Z("L17") = Z("L9") Then
         Z("N17") = Z("M9") - Z("M17")
   End If
   Z("N18") = 0
   If Z("L18") = Z("L8") Then
      Z("N18") = Z("M8") - Z("M18")
      ElseIf Z("L18") = Z("L9") Then
         Z("N18") = Z("M9") - Z("M18")
   End If

'¥H¤U¬O [J14:M19] = [J5:M10].Value ¥H¦r¨å¤è¦¡Âà´«
   Z("J14") = Z("J5"): Z("J15") = Z("J6"): Z("J16") = Z("J7"): Z("J17") = Z("J8"): Z("J18") = Z("J9"): Z("J19") = Z("J10")
   Z("L14") = Z("L5"): Z("L15") = Z("L6"): Z("L16") = Z("L7"): Z("L17") = Z("L8"): Z("L18") = Z("L9"): Z("L19") = Z("L10")
   Z("M14") = Z("M5"): Z("M15") = Z("M6"): Z("M16") = Z("M7"): Z("M17") = Z("M8"): Z("M18") = Z("M9"): Z("M19") = Z("M10")
End If

'¥H¤U¬O ±N­pºâ­È±a¤JA°}¦C¨Ã¼g¤JÀx¦s®æ
Z("L12") = Z("K19") + Z("L19")
Z("A2") = Format(Now, "HH:MM:SS")
Z("B2") = Z("I14") + Z("I15") + Z("I16") + Z("I17") + Z("I18")
Z("C2") = Z("N14") + Z("N15") + Z("N16") + Z("N17") + Z("N18")
Z("D2") = Z("K10") + Z("L10")
Z("E2") = Evaluate(E_2)
A(1, 1) = Z("A2"): A(1, 2) = Z("B2"): A(1, 3) = Z("C2"): A(1, 4) = Z("D2"): A(1, 5) = Z("E2")
ThisWorkbook.Sheets(1).Cells(Rows.Count, "A").End(3)(2).Resize(, 5) = A
Application.EnableEvents = True
End Sub

Sub ²M°£()
[A3].Resize(1000000, 5).ClearContents: ActiveWindow.ScrollRow = 1
End Sub

Sub ¦Û°Ê­«ºâ()
Application.Calculation = xlAutomatic
End Sub

Sub ¤â°Ê­«ºâ()
Application.Calculation = xlManual
End Sub

Andy2483

¦^´_ 30# ÂŤÑÄR¦À


    ¬O ¦Û°Ê­«ºâ / °±¤î´N«ö ¤â°Ê­«ºâ

Andy2483

¦^´_ 32# ÂŤÑÄR¦À

Z("K10") = Evaluate(FBS): Z("L10") = Evaluate(FAS)
Z("K12") = Z("K10") + Z("L10")

³o¨â­Óµ{¦¡½X­È¬O¨Ó¦Û©ó
FBS = "=XQTISC|Quote!'FITXN*1.TF-FiveBidSize'"
FAS = "=XQTISC|Quote!'FITXN*1.TF-FiveAskSize'"

³o¨â­ÓDDE³sµ²·|¨C¤Ñ¤@¼Ë¶Ü?

Andy2483

¦^´_ 34# ÂŤÑÄR¦À


    ½Ð¥Î¥H¤Uµ{¦¡½X°õ¦æ¬Ý¬Ýµ²ªG¬O¤°»ò©Î¥X²{¿ù»~
Sub TEST()
MsgBox Evaluate("=XQTISC|Quote!'FITXN*1.TF-FiveBidSize'")
MsgBox Evaluate("=XQTISC|Quote!'FITXN*1.TF-FiveAskSize'")
End Sub

Andy2483

¦^´_ 36# ÂŤÑÄR¦À


    ­ì¨Ó
[K10]Àx¦s®æ¬O =XQTISC|Quote!'FITXN*1.TF-FiveBidSize'
[L10]Àx¦s®æ¬O =XQTISC|Quote!'FITXN*1.TF-FiveAskSize'
¦pªG§â³o¨â¤½¦¡©ñ¤JÀx¦s®æ·|¦³­È¶Ü? ©Î¿ù»~­È? ½Ð¸Õ¸Õ¬Ý

Andy2483

¦^´_ 38# ÂŤÑÄR¦À


    [K10] ©M [10] Àx¦s®æ¦³­È¶Ü?

Andy2483

¦^´_ 41# ÂŤÑÄR¦À


    ©_©Ç¤F ³o¨â®æ´Ó¤J¤½¦¡¥i¥HÅã¥Ü¼Æ¦r,VBA«o§ì¤£¨ì­È
¨ä¥LªºDDE­ÈVBA³£§ì¨ì¤F,´N³o¨â­Ó­È§ì¤£¨ì
½ÐÀˬd¤@¤U­ì¥»³o L10»PK10 ³o¨â®æªº¤½¦¡¬O§_¥¿½T,¶K¤W¨Ó¬Ý¤@¤U

Andy2483

¦^´_ 43# ÂŤÑÄR¦À


    ­«·s¶}±ÒÀÉ®×,²{¦b¬O¨â­ÓDDE­ÈVBA§ì¤£¨ì,½ÐÀˬd¤@¤U³o¨â¦ê¦r  ¬O¤£¬O¦³¿ù»~
=XQTISC|Quote!'FITXN*1.TF-FiveBidSize'
=XQTISC|Quote!'FITXN*1.TF-FiveAskSize'