PDA

View Full Version : Data validation.........vba array



sconly
05-26-2010, 02:45 AM
Is it possible to use a vba array as a list in Data Validation?

And then also do a vlookup on the array?

Thanks!

p45cal
05-26-2010, 07:28 AM
Is it possible to use a vba array as a list in Data Validation?Not so far in my explorations..
I suppose you could arrange for the vba to write the array to a sheet, to have a dynamic name refer to that range and use that name in the Data Validation source field. In fact, if the list remains the same size, you could put an array-entered formula there eg. {=myaray} which will update itself each time the Name is updated.
..but I feel someone will be cleverer than that.


And then also do a vlookup on the array? Yes, via a name, but the name has to be updated each time the array xxx changes:
Dim xxx(4, 5)
For i = 0 To UBound(xxx, 1)
For j = 0 To UBound(xxx, 2)
xxx(i, j) = i * j + i + j * 33 'this can be anything
Next j
Next i
ThisWorkbook.Names.Add "myary", xxx