excel 如何使用VBA连接一维数组的元素?

ki1q1bka  于 2023-02-17  发布在  其他
关注(0)|答案(1)|浏览(137)

我试图在Outlook中循环浏览电子邮件,废弃唯一的ID号,并将它们连接为一个字符串。
每封邮件包含多个不同的ID,这些ID在整个电子邮件中成倍增加,如下所示:

ID 1111, ID 2222
ID 1111, ID 33333, ID 2222
ID 1111, ID 2222, ID 444, ID 33333
ID 2222, ID 1111

那么预期结果将是(排序):

ID 444, ID 1111, ID 2222, ID 33333

这是我的代码。

Sub Scrap_IDs()

Dim olApp As Outlook.Application: Set olApp = New Outlook.Application
Dim olFolder As MAPIFolder: Set olFolder = olApp.Session.GetDefaultFolder(olFolderInbox).Folders("Folder_name")

    Dim olMail As Variant: For Each olMail In olFolder.Items
        Dim mBody As String: mBody = olMail.Body
    
        With olMail
        ' Scrap all IDs using regex
             With New RegExp
                    .Global = True
                    .Pattern = "ID[ \d]+"
            
            Dim MatchID As Object: For Each MatchID In .Execute(mBody)
                    Dim i As Long: Dim arrMatchID(): ReDim Preserve arrMatchID(i)
                    arrMatchID(i) = MatchID.Value
                    i = i + 1
                Next
            End With
            
        ' Remove duplicates from array
            Dim RemArrDups As Variant: RemArrDups = WorksheetFunction.Sort(WorksheetFunction.Unique(WorksheetFunction.Transpose(arrMatchID)))
    
        ' Concatenate array items
            Dim IDs As String: IDs = Join(RemArrDups, ", ")
    
        End With
    Next

End Sub

与连接相关的行给出了运行时错误5 "无效的过程调用或参数"。这是为什么?
如果我在arrMatchID而不是RemArrDups上使用Join函数,它会起作用,但这样的话,值就不会是唯一的。

31moq8wy

31moq8wy1#

在VBA中使用Microsoft 365的UNIQUESORT

' This is a 1D array, a single row.
Dim arrMatchId(): arrMatchId = Array( _
    "ID 667", "ID 3", "ID 1111", "ID 2222", "ID 3", "ID 44", "ID 667")

' Remove duplicates and sort the array.
' Keep it a single row (don't transpose). Use 'True' as the parameters
' for the 'col' arguments of both functions.
Dim RemArrDups(): RemArrDups _
    = Application.Sort(Application.Unique(arrMatchId, True), , , True)

' Join.
Dim IDs As String: IDs = Join(RemArrDups, ", ")

Debug.Print IDs

相关问题