Category Archives: Excel

Excel VBA Validation dropdown workaround

2 Replies

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!-)

Az Excel és a sötétben bújkáló external referenciák

Leave a reply 

Sub getLinks()
    Dim myLinks As Variant, i as Long
    myLinks = ThisWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(myLinks) Then
        For i = LBound(myLinks) To UBound(myLinks)
            Debug.Print myLinks(i)
        Next i
    End If
End Sub
mégiscsak mutat külső hivatkozás(oka)t, és az Edit Linksben hiába nyomsz a Break Linkre, akkor sem szűnik meg a referencia, akkor gondolj arra, hogy a cellák validálásakor maradhatott bent kívülről jövő copy-paste során valami kiirthatatlannak látszó szemét, amit azonban a 
Sub killValidations()
    Dim S as Worksheet
    For Each S in ThisWorkbook.Worksheets
        S.UsedRange.Validation.Delete
    Next S
End Sub
majd kidob (az összes validationnel egyetemben). ]]>