excel - VBA 函数来测试字符串的缩写

Question

我有以下函数（UDF），它完全符合我的要求，但是我想知道是否可以没有字典（带有预定义缩写的数组）？该函数将字符串转换为正确的大小写，忽略缩写（总是大写）。我希望函数是动态的，这样每次数据更改时我都不必更改数组中的缩写（缩写的数量可能会大大增加）。这可能吗？

Public Function PROPERCASE(s As String)
'Variables
Dim bResult      As Boolean
Dim i            As Integer, j As Integer
Dim c            As Variant, v As Variant

c = Array("ENIS", "ENIR", "PCS", "WFC", "MBS", "SIE", "SCI", "TCS", "I/O", "SNMP", _
            "RX", "TX", "PSU", "UPS", "UHF", "FXO", "FXS", "VOIP", "TIN", "NMS", _
            "AC", "DC", "VRS", "TETRA", "NTP", "PoE", "TEMPEST")     'Abbreviations to ignore

v = Split(Replace(s, "/", " / "), " ")        'Separates the words

For j = LBound(v) To UBound(v)
    For i = LBound(c) To UBound(c)
        If StrComp(Replace(Replace(v(j), "(", ""), ")", ""), c(i), vbBinaryCompare) = 0 Or (v(j) Like "*#*") Then bResult = True: Exit For Else bResult = False
    Next i
    If bResult = False Then v(j) = StrConv(v(j), vbProperCase): bResult = False
Next j

PROPERCASE = Replace(Join(v, " "), " / ", "/")        'Joins the words
End Function

任何帮助将不胜感激。

标签： excelvba