Quick guide


0. Write a WS range to VBA static array


The non-trading day data for the Australian Stock Exchange (ASX non-trading days) is shown in figure 1:

TD
Fig 1: List of ASX non trading days - 2010 to 2024 inclusive. Nine columns wide including the Queen Elizabeth II :: National Day of Mourning

1. the VBA code


1.1 Write a Worksheet range to a text version of VBA static array


Write the field values from figure 1 to WS range Target shown in figure 2.



Code 1: Sub WriteVBAstaticArray constructs a text version of the static array in the Worksheet 
Sub WriteVBAstaticArray()
Dim noRows As Integer, noCols As Integer
Dim Source As Range, Target As Range
Dim i As Integer, j As Integer, k As Integer  ' i: Rows, j: Cols, k: Rows (Resettable)
Dim m As Integer    ' Write sequence column offset
Dim tmpYr As Integer, tmpMth As Integer, tmpDay As Integer
Dim tmpString As String

Set Source = Range("NonTrade")
Set Target = Range("M22")

With Source
    noRows = .Rows.Count
    noCols = .Columns.Count
End With

k = 0
For i = 1 To noRows
    For j = 1 To noCols
        '' Read WS range ======
        With Source(i, j)
            tmpYr = Year(.Value)
            tmpMth = Month(.Value)
            tmpDay = Day(.Value)
        End With
        '' Construct string ======
        tmpString = "NT(" & i & "," & j & ") = " & "#" & tmpMth & "/" & tmpDay & "/" & tmpYr & "#"
            If j <= 3 Then
                tmpString = tmpString & ":"

                '' Write elements 1 to 3 ======
                Target(i + k, j).Value = tmpString

                '' Write element 4 (end of row, no :)
            ElseIf j = 4 Then
                tmpString = tmpString
                Target(i + k, j).Value = tmpString

                '' Write elements 5+ to second last ======
            ElseIf j >= 5 And j < noCols Then
                If j = 5 Then
                    k = k + 1
                    m = -4
                End If
                tmpString = tmpString & ":"
                Target(i + k, j + m).Value = tmpString

                '' Write last element =====
            ElseIf j = noCols Then
                tmpString = tmpString
                Target(i + k, j + m).Value = tmpString
            End If

    Next j
Next i

Target.CurrentRegion.Copy   '' Copy to Clipboard, then SWitch to VBE and Paste (Ctrl + V)
End Sub

'

1.2 Code 1 outout


output
Fig 2: Target values for WriteVBAstaticArray procedure Colons ":" represented hardcode end-of-statement operators

Code 1, line 57 copies the Array text to the Windows ClipBoard. Code 2a contains a shell to Paste the ClipBoard contents.



Code 2a: Sub PasteStaticArrayDemo as PlaceHolder for Paste operation. 
Sub PasteStaticArrayDemo()
Dim NT(1 To 15, 1 To 9) As Date

' Paste clipboard here =====


End Sub

The completed Paste operation is shown in code 2b.



Code 2b: Sub PasteStaticArrayDemo after the Paste from ClipBoard operation 
Sub PasteStaticArrayDemo()
Dim NT(1 To 15, 1 To 9) As Date

