How can I pass an array (worksheets) to a function in VBA?-CodePudding

I want to iterate through an array of worksheets and then apply the "Lock_AllWithParams()" Method.

Thats the Code I have written so far. But VBA tells me all the time, that the argument within "Lock_AllWithParams(ws)" would not be optional. This confuses me.

Public Sub Lock_All()
    
    Dim ws As Worksheets
    
    '***Array mit den zu sperrenden Worksheets***'
    
    Set ws = ActiveWorkbook.Sheets(Array(WS_xy.Name, WS_yx.Name, WS_xyx.Name))
    
    
    Lock_AllWithParams (ws)
    
    End Sub


Public Sub Lock_AllWithParams(ByRef arr() As Worksheets)


    '***Setze Params***'
        
    For Each i In arr
        
        i.Protect , _
        Contents:=True, _
        Scenarios:=False, _
        DrawingObjects:=False, _
        UserInterfaceOnly:=False, _
        AllowFormattingCells:=True, _
        AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, _
        AllowInsertingColumns:=False, _
        AllowInsertingRows:=False, _
        AllowInsertingHyperlinks:=True, _
        AllowDeletingColumns:=False, _
        AllowDeletingRows:=False, _
        AllowSorting:=True, _
        AllowFiltering:=True, _
        AllowUsingPivotTables:=True
        
        i.EnableSelection = xlNoRestrictions
        
    Next i
    
End Sub

Thanks for your help!

I tried to change the arguments to another data type, set it to optional etc. I tried to get it to work using the answers within another thread. Nothing worked.

CodePudding user response：

Use Sheets instead of Worksheets - and don't declare the parameter as an array (= brackets after arr) Sheets already returns an array.

Sub lock_all()
Dim arrSheets As Sheets
Set arrSheets = ActiveWorkbook.Sheets(Array("Tabelle1", "Tabelle2"))

lock_AllWithParams arrSheets

End Sub

Public Sub lock_AllWithParams(arrSheets As Sheets)
Dim ws As Worksheet
For Each ws In arrSheets
    ws.Protect
Next
End Sub