CODEWITHSUNDEEP
arraysvbaexceldate
JustANoob
JustANoob

Reputation: 177

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

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

Upvotes: 1

Views: 3365

Answers (1)

Soulfire
Soulfire

Reputation: 4296

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.

Upvotes: 1

Related Questions