Board logo

標題: [發問] Calculate，能否回傳發生變動的儲存格的欄位 [打印本頁]
作者: ketrddem    時間: 2016-5-1 19:07     標題: Calculate，能否回傳發生變動的儲存格的欄位

本帖最後由 ketrddem 於 2016-5-1 19:10 編輯

Worksheet_Calculate事件中
假設我指定的範圍為sheet1.Columns("A:S").Calculate(第一張表)
大約有300列

如果某個公式運算符合條件，就會變成「YES」，沒有符合條件，就是空白「" "」

請問，有沒有什麼方法能夠讓我在第一時間知道是那一個欄位產生變動呢

比方，儲存格「D5」，符合條件了，從空白值變成「YES」

程式就會回傳到sheet2，比方回傳值為「D5發生YES事件」(把發生的欄位回傳到第二張表)
作者: c_c_lai    時間: 2016-5-2 05:54

本帖最後由 c_c_lai 於 2016-5-2 07:47 編輯

回復 1# ketrddem
提供參考：
[attach]24130[/attach]
ThisWorkbook:：
  1. Sub Ex()
  2.     [A1] = 10
  3.     [B1] = 8
  4.     [A1] = 6
  5. End Sub
複製代碼
工作表1：
  1. Private Sub Worksheet_Calculate()
  2.     Dim target As Range
  3.     Set target = Range("C1")

  5.     '  If Not Intersect(target, Range("C1")) Is Nothing Then
  6.     If target.Value = "YES" Then
  7.         '  Run my VBA code
  8.         MsgBox "Worksheet_Calculate is Activvated!"
  9.     End If
  10. End Sub

  12. Private Sub Worksheet_Change(ByVal target As Range)
  13.     '  If Not Intersect(target, Range("C1")) Is Nothing Then
  14.     If target.Address = "$C$1" Then
  15.         '  MyMacro()
  16.         MsgBox "Worksheet_Change = " & target.Address
  17.     End If
  18. End Sub
複製代碼
請用 F8 來 Trace 來仔細觀察  Worksheet_Calculate() 以及
Worksheet_Change(ByVal target As Range) 是如何運作，
目前 {A1:B1] 均是以 Assign value 方式進行，但如果它們都為
公式帶入值可能 Worksheet_Change() 不會有動作，如 [C1]
內容值為： =IF(A1>B1, "YES", "NO")  一樣。
{A1:B1] 你可以帶入 DDE 公式 來觀察資料匯入時之動態狀況。
作者: ML089    時間: 2016-5-2 07:13

回復 2# c_c_lai

If target.Address = "C1" Then
須小修改為
If target.Address = "$C$1" Then
作者: c_c_lai    時間: 2016-5-2 07:46

本帖最後由 c_c_lai 於 2016-5-2 07:48 編輯

回復 3# ML089
好久沒連繫了， 謝謝指證，疏忽了。
  1. target.Address = "$A$1" : True : Boolean
  2. target.Address = "A1"   : False : Boolean
複製代碼
作者: ML089    時間: 2016-5-2 08:56

回復 2# c_c_lai

執行的步驟跟我想像的邏輯有點不同，當A1值被改變時，C1公式才會連動(觸動)改變。
我覺得順序應該 A1改變，Worksheet_Change應該先被觸動，再來C1公式改變，而後 Worksheet_Change被觸動。
不知道有有何看法?



目前執行順序如下

VBA

STEP 1
[A1] = 10

STEP2 公式
C1 = IF(A1>B1, "YES", "NO")

STEP 3
Private Sub Worksheet_Calculate()

STEP 4
Private Sub Worksheet_Change(ByVal Target As Range)
作者: c_c_lai    時間: 2016-5-2 11:16

回復 5# ML089
不好意思，出去到國稅局報稅，剛從外頭回來。

Step 1:
當 [A1] = 10，  Excel 系統會先觸動
Worksheet_Calculate() 然後接著再觸動
Worksheet_Change(ByVal target As Range)
這時 target 為 [A1]；
[C1] 則由公式自動判斷，值為 "YES"
並未再觸發任何 Trigger。

Step 2:
當 [B1] = 8，Excel 系統會先觸動
Worksheet_Calculate() 然後接著再觸動
Worksheet_Change(ByVal target As Range)
這時 target 為 [B1]；
[C1] 則由公式自動判斷，值為 "YES"
並未再觸發任何 Trigger。

Step 3:
當 [A1] = 6，  Excel 系統亦會先觸動
Worksheet_Calculate() 然後接著再觸動
Worksheet_Change(ByVal target As Range)
這時 target 為 [A1]；
[C1] 則由公式自動判斷，值為 "NO"，也
並未再觸發任何 Trigger。

