vbalike詳解

一、vba里key的個數

在vba中，關鍵字是指代VBA語言結構的單詞，例如If、Then、Else、For、Next等。在VBA中的關鍵字有近1000個，會隨着版本的更新而有所不同。

Sub KeywordCount()
    Dim i As Integer
    i = 0
    For Each v In Keywords
        i = i + 1
    Next v
    MsgBox "VBA中共有 " & i & " 個關鍵字"
End Sub

二、vbalike匹配數字

vbalike可以用於模糊匹配字符串，比如匹配一段文本中是否包含某個數字。

Public Function ContainsNumber(text As String) As Boolean
    ContainsNumber = False
    For i = 0 To 9
        If text Like "*" & CStr(i) & "*" Then
            ContainsNumber = True
            Exit Function
        End If
    Next
End Function

三、vbalike函數模糊查找

vbalike還可以用於模糊查找，比如查找一個列表中包含特定字符串的項。

Public Function FindItem(needle As String, haystack As Range) As Boolean
    For Each cell In haystack
        If cell.Value Like "*" & needle & "*" Then
            FindItem = True
            Exit Function
        End If
    Next
End Function

四、vba里可以用函數嗎

在vba中可以自定義函數，方便實現程序的復用。

Public Function CalculateArea(radius As Double) As Double
    CalculateArea = 3.14 * radius * radius
End Function

五、vba里可以寫js嗎

在vba中不能直接寫JavaScript代碼，但可以通過Microsoft Script Control來實現在VBA中運行JavaScript代碼的功能。

Sub RunJavaScript()
    Dim ScriptControl As Object
    Set ScriptControl = CreateObject("MSScriptControl.ScriptControl")
    ScriptControl.Language = "JavaScript"
    ScriptControl.AddCode "function add(a, b) { return a + b }"
    MsgBox ScriptControl.Run("add", 1, 2)
End Sub

六、vba like 變量

vbalike不僅可以匹配固定字符串，也可以匹配變量中的值，如下例：

Public Function ContainsText(text As String, substring As String) As Boolean
    ContainsText = False
    If text Like "*" & substring & "*" Then
        ContainsText = True
    End If
End Function

Sub Example()
    Dim keyword As String
    keyword = "apple"
    Dim text As String
    text = "I like to eat apples"
    MsgBox ContainsText(text, keyword)
End Sub

七、vba中like函數

在vba中，allCharsString可以匹配任何字符，而anyCharString僅匹配單個字符，如下面的例子所示：

Sub TestLike()
    Debug.Print "123" Like allCharsString
    Debug.Print "" Like allCharsString
    Debug.Print " " Like allCharsString
    Debug.Print vbNewLine Like allCharsString
    Debug.Print "123" Like anyCharString
    Debug.Print "" Like anyCharString
    Debug.Print " " Like anyCharString
    Debug.Print vbNewLine Like anyCharString
End Sub

八、vba中like的用法

在vba中，like的用法如下：

• ?：匹配任意單個字符。
• *

：匹配任意個任意字符。

• [charlist]

：匹配charlist中的任意字符。

• [!charlist]

：匹配不在charlist中的任意字符。

Sub TestLike2()    Debug.Print "abcdef" Like "?*~"    Debug.Print "abcdef" Like "?*~@"    Debug.Print "abcdef" Like "[a-z]*~"    Debug.Print "ABC" Like "[a-z]*~"    Debug.Print "abcdef" Like "[!a-z]*~?"     Debug.Print "ABC" Like "[!a-z]*~" End Sub

九、vba的like的模糊用法

vba的like具有模糊匹配功能，因此可以用來處理模糊匹配的需求。

Public Function FuzzyMatch(text As String, wildcardPattern As String) As Boolean
    Dim arrChars() As String
    Dim arrWildcards() As String
    arrChars = Split(text, "")
    arrWildcards = Split(wildcardPattern, "?")
    For i = 0 To UBound(arrWildcards)
        arrChars(0) = Right(arrChars(0), Len(arrChars(0)) - Len(arrWildcards(i)))
        If i = UBound(arrWildcards) And Len(arrChars(0)) > 0 Then
            FuzzyMatch = False
            Exit Function
        End If
        arrChars = Split(arrChars(0), arrWildcards(i + 1), 2)
        If UBound(arrChars) = 0 Then
            FuzzyMatch = False
            Exit Function
        End If
    Next
    FuzzyMatch = True
End Function