Unable to use an array variable for data validation (Drop down list).

Also:

1. The immediate window doesn't show the value for MyArray when the line for debug.print is being executed.

2. Nothing happens when the line Range("A" & r).value = MyArray(r) is executed. The excel sheet is still blank.

   Sub Testing()
   
       Dim ws As Worksheet
       Dim MyArray() As String
       Dim r As Integer
       Dim WsCount As Integer
   
       r = 1
   
       WsCount = ThisWorkbook.Worksheets.Count
       ReDim MyArray(1 To WsCount)
   
       For Each ws In ThisWorkbook.Worksheets
           MyArray(r) = ws.Name
           r = r   1
           If r <= 12 Then
               Debug.Print MyArray(r)
               Range("A" & r).Value = MyArray(r)
           End If
       Next ws
   
       ActiveSheet.Cells(1, 2).Validation.Add _
       Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
             Operator:=xlBetween, Formula1:=MyArray
   
   End Sub
   

CodePudding user response：

Answering your 2nd question first:
Actually, the code does execute, however what you are doing is:

1. Fill cell at index r.
2. Increase index r (which now points to an empty cell of your array)
3. Write the empty string to your range

Now about the validation. Formula1 expects comma-delimited values (see here) aka a string, not an array.

You can easily correct that line of code this way (I removed the Operator which I think is unneeded).

ActiveSheet.Cells(1, 2).Validation.Add _
 Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
          Formula1:=WorksheetFunction.TextJoin(",", True, MyArray)