Menu
I've been trying to pass a sheet in my workbook to a function, but it doesn't seem to be working. The function is
Private Sub passToSheet(theData As Variant, Optional mySheet As Worksheet)
I have tried doing
Dim mySheet as Worksheet
Set mySheet = "Results"
then to call the function
passToSheet theData mySheet
but it doesn't seem to work. I'm not really sure what to google to get the right way to reference the worksheet though! Any help is much appreciated!
453
You can pass an argument by value if you include the ByVal keyword in the procedure's declaration. Arguments passed by value consume from 2–16 bytes within the procedure, depending on the argument's data type. Larger data types take slightly longer to pass by value than smaller ones.
To allow for a variable number of ranges to be used in the function, you need to declare a ParamArray variant array in your argument list. Then, you can process each of the ranges in the array in turn. This function could then be used in the worksheet to add multiple ranges.
Use
'Set mySheet to worksheet
Set mySheet = Worksheets("Results")
to get a reference to the Worksheet.
67
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
