VBA Compiler Long Statement

I'm trying to perform an Application.Activesheet.Protect and I can't seem to get a multiple line statement or even a single line statement to execute. All of the variables below are defined. Here's a couple of examples:

Application.Activesheet.Protect Password:=S_Password, _
DrawingObjects:=S_DrawingObjects, _
Contents:=S_Contents, _
Scenarios:=S_Scenarios, _
UserInterfaceOnly:=S_UserInterfaceOnly, _
AllowFormattingCells:=S_AllowFormattingCells, _
AllowFormattingRows:=S_AllowFormattingRows, _
AllowFormattingColumns:=S_AllowFormattingColumns, _
AllowInsertingColumns:=S_AllowInsertingColumns, _
AllowInsertingRows:=S_AllowInsertingRows, _
AllowInsertingHyperlinks:=S_AllowInsertingHyperlinks, _
AllowDeletingColumns:=S_AllowDeletingColumns, _
AllowDeletingRows:=S_AllowDeletingRows, _
AllowSorting:=S_AllowSorting, _
AllowFiltering:=S_AllowFiltering, _
AllowUsingPivotTables:=S_AllowUsingPivotTables

Even a reduced statement fails:

Application.Activesheet.Protect Password:=S_Password, DrawingObjects:=S_DrawingObjects, Contents:=S_Contents, Scenarios:=S_Scenarios, UserInterfaceOnly:=S_UserInterfaceOnly, AllowFormattingCells:=S_AllowFormattingCells



Is there a maximum statement size or some other technique I can use to execute this long statement?

Thank you for the detailed example. This is not a statement size limit: it is a current limitation in our VBA compiler. When a method is called with several named arguments and without parentheses, the compiled code passes them with the wrong names, and Excel rejects the call at runtime (error 0x80020004, parameter not found). A call with a single named argument is not affected.

Until it is fixed, wrap the call in Call with parentheses, which gives each named argument its correct name:


The same applies to any other method called with two or more named arguments. We will fix this in a next update.

gdgsupport said:

Thank you for the detailed example. This is not a statement size limit: it is a current limitation in our VBA compiler. When a method is called with several named arguments and without parentheses, the compiled code passes them with the wrong names, and Excel rejects the call at runtime (error 0x80020004, parameter not found). A call with a single named argument is not affected.

Until it is fixed, wrap the call in Call with parentheses, which gives each named argument its correct name:

Code:

Call Application.ActiveSheet.Protect(Password:=S_Password, _
    DrawingObjects:=S_DrawingObjects, Contents:=S_Contents, Scenarios:=S_Scenarios, _
    UserInterfaceOnly:=S_UserInterfaceOnly, AllowFormattingCells:=S_AllowFormattingCells, _
    AllowFormattingColumns:=S_AllowFormattingColumns, AllowFormattingRows:=S_AllowFormattingRows, _
    AllowInsertingColumns:=S_AllowInsertingColumns, AllowInsertingRows:=S_AllowInsertingRows, _
    AllowInsertingHyperlinks:=S_AllowInsertingHyperlinks, AllowDeletingColumns:=S_AllowDeletingColumns, _
    AllowDeletingRows:=S_AllowDeletingRows, AllowSorting:=S_AllowSorting, _
    AllowFiltering:=S_AllowFiltering, AllowUsingPivotTables:=S_AllowUsingPivotTables)

The same applies to any other method called with two or more named arguments. We will fix this in a next update.

Click to expand...

Thanks for the response. Couldn't continue the statement to more than a couple of lines so strung it together. I used positional parameters and it worked. Here's what the code looks like:

Application.Activesheet.Protect(S_Password, S_DrawingObjects,S_Contents,S_Scenarios, S_UserInterfaceOnly,S_AllowFormattingCells,S_AllowFormattingColumns,S_AllowFormattingRows,S_AllowInsertingColumns,S_AllowInsertingRows,S_AllowInsertingHyperlinks,S_AllowDeletingColumns,S_AllowDeletingRows,S_AllowSorting,S_AllowFiltering,S_AllowUsingPivotTables)