Use RegEx in vba sub in general sheet

Report
Posts
13
Registration date
Thursday May 9, 2019
Status
Member
Last seen
May 28, 2019
-
ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
-
How to use regex in general vba sheet. i am getting lot of red lines and not able to find the reason for that.

I have enabled the Regularexpression vbScript also.

Private Const FCheckRgAddress As String = "A1:A100"
Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140905
    Dim a As String

    Set xChanged = Application.Intersect(Range(FCheckRgAddress), Target)
    If xChanged Is Nothing Then Exit Sub
End If

    Set xRegExp = CreateObject("VBScript.RegExp")
    xRegExp.Global = True
    xRegExp.IgnoreCase = True
    xRegExp.Pattern = "[^0-9a-z]"
    For Each xRg In xChanged
        If xRegExp.Test(xRg.Value) Then
            xHasErr = True
            Application.EnableEvents = False
            xRg.ClearContents
            Application.EnableEvents = True
        End If
    Next
    If a=1 Then

MsgBox "These cells had invalid entries and have been cleared:"
End If
End Sub


I got it from someother site, most of the lines are red

Plz help me
Posts
13
Registration date
Thursday May 9, 2019
Status
Member
Last seen
May 28, 2019
3
Thank You. I found answer for this.

But i have one doubt, how to read the cell value in vba if A1 has special character like --------

I used Range("A1").Value , Its throwing an error

Plz help me on this.
ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1573
How does that value get entered in?
Sanju_7454
Posts
13
Registration date
Thursday May 9, 2019
Status
Member
Last seen
May 28, 2019
3
Copy pasted from non-formatted cell to Cell A1
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1573
I dont get an error with this:

Sub showit()
Dim theval
theval = Range("A1").Value
MsgBox (theval)
End Sub