Why is my created function returning an error?

Question

I have created a function which has 4 parameters: SearchDate, StartDate, EndDate, Events. The way I wanted the function to work was if the SearchDate is >= for some start date and =< for some end date then the function pulls the events name. For example, if the search was June 17 and the start/end date was June 15/June 18 then it would pull the event.

However the code doesn't seem to work; when I try to use it gives me a value error. I have posted the code and a table, that the function is based on, below.

Function Calendar_Events(SearchDate As Date, StartColumn As Range, EndColumn As Range, EventsColumn As Range)

Dim x As Long
Dim output As Range
For x = 1 To StartColumn.Cells.CountLarge
    If Int(StartColumn.Cells(x)) <= SearchDate And Int(EndColumn.Cells(x)) >= SearchDate Then
       'in place for the case of more events then rows 
       If y >= 3 Then
           output = output & "........"
           Exit For
       End If
       output = output & Left(EventsColumn.Cells(x), 20) & vbNewLine
       y = y + 1
    End If
Next x

End Function

Table:

Start Date  End Date    Event

1/12/2018   1/19/2018   Software Sale
1/31/2018   1/31/2018   Dinner Party
2/1/2018    2/1/2018    Baby Shower
2/12/2018   2/16/2018   Team Retreat
2/15/2018   2/16/2018   Bank Meetings
2/15/2018   2/15/2018   Lunch Date
2/15/2018   2/15/2018   Dinner Date
3/26/2018   3/29/2018   Vacation
3/28/2018   3/29/2018   Swimming
3/28/2018   3/28/2018   Mountain Biking
3/29/2018   3/29/2018   Put away clothes
3/29/2018   4/4/2018    Cottage 
4/2/2018    4/2/2018    Family Photo
4/2/2018    4/4/2018    Software Sale
4/2/2018    4/6/2018    Hire Nanny
4/6/2018    4/6/2018    Day Off

Your function isn't returning anything. I'm not sure if that's the root cause, but it's probably not helping. Put a breakpoint on the last statement (End Function) and see if that line ever hits. — David Zemens
Why is StartDate a Range of multiple cells? If nothing else, that's a bad name for that variable, because it's not properly descriptive. Further, you should provide some example data so that others can more easily assist you. This is known as a minimal reproducible example and is part of How to Ask a good question — David Zemens
You are correct I should change startdate/enddate to startdates/enddates as it would more make it descriptive. I will also provide the table the function is supposed to be based off if that helps make things more clear. — wildesbare
I'd even suggest something like StartColumn and EndColumn or StartRange, etc., rather than StartDates, etc. — David Zemens
You need Calendar_Events = output to return the value. To debug your function, call it from a Sub, not from a worksheet cell. — Tim Williams

JNevill JNevill · Accepted Answer · 2018-07-17T17:42:17

1. In order to return a value from a Function you must set the Function name equal to what you want to return.

So at the end of your code you need:

Calendar_Events = output

So it knows to return the output variable you've been building.

2. Furthermore your output variable should be String. You are not collecting Ranges here, but rather the values inside of cells that match your criteria, so:

Dim Output As String

3. Also, there is no need to convert the cell values containing dates to integers. You are comparing dates to dates and that is good to go without converting. so:

If StartColumn.Cells(x).Value <= SearchDate And EndColumn.Cells(x).Value >= SearchDate Then

I've also added .value to the end of the Cell() reference. It will default to the .value property of the cell, but I'm a big fan of explicit coding instead of just hoping the compiler will know which property you meant.

4. Lastly (and optionally) you should declare the TYPE of the return from the function in the function definition. so:

Function Calendar_Events(SearchDate As Date, StartColumn As Range, EndColumn As Range, EventsColumn As Range) As String

All of this together:

Function Calendar_Events(SearchDate As Date, StartColumn As Range, EndColumn As Range, EventsColumn As Range) As String

    Dim x As Long
    Dim output As String
    For x = 1 To StartColumn.Cells.CountLarge
        Debug.Print StartColumn.Cells(x).Value, EndColumn.Cells(x).Value
        If StartColumn.Cells(x).Value <= SearchDate And EndColumn.Cells(x).Value >= SearchDate Then
           'in place for the case of more events then rows
           If y >= 3 Then
               output = output & "........"
               Exit For
           End If
           output = output & Left(EventsColumn.Cells(x), 20) & vbNewLine
           y = y + 1
        End If
    Next x
    Calendar_Events = output
End Function

Why is my created function returning an error?

1 Answers