Board logo

標題: EXCEL VBA 如何將重複的程式合併再一起 [打印本頁]
作者: ken2192    時間: 2015-9-23 16:48     標題: EXCEL VBA 如何將重複的程式合併再一起

各位版友好，
以下是我在excel vba中，輸入股票代號就自動帶出相關DDE的程式，例如在A5輸入2498，b5~ae5就會跑出相關連結的DDE，但為了重複此程式應用在各列中，於是我只能複製貼上一樣的指令，並修改5-->6，帶出第6列的直，
但由於太繁瑣且要應用在5-25列，請問版友我如何將重複的指令合併在同一個程式裡，不管輸入在A5或A9他都可以自動帶出來!!

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
作者: stillfish00    時間: 2015-9-23 17:09

回復 1# ken2192
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     Dim ddeString As String

  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
複製代碼
作者: ken2192    時間: 2015-9-23 17:26

感恩stillfish00快速回復小弟我的問題，試起來沒甚麼問題!!



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