topic RE: Param in function vba in Product Forum

Param in function vba

BordessoullesVi — Thu, 24 May 2018 20:19:00 GMT

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 ?

BP code stages use .Net code…

John__Carter — Fri, 25 May 2018 19:12:00 GMT

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.

RE: Param in function vba

MiguelCarrillo — Wed, 02 Sep 2020 22:54:00 GMT

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
------------------------------

RE: Param in function vba

AndreyKudinov — Thu, 03 Sep 2020 13:13:00 GMT

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
------------------------------