vba - 检查Excel单元格中的连续字符

Question

如果你能帮助我，我需要找出字母表中的字符是否在单元格中连续重复 3 次或更多次，例如，如果单元格是“aronfff”或“aaaaaron”，我希望它返回 true，否则返回 false 例如“aaron”。

Function InRowChars(cell As String) As Boolean
Dim repeats As Integer, char As String, i As Integer
repeats = 0
char = "abcdefghijklmnopqrstuvwxyz"

For i = 1 To Len(cell)
    If cell.Value = " " Then
        repeats = chars + 1
    Else
        chars = 0
    End If
Next i

If chars = 3 Then
    InRowChars = True
Else
    InRowChars = False
End If

End Function

我不知道如何获取要对照字母表检查的单元格的值。

Answer 1

这可以通过正则表达式来实现。我制作了一个函数示例，它也接受所需的最小字符数:

'Add a reference to Microsoft VBScript Regular Expressions 5.5
Function ContainsConsecutiveChars(ByRef CellRef As Range, Optional ConsecutiveCount As Long = 3) As Boolean

  Dim chars() As String
  chars = Split("a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z", ",")

  With New RegExp
    .Pattern = Join(chars, "{" & ConsecutiveCount & ",}|")
    ContainsConsecutiveChars = .test(CellRef.Value2)
  End With

End Function