Execute Macro Output Query

[Kyleb91 (Kyle Brown)]

Hey guys!

Basically I have a Workbook with a macro that I am executing, and passing in VAR parameters through Uipath.

In the VB Code I am then setting Values from the workbook to Variables but I can’t seem to pass these out to UiPath from the execute macro activity.

Wondering how do you retrieve output from this activity, as any time I use it, the output is null.

Many Thanks

[lakshman (Ganta lakshman)]

@Kyleb91

@Kyleb91

Wondering how do you retrieve output from this activity, as any time I use it, the output is null.

Execute Macro activity will give output of type Object.

Activities - Execute Macro

Is Execute Marco activity executing properly on that excel file ?

[Kyleb91 (Kyle Brown)]

Hi @lakshman Yes the activity works perfectly and executes the macro in the Workbook, but doesn’t give any output… Should I have something in the VB Code of the Macro to give output?

[lakshman (Ganta lakshman)]

@Kyleb91

@Kyleb91

but doesn’t give any output… Should I have something in the VB Code of the Macro to give output?

Yes it should be there in your Macro code. Then only it will give output else not.

[ermanoj3101 (MANOJ)]

Hi @Kyleb91 ,

Can you show me your macro function?

Are you returning value from macro function?

[Kyleb91 (Kyle Brown)]

Sub CRT_Paste(VAR1 As String, VAR2 As String, VAR3 As String, VAR4 As String)
    

        Range("D19") = VAR1
        Range("E19") = VAR2

        
        VAR3 = Range("R19")
        VAR4 = Range("L5")
        
        
End Sub

Basically I want to Return VAR3 and VAR4 back to UiPath from this Execute Macro Activity, via this macro code.

[ermanoj3101 (MANOJ)]

@Kyleb91

Sub CRT_Paste(VAR1 As String, VAR2 As String, VAR3 As String, VAR4 As String)
    

        Range("D19") = VAR1
        Range("E19") = VAR2

        
        VAR3 = Range("R19")
        VAR4 = Range("L5")
        
        
End Sub

Hi @Kyleb91 ,

Try like this

Sub CRT_Paste(VAR1 As String, VAR2 As String, VAR3 As String, VAR4 As String) As String

    Range("D19") = VAR1
    Range("E19") = VAR2

    
    VAR3 = Range("R19")
    VAR4 = Range("L5")
    CRT_Paste = VAR3

End Sub

Try first for Var3

[prasath_S]

Hi @Kyleb91

Procedures cannot return a value in VBA, you have to use function for that.

FUNCTION CRT_Paste(VAR1 As String, VAR2 As String, VAR3 As String, VAR4 As String) as string
    

        Range("D19") = VAR1
        Range("E19") = VAR2

        
        VAR3 = Range("R19")
        VAR4 = Range("L5")


        CRT_Paste = VAR3
        
End function

Thanks

[Kyleb91 (Kyle Brown)]

@prasath_S

rocedures cannot return a value in VBA

Brilliant, I thought that may have been the issue…

May I ask you how I would call on the value in UiPath form the output object after this? For VAR3

[prasath_S]

@Kyleb91 have you create a variable for output in execute macro, and check the value in message box.

Thanks

[Kyleb91 (Kyle Brown)]

Thanks I have got VAR3, but how do I pass out VAR3 AND Var4?

[prasath_S]

You can put like this in VBA,

FUNCTION CRT_Paste(VAR1 As String, VAR2 As String, VAR3 As String, VAR4 As String) as string
    

        Range("D19") = VAR1
        Range("E19") = VAR2

        
        VAR3 = Range("R19")
        VAR4 = Range("L5")

CRT_Paste = Var1 & "," & Var2 & "," & Var3 & "," & Var4

End function

And after getting the output from UiPath use split function to split the values by comma,like this, it will give an array of values.

arrVal - arrray of String (system.string)

arrVal = Output.split(","c)

Var1 = arrVal(0)
Var2 = arrVal(1)
Var3 = arrVal(2)
Var4 = arrVal(3)

If you want all 4 values

Thanks

[ppr (Peter Preuss)]

Example:

Public Function test() As String
    test = "1"
End Function

Function name is test, so with the same name test with value “1” will be returned

Returning multiple values we can do when complex datatypes (e.g. dictionary) is not supported ( and we do not want use arrays) by returning a serialized string e.g. {{“Key1”,“Val1”},{“Key2”,“Val2”}}

Afterwards we can easy bring this again in a dictionary

[Kyleb91 (Kyle Brown)]

Thanks! Although the below doesn’t seem to be accepted by VBA, do I need to change the CRT_Paste as String to something else first?

CRT_Paste = Var1 & “,” Var2 & “,” Var3 & “,” Var4

[Kyleb91 (Kyle Brown)]

Hey Peter, thanks again!

Could you show your above code with an example of 2 VARs being passed out? Like the dictionary you say.

Many Thanks

[ppr (Peter Preuss)]

@ppr

{{“Key1”,“Val1”},{“Key2”,“Val2”}}

{{“Key1”,“Val1”},{“Key2”,“Val2”}} would be the string

[prasath_S]

@Kyleb91 try like this, typing in mobile so had some mistakes

FUNCTION CRT_Paste(VAR1 As String, VAR2 As String, VAR3 As String, VAR4 As String) as string
    

        Range("D19") = VAR1
        Range("E19") = VAR2

        
        VAR3 = Range("R19")
        VAR4 = Range("L5")

CRT_Paste = Var1 & "," & Var2 & "," & Var3 & "," & Var4

End function

Thanks

[Kyleb91 (Kyle Brown)]

@prasath_S

CRT_Paste = Var1 & "," & Var2 & "," & Var3 & "," & Var4

Thanks! But…

CRT_Paste = Var1 & “,” & Var2 & “,” & Var3 & “,” & Var4

This still does not work…

[prasath_S]

@Kyleb91 it should work, I just tested it, do you get any errors.

Thanks

[Kyleb91 (Kyle Brown)]