Function repl(rng As Range)
Dim d, reg, matchs, match, k, t, s$, i%
'找出匹配项(即物料),生成替换项
s = rng.Text
Set reg = CreateObject("VBScript.RegExp")
Set d = CreateObject("scripting.dictionary")
With reg
.Global = True
.Pattern = "[\u4E00-\u9FA5]+"
Set matchs = .Execute(s)
For Each match In matchs
If Not d.exists(match.Value) Then
i = i + 1
d(match.Value) = Chr(i + 64)
End If
Next
End With
k = d.keys: t = d.items
Set d = Nothing: Set reg = Nothing
'排序。如果让字符串长度短的先替换,则可能结果出错
Call SelectionSort(k, t)
'替换
For i = 0 To UBound(k)
s = VBA.Replace(s, k(i), t(i))
Next i
repl = s
End Function
Sub SelectionSort(arr, brr)
Dim i, j, t, k
For i = LBound(arr) To UBound(arr) - 1
k = i
For j = i + 1 To UBound(arr)
If Len(arr(k)) < Len(arr(j)) Then k = j '降序:字符长的放前面
Next
If k <> i Then
t = arr(k): arr(k) = arr(i): arr(i) = t
t = brr(k): brr(k) = brr(i): brr(i) = t
End If
Next
End Sub
代替2.rar
(14.96 KB, 下载次数: 10)