xlf EandA series
UDF named YDate
Code development version. This document provides some assignment solution ideas, presented here as an exercise.
0. Preliminary
The EXERCISE - implement the following
Part A Function procedures
Insert a Code Module and name it Module1. All code for part A is written in this module.
A ii. Write a Public (UDF) Function procedure named YDate with syntax:
YDate(asx_date,[switch_dates])
The YDate function returns the YAHOO date, adjusted for seasonal time adjustments, corresponding to the ASX trading day date.
The YDate function has the following arguments:
- asx_date Required. A date that represents an ASX trading date
- switch_dates Optional. An optional list of two or more dates to align the ASX trading dates with the YAHOO data dates. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates
A iv. Include one or more simple test procedures.
1. Tasks
This is a code development version - that describes a series of tasks with suggested coding solutions. Note: The switch_dates in this version are not the same as those required for the implementation of the function in a practical setting. Like wise the test dates may not be actual ASX business days.
1.1 Task A ii
Knowledge requirements to complete this exercise:
- WS User Defined Functions, including arguments, their declaration, and type
- Function with Optional arguments
- Passing arguments to a WS UDF
- WS array constant
- Format and specifications of an array constant
- VBA Split function
- Variant array
- Array of variants
- VBA data type conversion techniques
1.2 Notes
User Defined Function (UDF) is a function procedure written in an ordinary VBA code module
A function is a procedure that:
- Takes a number of inputs - called arguments
- Does some sort of process - formula, decision rule, etc
- Returns an answer - called the return value
- This is all that a function should do
Important: the switch_dates are an argument to the function, and must not be hard coded within the procedure
Array constant - this is the more challenging part of the exercise, and would either be missed by many session participants, or incorrectly used as justification for hard coded switch_dates in the procedure
Discussion: the YDate UDF could be categorised as a member of the Date & Time group. It performs a conditional switching routine involving date comparisons. The workday type functions provide some guidance for the "array constant of the serial numbers" aspect of this exercise
The NETWORKDAYS function has syntax: NETWORKDAYS(start_date, end_date, [holidays]) where holidays is optional. "An optional range of one or more dates … [that] … can be either a range of cells that contains the dates or an array constant of the serial numbers that represent the dates"
The WORKDAY function has syntax: WORKDAY(start_date, days, [holidays]) where holidays is optional. "An optional range of one or more dates … [that] … can be either a range of cells that contains the dates or an array constant of the serial numbers that represent the dates"
1.3 The switch_dates concept
Pseudodata switch_dates are shown in figure 1. The formatted dates, columns E and F, and date serial numbers in columns H and I, are used below in the code 2 test routine.