公式值的變化，實際上並不會觸發任何知 Trigger
內在事件 (Event)。
假設 [A1]、[B1] 如為 DDE 匯入之公式，開盤後之
DDE 數據值的任何變動都不會去觸發 Worksheet_Change
至於 針對  [A1]、[B1] 兩欄的數據變動是否會牽動
Worksheet_Calculate() ，實有待實務觀察，
但如另一欄位設定為 =A1、或 =B1，則該欄位之數據異動
會牽動 Worksheet_Calculate() 、以及 Worksheet_Change() 的。
作者: ketrddem    時間: 2016-5-2 11:37

不好意思，可能小弟說明不清

D5只是個比方而已
其實發生變動 是在A2:S300的任何一個儲存格都可能發生

所以上面c_c_lai大大提供的範例，都是有指定欄位的變動

小弟想做的是，在A2:S300的範圍裏
不管那一個儲存格發生變動
都能獲得「發生變動的那個儲存格位址」
作者: ketrddem    時間: 2016-5-2 11:46

做個小小的說明，因為權限有限，不能貼附件：

A到S欄中，當中D欄公式設為「成交價>五日均線，AND 五日均量>十日均量，AND KD>30」
第一列到第300列當中，假設第150列為股號1101，當D150儲存格符合條件，就顯示YES。

問題來了，我想知道，現在D150正發生了變動

如果無法獲知變動的是D150這個儲存格

我就無法知道，股號1101，目前符合了我的挑股的條件，也就是「成交價>五日均線，AND 五日均量>十日均量，AND KD>30」的條件

於是乎

小弟想用CALCULATE方法，去做整張工作表設計

然後，發生變動的欄位，能得到該欄位的列號跟欄名，這樣子才能用OFFSET定位出是那一檔股票，以及在什麼條件下符合了。

再次感謝各位先進指導，再請不吝指教。謝謝。
作者: c_c_lai    時間: 2016-5-2 11:52

回復 7# ketrddem
你實際跑一下程式，看它到底實際會觸動那些事件，便知。
如果是觸動 _Change 即可迅速得知觸動欄位的位址。如僅僅
只觸動 _Calculate 那就頭大了！
作者: c_c_lai    時間: 2016-5-2 12:06

回復 8# ketrddem
你可以將檔案壓縮成 .rar 壓縮檔便能上傳了，
選用高級模式內的檔案上傳 (迴紋針)。
作者: ketrddem    時間: 2016-5-2 12:09

因為整張表都是公式，change事件應該是不會被觸動的
所以才直接想到用calculate
作者: ML089    時間: 2016-5-2 12:11

回復 6# c_c_lai

謝謝你詳細的解說，終於懂了。
其實 Private Sub Worksheet_Calculate()並未傳入參數，我將  If Target.Value = "YES" 中的 Target 誤認為是傳入參數值，才以為是 C3公式觸動Worksheet_Calculate。
後來仔細觀察 Private Sub Worksheet_Change(ByVal Target As Range) 中的 If Target.Value = "YES" Then 根本不會執行到，因 Target 僅有A1、B1，C3是公式沒有觸發是我自己觀察不夠，你真是 用心良苦。


Private Sub Worksheet_Calculate()
    Dim Target As Range
    Set Target = Range("C1")

    '  If Not Intersect(target, Range("C1")) Is Nothing Then
    If Target.Value = "YES" Then
        '  Run my VBA code
        MsgBox "Worksheet_Calculate is Activvated!"
    End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    '  If Not Intersect(target, Range("C1")) Is Nothing Then
    If Target.Address = "$C$1" Then
        '  MyMacro()
        MsgBox "Worksheet_Change = " & Target.Address
    End If
End Sub
作者: c_c_lai    時間: 2016-5-2 12:38

回復 11# ketrddem
你使用哪一家券商軟體？
作者: 准提部林    時間: 2016-5-2 13:12

〔觸動〕與〔變動〕應是兩回事！
〔觸動〕後，不一定公式值有〔變動〕，
若儲存格之間又有關聯參照，也許〔連動〕也會發生，
沒有檔案，這樣猜不是辦法！！！
作者: ketrddem    時間: 2016-5-2 13:40

回復 13# c_c_lai


    元大RTD，沒有使用DDE。

DDE太不方便了，還是RTD比較好用
作者: c_c_lai    時間: 2016-5-2 14:00

