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