' Paste clipboard here =====
NT(1, 1) = #1/1/2010#:  NT(1, 2) = #1/26/2010#: NT(1, 3) = #4/2/2010#:  NT(1, 4) = #4/5/2010#
NT(1, 5) = #4/26/2010#: NT(1, 6) = #6/14/2010#: NT(1, 7) = #12:00:00 AM#: NT(1, 8) = #12/27/2010#: NT(1, 9) = #12/28/2010#
NT(2, 1) = #1/3/2011#:  NT(2, 2) = #1/26/2011#: NT(2, 3) = #4/22/2011#: NT(2, 4) = #4/25/2011#
NT(2, 5) = #4/26/2011#: NT(2, 6) = #6/13/2011#: NT(2, 7) = #12:00:00 AM#: NT(2, 8) = #12/26/2011#: NT(2, 9) = #12/27/2011#
NT(3, 1) = #1/2/2012#:  NT(3, 2) = #1/26/2012#: NT(3, 3) = #4/6/2012#:  NT(3, 4) = #4/9/2012#
NT(3, 5) = #4/25/2012#: NT(3, 6) = #6/11/2012#: NT(3, 7) = #12:00:00 AM#: NT(3, 8) = #12/25/2012#: NT(3, 9) = #12/26/2012#
NT(4, 1) = #1/1/2013#:  NT(4, 2) = #1/28/2013#: NT(4, 3) = #3/29/2013#: NT(4, 4) = #4/1/2013#
NT(4, 5) = #4/25/2013#: NT(4, 6) = #6/10/2013#: NT(4, 7) = #12:00:00 AM#: NT(4, 8) = #12/25/2013#: NT(4, 9) = #12/26/2013#
NT(5, 1) = #1/1/2014#:  NT(5, 2) = #1/27/2014#: NT(5, 3) = #4/18/2014#: NT(5, 4) = #4/21/2014#
NT(5, 5) = #4/25/2014#: NT(5, 6) = #6/9/2014#:  NT(5, 7) = #12:00:00 AM#: NT(5, 8) = #12/25/2014#: NT(5, 9) = #12/26/2014#
NT(6, 1) = #1/1/2015#:  NT(6, 2) = #1/26/2015#: NT(6, 3) = #4/3/2015#:  NT(6, 4) = #4/6/2015#
NT(6, 5) = #4/25/2015#: NT(6, 6) = #6/8/2015#:  NT(6, 7) = #12:00:00 AM#: NT(6, 8) = #12/25/2015#: NT(6, 9) = #12/28/2015#
NT(7, 1) = #1/1/2016#:  NT(7, 2) = #1/26/2016#: NT(7, 3) = #3/25/2016#: NT(7, 4) = #3/28/2016#
NT(7, 5) = #4/25/2016#: NT(7, 6) = #6/13/2016#: NT(7, 7) = #12:00:00 AM#: NT(7, 8) = #12/26/2016#: NT(7, 9) = #12/27/2016#
NT(8, 1) = #1/2/2017#:  NT(8, 2) = #1/26/2017#: NT(8, 3) = #4/14/2017#: NT(8, 4) = #4/17/2017#
NT(8, 5) = #4/25/2017#: NT(8, 6) = #6/12/2017#: NT(8, 7) = #12:00:00 AM#: NT(8, 8) = #12/25/2017#: NT(8, 9) = #12/26/2017#
NT(9, 1) = #1/1/2018#:  NT(9, 2) = #1/26/2018#: NT(9, 3) = #3/30/2018#: NT(9, 4) = #4/2/2018#
NT(9, 5) = #4/25/2018#: NT(9, 6) = #6/11/2018#: NT(9, 7) = #12:00:00 AM#: NT(9, 8) = #12/25/2018#: NT(9, 9) = #12/26/2018#
NT(10, 1) = #1/1/2019#: NT(10, 2) = #1/28/2019#: NT(10, 3) = #4/19/2019#: NT(10, 4) = #4/22/2019#
NT(10, 5) = #4/25/2019#: NT(10, 6) = #6/10/2019#: NT(10, 7) = #12:00:00 AM#: NT(10, 8) = #12/25/2019#:  NT(10, 9) = #12/26/2019#
NT(11, 1) = #1/1/2020#: NT(11, 2) = #1/27/2020#: NT(11, 3) = #4/10/2020#: NT(11, 4) = #4/13/2020#
NT(11, 5) = #4/25/2020#: NT(11, 6) = #6/8/2020#: NT(11, 7) = #12:00:00 AM#: NT(11, 8) = #12/25/2020#:   NT(11, 9) = #12/28/2020#
NT(12, 1) = #1/1/2021#: NT(12, 2) = #1/26/2021#: NT(12, 3) = #4/2/2021#: NT(12, 4) = #4/5/2021#
NT(12, 5) = #4/25/2021#: NT(12, 6) = #6/14/2021#: NT(12, 7) = #12:00:00 AM#: NT(12, 8) = #12/27/2021#:  NT(12, 9) = #12/28/2021#
NT(13, 1) = #1/3/2022#: NT(13, 2) = #1/26/2022#: NT(13, 3) = #4/15/2022#: NT(13, 4) = #4/18/2022#
NT(13, 5) = #4/25/2022#: NT(13, 6) = #6/13/2022#: NT(13, 7) = #9/22/2022#: NT(13, 8) = #12/27/2022#: NT(13, 9) = #12/26/2022#
NT(14, 1) = #1/2/2023#: NT(14, 2) = #1/26/2023#: NT(14, 3) = #4/7/2023#: NT(14, 4) = #4/10/2023#
NT(14, 5) = #4/25/2023#: NT(14, 6) = #6/12/2023#: NT(14, 7) = #12:00:00 AM#: NT(14, 8) = #12/25/2023#:  NT(14, 9) = #12/26/2023#
NT(15, 1) = #1/1/2024#: NT(15, 2) = #1/26/2024#: NT(15, 3) = #3/29/2024#: NT(15, 4) = #4/1/2024#
NT(15, 5) = #4/25/2024#: NT(15, 6) = #6/10/2024#: NT(15, 7) = #12:00:00 AM#: NT(15, 8) = #12/25/2024#:  NT(15, 9) = #12/26/2024#

