A probléma
Microsoft Excel VBA-ból akarok egy custom dropdown validation listát rátenni egy cellára.
Erre való a Range.Validation.Add metódus – a Formula1 paraméterének egy stringet kell átadnom, amit a VBA engine elemekre splittel (valami eszement okból kifolyólag ehhez nem lehet tömböt átadni paraméterként). A split szeparátornak az engine a dokumentáció szerint a “,” karaktert használja – vagy nem, és itt kezdődnek a bajok.
Akár a “,”, akár az Application.International(xlListSeparator) által definiált szeparátor karaktert használom a Formula1 paraméternek átadott stringben, a VBA kedve szerint válogat, hogy épp mikor mivel végzi el a splitet. Vannak esetek, amikor az egyik és vannak amikor a másik szeparátor a nyerő. A probléma az, hogy nem tudom eldönteni, hogy a VBA motor mi alapján választ.
A fura helyzet csak akkor áll fenn, ha az Excel alatti regionális beállítások nem az US Intl standard szerintiek és ezért nem a standard “,” a listaelválasztó karakter.
Hab a tortán, hogy a hibát baromi nehéz reprodukálni – egy ilyen öcsi mintakód simán működik és tökéletesen legyártja a .Validation dropdown-t:
Dim wb As Workbook, s As Worksheet
Set wb = Workbooks.Add
Set s = wb.Worksheets(1)
Dim items As Variant
items = Array("egy", "ketto", "harom")
With s.Cells(1,1).Validation
.Add _
Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, _
Formula1:=Join(items, ",")
End With
Tovább “szépül” a feladat, ha leteszek egy breakpointot a Validation híváshoz – ekkor ugyanis soha nem hibázik a rohadék.
Nekem egy nagy monolitikus kódban jött elő a gond – 9 órán át kerestem, hogy vajon mi a fene döntheti el, hogy a VBA motor épp melyik lista szeparátor karaktert preferálja, de nem jöttem rá.
A megoldás
A VBA azt ugyan nem árulja el, hogy hány elemű lett az általa a stringből felépített lista, viszont azt megmondja, hogy egy .Validation objektumot tartalmazó cella tartalma valid-e – erre való a .Validation.Value propetry. Ha tehát a gyogyós engine nem splitteli a stringünket, akkor az általa definiált Validation csak egy elemmel lesz valid, az pedig a teljes string – és már meg is fogtuk:
Dim seps As Variant, sep As Variant
seps = Array(",", Application.International(xlListSeparator))
' create list selector for target cell
For Each sep In seps
With tgtCell.Validation
.delete
.Add _
Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, _
Formula1:=Join(items, sep)
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
' check if bloody VBA made the split properly
tgtCell.value = items(LBound(items))
If tgtCell.Validation.value Then Exit For
Next sep
És már megint gyorsabb voltam, mint a Microsoft hotline!-)
