Call Excel VBA function with set-type from Outlook VBA Sub

Question

I'm trying to call –unsuccessfully– a set-type function in Excel, from an Outlook Sub.

So, I have the following type in Excel VBA:

Type Settings
    Value As String
    Stop As Boolean
    ID As Long
End Type

As well, I'm using the following function to fill it in:

Function Projects_List_1(Var_Num As Long) As Settings

Dim c As Long, Sets As Settings
Set Rng5 = CDU.Range("CDU_Projects")

For c = 1 To Rng5.Count
    If Rng5.Cells(c, 1).Offset(0, 1).Value = "" Then
        If Var_Num = c Then
            Sets.Value = Rng5.Cells(c, 1).Value
            If Var_Num > Rng5.Count Then
                Sets.Stop = True
                Exit Function
            Else
                Sets.ID = Var_Num + 1
                Sets.Stop = False
                Exit For
            End If
        End If
    End If
Next c
Projects_List_1 = Sets

End Function

Then, I have the following testing code:

Sub Test_Excel()

Dim mysets As Settings, aaa As Long

aaa = 9
mysets = Projects_List_1(aaa)

Debug.Print mysets.Value
Debug.Print mysets.ID
Debug.Print mysets.Stop

End Sub

That would give me successful results (it's just data from a worksheet):

02.9 Data Cleanup
 10 
False

However, I'm not being able to call the function from Outlook VBA. I thought it was as simple as:

Sub Test_Outlook()

'Here I'm just creating the conextion to my workbook. There are no issues here.
Dim ExApp As Excel.Application, ExWbk As Workbook, WB As Workbook
Dim mysets As Settings, aaa As Long
Set ExApp = GetObject(, "Excel.Application")
For Each WB In ExApp.Workbooks
    If WB.Name = "3 Control de Requerimientos Main.xlsm" Then
        Set ExWbk = WB
    End If
Next WB

aaa = 9
mysets = ExWbk.Application.Run("Projects_List_1", aaa)

Debug.Print mysets.Value
Debug.Print mysets.ID
Debug.Print mysets.Stop

End Sub

But I'm getting error «Only public user defined types defined in public object modules can be coerced to...». I tried as well to create a type-set in Outlook as well, but I haven't been able to make it run.

I have a workaround, but it would be nice to know how I can trigger it this way.

Edit 1: I'm able to call the function from Outlook to get back one value*. The issue I'm having is when I'm trying to get back a set of parameters in one run.

Eugene Astafiev Eugene Astafiev · Accepted Answer · 2020-05-20T06:11:08

It seems you just need to include the workbook name:

Application.Run "'My Work Book.xls'!Macro1"

Then according to the error message you need to define a public function:

Public Sub Macro1(Var_Num As Long)
' your code goes here
End Sub

See How do I use Application.Run in Excel for the sample code.

Call Excel VBA function with set-type from Outlook VBA Sub

1 Answers