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:


1.2 Notes

xlf tip 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


xlf tip 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.

xlf-ydate-switch-dates
Fig 1: Constructing switch_dates pseudodata - any date in the On - Off range will trigger the adjustment routine [Scroll to view]

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

  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))
        

xlf-switch-date-arrays
Fig 2: A Locals Window view - of VBA arrays used in the WS array constant scenario [Scroll to view]
  1. Question: why is SwDate of type Variant and not a Date type?
  2. Answer:
      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)
xlf-switch-date-arrays-swdate
Fig 3: A Locals Window view - of the SwDte array with type date added with a ReDim statement
  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

References