CODEWITHSUNDEEP
arraysvbafunctiondynamicreturn
BobH
BobH

Reputation: 1

VBA getting compile-time 'type mismatch' trying to call function that returns a String array

  1. Calling routine has variable declared as dynamic string array by Sub that calls - I get the error whether or not I use ReDim on any calling routine (to initially size the dynamic array):
Dim arrEmail as String()
.
.
.
SaveAttachmentsToTempFolderThenRetrieve olItem, arrEmail

Sub SaveAttachmentsToTempFolderThenRetrieve(ByVal olItem As Object, MatchEmail() As String)
.
.
.
MatchEmail = GetMatchEmail(Msg) 'this is the function call - Msg is type Object

Function GetMatchEmail(ByVal olItem As Object) As String()
'using public constant SenderMatchString

Dim numRecipients As Integer, i As Integer
Dim temparr() As String
Dim pa As PropertyAccessor

If InStr(SenderMatchString, olItem.SenderEmailAddress) > 0 Then
        numRecipients = olItem.Recipients.Count
        ReDim temparr(numRecipients)
        'sometimes the recipient is a 'group name' or something like that, not en email address
        For i = 1 To numRecipients
            If InStr(olItem.Recipients(i), "@") > 0 Then
                temparr(i) = Replace(olItem.Recipients(i), "'", vbNullString)    'needed to add this to strip out "'" characters in recipient
            Else
                Set pa = olItem.Recipients(i).PropertyAccessor
                temparr(i) = pa.GetProperty(PR_SMTP_ADDRESS)
            End If
        Debug.Print i, olItem.Recipients(i), temparr(i)
        Next
Else
    ReDim temparr(1)
    temparr(1) = olItem.SenderEmailAddress
End If
GetMatchEmail = temparr
end sub

  1. I've read many posts online about using Variant as type when returning an array from a function. I tried that (and changed type in all calling routines that get the return value to Variant) - that fails the same way

  2. I'm guessing that maybe VBA Functions that return arrays cannot receive parameters that aren't the same type(?). Either that, or maybe I'll have to give up and just build a single String (not an array) in the function and return that. Calling routines can use Split function to extract elements.

Upvotes: 0

Views: 68

Answers (2)

freeflow
freeflow

Reputation: 4355

There are a number of data structure available to VBA which can make like easier.

In your case such structures would be either a Scripting.Dictionary or an ArrayList. To get Scripting.Dictionaries and/or Arraylists you need to use tools.references.add to add references to Microsoft Scripting Runtime, or mscorelib respectively.

The scripting.dictionary has a .Items method which returns a variant array of the items in the dictionary. ArrayList uses a .ToArray method to convert its items to a variant array.

Your function, rewritten to use an Arraylist is below. As I don't have your Outlook the code obviously hasn't been tested, but returns no negative inspection using the free and fantastic Rubberduck addin for VBA.

Function GetMatchEmail(ByVal olItem As Object) As arraylist
    'using public constant SenderMatchString

    Dim numRecipients As Long, i As Long
    Dim temparr As arraylist
    Set temparr = New arraylist
    
    Dim pa As PropertyAccessor

    If InStr(sendermatchstring, olItem.SenderEmailAddress) > 0 Then
    
        numRecipients = olItem.Recipients.Count
       
        'sometimes the recipient is a 'group name' or something like that, not en email address
        
        For i = 1 To numRecipients
        
            If InStr(olItem.Recipients(i), "@") > 0 Then
            
                temparr.Add Replace(olItem.Recipients(i), "'", vbNullString) 'needed to add this to strip out "'" characters in recipient
                
            Else
            
                Set pa = olItem.Recipients(i).PropertyAccessor
                temparr.Add pa.GetProperty(PR_SMTP_ADDRESS)
                
            End If
            
            Debug.Print i, olItem.Recipients(i), temparr.Item(i)
            
        Next
        
    Else
    
        temparr.Clear
        temparr.Add olItem.SenderEmailAddress
        
    End If
    
    Set GetMatchEmail = temparr
    
End Function

Upvotes: 0

Xavier Junqué
Xavier Junqué

Reputation: 452

Have you tried over dimensioning temparr() enough and supressing redim() ?

Upvotes: 0

Related Questions