I am trying to retrieve some cell addresses from excel sheet with UIPath invoke VBA activity. I am new to VBA coding, here I have written a function to retrieve cell address array and I have written a sub procedure to call that function. I am calling the sub in UIPath activity named' invoke VBA. So when I run this it says
Invoke VBA : Cannot run the macro 'Main'. The macro may not be available in this workbook or all macros may be disabled.
So Please help me to get over with this?
This is the screen shot where I have enabled macro in Excel:
Sub Main(Amount As Integer) Call findcellFunction(Amount) End Sub Function findcellFunction(Amount 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 'Iterate until all cell values are found For index=1 To Amount Set rngX = Worksheets("rptBOMColorPrint").Range("A1:EZ50").Find("Colour 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 iTotel =iTotal + index Next index 'shows list that has been populated with cell addresses For Each cellAddress In CellArray MsgBox "list populated " & cellAddress Range(cellAddress).Value = "Colour Name" Next CellArray = findcellFunction(Amount) End Function
This is how I call the VBA from the UIPath activity.