Compile Error: Expected Function or Variable in excel

Question

I have a function that is supposed to take a time, ex, 05:00:00 and check it with the current time (and an optional another time), and if the time in the argument has already passed, it's supposed to return the same time, but the next day.

For example, if tm = 05:00:00 and now = 2018-08-14 12:00:00, then the function should return 2018-08-15 05:00:00, but if the time hasn't passed, ex. tm = 19:00 i.e., now < tm, then it should return 2018-08-14 19:00.

My function is (in a separate module):

'Day Adjustment
Sub DayAdjust(tm As Double, Optional tm2 As Double = 0)
    If tm2 = 0 Then
        tm2 = DayAdjust(tm)
    End If

    If (Date + tm) < Now() Or (Date + tm) < tm2 Then
        tm = (Date + 1 + tm)
    End If
    MsgBox tm
    DayAdjust = tm
End Sub

The code that calls the function is:

Dim schStart, schEnd As Double
schStart = Range("cdh_schStart").Value
schStartTime = DayAdjust(CDbl(schStart))
schEnd = Range("cdh_schEnd").Value
schEndTime = DayAdjust(schEnd, schStart)

The cell cdh_schStart contains the value 05:00:00, and cdh_schEnd has 08:00:00 with the present system time set to 2018-08-14 18:30:00.

On the 3rd line, schStartTime = DayAdjust(CDbl(schStart)), I get the error:

Compile Error:

Expected Function or Variable

I need some help figuring out why this happens and how to fix it.

It's because you defined DayAdjust as a Sub not a Function. — Brian M Stafford
– Brian M Stafford, Commented Aug 14, 2018 at 13:08
I think you are likely to end up with an infinite loop due to that first section of code if you don't pass tm2 or pass 0 by mistake. — Rory
– Rory, Commented Aug 14, 2018 at 13:12

QHarr · Accepted Answer · 2018-08-14 14:03:11Z

2

You are attempting an assignment with schStartTime = DayAdjust(CDbl(schStart)) so your sub needs to be written as a function. Indeed, you have a return value assignment in your sub but I guess you typo'd or didn't realise it must be declared as a function .

Good point raised by @Rory re potential for infinite loop with tm2 if passed as zero or is not supplied, and defaults to zero, as the function will be called recursively without a breakout. You will need to find an appropriate way to handle this.

Public Function DayAdjust(tm As Double, Optional tm2 As Double = 0) As Variant
    If tm2 = 0 Then
        tm2 = DayAdjust(tm)
    End If

    If (Date + tm) < Now() Or (Date + tm) < tm2 Then
        tm = (Date + 1 + tm)
    End If
    MsgBox tm
    DayAdjust = tm
End Function

edited Aug 14, 2018 at 14:03

answered Aug 14, 2018 at 13:07

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

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

Collectives™ on Stack Overflow

Compile Error: Expected Function or Variable in excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related