1.4 A version of the YDate function
This version of the answer is based on techniques covered in 90045. It began with a version based on the "range of cells" requirement. The "array constant of the serial number" version was developed independently in section 2, code 3, then added to code 1 after operational testing with the procedures in code 2.
Code 1: the
YDate
function interpretation of task i
Function YDate(asx_date As Date, Optional switch_dates As Variant) As Date Dim Dates As String Dim Dte As Variant ' a Variant array Dim DteS() As Variant ' a dynamic array of Variants Dim DteD() As Date ' a dynamic array of Dates Dim NoRows As Integer Dim i As Integer ' switch_dates can be: ' 1. missing, or ' 2. a range, or ' 3. an array constant ' no switch dates If IsMissing(switch_dates) Then YDate = asx_date Exit Function End If ' test for range of dates If TypeName(switch_dates) = "Range" Then NoRows = switch_dates.Rows.Count For i = 1 To NoRows If switch_dates(i, 1) <= asx_date And asx_date <= switch_dates(i, 2) Then YDate = asx_date - 1 Exit For Else YDate = asx_date End If Next i Else ' switch_dates must be an array constant of date serial numbers Dates = switch_dates ' Remove leading and trailing braces {} Dates = Replace(Dates, "{", "") Dates = Replace(Dates, "}", "") ' Write each row of Dates to an element of the SwDte array Dte = Split(Dates, ";") ReDim DteS(1 To UBound(Dte) + 1) ReDim SwDte(1 To UBound(Dte) + 1, 0 To 1) For i = 1 To UBound(DteS) DteS(i) = Split(Dte(i - 1), ",") ' Convert serial numbers to VBA dates SwDte(i, 0) = CDate(DteS(i)(0)) SwDte(i, 1) = CDate(DteS(i)(1)) Next i For i = 1 To UBound(SwDte, 1) If SwDte(i, 0) <= asx_date And asx_date <= SwDte(i, 1) Then YDate = asx_date - 1 Exit For Else YDate = asx_date End If Next i End If End Function
About Code 1
- Line 1: function procedure declaration - Public function with 1. Compulsory argument, a VBA date named asx_date. 2. Optional argument named switch_dates as Variant. A Variant has no default value and is essential to the operation of the IsMissing statement in line 1
- Lines 14 to 17: - the missing dates scenario - use IsMissing to test switch_dates
- Line 20: - the range scenario - use TypeName(switch_dates) = "Range" to test switch_dates as a range object
- Line 30: - the array constant scenario - no explicit testing in this version
- Line 31: Using the code 3 test procedure, the statement in line 75 calls YDate with the parameter values:
Ans = YDate(VBA.DateSerial(2018, 1, 1), "{42644,42826;43009,43191;43374,43556}")
The switch_dates array (figure 2) has value"{42644,42826;43009,43191;43374,43556}"
with sub type String. This value is assigned in code 1 line 1. The next task is to convert this assign the elements of this (3 x 2) delimited string to a 3 x 2 array - Lines 31 to 34: remove the leading and trailing braces ("{","}") from switch_dates and assign to Dates (figure 2), a variable of type String
- Line 36: extract each row of the array constant string, delimited by the ";" character
Dte = Split(Dates, ";")
and write to elements of the Dte variant array. Dte has 3 elements (base 0), each assigned a comma delimited string. This method avoids having to redimension a dynamic array. Dte has sub type String(0 to 2) - Line 37 and Line 40: dimension the DteS dynamic array and extract each element of the array constant string, delimited by the "," character
ReDim DteS(1 To UBound(Dte) + 1)
DteS(i) = Split(Dte(i - 1), ",")
and write to elements of the DteS array of variants. Dte has 3 elements (base 0), each assigned a comma delimited string. DteS (figure 2) is of type Variant indexed (1 to 3) because of the ReDim statement at line 37
Each element of DteS contains an array of sub type String(0 to 1) of the formDteS(1)(0), DteS(1)(1), DteS(2)(1), … , DteS(3)(1)
. - Lines 42 to 43: write the DteS array of variants to the SwDte array and convert the serial numbers to explicit type Date. CDate is a VBA function with syntax CDate(Expression) As Date. Assign each return value from CDate to an element of SwDate (figure 2, Scroll to View), an array of sub-type Date
SwDte(i, 0) = CDate(DteS(i)(0)) SwDte(i, 1) = CDate(DteS(i)(1))

- Question: why is SwDate of type Variant and not a Date type?
- Answer:
-
SwDate was not declared in the procedure declaration section
- Normally a dynamic array is declared with a Dim statement (to reserve the name)
- Then given a dimension using the ReDim statement. ReDim does not allow a change of Type
- Steps 1 and 2 are the correct procedure
- Another practical technique is to add the Type as the first ReDim statement (in the case where the original declaration is missing)
ReDim SwDte(1 To UBound(Dte) + 1, 0 To 1) As Date
This provides a practical work around (see figure 3)

