# 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.

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.

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



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
2. Lines 14 to 17: - the missing dates scenario - use IsMissing to test switch_dates
3. Line 20: - the range scenario - use TypeName(switch_dates) = "Range" to test switch_dates as a range object
4. Line 30: - the array constant scenario - no explicit testing in this version

5. 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
6. Lines 31 to 34: remove the leading and trailing braces ("{","}") from switch_dates and assign to Dates (figure 2), a variable of type String
7. 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)
8. 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 form DteS(1)(0), DteS(1)(1), DteS(2)(1), … , DteS(3)(1).
9. 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))


1. Question: why is SwDate of type Variant and not a Date type?
SwDate was not declared in the procedure declaration section
1. Normally a dynamic array is declared with a Dim statement (to reserve the name)
2. Then given a dimension using the ReDim statement. ReDim does not allow a change of Type
3. Steps 1 and 2 are the correct procedure
4. 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)
1. 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