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:
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
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
- Download: xlf-nontrad-range-to-static-array.xlsm [173 KB ]
- Development platform: Microsoft® Excel® for Microsoft 365 MSO (Version 2411 Build 16.0.18217.20000) 64-bit
- Published: 28th October 2024
- Revised: Friday 1st of November 2024 - 05:33 PM, Pacific Time (PT)