Param in function vba
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
24-05-18 09:19 PM
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 ?
3 REPLIES 3
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
25-05-18 08:12 PM
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.
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
02-09-20 11:54 PM
Create an integer matrix
Then you could use this as pararemter for FieldInfo
Full code
------------------------------
Miguel Carrillo
------------------------------
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
------------------------------
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
03-09-20 02:13 PM
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
------------------------------
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
------------------------------
