Solved Pass a worksheet through a function (1 Viewer)

[Valentine]

I have :

    Set xlApp = CreateObject("Excel.Application")
    Set xlWB = xlApp.Workbooks.Add
    Set xlSh = xlWB.ActiveSheet

I want to pass that through a function that fills in the rest of the information into the spreadsheet depending on if its a leap year or not.

currently :

    If Year(Date) Mod 4 = 0 Then
        Leap (xlSh)
    Else
        Calendar (xlSh)
    End If

I am getting object doesn't support this property or method.

My function titles are:

Public Function Calendar(xlSh As Object)

and:

Public Function Leap(xlSh As Object)

 

[sonic8]

I am getting object doesn't support this property or method.

Investigate where exactly this error happens. I don't see anything wrong with the code you posted and I think the error must happen somewhere else.

 

[Valentine]

Functions confuse the hell out of me.

xlSh.range("D3").Autofill Destination:=xlSh.range("D3:ND3"), Type:=xlFillWeekdays
        'Set months title
    With xlSh.range("D1", "AH1")
        .Font.Bold = True
        .Font.Size = 12
        .VerticalAlignment = xlVAlignCenter
        .HorizontalAlignment = xlVAlignCenter
        .Mergecells = True
        .Interior.ColorIndex = 37
        .Value = "January"
    End With
    'Set day numbers for January
    xlSh.range("D2:E2").Autofill Destination:=xlSh.range("D2:AH2"), Type:=xlFillSeries
    'This is set to a non leap year standard
    With xlSh.range("AI1", "BJ1")
        .Font.Bold = True
        .Font.Size = 12
        .VerticalAlignment = xlVAlignCenter
        .HorizontalAlignment = xlVAlignCenter
        .Mergecells = True
        .Interior.ColorIndex = 33
        .Value = "February"
    End With

that is a snippet of what is in the function. It worked perfectly fine while apart of the main sub. I have tried to return as object at the end of the function title still same error. I think its something to do with me calling xlSh.range throughout the function that is causing the error. I have tried to change that to calendar.range and still same error.

 

[MajP]

You should be calling your procedures without parentheses

3.1) An argument list for a function call with an assignment to the returned value must be surrounded by parens: Result = MyFunc(MyArg)
3.2) An argument list for a subroutine call (or a function call with no assignment) that uses the Call keyword must be surrounded by parens: Call MySub(MyArg)
3.3) If 3.1 and 3.2 do not apply then the list must NOT be surrounded by parens.

And finally there is the byref rule: arguments are passed byref when possible but if there are “extra” parens around a variable then the variable is passed byval, not byref.

NOT
Leap (xlSh)
Else
Calendar (xlSh)

But
Leap XLSH
else
Calendar xlSH

What do you mean 'cannot use parentheses?'

docs.microsoft.com

If you do that then I am thinking it is of the case where it thinks you are forcing to pass the xlsheet byval which you cannot do. Thus the "does not support this property...".

 

[Gasman]

Your leap year check is also a little off?

 

[MajP]

I am correct it is a simple test to prove it.

Public Sub Main()
  Dim xlsh As Worksheet
  Set xlsh = ActiveWorkbook.Worksheets(1)
 
 'This works
  calendar XLSH
 
 'This throws Error 438 Object does not support this property or method.
  Calendar (xlsh)
End Sub


Public Function Calendar(xlsh As Object)
  MsgBox xlsh.Name
End Function

 

[Valentine]

Thank you it works perfectly now. I spent like 3 hours trying multiple different things OTHER then removing the parenthesis, these little things make me feel like a moron.

 

[MajP]

Thank you it works perfectly now. I spent like 3 hours trying multiple different things OTHER then removing the parenthesis, these little things make me feel like a moron.

I would not feel that way. This is very sloppily designed in VBA and designed to cause confusion. There are some subtleties there that would confuse even very seasoned VBA coders.

Private Sub Command1_Click()
  Dim X As Integer
  X = 5
  testFunc (X)
  Debug.Print X
  testFunc X
  Debug.Print X
End Sub

Public Function testFunc(ByRef thevalue)
  thevalue = thevalue * 10
End Function

Both ways run without problem. The debug.print prints
5
50

I bet If I asked most Access VIPs on this site why it runs either way and why I get those results, they could not explain it. Then it gets even more confusing if you add in the legacy "Call" keyword. You have to read that article a few times.

 

[Gasman]

I am going to guess that the () forces ByVal ?

 

[MajP]

I am going to guess that the () forces ByVal ?

Yep.

Rarely ever seen, but

Private Sub Command1_Click()
  Dim X As Integer
  X = 5

  'testFunc (X, "Test") will not compile
  testFunc (X), "Test"
  Debug.Print X
End Sub

Public Function testFunc(ByRef thevalue, ByRef SomeString As String)
  thevalue = thevalue * 10
End Function

The first will not even compile, but second does.