Help using VBA and variables

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

Hello,

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"}
2 Likes

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.

1 Like

Many thanks Achi and Msan, it works like this :grinning:

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.