回復 15# ketrddem
RTD 不需先開啟券商軟體，它會自動連結，
這種 COM 的機制比較好用。以前也使用元大。
我曾經也使用過 Workbook_Calculate()
但是究竟是哪個欄位在觸發，欄位位址為何，
很難掌握，你可有好的 Idea ？
作者: ketrddem    時間: 2016-5-2 14:07

小弟不才，只有想法，整個EXCEL還沒真的寫完整。
所以提供圖例，希望各位能提供些想法供小弟參考。
作者: ketrddem    時間: 2016-5-2 14:12

回復 16# c_c_lai


我有想過，陣列比對。

每十秒或二十秒比對一次。

假設
10:00分0秒，儲存內容為陣列1
10:00分10秒，儲存內容為陣列2

陣列1與陣列2比對，找出差異欄位，定位出股號及條件原因
然後把資訊寫進別的儲存格
寫進後，陣列1刪除，陣列2變更為陣列1，等著下個十秒再做陣列2的比對
但有想法卻不知是否真正可行，寫了不知對系統會不會造成很大負擔。
更重要的是，小弟對陣列不太熟。就算陣列寫出來，也不知如何做比對定位。
作者: 准提部林    時間: 2016-5-2 14:20

回復 17# ketrddem

以這樣的表格, 同時成立條件的可能不只一個, 而且自動重算無法判定是哪個(些)儲存格,
建議公式改為:
=IF(條件成立,"YES",0)

當發生事件觸動,
Set Rng = [H:H].SpecialCells(xlCellTypeFormulas, 2)  '抓取公式中的[文字值]儲存格
再用
For Each xR in Rng
    逐一記錄
next
作者: ketrddem    時間: 2016-5-2 14:41

回復 19# 准提部林

准提大大，承蒙你關照，沒想到果然把您釣出來了。以前在奇摩知識就發過許多問題，也蒙你指導許多。

這個例子，並不是要取儲存格中的文字「YES」

而是要取「YES」這個值它的列號及欄名，以便得知是那一支股票(列號)符合了選股條件，以及發生在那一種條件下(欄名)的選股條件。

圖例更改剛剛小弟發的圖，發錯圖了，這張後面還有說明，比較完整。
作者: 准提部林    時間: 2016-5-2 15:35

回復 20# ketrddem


Ｋ２：１２１６統一．符合條件１．建議買進
下一分鐘．條件不成立，Ｋ２如何顯示？

最好附上簡單範例檔案，否則一來一往切也不到點，太浪費大家時間！
若要動態顯示，也許只用公式即可解決！
作者: ketrddem    時間: 2016-5-2 15:41

目前的想法，下一分鐘，條件不成立，K2不填入任何資料
如果是一下成立一下不成立
就在值填入K2之前，先掃一遍K欄，看有沒有同時符合1216條件一。如果有，就跳過不填入

換言之，同一個條件只會產生一次訊號，而且只提供多訊或空訊，帶進不帶出。

這都是次要問題，主要是如何取得發生變動的欄位的位址。位址抓不到，後續的動作都無法進行了。
作者: ketrddem    時間: 2016-5-2 15:57

小弟想出解法了，供各位參考指教：

可能繞了一大圈，但真沒辦法的狀況下，也許這個土法鍊鋼的方法反而比較簡單

SHEET1，為CALCULATE的工作表重算事件
SHEET2，用VBA，每十秒將SHEET1整張表複製值到SHEET2上
SHEET3，比對SHEET2與SHEET1的欄位，因為SHEET3不是用到CALCULATE方法，所以差異欄位就很容易找了。
作者: ketrddem    時間: 2016-5-2 16:42

小弟又想出解法了
二層迴圈
FOR I TO LOSTROW
       FOR J TO LOSTCOLUMN
              IF CELLS(I,J).VALUE = "YES"
                    MSGBOX  "股號" & CELLS(I,1).VALUE & "符合條件" & CELLS(1,J).VALUE
              END IF
      NEXT
NEXT

以上，晚上再實際寫寫看，運作看看
作者: 准提部林    時間: 2016-5-2 18:38

給個參考方案：
[attach]24143[/attach]
http://www.funp.net/921173

此帖我發意見至此為止囉！！！
作者: c_c_lai    時間: 2016-5-2 19:10

回復 24# ketrddem
[attach]24144[/attach]
作者: ketrddem    時間: 2016-5-2 22:17

回復 27# c_c_lai


    太感謝了，完全就是我想實現的狀況
    仔細看了語法，其實是之前自已想得太複雜了
     其實就單純找「yes」就好了呀。

    換言之，其實不需要做到二層迴圈，用if一欄一欄的找就好了。
    感謝不吝指教。



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)