Function - How do I set it up so I can run it from a Sub with straight VBA code?

[flynavy]

I've written a lot of VBA code in the last 2 years (Excel 2010), and have a recurring need within one Sub that I think would be best served by "calling" a Function from the Sub. I've been unsuccessful in setting the function up. There must be a trick I'm unaware of. It seems so simple. I've Googled it until my fingers bleed. When I create a VBA program, I simply go to the "Developer" tab on the Excel ribbon, then to "Macros", name the VBA code, insert the code, save it and I'm good to go. But again, not sure how/where to put the Function code. Could I get some help?

 

[Joe4]

You can use ALT-F11 to get into the VB Editor (a keyboard shortcut option).
Once in there, you can insert a new Module in your workbook, and then just put any VBA code (including Functions) there.

 

[flynavy]

You can use ALT-F11 to get into the VB Editor (a keyboard shortcut option).
Once in there, you can insert a new Module in your workbook, and then just put any VBA code (including Functions) there.

Thank you Joe. That is exactly what I did. I must have a coding or compile issue. Would you mind if I throw the code back at you (and the error message I'm getting) for a quick look? The code is very short. I borrowed it. All I'm trying to do is to get the workdays between two dates. When I run the Macro you see below, I get a Compile error Argument not optional, and the "CWD" in the sub is highlited.

Function CWD(myDate As Date) As Long    Dim StartDate As Date, EndDate As Date
    StartDate = myDate - Day(myDate) + 1
    EndDate = DateSerial(Year(myDate), Month(myDate) + 1, 0)
    CWD = DateDiff("d", StartDate, EndDate) - DateDiff("ww", StartDate, EndDate) * 2 - (Weekday(EndDate) <> 7) + (Weekday(StartDate) = 1)
End Function


Sub CallerSub()
myDate = 4 / 3 / 17
'  StartDate = 2 / 16 / 17
Cells(r1, c1) = CWD
'  MsgBox CWD
End Sub

 

[Joe4]

CWD function is like any other Excel function. It has a required argument, the Date to feed into it. You are not feeding any date.
It would be like:

CWD(some date here)

So if your myDate variable is date, you would use:

CWD(myDate)

Also note. In your code, you seem to be referencing r1 and c1, but you have not defined them anywhere.
Unless they are some global variables you set elsewhere, that will cause an error.

 

[flynavy]

Made the change. Same error. I'm sure it is code - maybe variable type. By the way, the Cells(r1,c1) code line... I was simply hoping to stick the date in row 1 col 1 of the spreadsheet containing the macro code. Here is the updated code...

Function CWD(myDate As Date) As Long    Dim StartDate As Date, EndDate As Date
    StartDate = myDate - Day(myDate) + 1
    EndDate = DateSerial(Year(myDate), Month(myDate) + 1, 0)
    CWD = DateDiff("d", StartDate, EndDate) - DateDiff("ww", StartDate, EndDate) * 2 - (Weekday(EndDate) <> 7) + (Weekday(StartDate) = 1)
End Function


Sub CallerSub()
myDate = 4 / 3 / 17
CWD (myDate)
'  StartDate = 2 / 16 / 17
'  Cells(r1, c1) = CWD
MsgBox CWD
End Sub

 

[Joe4]

OK. A few things to understand. CWD is a function, not a variable. Think of other Excel functions, like LEFT. You wouldn't use:

MsgBox Left

that doesn't make any sense, because there aren't any arguments. Left of what?
User Defined Functions work the same way as native Functions.

Also, this line:

myDate = 4 / 3 / 17

says take 4, divide by 3, then divide by 17. It does not return the date 4/3/17.

And since you have not defined r1 or c1 anywhere in your code, they are both zeroes, which will return errors.

Try rewriting your whole procedure like this:

Sub CallerSub()
    Dim myDate As Date
    myDate = DateValue("4/3/17")
    Cells(1, 1) = CWD(myDate)
End Sub