Search code examples
excelvbadatevalidationevaluate

Dynamically generate array of Years for data validation list Excel VBA

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

enter image description here

Solution
    1. your math is incorrect:
    [Transpose(Text(date( (year(today())+2) - row(1:10), 1, 1), "yyyy"))]
    1. 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