How to convert output value of invokeVBA into a collection?


#1

Please help me to solve this problem. uipath invokeVBA activity gets any variant we return from a function as a “COM Component/object” . So I am returning a collection from the macro. and invokeVBA gets it as a COM object(lets refer it as OBJECT) . So i need to convert/read it into a collection again. and i am unable to do it. i am trying this for a week now. Please help.

color code excel example.xlsx 1 (7.9 KB)

find cell adresses.xaml (9.4 KB)

These are the excel i am trying get cell values collection and the uipath module that i have created.
Below please find the macro(vba).

Function findcellFunction(ByAmount As Integer)As Collection
	'On Error Resume Next
    Dim rngX As Range			
	Dim WS As Worksheet
	Dim datax As Range
	Dim cellAddress As Variant
	Dim index As Integer
	Dim iTotal As Integer
	Dim CellArray 
	iTotal = 0
	Set CellArray = New Collection
	For index=1 To Amount
		Set rngX = Worksheets("Sheet1").Range("A1:EZ50").Find("Color Name", lookat:=xlPart)
		If Not rngX Is Nothing Then
		MsgBox "Found at " & rngX.Address
		CellArray.Add rngX.Address
		End If
		Cells(rngX.Row,rngX.Column).Delete
		iTotal =iTotal + index
	Next index

	For Each cellAddress In CellArray
	MsgBox "list populated " & cellAddress
	Range(cellAddress).Value = "Color Name"
	Next
	Set findcellFunction= CellArray
End Function