VBA- How to search date in array of dates coming from excel

Question

I am trying to write a simple function in VBA which takes as input a date and an array of dates both coming from Excel. It then returns true if the given date is part of the array or false otherwise.

My problem is that arrays coming from excel are 2 dimensional but I always pass in a 1 dimensional array. In other words, a column and a row value so I can check values in my array but I pass in a 1 dimensional array.

Here is my code:

Function IsInArray(ByVal MyDate As Date, ByRef Holiday_Calendar As Range) As Boolean
Dim length As Integer
length = WorksheetFunction.Count(Holiday_Calendar)
Dim counter As Integer
'counter = 0

For counter = 0 To length
If Holiday_Calendar(counter) = MyDate Then
IsInArray = True
End If
Next counter

IsInArray = False
End Function

my function returns False all the time because Holiday_Calendar(counter) = MyDate is always False because Holiday_Calendar(counter) needs a row and a column value for some reason — JustANoob
– JustANoob, Commented Aug 4, 2015 at 15:32
So Holiday_Calendar is one dimensional, but some other array is two dimensional? Try If Holiday_Calendar(counter,1) = MyDate Then — BruceWayne
– BruceWayne, Commented Aug 4, 2015 at 15:38
Your function always returns false because you put IsInArray = False at the end of your function. (; — Tom K.
– Tom K., Commented Aug 4, 2015 at 15:44
My test case is calling the function in excel and MyDate is 8/7/2015 and Holiday_Calendar is 8/3/2015, 8/7/2015, 8/10/2015, 8/13/2015, 8/14/2015. I am still getting false weather I use Holiday_Calendar(counter) or Holiday_Calendar(counter, 1) or Holiday_Calendar(1, counter) in the if statement — JustANoob
– JustANoob, Commented Aug 4, 2015 at 15:44
Oh ok Tom but shouldn't my function hit IsInArray = True first? (since condition is true the second time) — JustANoob
– JustANoob, Commented Aug 4, 2015 at 15:48

Soulfire · Accepted Answer · 2015-08-04 15:48:50Z

I've made some tweaks and changes to your code in order to help you out.

First, you can convert a Range object, which is normally two-dimensional, into a one-dimensional array by using the WorksheetFunction.Transpose(myRange) method where myRange is the range you want to convert to one dimension.

I did that in the Call statement, so it looks something like this:

Option Explicit

Private Sub DateArrays()

        Dim rngToPass As Range
        Dim dtMyDate As Date
        Dim containsDt As Boolean

        Set rngToPass = Sheet1.Range("A1:A4")
        dtMyDate = "8/7/2015"

        containsDt = IsInArray(dtMyDate, Application.WorksheetFunction.Transpose(rngToPass))

End Sub

This will pass in a one-dimensional array made from rngToPass.

I did have to modify your function slightly:

Function IsInArray(ByVal MyDate As Date, ByRef Holiday_Calendar As Variant) As Boolean
        Dim length As Integer
        length = WorksheetFunction.Count(Holiday_Calendar)
        Dim counter As Integer

        For counter = 1 To length
                If Holiday_Calendar(counter) = MyDate Then
                        IsInArray = True
                        Exit Function
                End If
        Next counter

        IsInArray = False
End Function

I noticed that your counter variable started at 0, but the transposed array index begins at 1 so I changed the 0 to 1.

I also noticed that when the condition matches, the function briefly sets IsInArray to true but execution would continue and thus IsInArray would get reset to false at the end of the function, and so was always returning false. I've included an Exit Function statement to break out of the function if we find a match.

This should get you started at least.

Thank you so much Joshua!!! The Exit Function command made it work. I come from C/Java and someone told me IsInArray = True is the equivalent of return True. Clearly that is not accurate because in C/Java, the function ends when it hits a return statement.

Collectives™ on Stack Overflow

VBA- How to search date in array of dates coming from excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related