MsgBox NT(15, 5)

End Sub

The #12:00:00 AM# values resulting from the blank cells in the Source data can be ignored.



1.3 ASX non trading 2020 to 2025


Extra code, not included in the xlsm file.



Code 2b: Sub PasteStaticArrayDemo after the Paste from ClipBoard operation 
Sub ASX20to25NTarray()
' Declare a static array for 9 holidays per year
Dim NT(2020 To 2025, 1 To 9) As Date

' Assign dates (using VBA date literal - enclosed within number signs (#) when using the "Date" data type)
NT(2020, 1) = #1/1/2020#: NT(2020, 2) = #1/27/2020#: NT(2020, 3) = #4/10/2020#: NT(2020, 4) = #4/13/2020#
NT(2020, 5) = #4/25/2020#: NT(2020, 6) = #6/8/2020#: NT(2020, 7) = #12:00:00 AM#: NT(2020, 8) = #12/25/2020#:   NT(2020, 9) = #12/28/2020#
NT(2021, 1) = #1/1/2021#: NT(2021, 2) = #1/26/2021#: NT(2021, 3) = #4/2/2021#: NT(2021, 4) = #4/5/2021#
NT(2021, 5) = #4/25/2021#: NT(2021, 6) = #6/14/2021#: NT(2021, 7) = #12:00:00 AM#: NT(2021, 8) = #12/27/2021#:  NT(2021, 9) = #12/28/2021#
NT(2022, 1) = #1/3/2022#: NT(2022, 2) = #1/26/2022#: NT(2022, 3) = #4/15/2022#: NT(2022, 4) = #4/18/2022#
NT(2022, 5) = #4/25/2022#: NT(2022, 6) = #6/13/2022#: NT(2022, 7) = #9/22/2022#: NT(2022, 8) = #12/27/2022#: NT(2022, 9) = #12/26/2022#
NT(2023, 1) = #1/2/2023#: NT(2023, 2) = #1/26/2023#: NT(2023, 3) = #4/7/2023#: NT(2023, 4) = #4/10/2023#
NT(2023, 5) = #4/25/2023#: NT(2023, 6) = #6/12/2023#: NT(2023, 7) = #12:00:00 AM#: NT(2023, 8) = #12/25/2023#:  NT(2023, 9) = #12/26/2023#
NT(2024, 1) = #1/1/2024#: NT(2024, 2) = #1/26/2024#: NT(2024, 3) = #3/29/2024#: NT(2024, 4) = #4/1/2024#
NT(2024, 5) = #4/25/2024#: NT(2024, 6) = #6/10/2024#: NT(2024, 7) = #12:00:00 AM#: NT(2024, 8) = #12/25/2024#:  NT(2024, 9) = #12/26/2024#
NT(2025, 1) = #1/1/2025#: NT(2025, 2) = #1/27/2025#: NT(2025, 3) = #4/18/2025#: NT(2025, 4) = #4/21/2025#
NT(2025, 5) = #4/25/2025#: NT(2024, 6) = #6/9/2025#: NT(2025, 7) = #12:00:00 AM#: NT(2025, 8) = #12/25/2025#:  NT(2025, 9) = #12/26/2025#
' MsgBox NT(2024, 5)    ' expect 25-Apr-2024

End Sub