EXCEL VBA ¦p¦ó±N­«½Æªºµ{¦¡¦X¨Ö¦A¤@°_

ken2192

¦U¦ìª©¤Í¦n¡A
¥H¤U¬O§Ú¦bexcel vba¤¤¡A¿é¤JªÑ²¼¥N¸¹´N¦Û°Ê±a¥X¬ÛÃöDDEªºµ{¦¡¡A¨Ò¦p¦bA5¿é¤J2498¡Ab5~ae5´N·|¶]¥X¬ÛÃö³sµ²ªºDDE¡A¦ý¬°¤F­«½Æ¦¹µ{¦¡À³¥Î¦b¦U¦C¤¤¡A©ó¬O§Ú¥u¯à½Æ»s¶K¤W¤@¼Ëªº«ü¥O¡A¨Ã­×§ï5-->6¡A±a¥X²Ä6¦Cªºª½¡A
¦ý¥Ñ©ó¤ÓÁcº¾¥B­nÀ³¥Î¦b5-25¦C¡A½Ð°Ýª©¤Í§Ú¦p¦ó±N­«½Æªº«ü¥O¦X¨Ö¦b¦P¤@­Óµ{¦¡¸Ì¡A¤£ºÞ¿é¤J¦bA5©ÎA9¥L³£¥i¥H¦Û°Ê±a¥X¨Ó!!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$5" And [A5] <> "" Then
[b5] = "=XQCTS|Quote!'" & [A5] & ".TW-Name'"
[c5] = "=XQCTS|Quote!'" & [A5] & ".TW-Bid'"
[d5] = "=XQCTS|Quote!'" & [A5] & ".TW-Ask'"
[e5] = "=XQCTS|Quote!'" & [A5] & ".TW-Price'"
[f5] = "=XQCTS|Quote!'" & [A5] & ".TW-PriceChange'"
[g5] = "=XQCTS|Quote!'" & [A5] & ".TW-PriceChangeRatio'"
[i5] = "=XQCTS|Quote!'" & [A5] & ".TW-Volume'"
[n5] = "=XQCTS|Quote!'" & [A5] & ".TW-TotalVolume'"
[o5] = "=XQCTS|Quote!'" & [A5] & ".TW-PreTotalVolume'"
[r5] = "=XQCTS|Quote!'" & [A5] & ".TW-BestBidSize1'"
[s5] = "=XQCTS|Quote!'" & [A5] & ".TW-BestBid1'"
[t5] = "=XQCTS|Quote!'" & [A5] & ".TW-BestAsk1'"
[u5] = "=XQCTS|Quote!'" & [A5] & ".TW-BestAskSize1'"
[y5] = "=XQCTS|Quote!'" & [A5] & ".TW-PreClose'"
[z5] = "=XQCTS|Quote!'" & [A5] & ".TW-Open'"
[aa5] = "=XQCTS|Quote!'" & [A5] & ".TW-High'"
[ab5] = "=XQCTS|Quote!'" & [A5] & ".TW-Low'"
[ac5] = "=XQCTS|Quote!'" & [A5] & ".TW-UpLimit'"
[ad5] = "=XQCTS|Quote!'" & [A5] & ".TW-DownLimit'"
[ae5] = "=XQCTS|Quote!'" & [A5] & ".TW-Time'"
End If

If Target.Address = "$A$6" And [A6] <> "" Then
[b6] = "=XQCTS|Quote!'" & [A6] & ".TW-Name'"
[c6] = "=XQCTS|Quote!'" & [A6] & ".TW-Bid'"
[d6] = "=XQCTS|Quote!'" & [A6] & ".TW-Ask'"
[e6] = "=XQCTS|Quote!'" & [A6] & ".TW-Price'"
[f6] = "=XQCTS|Quote!'" & [A6] & ".TW-PriceChange'"
[g6] = "=XQCTS|Quote!'" & [A6] & ".TW-PriceChangeRatio'"
[i6] = "=XQCTS|Quote!'" & [A6] & ".TW-Volume'"
[n6] = "=XQCTS|Quote!'" & [A6] & ".TW-TotalVolume'"
[o6] = "=XQCTS|Quote!'" & [A6] & ".TW-PreTotalVolume'"
[r6] = "=XQCTS|Quote!'" & [A6] & ".TW-BestBidSize1'"
[s6] = "=XQCTS|Quote!'" & [A6] & ".TW-BestBid1'"
[t6] = "=XQCTS|Quote!'" & [A6] & ".TW-BestAsk1'"
[u6] = "=XQCTS|Quote!'" & [A6] & ".TW-BestAskSize1'"
[y6] = "=XQCTS|Quote!'" & [A6] & ".TW-PreClose'"
[z6] = "=XQCTS|Quote!'" & [A6] & ".TW-Open'"
[aa6] = "=XQCTS|Quote!'" & [A6] & ".TW-High'"
[ab6] = "=XQCTS|Quote!'" & [A6] & ".TW-Low'"
[ac6] = "=XQCTS|Quote!'" & [A6] & ".TW-UpLimit'"
[ad6] = "=XQCTS|Quote!'" & [A6] & ".TW-DownLimit'"
[ae6] = "=XQCTS|Quote!'" & [A6] & ".TW-Time'"
End If

If Target.Address = "$A$7" And [A7] <> "" Then
[b7] = "=XQCTS|Quote!'" & [A7] & ".TW-Name'"
[c7] = "=XQCTS|Quote!'" & [A7] & ".TW-Bid'"
[d7] = "=XQCTS|Quote!'" & [A7] & ".TW-Ask'"
[e7] = "=XQCTS|Quote!'" & [A7] & ".TW-Price'"
[f7] = "=XQCTS|Quote!'" & [A7] & ".TW-PriceChange'"
[g7] = "=XQCTS|Quote!'" & [A7] & ".TW-PriceChangeRatio'"
[i7] = "=XQCTS|Quote!'" & [A7] & ".TW-Volume'"
[n7] = "=XQCTS|Quote!'" & [A7] & ".TW-TotalVolume'"
[o7] = "=XQCTS|Quote!'" & [A7] & ".TW-PreTotalVolume'"
[r7] = "=XQCTS|Quote!'" & [A7] & ".TW-BestBidSize1'"
[s7] = "=XQCTS|Quote!'" & [A7] & ".TW-BestBid1'"
[t7] = "=XQCTS|Quote!'" & [A7] & ".TW-BestAsk1'"
[u7] = "=XQCTS|Quote!'" & [A7] & ".TW-BestAskSize1'"
[y7] = "=XQCTS|Quote!'" & [A7] & ".TW-PreClose'"
[z7] = "=XQCTS|Quote!'" & [A7] & ".TW-Open'"
[aa7] = "=XQCTS|Quote!'" & [A7] & ".TW-High'"
[ab7] = "=XQCTS|Quote!'" & [A7] & ".TW-Low'"
[ac7] = "=XQCTS|Quote!'" & [A7] & ".TW-UpLimit'"
[ad7] = "=XQCTS|Quote!'" & [A7] & ".TW-DownLimit'"
[ae7] = "=XQCTS|Quote!'" & [A7] & ".TW-Time'"
End If

End Sub

ken2192

·P®¦stillfish00§Ö³t¦^´_¤p§Ì§Úªº°ÝÃD¡A¸Õ°_¨Ó¨S¬Æ»ò°ÝÃD!!

stillfish00

¦^´_ 1# ken2192

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2.     Dim ddeString As String
   
3. 
   
4.     If Intersect(Target, Range("A5:A25")) Is Nothing Then Exit Sub
   
5.    
   
6.     If Target.Count = 1 And Len(Target.Cells(1).Value) > 0 Then
   
7.         Application.EnableEvents = False
   
8.         
   
9.         ddeString = "=XQCTS|Quote!'" & Target.Value
   
10.         With Target
    
11.             .Cells(1, 2).Value = ddeString & ".TW-Name'"
    
12.             .Cells(1, 3).Value = ddeString & ".TW-Bid'"
    
13.             .Cells(1, 4).Value = ddeString & ".TW-Ask'"
    
14.             .Cells(1, 5).Value = ddeString & ".TW-Price'"
    
15.             
    
16.             '²¤
    
17.             
    
18.         End With
    
19.         
    
20.         Application.EnableEvents = True
    
21.     End If
    
22. End Sub

½Æ»s¥N½X