convert function to a sub routine excel vba

Question

I have a function like below.I need to convert it to a subroutine.Or is there any way to call this function from inside a subroutine?

'function to calculate nth specific last day of a month like  last saturday or last monday   

Function LastDayOfMonth(Which_Day As String, Which_Date As String) As Date

    Dim i As Integer
    Dim iDay As Integer
    Dim iDaysInMonth As Integer
    Dim FullDateNew As Date

    Which_Date = CDate(Which_Date)

    Select Case UCase(Which_Day)
        Case "SUN"
            iDay = 1
        Case "MON"
            iDay = 2
        Case "TUE"
            iDay = 3
        Case "WED"
            iDay = 4
        Case "THU"
            iDay = 5
        Case "FRI"
            iDay = 6
        Case "SAT"
            iDay = 7
    End Select

    iDaysInMonth = Day(DateAdd("d", -1,DateSerial(Year(Which_Date),Month(Which_Date)+ 1, 1))) 
    FullDateNew = DateSerial(Year(Which_Date), Month(Which_Date), iDaysInMonth)

    For i = 0 To iDaysInMonth
        If Weekday(FullDateNew - i) = iDay Then
           LastDayOfMonth = FullDateNew - i
          Exit For
       End If
    Next i
End Function

You could just replace "Function" with "Sub", but I have a feeling that there's more to your question... — Joe
– Joe, Commented Oct 16, 2013 at 16:43
Yes you can do it both ways. But keeping it as a function and calling it form a subroutine would be good. Inside the subroutine, simply say Ret = LastDayOfMonth(param1,parm2) — Siddharth Rout
– Siddharth Rout, Commented Oct 16, 2013 at 16:43

Tim Williams · Accepted Answer · 2013-10-16 16:43:32Z

1

sub callit()
    Dim d as Date
    d = LastDayOfMonth(...)
end sub

answered Oct 16, 2013 at 16:43

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Siddharth Rout Over a year ago

+ 1 Yeah! I beat you this time :p

Collectives™ on Stack Overflow

convert function to a sub routine excel vba

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related