Compile error about multiline initializtion of date array

**craigvan888** · 09-29-2024, 06:25 PM

Hello, can anyone help me out? I want to init one big date array that could not use loop. And it has multilines. The compile failed.

The code looks like below:

Dim holidays As Variant
    Dim i As Long
holidays = Array(#1/1/2024#, #2/9/2024#, #2/12/2024#, #2/13/2024#, #2/14/2024#, #2/15/2024#, #2/16/2024#, #4/4/2024#, #4/5/2024#, #5/1/2024#, #5/2/2024#, #5/3/2024#, #6/10/2024#, #9/16/2024#, #9/17/2024#, #10/1/2024#, #10/2/2024#, #10/3/2024#, #10/4/2024#, #10/7/2024#, # _
1/1/2013#, #1/2/2013#, #1/3/2013#, #2/11/2013#, #2/12/2013#, #2/13/2013#, #2/14/2013#, #2/15/2013#, #4/4/2013#, #4/5/2013#, #4/29/2013#, #4/30/2013#, #5/1/2013#, #6/10/2013#, #6/11/2013#, #6/12/2013#, #9/19/2013#, #9/20/2013#, #10/1/2013#, #10/2/2013#, #10/3/2013#, #10/4/2013#, #10/7/2013#, # _
1/1/2014#, #1/31/2014#, #2/3/2014#, #2/4/2014#, #2/5/2014#, #2/6/2014#, #4/7/2014#, #5/1/2014#, #5/2/2014#, #6/2/2014#, #9/8/2014#, #10/1/2014#, #10/2/2014#, #10/3/2014#, #10/6/2014#, #10/7/2014#, # _
1/1/2015#, #1/2/2015#, #2/18/2015#, #2/19/2015#, #2/20/2015#, #2/23/2015#, #2/24/2015#, #4/6/2015#, #5/1/2015#, #6/22/2015#, #10/1/2015#, #10/2/2015#, #10/5/2015#, #10/6/2015#, #10/7/2015#, # _
1/1/2016#, #2/8/2016#, #2/9/2016#, #2/10/2016#, #2/11/2016#, #2/12/2016#, #4/4/2016#, #5/2/2016#, #6/9/2016#, #6/10/2016#, #9/15/2016#, #9/16/2016#, #10/3/2016#, #10/4/2016#, #10/5/2016#, #10/6/2016#, #10/7/2016#, # _
1/2/2017#, #1/27/2017#, #1/30/2017#, #1/31/2017#, #2/1/2017#, #2/2/2017#, #4/3/2017#, #4/4/2017#, #5/1/2017#, #5/29/2017#, #5/30/2017#, #10/2/2017#, #10/3/2017#, #10/4/2017#, #10/5/2017#, #10/6/2017#, # _
1/1/2018#, #2/15/2018#, #2/16/2018#, #2/19/2018#, #2/20/2018#, #2/21/2018#, #4/5/2018#, #4/6/2018#, #4/30/2018#, #5/1/2018#, #6/18/2018#, #9/24/2018#, #10/1/2018#, #10/2/2018#, #10/3/2018#, #10/4/2018#, #10/5/2018#, #12/31/2018#, # _
1/1/2019#, #2/4/2019#, #2/5/2019#, #2/6/2019#, #2/7/2019#, #2/8/2019#, #4/5/2019#, #5/1/2019#, #5/2/2019#, #5/3/2019#, #6/7/2019#, #9/13/2019#, #10/1/2019#, #10/2/2019#, #10/3/2019#, #10/4/2019#, #10/7/2019#, # _
1/1/2020#, #1/17/2020#, #1/27/2020#, #1/28/2020#, #1/29/2020#, #1/30/2020#, #4/6/2020#, #5/1/2020#, #5/4/2020#, #5/5/2020#, #6/25/2020#, #6/26/2020#, #10/1/2020#, #10/2/2020#, #10/5/2020#, #10/6/2020#, #10/7/2020#, #10/8/2020#, # _
1/1/2021#, #2/11/2021#, #2/12/2021#, #2/15/2021#, #2/16/2021#, #2/17/2021#, #4/5/2021#, #5/3/2021#, #5/4/2021#, #5/5/2021#, #6/14/2021#, #9/20/2021#, #9/21/2021#, #10/1/2021#, #10/4/2021#, #10/5/2021#, #10/6/2021#, #10/7/2021#, # _
1/3/2022#, #1/31/2022#, #2/1/2022#, #2/2/2022#, #2/3/2022#, #2/4/2022#, #4/4/2022#, #4/5/2022#, #5/2/2022#, #5/3/2022#, #5/4/2022#, #6/3/2022#, #9/12/2022#, #10/3/2022#, #10/4/2022#, #10/5/2022#, #10/6/2022#, #10/7/2022#, # _
1/2/2023#, #1/23/2023#, #1/24/2023#, #1/25/2023#, #1/26/2023#, #1/27/2023#, #4/5/2023#, #5/1/2023#, #5/2/2023#, #5/3/2023#, #6/22/2023#, #6/23/2023#, #9/29/2023#, #10/2/2023#, #10/3/2023#, #10/4/2023#, #10/5/2023#, #10/6/2023#)

Thanks
Craig

**Dave** · 09-29-2024, 06:45 PM

Maybe...

Dim holidays () As Variant

Dave
edit: Craig please review the forum guidelines
Compile error about multiline initializtion of date array (excelforum.com)

**arnelgp** · 09-29-2024, 06:56 PM

maybe enter you holidays to a range and create the array from the range, example:

' the holiday date are entered on sheet1 range a2 to a50
Dim holidays As Variant
holidays = Range("a2:a50")

**Aflatoon** · 09-30-2024, 03:06 AM

Cross-posted:
https://www.excelforum.com/excel-pro...ate-array.html
https://chandoo.org/forum/threads/co...te-array.57771

**Aussiebear** · 09-30-2024, 05:16 AM

Shame about the crossposting. Yet another person who can't be bothered to read the rules on the forums. I believe arrays are limited to 60 units, and this is well over that.

**p45cal** · 09-30-2024, 07:16 AM

As you've discovered it's difficult if not possible without looping. The single line of code would be too long for the vbe.
So why not looping? It's as fast or faster than manipulating multiple arrays.
Try something like:

Dim holidays As Variant
Dim i As Long
x = Array(#1/1/2024#, #2/9/2024#, #2/12/2024#, #2/13/2024#, #2/14/2024#, #2/15/2024#, #2/16/2024#, #4/4/2024#, #4/5/2024#, #5/1/2024#, #5/2/2024#, #5/3/2024#, #6/10/2024#, #9/16/2024#, #9/17/2024#, #10/1/2024#, #10/2/2024#, #10/3/2024#, #10/4/2024#, #10/7/2024#, #1/1/2013#, #1/2/2013#, #1/3/2013#, #2/11/2013#, #2/12/2013#, #2/13/2013#, #2/14/2013#, #2/15/2013#, #4/4/2013#, #4/5/2013#, #4/29/2013#, #4/30/2013#, #5/1/2013#, #6/10/2013#, #6/11/2013#, #6/12/2013#, #9/19/2013#, #9/20/2013#, #10/1/2013#, #10/2/2013#, #10/3/2013#, #10/4/2013#, #10/7/2013#, #1/1/2014#, #1/31/2014#, #2/3/2014#, #2/4/2014#, #2/5/2014#, #2/6/2014#, #4/7/2014#, #5/1/2014#, #5/2/2014#, #6/2/2014#, #9/8/2014#, #10/1/2014#, #10/2/2014#, #10/3/2014#, #10/6/2014#, #10/7/2014#, #1/1/2015#, #1/2/2015#, #2/18/2015#, #2/19/2015#, #2/20/2015#, #2/23/2015#, #2/24/2015#, #4/6/2015#, #5/1/2015#, #6/22/2015#, #10/1/2015#, #10/2/2015#, #10/5/2015#, #10/6/2015#, #10/7/2015#, #1/1/2016#, #2/8/2016#, #2/9/2016#)
y = Array(#2/10/2016#, #2/11/2016#, #2/12/2016#, #4/4/2016#, #5/2/2016#, #6/9/2016#, #6/10/2016#, #9/15/2016#, #9/16/2016#, #10/3/2016#, #10/4/2016#, #10/5/2016#, #10/6/2016#, #10/7/2016#, #1/2/2017#, #1/27/2017#, #1/30/2017#, #1/31/2017#, #2/1/2017#, #2/2/2017#, #4/3/2017#, #4/4/2017#, #5/1/2017#, #5/29/2017#, #5/30/2017#, #10/2/2017#, #10/3/2017#, #10/4/2017#, #10/5/2017#, #10/6/2017#, #1/1/2018#, #2/15/2018#, #2/16/2018#, #2/19/2018#, #2/20/2018#, #2/21/2018#, #4/5/2018#, #4/6/2018#, #4/30/2018#, #5/1/2018#, #6/18/2018#, #9/24/2018#, #10/1/2018#, #10/2/2018#, #10/3/2018#, #10/4/2018#, #10/5/2018#, #12/31/2018#, #1/1/2019#, #2/4/2019#, #2/5/2019#, #2/6/2019#, #2/7/2019#, #2/8/2019#, #4/5/2019#, #5/1/2019#, #5/2/2019#, #5/3/2019#, #6/7/2019#, #9/13/2019#, #10/1/2019#, #10/2/2019#, #10/3/2019#, #10/4/2019#, #10/7/2019#, #1/1/2020#, #1/17/2020#, #1/27/2020#, #1/28/2020#, #1/29/2020#, #1/30/2020#, #4/6/2020#, #5/1/2020#, #5/4/2020#, #5/5/2020#, #6/25/2020#, #6/26/2020#)
Z = Array(#10/1/2020#, #10/2/2020#, #10/5/2020#, #10/6/2020#, #10/7/2020#, #10/8/2020#, #1/1/2021#, #2/11/2021#, #2/12/2021#, #2/15/2021#, #2/16/2021#, #2/17/2021#, #4/5/2021#, #5/3/2021#, #5/4/2021#, #5/5/2021#, #6/14/2021#, #9/20/2021#, #9/21/2021#, #10/1/2021#, #10/4/2021#, #10/5/2021#, #10/6/2021#, #10/7/2021#, #1/3/2022#, #1/31/2022#, #2/1/2022#, #2/2/2022#, #2/3/2022#, #2/4/2022#, #4/4/2022#, #4/5/2022#, #5/2/2022#, #5/3/2022#, #5/4/2022#, #6/3/2022#, #9/12/2022#, #10/3/2022#, #10/4/2022#, #10/5/2022#, #10/6/2022#, #10/7/2022#, #1/2/2023#, #1/23/2023#, #1/24/2023#, #1/25/2023#, #1/26/2023#, #1/27/2023#, #4/5/2023#, #5/1/2023#, #5/2/2023#, #5/3/2023#, #6/22/2023#, #6/23/2023#, #9/29/2023#, #10/2/2023#, #10/3/2023#, #10/4/2023#, #10/5/2023#, #10/6/2023#)

ReDim holidays(1 To UBound(x) + UBound(y) + UBound(Z) + 3)
idx = 0
For Each arry In Array(x, y, Z)
  For Each ary In arry
    idx = idx + 1
    holidays(idx) = ary
  Next ary
Next arry

If you're putting this array on the sheet DON'T do this to put it into a column:

Range("A1").Resize(UBound(holidays)) = Application.Transpose(holidays)

Application.Transpose will convert dates to strings then leave it to the sheet to re-interpret strings to dates. If you're not in a month/day/year locale version of Excel it'll interpret wrongly.

Instead set up the holidays array vertically from the start by changing this bit to:

ReDim holidays(1 To UBound(x) + UBound(y) + UBound(Z) + 3, 1 To 1)
idx = 0
For Each arry In Array(x, y, Z)
  For Each ary In arry
    idx = idx + 1
    holidays(idx, 1) = ary
  Next ary
Next arry

then this will put the holidays array in column A:

Range("A1").Resize(UBound(holidays)) = holidays

**Paul_Hossler** · 10-01-2024, 01:45 PM

What are you ultimately trying to do? Check to see if an arbitrary date is a holiday?

**GTO** · 10-02-2024, 04:12 AM

Greetings Craig,

As others have suggested, please do take the time to read about cross-posting.

In the meantime, I noticed this...

#10/4/2024#, #10/7/2024#, # _

...at the end of the physical lines.

So I "fixed" it like this...

holidays = Array(#1/1/2024#, #2/9/2024#, #2/12/2024#, #2/13/2024#, #2/14/2024#, #2/15/2024#, #2/16/2024#, #4/4/2024#, #4/5/2024#, #5/1/2024#, #5/2/2024#, #5/3/2024#, #6/10/2024#, #9/16/2024#, #9/17/2024#, #10/1/2024#, #10/2/2024#, #10/3/2024#, #10/4/2024#, #10/7/2024#, _#1/1/2013#, #1/2/2013#, #1/3/2013#, #2/11/2013#, #2/12/2013#, #2/13/2013#, #2/14/2013#, #2/15/2013#, #4/4/2013#, #4/5/2013#, #4/29/2013#, #4/30/2013#, #5/1/2013#, #6/10/2013#, #6/11/2013#, #6/12/2013#, #9/19/2013#, #9/20/2013#, #10/1/2013#, #10/2/2013#, #10/3/2013#, #10/4/2013#, #10/7/2013#, _
#1/1/2014#, #1/31/2014#, #2/3/2014#, #2/4/2014#, #2/5/2014#, #2/6/2014#, #4/7/2014#, #5/1/2014#, #5/2/2014#, #6/2/2014#, #9/8/2014#, #10/1/2014#, #10/2/2014#, #10/3/2014#, #10/6/2014#, #10/7/2014#, _
#1/1/2015#, #1/2/2015#, #2/18/2015#, #2/19/2015#, #2/20/2015#, #2/23/2015#, #2/24/2015#, #4/6/2015#, #5/1/2015#, #6/22/2015#, #10/1/2015#, #10/2/2015#, #10/5/2015#, #10/6/2015#, #10/7/2015#, _
#1/1/2016#, #2/8/2016#, #2/9/2016#, #2/10/2016#, #2/11/2016#, #2/12/2016#, #4/4/2016#, #5/2/2016#, #6/9/2016#, #6/10/2016#, #9/15/2016#, #9/16/2016#, #10/3/2016#, #10/4/2016#, #10/5/2016#, #10/6/2016#, #10/7/2016#, _
#1/2/2017#, #1/27/2017#, #1/30/2017#, #1/31/2017#, #2/1/2017#, #2/2/2017#, #4/3/2017#, #4/4/2017#, #5/1/2017#, #5/29/2017#, #5/30/2017#, #10/2/2017#, #10/3/2017#, #10/4/2017#, #10/5/2017#, #10/6/2017#, _
#1/1/2018#, #2/15/2018#, #2/16/2018#, #2/19/2018#, #2/20/2018#, #2/21/2018#, #4/5/2018#, #4/6/2018#, #4/30/2018#, #5/1/2018#, #6/18/2018#, #9/24/2018#, #10/1/2018#, #10/2/2018#, #10/3/2018#, #10/4/2018#, #10/5/2018#, #12/31/2018#, _
#1/1/2019#, #2/4/2019#, #2/5/2019#, #2/6/2019#, #2/7/2019#, #2/8/2019#, #4/5/2019#, #5/1/2019#, #5/2/2019#, #5/3/2019#, #6/7/2019#, #9/13/2019#, #10/1/2019#, #10/2/2019#, #10/3/2019#, #10/4/2019#, #10/7/2019#, _
#1/1/2020#, #1/17/2020#, #1/27/2020#, #1/28/2020#, #1/29/2020#, #1/30/2020#, #4/6/2020#, #5/1/2020#, #5/4/2020#, #5/5/2020#, #6/25/2020#, #6/26/2020#, #10/1/2020#, #10/2/2020#, #10/5/2020#, #10/6/2020#, #10/7/2020#, #10/8/2020#, _
#1/1/2021#, #2/11/2021#, #2/12/2021#, #2/15/2021#, #2/16/2021#, #2/17/2021#, #4/5/2021#, #5/3/2021#, #5/4/2021#, #5/5/2021#, #6/14/2021#, #9/20/2021#, #9/21/2021#, #10/1/2021#, #10/4/2021#, #10/5/2021#, #10/6/2021#, #10/7/2021#, _
#1/3/2022#, #1/31/2022#, #2/1/2022#, #2/2/2022#, #2/3/2022#, #2/4/2022#, #4/4/2022#, #4/5/2022#, #5/2/2022#, #5/3/2022#, #5/4/2022#, #6/3/2022#, #9/12/2022#, #10/3/2022#, #10/4/2022#, #10/5/2022#, #10/6/2022#, #10/7/2022#, _
#1/2/2023#, #1/23/2023#, #1/24/2023#, #1/25/2023#, #1/26/2023#, #1/27/2023#, #4/5/2023#, #5/1/2023#, #5/2/2023#, #5/3/2023#, #6/22/2023#, #6/23/2023#, #9/29/2023#, #10/2/2023#, #10/3/2023#, #10/4/2023#, #10/5/2023#, #10/6/2023#)

...but as soon I place the cursor someplace else, the VBE changes it to leaving the pound sign (or hash tag if you wish) and moving the date to the next physical line. Very strange and I have no answer as to why that is.

I then broke the lines different, moving the comma:

Sub example()Dim holidays As Variant
Dim i As Long
  
  holidays = Array(#1/1/2024#, #2/9/2024#, #2/12/2024#, #2/13/2024#, #2/14/2024#, #2/15/2024#, #2/16/2024#, #4/4/2024#, #4/5/2024#, #5/1/2024#, #5/2/2024#, #5/3/2024#, #6/10/2024#, #9/16/2024#, #9/17/2024#, #10/1/2024#, #10/2/2024#, #10/3/2024#, #10/4/2024#, #10/7/2024# _
  , #1/1/2013#, #1/2/2013#, #1/3/2013#, #2/11/2013#, #2/12/2013#, #2/13/2013#, #2/14/2013#, #2/15/2013#, #4/4/2013#, #4/5/2013#, #4/29/2013#, #4/30/2013#, #5/1/2013#, #6/10/2013#, #6/11/2013#, #6/12/2013#, #9/19/2013#, #9/20/2013#, #10/1/2013#, #10/2/2013#, #10/3/2013#, #10/4/2013#, #10/7/2013# _
  , #1/1/2014#, #1/31/2014#, #2/3/2014#, #2/4/2014#, #2/5/2014#, #2/6/2014#, #4/7/2014#, #5/1/2014#, #5/2/2014#, #6/2/2014#, #9/8/2014#, #10/1/2014#, #10/2/2014#, #10/3/2014#, #10/6/2014#, #10/7/2014# _
  , #1/1/2015#, #1/2/2015#, #2/18/2015#, #2/19/2015#, #2/20/2015#, #2/23/2015#, #2/24/2015#, #4/6/2015#, #5/1/2015#, #6/22/2015#, #10/1/2015#, #10/2/2015#, #10/5/2015#, #10/6/2015#, #10/7/2015# _
  , #1/1/2016#, #2/8/2016#, #2/9/2016#, #2/10/2016#, #2/11/2016#, #2/12/2016#, #4/4/2016#, #5/2/2016#, #6/9/2016#, #6/10/2016#, #9/15/2016#, #9/16/2016#, #10/3/2016#, #10/4/2016#, #10/5/2016#, #10/6/2016#, #10/7/2016# _
  , #1/2/2017#, #1/27/2017#, #1/30/2017#, #1/31/2017#, #2/1/2017#, #2/2/2017#, #4/3/2017#, #4/4/2017#, #5/1/2017#, #5/29/2017#, #5/30/2017#, #10/2/2017#, #10/3/2017#, #10/4/2017#, #10/5/2017#, #10/6/2017# _
  , #1/1/2018#, #2/15/2018#, #2/16/2018#, #2/19/2018#, #2/20/2018#, #2/21/2018#, #4/5/2018#, #4/6/2018#, #4/30/2018#, #5/1/2018#, #6/18/2018#, #9/24/2018#, #10/1/2018#, #10/2/2018#, #10/3/2018#, #10/4/2018#, #10/5/2018#, #12/31/2018# _
  , #1/1/2019#, #2/4/2019#, #2/5/2019#, #2/6/2019#, #2/7/2019#, #2/8/2019#, #4/5/2019#, #5/1/2019#, #5/2/2019#, #5/3/2019#, #6/7/2019#, #9/13/2019#, #10/1/2019#, #10/2/2019#, #10/3/2019#, #10/4/2019#, #10/7/2019# _
  , #1/1/2020#, #1/17/2020#, #1/27/2020#, #1/28/2020#, #1/29/2020#, #1/30/2020#, #4/6/2020#, #5/1/2020#, #5/4/2020#, #5/5/2020#, #6/25/2020#, #6/26/2020#, #10/1/2020#, #10/2/2020#, #10/5/2020#, #10/6/2020#, #10/7/2020#, #10/8/2020# _
  , #1/1/2021#, #2/11/2021#, #2/12/2021#, #2/15/2021#, #2/16/2021#, #2/17/2021#, #4/5/2021#, #5/3/2021#, #5/4/2021#, #5/5/2021#, #6/14/2021#, #9/20/2021#, #9/21/2021#, #10/1/2021#, #10/4/2021#, #10/5/2021#, #10/6/2021#, #10/7/2021# _
  , #1/3/2022#, #1/31/2022#, #2/1/2022#, #2/2/2022#, #2/3/2022#, #2/4/2022#, #4/4/2022#, #4/5/2022#, #5/2/2022#, #5/3/2022#, #5/4/2022#, #6/3/2022#, #9/12/2022#, #10/3/2022#, #10/4/2022#, #10/5/2022#, #10/6/2022#, #10/7/2022# _
  , #1/2/2023#, #1/23/2023#, #1/24/2023#, #1/25/2023#, #1/26/2023#, #1/27/2023#, #4/5/2023#, #5/1/2023#, #5/2/2023#, #5/3/2023#, #6/22/2023#, #6/23/2023#, #9/29/2023#, #10/2/2023#, #10/3/2023#, #10/4/2023#, #10/5/2023#, #10/6/2023#)
  
End Sub

This seems to work fine. I personally would follow others suggestions as to storing the dates on a sheet and/or looping them into a date array.

Hope that helps,

Mark

**p45cal** · 10-02-2024, 10:43 AM

Originally Posted by GTO

I then broke the lines different, moving the comma:
This seems to work fine.

It does indeed!

**arnelgp** · 10-02-2024, 08:02 PM

This seems to work fine

it will do but very hard to maintain, specially when you
accidentally delete some text on the middle of your array declaration,
very hard to trace.

**GTO** · 10-04-2024, 01:02 AM

@p45cal:

For the confirmation, thank you Pascal.

Reference:

"Application.Transpose will convert dates to strings then leave it to the sheet to re-interpret strings to dates. If you're not in a month/day/year locale version of Excel it'll interpret wrongly."

This seems true in USA locale version (m/d/yyyy) as well. Thank you much for that as well. I would not have expected the conversion to strings.

Mark

**Aussiebear** · 10-04-2024, 04:51 AM

Hey Guys...Craigvan888 seems to lack the courage to respond, and given that he/she is a serial cross poster, can we drop the support for this thread. At not one of the multiple sites where this thread has been crossposted (to date), has he/she/it (who knows what woke pronouns are in place these days) ever apologised for crossposting.

**craigvan888** · 10-07-2024, 07:32 PM

Originally Posted by GTO

Greetings Craig,

As others have suggested, please do take the time to read about cross-posting.

In the meantime, I noticed this...

#10/4/2024#, #10/7/2024#, # _

...at the end of the physical lines.

So I "fixed" it like this...

holidays = Array(#1/1/2024#, #2/9/2024#, #2/12/2024#, #2/13/2024#, #2/14/2024#, #2/15/2024#, #2/16/2024#, #4/4/2024#, #4/5/2024#, #5/1/2024#, #5/2/2024#, #5/3/2024#, #6/10/2024#, #9/16/2024#, #9/17/2024#, #10/1/2024#, #10/2/2024#, #10/3/2024#, #10/4/2024#, #10/7/2024#, _#1/1/2013#, #1/2/2013#, #1/3/2013#, #2/11/2013#, #2/12/2013#, #2/13/2013#, #2/14/2013#, #2/15/2013#, #4/4/2013#, #4/5/2013#, #4/29/2013#, #4/30/2013#, #5/1/2013#, #6/10/2013#, #6/11/2013#, #6/12/2013#, #9/19/2013#, #9/20/2013#, #10/1/2013#, #10/2/2013#, #10/3/2013#, #10/4/2013#, #10/7/2013#, _
#1/1/2014#, #1/31/2014#, #2/3/2014#, #2/4/2014#, #2/5/2014#, #2/6/2014#, #4/7/2014#, #5/1/2014#, #5/2/2014#, #6/2/2014#, #9/8/2014#, #10/1/2014#, #10/2/2014#, #10/3/2014#, #10/6/2014#, #10/7/2014#, _
#1/1/2015#, #1/2/2015#, #2/18/2015#, #2/19/2015#, #2/20/2015#, #2/23/2015#, #2/24/2015#, #4/6/2015#, #5/1/2015#, #6/22/2015#, #10/1/2015#, #10/2/2015#, #10/5/2015#, #10/6/2015#, #10/7/2015#, _
#1/1/2016#, #2/8/2016#, #2/9/2016#, #2/10/2016#, #2/11/2016#, #2/12/2016#, #4/4/2016#, #5/2/2016#, #6/9/2016#, #6/10/2016#, #9/15/2016#, #9/16/2016#, #10/3/2016#, #10/4/2016#, #10/5/2016#, #10/6/2016#, #10/7/2016#, _
#1/2/2017#, #1/27/2017#, #1/30/2017#, #1/31/2017#, #2/1/2017#, #2/2/2017#, #4/3/2017#, #4/4/2017#, #5/1/2017#, #5/29/2017#, #5/30/2017#, #10/2/2017#, #10/3/2017#, #10/4/2017#, #10/5/2017#, #10/6/2017#, _
#1/1/2018#, #2/15/2018#, #2/16/2018#, #2/19/2018#, #2/20/2018#, #2/21/2018#, #4/5/2018#, #4/6/2018#, #4/30/2018#, #5/1/2018#, #6/18/2018#, #9/24/2018#, #10/1/2018#, #10/2/2018#, #10/3/2018#, #10/4/2018#, #10/5/2018#, #12/31/2018#, _
#1/1/2019#, #2/4/2019#, #2/5/2019#, #2/6/2019#, #2/7/2019#, #2/8/2019#, #4/5/2019#, #5/1/2019#, #5/2/2019#, #5/3/2019#, #6/7/2019#, #9/13/2019#, #10/1/2019#, #10/2/2019#, #10/3/2019#, #10/4/2019#, #10/7/2019#, _
#1/1/2020#, #1/17/2020#, #1/27/2020#, #1/28/2020#, #1/29/2020#, #1/30/2020#, #4/6/2020#, #5/1/2020#, #5/4/2020#, #5/5/2020#, #6/25/2020#, #6/26/2020#, #10/1/2020#, #10/2/2020#, #10/5/2020#, #10/6/2020#, #10/7/2020#, #10/8/2020#, _
#1/1/2021#, #2/11/2021#, #2/12/2021#, #2/15/2021#, #2/16/2021#, #2/17/2021#, #4/5/2021#, #5/3/2021#, #5/4/2021#, #5/5/2021#, #6/14/2021#, #9/20/2021#, #9/21/2021#, #10/1/2021#, #10/4/2021#, #10/5/2021#, #10/6/2021#, #10/7/2021#, _
#1/3/2022#, #1/31/2022#, #2/1/2022#, #2/2/2022#, #2/3/2022#, #2/4/2022#, #4/4/2022#, #4/5/2022#, #5/2/2022#, #5/3/2022#, #5/4/2022#, #6/3/2022#, #9/12/2022#, #10/3/2022#, #10/4/2022#, #10/5/2022#, #10/6/2022#, #10/7/2022#, _
#1/2/2023#, #1/23/2023#, #1/24/2023#, #1/25/2023#, #1/26/2023#, #1/27/2023#, #4/5/2023#, #5/1/2023#, #5/2/2023#, #5/3/2023#, #6/22/2023#, #6/23/2023#, #9/29/2023#, #10/2/2023#, #10/3/2023#, #10/4/2023#, #10/5/2023#, #10/6/2023#)

...but as soon I place the cursor someplace else, the VBE changes it to leaving the pound sign (or hash tag if you wish) and moving the date to the next physical line. Very strange and I have no answer as to why that is.

I then broke the lines different, moving the comma:

Sub example()Dim holidays As Variant
Dim i As Long
  
  holidays = Array(#1/1/2024#, #2/9/2024#, #2/12/2024#, #2/13/2024#, #2/14/2024#, #2/15/2024#, #2/16/2024#, #4/4/2024#, #4/5/2024#, #5/1/2024#, #5/2/2024#, #5/3/2024#, #6/10/2024#, #9/16/2024#, #9/17/2024#, #10/1/2024#, #10/2/2024#, #10/3/2024#, #10/4/2024#, #10/7/2024# _
  , #1/1/2013#, #1/2/2013#, #1/3/2013#, #2/11/2013#, #2/12/2013#, #2/13/2013#, #2/14/2013#, #2/15/2013#, #4/4/2013#, #4/5/2013#, #4/29/2013#, #4/30/2013#, #5/1/2013#, #6/10/2013#, #6/11/2013#, #6/12/2013#, #9/19/2013#, #9/20/2013#, #10/1/2013#, #10/2/2013#, #10/3/2013#, #10/4/2013#, #10/7/2013# _
  , #1/1/2014#, #1/31/2014#, #2/3/2014#, #2/4/2014#, #2/5/2014#, #2/6/2014#, #4/7/2014#, #5/1/2014#, #5/2/2014#, #6/2/2014#, #9/8/2014#, #10/1/2014#, #10/2/2014#, #10/3/2014#, #10/6/2014#, #10/7/2014# _
  , #1/1/2015#, #1/2/2015#, #2/18/2015#, #2/19/2015#, #2/20/2015#, #2/23/2015#, #2/24/2015#, #4/6/2015#, #5/1/2015#, #6/22/2015#, #10/1/2015#, #10/2/2015#, #10/5/2015#, #10/6/2015#, #10/7/2015# _
  , #1/1/2016#, #2/8/2016#, #2/9/2016#, #2/10/2016#, #2/11/2016#, #2/12/2016#, #4/4/2016#, #5/2/2016#, #6/9/2016#, #6/10/2016#, #9/15/2016#, #9/16/2016#, #10/3/2016#, #10/4/2016#, #10/5/2016#, #10/6/2016#, #10/7/2016# _
  , #1/2/2017#, #1/27/2017#, #1/30/2017#, #1/31/2017#, #2/1/2017#, #2/2/2017#, #4/3/2017#, #4/4/2017#, #5/1/2017#, #5/29/2017#, #5/30/2017#, #10/2/2017#, #10/3/2017#, #10/4/2017#, #10/5/2017#, #10/6/2017# _
  , #1/1/2018#, #2/15/2018#, #2/16/2018#, #2/19/2018#, #2/20/2018#, #2/21/2018#, #4/5/2018#, #4/6/2018#, #4/30/2018#, #5/1/2018#, #6/18/2018#, #9/24/2018#, #10/1/2018#, #10/2/2018#, #10/3/2018#, #10/4/2018#, #10/5/2018#, #12/31/2018# _
  , #1/1/2019#, #2/4/2019#, #2/5/2019#, #2/6/2019#, #2/7/2019#, #2/8/2019#, #4/5/2019#, #5/1/2019#, #5/2/2019#, #5/3/2019#, #6/7/2019#, #9/13/2019#, #10/1/2019#, #10/2/2019#, #10/3/2019#, #10/4/2019#, #10/7/2019# _
  , #1/1/2020#, #1/17/2020#, #1/27/2020#, #1/28/2020#, #1/29/2020#, #1/30/2020#, #4/6/2020#, #5/1/2020#, #5/4/2020#, #5/5/2020#, #6/25/2020#, #6/26/2020#, #10/1/2020#, #10/2/2020#, #10/5/2020#, #10/6/2020#, #10/7/2020#, #10/8/2020# _
  , #1/1/2021#, #2/11/2021#, #2/12/2021#, #2/15/2021#, #2/16/2021#, #2/17/2021#, #4/5/2021#, #5/3/2021#, #5/4/2021#, #5/5/2021#, #6/14/2021#, #9/20/2021#, #9/21/2021#, #10/1/2021#, #10/4/2021#, #10/5/2021#, #10/6/2021#, #10/7/2021# _
  , #1/3/2022#, #1/31/2022#, #2/1/2022#, #2/2/2022#, #2/3/2022#, #2/4/2022#, #4/4/2022#, #4/5/2022#, #5/2/2022#, #5/3/2022#, #5/4/2022#, #6/3/2022#, #9/12/2022#, #10/3/2022#, #10/4/2022#, #10/5/2022#, #10/6/2022#, #10/7/2022# _
  , #1/2/2023#, #1/23/2023#, #1/24/2023#, #1/25/2023#, #1/26/2023#, #1/27/2023#, #4/5/2023#, #5/1/2023#, #5/2/2023#, #5/3/2023#, #6/22/2023#, #6/23/2023#, #9/29/2023#, #10/2/2023#, #10/3/2023#, #10/4/2023#, #10/5/2023#, #10/6/2023#)
  
End Sub

This seems to work fine. I personally would follow others suggestions as to storing the dates on a sheet and/or looping them into a date array.

Hope that helps,

Mark

Thanks all! I stored the dates on a sheet. It's ok now.

Thread: Compile error about multiline initializtion of date array

Thread Tools

Display

Compile error about multiline initializtion of date array

Tags for this Thread

Posting Permissions