Param in function vba

[BordessoullesVi]

Hi, I have this code : Dim ws, excel,sheet As Object ws = GetWorksheet(Handle, Workbook, Worksheet) ws.Activate() excel = ws.Application sheet = excel.ActiveSheet ws.Range([range]).RemoveDuplicates(Columns :=Array(1,1), Header:=[Header]) input :range - string (ex:"B:B") input :Header- flag(ex:"True") input : Columns - ???   This code implement in Excel vbo RemoveDuplicates function of Excel. But I need help because I do not understand how to pass the argument Array (x, x) in bluePrism!? How should I declare it? Knowing that this argument Array must be dynamic. If I put the function in a vba excel, it runs correctly. Here I have the following error at compile: 'Array' is a type and can not be used as an expression. if I replace with a "string", it does not work   Bonus question (still in Excel VBO): how to do when you have a function that can take optional arguments to pass empty values? for example in the function:         ws.Range([range]).TextToColumns(Destination :=destinationRange, _                                 DataType :=[DataType], _                                 TextQualifier :=[TextQualifier], _                                 ConsecutiveDelimiter :=[ConsecutiveDelimiter], _                                 Tab :=[Tab], _                                 Semicolon :=[Semicolon], _                                 Comma :=[Comma], _                                 Space :=[Space], _                                 Other :=[Other], _                                 OtherChar :=[OtherChar], _                                 FieldInfo :=[FieldInfo], _                                 TrailingMinusNumbers :=[TrailingMinusNumbers]) Can become: .TextToColumns( "A:A", 2, 4, , , , , True, , , , , , ) I dont find the way in bluePrism to pass him the null arguments! An idea ?

[John__Carter]

BP code stages use .Net code, not VBA. The 2 languages are similar but not identical and you can't paste a macro into BP and expect it to work, you have to write .Net code. The VBA can act as a good skeleton, but it will need to be adjusted. Exactly how you do that is too big a subject for a forum.

[MiguelCarrillo]

Create an integer matrix 

Dim matrix = New Integer(6, 1) {{1, 1}, {2, 1}, {3, 9}, {4, 1}, {5, 1}, {6, 1}, {7, 1}}

Then you could use this as pararemter for FieldInfo

FieldInfo:=matrix

Full code

Dim wb, ws As Object
Dim excel, sheet, range As Object
Dim matrix = New Integer(6, 1) {{1, 1}, {2, 1}, {3, 9}, {4, 1}, {5, 1}, {6, 1}, {7, 1}}

Try

wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()
ws.Activate()
excel = ws.Application

excel.Selection.TextToColumns (Destination:=excel.Range(Cell_Reference), DataType:=1, _
TextQualifier:=-4142, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=Delimiter, FieldInfo:=matrix, TrailingMinusNumbers:=True)

Success = True

Catch e As Exception
Success = False
Message = e.Message
Finally
wb = Nothing
ws = Nothing
excel = Nothing
sheet = Nothing
range = Nothing
End Try

------------------------------
Miguel Carrillo
------------------------------

[AndreyKudinov]

That would be Type.Missing in .Net, although better use named arguments like Miguel suggested.

sourcerange.TextToColumns(Type.Missing,1,1,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,{1, 1},Type.Missing,Type.Missing)

------------------------------
Andrey Kudinov
Project Manager
MobileTelesystems PJSC
Europe/Moscow
------------------------------