Why first argument of subroutine must be passed ByVal

user_4279468 0
Viewed 39

I've following code:

Sub AddValidation()
   Dim ws, wsDefinitions As Worksheet
   Set ws = ThisWorkbook.Worksheets("TData")
   Set wsDefinitions = ThisWorkbook.Worksheets("Definitions")

   Call AddValidator(ws, wsDefinitions, "FaultType", FaultTypeColumn)
End Sub

And

Sub AddValidator(targetWs As Worksheet, definitionsWs As Worksheet, definitionTableName As String, targetColumnNumber)

    Dim definitionsRange As Range, targetRange As Range

    Set definitionsRange = definitionsWs.ListObjects(definitionTableName).ListColumns(1).DataBodyRange
    Set targetRange = targetWs.ListObjects("Table1").ListColumns(targetColumnNumber).DataBodyRange

    With targetRange.Validation
        .Delete 'delete previous validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Formula1:="='" & definitionsWs.Name & "'!" & definitionsRange.Address
    End With

End Sub

During Compilation I get "ByRef argument type missmatch" for the first argument of AddValidator - "ws". When I add ByVal to the definition:

Sub AddValidator(ByVal targetWs As Worksheet, definitionsWs As Worksheet, definitionTableName As String, targetColumnNumber)

Everything compiles fine. Two first arguments of "AddValidator" macro are Worksheet types but only first is required to be passed by Value.

Can someone tell me why?

vba excel
1 Answers

The issue is in declaration of variables. 

Dim ws, wsDefinitions As Worksheet

Here ws gets declared as variant and not as sheet object and therefore Excel prompts you update the argument. If you fix this then Excel won't raise an error. Following sub will work without error.

Sub Test()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")
    mySub ws1, ws2
End Sub
Sub mySub(targetWS As Worksheet, sourceWS As Worksheet)
    MsgBox targetWS.Name
    MsgBox sourceWS.Name
End Sub
user_87599270
Related