VBA Function in Excel Returning VALUE

Question

I've plenty long excel formula like this

IF(ISNUMBER(E6),IF(E6<standard_hour,0,IF(((E6-standard_hour)*24*60)>90,2,CEILING(((E6-standard_hour)*24*60)/30*0.5,0.5))),VLOOKUP(E6,Refer,2,FALSE))

Because I use this formula a lot in spreadsheet, I decide to make a custom function for it. This is the function

Function morning_check(start_hour)
    Dim sheet As Worksheet
    Set sheet = ActiveWorkbook.Sheets("Setup")

    If WorksheetFunction.IsNumber(start_hour) Then
        If start_hour < sheet.Range("E1").Value Then
            morning_check = 0
        Else
            If ((start_hour - sheet.Range("E1").Value) * 24 * 60 > 90) Then
                morning_check = 2
            Else
                morning_check = Application.WorksheetFunction.Ceiling(((start_hour - sheet.Range("E1")) * 24 * 60) / 30 * 0.5, 0.5)
            End If
        End If
    Else
        morning_check = Application.WorksheetFunction.VLookup(start_hour, sheet.Range("Refer"), 2, False)
    End If

End Function

The input of this function could be string (example : "TS") or time (example : 07:00)

Using String as Input, this function work correctly, but when I using time it just throw #Value!

Which line is the source of the error? Excel should highlight it if you select Debug. — Joe DeRose
– Joe DeRose, Commented Jul 11, 2014 at 14:52

Joe DeRose · Accepted Answer · 2014-07-11 15:20:24Z

1

Your error is coming from the following lines:

Set standard_hour = TimeValue(sheet.Cells("E1"))
Set user_hour = TimeValue(start_hour)

Set in VBA is used for object creation, while you are simply trying to set a variable. This is why you're getting an "Object Required" error.

Just drop the word Set and you should be able to get on with your debugging.

As a matter of investigation (if your copy of Excel behaved as mine did), Excel highlighted the first line in yellow (which was not helpful), but it also automatically selected the text standard_hour (which identified the location on the problem).

answered Jul 11, 2014 at 15:20

Joe DeRose

3,5883 gold badges30 silver badges35 bronze badges

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

2 Comments

l1th1um Over a year ago

Hi, I edit my code, and now it don't throw the error again, but it still not working for non string (first branch of IF)

l1th1um Over a year ago

Just ignore, everything work fine now, At the last step I forgot to format my Cell :D. Anyway, thank you for the explanation. This is my first time using VBA

Collectives™ on Stack Overflow

VBA Function in Excel Returning VALUE

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related