- In summary, the YDdate procedure in code 1 is presented as assignment solution ideas. Its structure can be improved in areas such as the switch_dates range and the SwDate array
If switch_dates(i, 1) ≤ asx_date And asx_date ≤ switch_dates(i, 2) Then If SwDte(i, 0) ≤ asx_date And asx_date ≤ SwDte(i, 1) Then
The discussion concludes at this point, and forms a seed module for future development.
1.5 Test procedures
Code 2: the
TestYdate
procedures
Sub TestYdate1() Dim Ans As Date Ans = YDate(VBA.DateSerial(2018, 1, 1)) Ans = YDate(VBA.DateSerial(2018, 1, 1), Range("SwitchD")) Ans = YDate(VBA.DateSerial(2018, 7, 1)) Ans = YDate(VBA.DateSerial(2018, 7, 1), Range("SwitchD")) End Sub ' =========================== Sub TestYdate2() Dim Ans As Date Ans = YDate(VBA.DateSerial(2018, 1, 1)) Ans = YDate(VBA.DateSerial(2018, 1, 1), "{42644,42826;43009,43191;43374,43556}") Ans = YDate(VBA.DateSerial(2018, 7, 1)) Ans = YDate(VBA.DateSerial(2018, 7, 1), "{42644,42826;43009,43191;43374,43556}") End Sub
2. Code development
As stated in section 1, this module is a code development version, so a number of points are available for demonstration.
2.1 The dates as an array constant of serial numbers
Code 3: the
TestArrayConstant1
macro with an array constant of the serial numbers that represent the dates
Sub TestArrayConstant1() ' Dates as serial numbers Dim Data As String Dim Dte As Variant ' a Variant array Dim DteS() As Variant ' a dynamic array of Variants (strings in this example) Dim DteD() As Date ' a dynamic array of Dates Dim i As Integer ' loop counter ' Test data - array constant with dimension 3 x 2 Data = "{42644,42826;43009,43191;43374,43556}" ' Development statistics - not needed in final version ' NoComma = Len(Data) - Len(Replace(Data, ",", "")) ' NoSemiColon = Len(Data) - Len(Replace(Data, ";", "")) ' NoRows = NoSemiColon + 1 ' Remove leading and trailing braces {} Data = Replace(Data, "{", "") Data = Replace(Data, "}", "") ' Write each row of Data to an element of the Dte array Dte = Split(Data, ";") ReDim DteS(1 To UBound(Dte) + 1) ReDim DteD(1 To UBound(Dte) + 1, 0 To 1) For i = 1 To UBound(DteS) DteS(i) = Split(Dte(i - 1), ",") DteD(i, 0) = CDate(DteS(i)(0)) DteD(i, 1) = CDate(DteS(i)(1)) Next i Stop End Sub
2.2 The dates as an array constant of strings
Not required in the context of the assigned tasks. Note: a string of strings requires an escape sequence for the double quotes. For example, "1-Oct-2016" is written as """1-Oct-2016"" in line 129. Another technique is the Chr(34) in line 144.
Code 4:
TestArrayConstant2
macro with an array constant of strings that represent the dates
Sub TestArrayConstant2() ' Dates as strings Dim Data As String Dim Dte As Variant ' a Variant array Dim DteS() As Variant ' a dynamic array of Variants Dim DteD() As Date ' a dynamic array of Dates Dim i As Integer, j As Integer ' loop counter ' Test data - array constant with dimension 3 x 2 Data = "{""1-Oct-2016"",""1-Apr-2017"";""1-Oct-2017"",""1-Apr-2018"";""1-Oct-2018"",""1-Apr-2019""}" ' Remove leading and trailing braces {} Data = Replace(Data, "{", "") Data = Replace(Data, "}", "") ' Write each row of Data to an element of the Dte array (with base zero) Dte = Split(Data, ";") ReDim DteS(1 To UBound(Dte) + 1) ReDim DteD(1 To UBound(Dte) + 1, 0 To 1) For i = 1 To UBound(DteS) DteS(i) = Split(Dte(i - 1), ",") For j = 0 To 1 ' DteS is an array of arrays (strings). Write to DteD, an array or dates DteD(i, j) = CDate(Replace(DteS(i)(j), Chr(34), "")) Next j Next i Stop End Sub
References
- ExcelAtFinance, User Defined Functions. [Accessed 4 November 2018]
- ExcelAtFinance, VBA input box method (2). [Accessed 4 November 2018]
- ExcelAtFinance, WS array constant. [Accessed 4 November 2018]
- ExcelAtFinance, xlfSplit :: procedures - VBA code. [Accessed 4 November 2018]
- Download the Excel file for this module: xlf-ydate-v2.xlsm [35 KB]
- Development platform: Excel 2016 (64 bit) Office 365 ProPlus (Version 1810 Build 11001.20074 Click-to-Run) and VBA 7.1
- Revised: Saturday 25th of February 2023 - 09:38 AM, [Australian Eastern Time (AET)]