Dynamically generate array of Years for data validation list Excel VBA

Question

I am adding a Data validation dropdown list to a cell in a table and want to dynamically populate it with an array of Years beginning from Today()+1 to 10 years back. e.g. 2020,2019,2018,2017,2016,2015,2014,2013,2012,2011,2010. I tried using the shorthand for Evaluate, i.e.[] but for some reasons, i am getting Error 2015.

    With .ListColumns("Select Year").DataBodyRange
        With .Cells(1).Validation
            .Delete
            '.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="<Year>,2020, 2019,2018,2017,2016,2015,2014,2013,2012,2011,2010"
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=[Transpose(Text(date( (year(today())+1) + row(1:10), 1, 1), "yyyy"))]
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
        .Cells(1).Value2 = "<Year>"
    End With

The formula is :

Formula1:=[Transpose(Text(date( (year(today())+1) + row(1:10), 1, 1), "yyyy"))]

Why is it not working suddenly? Is there a alternative fast way to generate the years?

Here is a screenshot i have added below. It was working earlier until i ran the following code:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
If Target.Name = "Instructions" Then
    ThisWorkbook.Sheets("Instructions").Visible = True
    Target.Follow ' here is where error occurred and evaluate stopped working!
End If
End Sub

Where (in which module) is the square-bracket expression written in? If it's in a worksheet's code-behind, I suspect it's working in the context of the worksheet (i.e. `Worksheet.Evaluate`) - otherwise, it's working in the context of the `ActiveSheet` (i.e. `Application.Evaluate`). You might want to make the context explicit by replacing the square-brackets expression with a string that you explicitly pass to the appropriate `Evaluate` method. — Mathieu Guindon, Nov 01 '19 at 20:00
i am testing it in a `Module`. tried `Application.Evaluate` with a string passed to it, but still getting `Error 2015`. see here: `arr = Application.Evaluate("Transpose(Text(date( (year(today())+2) - row(1:10), 1, 1), ""yyyy""))")` — sifar, Nov 01 '19 at 20:02

Scott Craner · Accepted Answer · 2019-11-01T20:11:57.363

4

your math is incorrect:

[Transpose(Text(date( (year(today())+2) - row(1:10), 1, 1), "yyyy"))]

When using xlValidateList it expects a comma delimited list not an array. So create and array variable and use Join:

    Dim arr As Variant
    arr = [Transpose(Text(date( (year(today())+2) - row(1:10), 1, 1), "yyyy"))]


     With .ListColumns("Select Year").DataBodyRange
        With .Cells(1).Validation
            .Delete
            '.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="<Year>,2020, 2019,2018,2017,2016,2015,2014,2013,2012,2011,2010"
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(arr, ",")
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
        .Cells(1).Value2 = "<Year>"
    End With

Instead of fill the array with a formula why not a simple loop:

Dim arr(1 To 10) As Long

Dim i As Long
For i = 1 To 10
    arr(i) = Year(Date) + 2 - i
Next i

edited Nov 01 '19 at 20:11

answered Nov 01 '19 at 19:42

Scott Craner

148,073
10
49
81

2

Note: the list has a maximum number of characters it can accept; consider defining a workbook-scoped `Name` instead (the square-bracket expression is already being evaluated by Excel's calc engine anyway), that way the validation list can just be `=TheDefinedName`. – Mathieu Guindon Nov 01 '19 at 19:45
@scott Craner, @mathieu Guindon i had to edit my post to explain it in a better way so you could gather what actually happened and enlighten me why `evaluate` stopped working. – sifar Nov 01 '19 at 20:00
@ScottCraner getting `Invalid procedure call or argument` on `.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(Arr, ",")` – sifar Nov 01 '19 at 20:21
@sifar it is working for me without issue. so not sure. – Scott Craner Nov 01 '19 at 20:24
@ScottCraner i declared the Arr in Workbook_Open so it was giving that error. When i declared it as `Public Arr` in a Module, it was able to add the data validation without any issues. Thanks a bunch! :-) – sifar Nov 01 '19 at 20:26

score 2 · Answer 2 · answered Nov 01 '19 at 20:05

2

Define a workbook-scoped named range with your square-bracket expression:

'Years' refers to '=TRANSPOSE(TEXT(DATE((YEAR(TODAY())+1)+ROW...'

Now your data validation formula can simply be =Years.

Rule of thumb, avoid using square-bracket expressions in VBA code. It's good for throw-away code and debugger instructions in the immediate pane, not for production code.

answered Nov 01 '19 at 20:05

Mathieu Guindon

69,817
8
107
235

how do i add the above named range using vba? – sifar Nov 01 '19 at 20:08
1

@sifar `ThisWorkbook.Names.Add "Years", "=TRANSPOSE(TEXT(DATE((YEAR(TODAY())+1) + ROW(1:10), 1, 1), ""yyyy""))"` – Mathieu Guindon Nov 01 '19 at 20:10
FWIW I've verified that this works - `?TypeName([Years])` prints `Variant()` – Mathieu Guindon Nov 01 '19 at 20:12

Dynamically generate array of Years for data validation list Excel VBA

2 Answers2