Sub inside a UserForm can't receive Worksheet as parameter

Question

for my internship I have to do some excel UserForms and vba macros. I have a Private Sub inside my UserForm and i need to pass a WorkSheet as parameter to it. But i always get

"Execution error 438"

when the Sub is called.

What bothers me is that FirstBlankColumn works but FillAllBox doesn't.

I tried:

-putting FillAllBox in Public and neither Private nor Public

-turning FillAllBox into a Function

-changing the way i pass the Worksheet (ie: Set ws = Worksheets("ExportedData") then passingws)

'''vba
Private Function FirstBlankColumn(ws As Worksheet) As Long
   'Yadda yadda does some stuff
   '
   'returns the number of the first blank column
End Function


Private Sub FillAllBox(ws As Worksheet)
    ' we never get to the FillBox calls
    FillBox UserBox, ws
    FillBox StockTransBox, ws
    FillBox SemaineBox, ws
    FillBox LocationBox, ws
    FillBox ValeurBox, ws
End Sub

Private Sub UserForm_Initialize()
    ' displays correctly the number of the first blank column
    MsgBox FirstBlankColumn(Worksheets("ExportedData"))
    ' Throws "error 438" upon call
    FillAllBox (Worksheets("ExportedData"))
End Sub
'''

For a long time i passed the Sheet names and String to pass sheet as parameters. But having to call the sheet list in every function/sub to retrieve my sheet isn't optimised.

I'd like to be able to directly pass Sheets or Worksheets as parameter, reliably.

Thanks in advance.

Robert Todar Robert Todar · Accepted Answer · 2019-07-25T14:58:21

This issue is your use of parentheses(()). Remove those from your call and you should be good to go.

Here is Microsoft's documentation on the use of parentheses.

Sub procedures, built-in statements, and some methods don't return a value, so the arguments aren't enclosed in parentheses

Function procedures, built-in functions, and some methods do return a value, but you can ignore it. If you ignore the return value, don't include parentheses. Call the function just as you would call a Sub procedure. Omit the parentheses, list any arguments, and don't assign the function to a variable.

 FillAllBox Worksheets("ExportedData")

Sub inside a UserForm can't receive Worksheet as parameter

1 Answers