[VBA] .ListFillRange filteren

[YellowOnline]

Lange versie
Na lang zwoegen is een redelijk groot script van me af - een leuke mix van Powershell, (de hoofdcode), C# (om een class aan te maken), SQL (om... SQL data op te vragen ) en VBA. Voor dat laatste dank aan enkele tips van F_J_K trouwens. Het is wederom voor VBA dat ik me tot GoT wend.

Alvorens het script in productie te nemen zou ik nog één ding willen veranderen dat het iets gebruiksvriendelijker maakt. Eerst even kort schetsen wat er in dat Excel-document zit (of toch het gedeelte relevant voor dit verhaal): drie werkbladen met daarop een dropbox die respectievelijk gepackagete software toont, toegelaten ongepackagete software en niet toegelaten ongepackagete software.

Een voorbeeld van de lijst met packages:


Wanneer op een waarde geklikt wordt in de dropbox dan wordt weergegeven welke gebruiker/machine enz. die software geïnstalleerd heeft. Nu is het zo dat die gebruikers enkel opgelijst worden voor de niet-gepackagete software. Voor de gepackagete software wordt dit niet gedaan aangezien dit een de facto dump van de SQL DB in het Excelbestand zou zijn. We willen enkel voor de niet-gepackagete software weten wie/wat/waar én daarnaast ook voor de gepackagete software waar specifiek aangegeven is dat we het wel willen weten. Concreet voorbeeld: van Office willen we niet weten welke gebruikers dit hebben (alle 20 000 immers) maar van pakweg AutoCAD wel.

Concreet: er is een kolom A met daarin een lijst van packages. Momenteel nog overzichtelijk (200 of zo) in de toekomst niet (1000+). Er is een kolom B met daarin een waarde True of False die bepaald of de gebruikersgegevens die bij dat package horen opgevraagd worden op de SQL DB of niet. Op een ander werkblad staat een dropbox waar ik een package kan kiezen en dan zie ik de gebruikersgegevens wanneer ik een package aanklik waarvan de kolom B waarde op True staat. Dat werkt dus allemaal zoals het hoort - want daar schuilt mijn PoSh-script achter die de SQL ondervraagt alvorens het Exceldocument aan te maken. Voor de SQL query is die kolom B niet nodig, neen, ik wil die voor wat anders gebruiken: nl. om mijn dropbox te filteren, zodat enkel de packages zichtbaar zijn waarvoor een waarden bestaan, dwz. waar de waarde in kolom B = True

---

tl;dr
Het is een heel verhaal en misschien niet helemaal duidelijk, maar het is niet zo vanzelfsprekend om een script van 3000 lijnen in een specifieke omgeving kort samen te vatten.

Mijn dropbox wordt als volgt gevuld:

Visual Basic .NET:

ddlSelectPackage.ListFillRange = "'Packages Data'!$A$2:$A$" & intLastRow

En ik wil hier enkel de A's overhouden die in kolom B een waarde hebben die gelijk is aan TRUE.

---

Noot: Ik heb een hekel aan het vervoegen van het Engelse werkwoord packagen in het Nederlands, maar verpakken klinkt in de context van software distribution toch bizar.

[LiquidT_NL]

Destijds heb ik een soortgelijk probleem gehad, maar de enige manier is om de items stuk-voor-stuk te feeden aan de combobox. Met:
dllSelectPackage.additem = "item".

Een simpele For of Do While loop er omheen is dan het makkelijkste denk ik. Dat is mij in ieder geval ook destijds aangeraden op een van de VBA fora.

[F_J_K]

Ik zou inderdaad gaan voor AddItem. Op die manier kan je eenvoudig 'any' criterium gebruiken voor de vulling (pak-em-beet wil je misschien ooit bepaalde andere categorien uitsluiten, bijv wegens privacy o.i.d.).

[YellowOnline]

