So,finally my first time using VBA and I’ve found this script to hide columns in an excel file:
Sub Hide_Columns_Containing_Value()
Dim c As Range
For Each c In Range("A1:G1").Cells
If c.Value = ("SearchThis") Then
c.EntireColumn.Hidden = True
End If
Next c
End Sub
This works fine using as Invoke VBA within an Excel Application Scope. However, I want the “SearchThis” to be a variable from the workflow. So I add it in the top VBA row, and add a value in EntryMethodParameters: {“Header1”}
or even just {1}
However, even when I don’t start using the new variable in the VBA, the I always get the error “Number of parameters specified does not match the expected number”.
Sub Hide_Columns_Containing_Value(SearchThis)
Dim c As Range
For Each c In Range("A1:G1").Cells
If c.Value = ("SearchThis") Then
c.EntireColumn.Hidden = True
End If
Next c
End Sub
How can I get this to work? I’ve looked a other topics and all I see is that the number of values between the ( ) should be the same as the number of values in EntryMethodParameters.
So
(VBA opening: )Sub Hide_Columns_Containing_Value(SearchThis,Range)
(Uipath Invoke VBA property:) EntryMethodParameters {"Header4","A1:X1"}
I’m not experienced in that matter but please try the following:
VBA
Public Sub Hide_Columns_Containing_Value(inSearch As String, inRange As String)
Dim cell As Range
For Each cell In Range(inRange).Cells
If cell.Value = (inSearch) Then
cell.EntireColumn.Hidden = True
End If
Next cell
End Sub
UiPath
EntryMethodParameters = New List(of Object) From {"Header4", "A1:X1"}
Hi, msan’s solution should work.
Here is some additional info on your vba code:
In Sub Hide_Columns_Containing_Value(SearchThis) you tell your Sub procedure to expect just one variable (in UiPath that would be an attribute). If you just give it a name, the variable has the data type variant, i.e. it can be a string, a number or anything. It is usually better to define the data type, like msan did: SearchThis as String. The procedure then only accepts a string value. In VBA you would usually use strSearchThis, because if you call the sub in VBA, the editor will show the (list of) variables expected by the procedure, but not its type, so the prefix “str” in strSearchThis will tell you at that point that it expects a string. Irrelevant for UiPath, I guess. There is also no in and out in VBA. It is basically just “in”.
Next, you tried to use the variable in your expression If c.Value = (“SearchThis”) Then. However, by putting it into quotation marks you made it a string, not a variable. That means that your VBA procedure did not look for the value contained in your variable SearchThis, but for the text (or string) “SearchThis”.
Hope that makes it clearer.