worksheet name versus reference

[cahinton]

I'm having trouble with assigning a worksheet reference, then passing that to a sub. In the sample below I use two different ways to identify the worksheet that I want to format. I can't figure out the difference between passing the reference to the worksheet versus its name.
Thanks in advance!

[INDENT]Sub fmtDate_Sputter1(ws As Worksheet)
'
    ws.Range("C:C").NumberFormat = "yyyy-mmm-dd, hh:mm:ss"
End Sub


Sub fmtDate_Sputter2(wsname As String)
'
    Worksheets(wsname).Range("C:C").NumberFormat = "yyyy-mmm-dd, hh:mm:ss"
End Sub


Sub Foo23()
' test driver for fmtDate_Sputter()
    Dim wsCycles As Worksheet
    strCycleSheetName = "cycle"
    Set wsCycles = Worksheets(strCycleSheetName) 
    'Set wsCycles = Sheets(strCycleSheetName)
    fmtDate_Sputter1 (wsCycles)    ' err 438: no support method / property
    fmtDate_Sputter1 (Worksheets(strCycleSheetName)) ' err 438: no support method / property
    fmtDate_Sputter2 (strCycleSheetName) ' works
End Sub
[/INDENT]

 

[Fluff]

Try

    fmtDate_Sputter1 wsCycles
    fmtDate_Sputter1 Worksheets(strCycleSheetName)

 

[cahinton]

Thanks for the quick help! I found I needed to retain the parentheses when I was passing the string in a variable, but not as a literal.

Sub Foo23()
' test driver for fmtDate_Sputter()
    Dim wsCycles As Worksheet
    strCycleSheetName = "cycle"
    Set wsCycles = Worksheets(strCycleSheetName) ' err 438: no support method / property
    'Set wsCycles = Sheets(strCycleSheetName)
    fmtDate_Sputter1 wsCycles
    fmtDate_Sputter1 Worksheets(strCycleSheetName)
    fmtDate_Sputter2 (strCycleSheetName)
    fmtDate_Sputter2 "Cycle"
End Sub

 

[RoryA]

Thanks for the quick help! I found I needed to retain the parentheses when I was passing the string in a variable, but not as a literal.

That's only because you didn't declare your variable so you were attempting to pass a Variant to a routine that requires a String. Adding the parentheses allows evaluation of the variable to a string.