Public Function Called When it shouldn't be

[talt0612]

Hi- I have a UDF in one of my workbooks that is called when it shouldn't be. I don't think it is causing any errors (yet), but it is def slowing down my macro. Any thoughts are appreciated. Here is the UDF:

Public Function CFwd(dtEnd As Date)
'-- Return the last working day of the current month
   Dim Searching As Boolean
   Searching = True
   CFwd = DateSerial(Year(dtEnd), Month(dtEnd) + 1, 0)   '-- Start at the last day of the month
   Do While Searching
      If Weekday(CFwd, vbMonday) > 5 Then
         '-- Weekend day, back up a day
         CFwd = CFwd - 1
      Else
         '-- If you have a Holiday Table then enable the next IF...Else
'         If Weekday(CFwd, vbMonday) > 5 Or _
            Not IsNull(DLookup("[HoliDate]", "tblHolidays", _
                               "[HoliDate] = " & Format(CFwd, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) Then
            '-- The above Format of CFwd works with US or UK dates!
'            CFwd = CFwd - 1
'         Else
            '-- The search is over
            Searching = False
         End If
      Loop
End Function

It is hard to pinpoint when/why the UDF is running, but here is an example:

  LR = Cells(Rows.Count, "R").End(xlUp).Row
        ''clear zeros
            For i = LR To 1 Step -1
            If Cells(i, "R").Value = 0 Then
           [COLOR=yellow] Range(Cells(i, "Q"), Cells(i, "T")).Delete shift:=xlUp
[/COLOR]            End If
            Next i

When I hit the yellow text, I jump in to the UDF. anybody else seen this before?

 

[shg]

I expect the workbook is recalculating in response to deleting the cells. You could turn calculation off in the procedure to avoid that.

Why are you using a UDF to calculate the last workday of the current month?

=WORKDAY(EOMONTH(TODAY(), 0)+1, -1, tblHolidays)

 

[jasonb75]

The cell deletion is causing recalculation of formula that are using the UDF.

Have you set calculation to manual while the code executes?

 

[talt0612]

Thanks very much for your responses Shg and jasonb75. The calculation was not set to manual while the code executed. I've set it to manual and that indeed solves the problem.

Shg: I inherited this worksheet and the UDFs were already set up (I'm guessing that either the creator was unaware of the the workday formula, or the sheet was created in an earlier version of excel).

I do know that the "last workday" UDF is used in several spots across the workbook and that it was set up to accomodate any date (i.e. a date other than today).

Maybe they just didn't want to continually type =workday(eomonth...)?

 

[shg]

and that it was set up to accomodate any date

Replace TODAY() with a reference to any other date.

The problem with using a UDF like that is that Excel sees no dependency on values (tblHoliday in this case) that are not passed as arguments. It's bad coding practice.

 

[talt0612]

The problem with using a UDF like that is that Excel sees no dependency on values (tblHoliday in this case) that are not passed as arguments. It's bad coding practice.

Completely understand. Thanks very much for the assist.