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

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

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.

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

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)

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

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

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.

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

Copyright © 2011 – 2023 ♦ Dr Ian O'Connor, CPA.   |   Privacy policy

Comment & Reply policy