如何修改vba

duck_simon

如附加檔案說明 ,請教老師先進如何修改vba? 謝謝!
Book1.rar (8.09 KB)

下載次數: 15

2011-9-25 11:14

GBKEE

回復 1# duck_simon

1. Function JoinStr(rng As Range)
   
2.     Dim D As Object, E As Variant, AR(), i As Integer
   
3.     Set D = CreateObject("SCRIPTING.DICTIONARY")
   
4.     For Each E In rng
   
5.         If E <> "" Then D(E.Value) = ""
   
6.     Next
   
7.     ReDim AR(1 To D.Count)
   
8.     For i = 1 To D.Count
   
9.         AR(i) = Application.Small(D.KEYS, i)
   
10.     Next
    
11.     JoinStr = Join(AR, "")
    
12. End Function

複製代碼

duck_simon

回復 2# GBKEE

請教 GBKEE  老師:  
執行後 出現#VALUE  
是否   CreateObject("SCRIPTING.DICTIONARY") 這段我要設定引用項目??
VBA 我是完全門外漢!  謝謝 !

chin15

增加一個參數

1. Function JoinStr(rng As Range, w)
   
2.     Dim D As Object, E As Variant, AR(), i As Integer
   
3.     Set D = CreateObject("SCRIPTING.DICTIONARY")
   
4.     For Each E In rng
   
5.         If E <> "" Then D(E.Value) = ""
   
6.     Next
   
7.     ReDim AR(1 To D.Count)
   
8.     For i = 1 To D.Count
   
9.         AR(i) = Application.Small(D.KEYS, i)
   
10.     Next
    
11.     JoinStr = Join(AR, w)
    
12. End Function

複製代碼

公式輸入：=JoinStr($B$2:$M$3," ")
兩個雙引號間可以是任何符號

GBKEE

回復 3# duck_simon
試試看
指令 工具->選項->計算: 勾選 [自動]

duck_simon

回復 4# chin15
可以了! 感謝chin15 & GBKEE 兩位大大老師!  謝謝 !

duck_simon

回復 5# GBKEE
回 GBKEE 老師:
因為有些時候更改某數值,都要等"自動計算"算完, 有點浪費時間=懶得等..哈哈
所以設"手動",但我都會按"F9"  謝謝大大