Workday function with read from excel array code

[Ting_92]

I tried using the function belows
1. Function to help convert range to array from an exisiting file.
2. Function to include workday1 with the exceptions of holidays (as per covert rangetoarray)

The output i get when i use workday1 function is #VALUE .

Any help will be greatly appreciated.

---------------------



Public Function ConvertRangesToArray() As Variant


Dim ws2 As Worksheet
Dim rng2 As Range
Dim arrHolidays2 As Variant
Dim rngCell2 As Range
Dim colHolidays2 As New Collection


Dim wb As Workbook
Set wb = Workbooks.Open("C:\Desktop\Holiday.xlxs")

Set ws2 = wb.Sheets("HOLIDAY")
Set rng2 = wb.Sheets("HOLIDAY").Range("F2:F20")

'loops through secondn range and adds values to collection
For Each rngCell2 In rng2
colHolidays2.Add rngCell2.Value2
Next


'gets values in arrHolidays from the colHolidays
arrHolidays2 = ConvertToArray(colHolidays2)

End Function




Function ConvertToArray(colHolidays As Collection)


'converts collection to array
Dim arrTemp() As Variant
ReDim arrTemp(1 To colHolidays.Count) As Variant

For i = 1 To colHolidays.Count
arrTemp(i) = colHolidays(i)
Next

ConvertToArray = arrTemp

End Function




Function workday1(reqdate As Date)


Dim workday2P3 As Date
Dim workday4WSat As Date
Dim arrDates As Variant
Dim arrDates2 As Variant


'arrDates for 2P3 (exclude all sat exception of working sat, exclude public holidays)
'arrDates2P3 = Array("1/1/2019", "5/2/2019", "7/3/2019", "3/4/2019", "19/4/2019", "1/5/2019", "19/5/2019", "30/5/2019", "1/6/2019", "5/6/2019", "6/6/2019", "11/8/2019", "17/8/2019", "1/9/2019", "9/11/2019", "25/12/2019", "3/6/2019", "4/6/2019", "31/12/2019", "5/1/2019", "12/1/2019", "19/1/2019", "26/1/2019", "2/2/2019", "9/2/2019", "16/2/2019", "23/2/2019", "2/3/2019", "9/3/2019", "16/3/2019", "23/3/2019", "30/3/2019")

Call ConvertRangesToArray

'arrDates2 = Array("1/1/2019", "3/25/2019", "3/28/2019")


workday2 = WorksheetFunction.workday(reqdate, -5, arrHolidays2)


workday3 = WorksheetFunction.WorkDay_Intl(workday2P3, -4, 11, arrHolidays2)

workday1 = workday3



End Function


' Note that arrHoliday2 doesnt want to be fed in as a function, but more of as an auto

 

[bobsan42]

this is not the way I would do this and really - why bother making a function which duplicates a built-in one?
- I wouldn't open a file just to get a small array of values and then leave it opened.
- for your code to work arrHolidays2 must be declared as module level variable
- workday3 will return error because workday2P3 has no value (maybe you meant it to be workday2 ?)

Probably more remarks can be made ...
Basically your function boils down to a two-liner:

arrHolidays2 = ConvertRangesToArray
workday1 = WorksheetFunction.WorkDay_Intl(WorksheetFunction.WorkDay(reqdate, -5, arrHolidays2), -4, 11, arrHolidays2)