Hmz, spijtig, want item-per-item werken is véél trager dan met ranges werken. Aan de andere kant: deze dropbox zal nooit veel items bevatten, in tegenstelling tot de andere dropboxes die 1000en items (lees: alle mogelijke software op de wereld die 20 000 gebruikers kunnen installeren) kunnen bevatten. Hoewel de code dezelfde is, kan ik gemakkelijk voor dat specifieke werkblad anders werken.

Visual Basic .NET:

Sub SelectPackage()
    'Declarations
    Dim intLastRow As Integer
    Dim ddlSelectPackage As Object
        
    'Creating the object
    ActiveSheet.DropDowns.Add(108.75, 0, 266.25, 15.7, False).Select
    Set ddlSelectPackage = ActiveSheet.DropDowns(1)
    If ActiveSheet.Name = "Packages Graph" Or ActiveSheet.Name = "Packages Users" Then
        intLastRow = Worksheets(2).UsedRange.SpecialCells(11).Row
    Else
        If ActiveSheet.Name = "Unpackaged Allowed Graph" Or ActiveSheet.Name = "Unpackaged Allowed Users" Then
            intLastRow = Worksheets(5).UsedRange.SpecialCells(11).Row
        Else
            intLastRow = Worksheets(8).UsedRange.SpecialCells(11).Row
        End If
    End If
    
    ddlSelectPackage.Name = "ddlSelectPackage"
    ddlSelectPackage.Placement = xlFreeFloating
    If ActiveSheet.Name = "Packages Graph" Then
        ddlSelectPackage.ListFillRange = "'Packages Data'!$A$2:$A$" & intLastRow
    Else
        If ActiveSheet.Name = "Packages Users" Then
        'ddlSelectPackage.AddItem <- HIER WERK IK DAN MAAR :)
        Else
            If ActiveSheet.Name = "Unpackaged Allowed Graph" Or ActiveSheet.Name = "Unpackaged Allowed Users" Then
                ddlSelectPackage.ListFillRange = "'Unpackaged Allowed'!$A$2:$A$" & intLastRow
            Else
                ddlSelectPackage.ListFillRange = "'Unpackaged Disallowed'!$A$2:$A$" & intLastRow
            End If
        End If
    End If
    
    ddlSelectPackage.DropDownLines = 44 'Pretty much goes down to row 30
    ddlSelectPackage.Display3DShading = True
    ddlSelectPackage.LinkedCell = "$S$1"
    
    If InStr(ActiveSheet.Name, "Graph") > 0 Then
        Selection.OnAction = "ReplaceGraph"
    Else
        Selection.OnAction = "ReplaceUser"
    End If

Maar goed, ik zal het op deze manier doen. Dankuwel!

[ Voor 75% gewijzigd door YellowOnline op 11-09-2012 12:07 ]

[LiquidT_NL]

Inderdaad, zou het gewoon in hacken. Er is helaas geen andere methode, behalve dan misschien een schaduwlijst maken op basis van filters/itereren. Maar goed, het inlezen van een combo box kan helaas niet gepaard gaan met filters!

[YellowOnline]

Ter afsluiting, dit is het dus geworden (op de plek in de code waar staat "hier werk ik dan maar"):

Visual Basic .NET:

        If ActiveSheet.Name = "Packages Users" Then
            For intRow = 2 To intLastRow
                If Sheets("Packages Data").Cells(intRow, 2) = "True" Then
                    ddlSelectPackage.AddItem Sheets("Packages Data").Cells(intRow, 1)
                End If
            Next

Nogmaals dank voor jullie advies!

[ Voor 4% gewijzigd door YellowOnline op 11-09-2012 12:58 ]

[LiquidT_NL]

Your welcome! Ik ga het office hok maar eens in de gaten houden. Heb laatst een stukje automatisering voor een webshop geschreven om facturen en administratie automatisch in Excel te doen, en heb nogal wat kennis van VBA opgedaan. Kan ik die kennis weer delen hier!