Excel VBA Validation dropdown workaround

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:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
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
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
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:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
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
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
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!-)

2 thoughts on “Excel VBA Validation dropdown workaround

    1. eFi Post author

      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.

      Reply

Leave a Reply to Roli Cancel reply

Your email address will not be published. Required fields are marked *