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

flynavy

New Member
Joined
Mar 18, 2015
Messages
43
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?
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,655
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

flynavy

New Member
Joined
Mar 18, 2015
Messages
43
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.

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
'  StartDate = 2 / 16 / 17
Cells(r1, c1) = CWD
'  MsgBox CWD
End Sub
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,655
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
CWD(some date here)
So if your myDate variable is date, you would use:
Code:
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

New Member
Joined
Mar 18, 2015
Messages
43
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...
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,655
Office Version
  1. 365
Platform
  1. Windows
OK. A few things to understand. CWD is a function, not a variable. Think of other Excel functions, like LEFT. You wouldn't use:
Code:
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:
Code:
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:
Code:
Sub CallerSub()
    Dim myDate As Date
    myDate = DateValue("4/3/17")
    Cells(1, 1) = CWD(myDate)
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,660
Messages
5,626,152
Members
416,165
Latest member
hamburger138

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top