Undefined function but no MISSING REFERENCES

wrightyrx7

Well-known Member
Joined
Sep 15, 2011
Messages
994
Hi all,

Looked online and cannot find anything to correct this.

When the Database is opened by a new user or i send out an updated front-end to current users an error message appears saying: -

-Undefined function 'weekdays' in expression

This is referring to my function that calculates working days which i use in a couple of my queries. I found this function online and it seems to work but i cannot get rid of the error when opening.

Hope someone can help.

Many thanks
Chris

Code:
Public Function Weekdays(ByRef startDate As Date, ByRef endDate As Date) As Integer
    ' Returns the number of weekdays in the period from startDate
    ' to endDate inclusive. Returns -1 if an error occurs.
    ' If your weekend days do not include Saturday and Sunday and
    ' do not total two per week in number, this function will
    ' require modification.
    On Error GoTo Weekdays_Error
    
    ' The number of weekend days per week.
    Const ncNumberOfWeekendDays As Integer = 2
    
    ' The number of days inclusive.
    Dim varDays As Variant
    
    ' The number of weekend days.
    Dim varWeekendDays As Variant
    
    ' Temporary storage for datetime.
    Dim dtmX As Date
    
    ' If the end date is earlier, swap the dates.
    If endDate < startDate Then
        dtmX = startDate
        startDate = endDate
        endDate = dtmX
    End If
    
    ' Calculate the number of days inclusive (+ 1 is to add back startDate).
    varDays = DateDiff(Interval:="d", _
        date1:=startDate, _
        date2:=endDate) + 1
    
    ' Calculate the number of weekend days.
    varWeekendDays = (DateDiff(Interval:="ww", _
        date1:=startDate, _
        date2:=endDate) _
        * ncNumberOfWeekendDays) _
        + IIf(DatePart(Interval:="w", _
        Date:=startDate) = vbSunday, 1, 0) _
        + IIf(DatePart(Interval:="w", _
        Date:=endDate) = vbSaturday, 1, 0)
    
    ' Calculate the number of weekdays.
    Weekdays = (varDays - varWeekendDays)
    
Weekdays_Exit:
    Exit Function
    
Weekdays_Error:
    Weekdays = -1
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
        vbCritical, "Weekdays"
    Resume Weekdays_Exit
End Function
 
Last edited:

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

wrightyrx7

Well-known Member
Joined
Sep 15, 2011
Messages
994
maybe I'm a tad blind but I'm not seeing
' DateValue returns the date part only.
startDate = DateValue(startDate)
endDate = DateValue(endDate)

from
https://msdn.microsoft.com/en-us/library/dd327646(v=office.12).aspx

maybe its struggling to initialise without a date ?


Hi Mole,

Many thanks for your promp reply.

However im not sure what you mean (im sorry), as you can see I have just got the code from the link you provided and it seems to work when I place it in my query. Im just not sure why i get the error.

Thanks again
Chris
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
You defined StartDate as a Date which is fine, but where in the code tells StartDate which date it actually is, if you can trigger the formula then hover over the values, maybe it will show you what is being used, if it returns "" then you don't have a value for the function to manipulate
 

wrightyrx7

Well-known Member
Joined
Sep 15, 2011
Messages
994

ADVERTISEMENT

You defined StartDate as a Date which is fine, but where in the code tells StartDate which date it actually is, if you can trigger the formula then hover over the values, maybe it will show you what is being used, if it returns "" then you don't have a value for the function to manipulate

I stepped through the code when it started running and it is picking up the Start and End date fine :/ i have manually calculated some of the results its given and they all are correct.

SO im confised why its giving me the error message when its working :(
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
I don't have any other current ideas
 

james_lankford

Well-known Member
Joined
Jan 11, 2009
Messages
1,180
...

... or i send out an updated front-end to current users an error message appears saying: -

-Undefined function 'weekdays' in expression

...

so this function is in a module somewhere

and when you sent out the new front end to the user you're actually 100% positive you included the module in the new front end ?

just because you have doesn't mean they do
 

wrightyrx7

Well-known Member
Joined
Sep 15, 2011
Messages
994
so this function is in a module somewhere

and when you sent out the new front end to the user you're actually 100% positive you included the module in the new front end ?

just because you have doesn't mean they do

Hi James,

I think I found the problem. The function runs in a query when the database opens, however the query cannot run until it has data.


On the first occasion when someone opens the database it asks them to "enable content". Once this has been done the database is fine for the user... until i send out a new front-end, they just have to enable content again.


Chris
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
does that mean it didn't have a date to work the function with ?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,873
Messages
5,627,399
Members
416,245
Latest member
Xterminat

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