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!-)
Egyébként a support mit válaszolt, mi a valid separator nem US-nél?
A support ilyen részletekbe nem szokott belemenni. Azzal indul, hogy belekötnek abba, hogy nem nekem, a business előfizetőnek van a Twitter accountjuk, aztán elirányítanak egy magyar számra, ahol meg nincs olyan ember, aki ehhez értene.
Minden egyes ilyen alkalommal az a vége, hogy nekem kell kitalálnom a workaroundot, vagy meg vagyok lőve. 20 éve csinálom ezt, egyetlen egyszer sem tudott segíteni a hivatalos customer service.
Egyébként non-US-nél semmi más megoldás nincs, mint a fenti workaround, mert a VBA ad hoc találgatja, hogy épp melyik list separatort fogadja el, így neked kell rájönnöd, hogy adott esetben mit szeret. Tudom agyrém, de ez van – a VBA engine 1995-ben készült és kb azóta sz@rnak rá.
Órákat tudnék mesélni ilyen bugokról… Hogy csak még egyet említsek, ha már belekezdtem:
Ha az átlagnál picit komplexebb kódot csinálsz, akkor a comppiler egy idő után valamit szarul kezd el címezni és a kódod egy random helyen megáll egy “Bad DLL Convention” hibával. Erre az a megoldás, hogy a forrásban meg kell módosítani valamit (én általában hozzáadok egy új tagot egy enumhoz) , hogy a compiler újrafordítson és onnantól MAGICALLY nincs a Bad DLL Convention hiba. És ezért pénzt kérnek, a mai napig.