VB code looping through array and check each item

[stefi]

Hi,

I use a Excel-macro within ExecuteMacro activity. The macro checks individual cells for formalities and content.
The specific cell to which I refer my question can contain one or more email-addresses splitted by semikolon (;). For instance:

In the macro check I want to read this cell as array and loop through each items.
Every item should be checked if the email format is set correctly.
In the example above, the second email-address is not correct due to a false domain (“@expLcom”).
If all containing email-addresses are in a correct way, then the cell should not be coloured red.
Otherwise, if only one or even more email-addresses are false formatted, then the cell should be coloured red - independent where the false formatted email-address is mentioned (on first, second,…,last place).

I already created following VB code but Excel returns still an error and the macro cannot be executed.

Dim booCheck As Boolean
Dim strArray() As String
Dim intMails As Long
Dim intFalseMails As Integer

'//Checking email-addresses

    strArray = Split(Trim(SpecificCell.Value), ";")
    For intMails = LBound(strArray) To UBound(strArray)
    If strArray.Value Like "*@*.*" Then
        intFalseMails = intFalseMails
    Else
        intFalseMails = intFalseMails + 1
    End If
    Next intMails
    If intFalseMails > 0 Then
        SpecificCell.Interior.Color = vbRed
        booCheck = False
    Else
        SpecificCell.Interior.Pattern = xlNone
    End If

Is there anybody who can help?

Any help much appreciated!

[ashwin.ashok ((TheCodingTheory))]

Hi @stefi ,

I think there is an issue with the Regex Pattern, is this what you wanted to detect?

If so then try using this pattern instead and let us know if it works out for you.

.*@*\..*

Also, this can be easily taken care of with UiPath. Macros aren’t required, but if that is what you are comfortable with then go for it.

If that doesn’t work, we’d appreciate it if you could share a sample workbook for us to experiment with.

Kind Regards,
Ashwin A.K

[stefi]

Hi @ashwin.ashok ,
The Regex Pattern should be not the problem because the error which Excel returns relates to “no object”.
Regarding a sample workbook I only can provide you an xlsx file because xlsm cannot be uploaded here.
Test file VB code.xlsx (11.0 KB)

But I used following VB code in a xlsm file with the same worksheet input:

Sub Modul1()

Dim strArray() As String
Dim intMails As Long
Dim intFalseMails As Integer

'//Checking email-addresses

strArray = Split(Trim(A2.Value), “;”)
For intMails = LBound(strArray) To UBound(strArray)
If A2.Value Like “@.*” Then
intFalseMails = intFalseMails
Else
intFalseMails = intFalseMails + 1
End If
Next intMails
If intFalseMails > 0 Then
A2.Interior.Color = vbRed
Else
A2.Interior.Pattern = xlNone
End If
End Sub

Hope that is useful.

Kind regards
Stefi

[ashwin.ashok ((TheCodingTheory))]

Hi @stefi ,

I’ve edited the macro, could you give this a try and let me know if it works as expected?

Sub Modul1()

Dim strArray() As String
Dim bool_invalidEmail As Boolean
Dim Sh As Worksheet
Dim objRegex As Object

    'Initializing Regex Object
    Set objRegex = CreateObject("VBScript.RegExp")
    
    'Initializing Boolean to check whether email is in right format or not
    bool_invalidEmail = False
    
    'Initializing Worksheet to work with
    Set Sh = ThisWorkbook.Worksheets("Tabelle3")
    strArray = Split(Trim(Sh.Range("A2").Value), ";")
       
    For intMails = LBound(strArray) To UBound(strArray)
        
        'Setting Pattern to work with
        With objRegex
            .Pattern = ".*@.*\..*"
        End With
        
        'Checking to see whether the pattern matches or not
        If Not objRegex.Test(strArray(intMails)) Then
            bool_invalidEmail = True
            Debug.Print strArray(intMails)
            Exit For
        End If
    
    Next intMails

    If bool_invalidEmail Then
        Sh.Range("A2").Interior.Color = vbRed
    Else
        Sh.Range("A2").Interior.Pattern = xlNone
    End If
    
End Sub

Kind Regards,
Ashwin A.K

[stefi]

Hi @ashwin.ashok

Very much appreciated your help! The vb code in your way works! Many thanks !!!

Kind regards
stefi