Lista Sprawdzania Poprawności z danych opartych o zakres nieciągły   strona główna:
A po co ten Excel ;-)
 
Dziś inny ciekawy problem.  
     Mamy np.: 3 tabele (3 kolumny / x wierszy) w zakresach: G4:I10; K5:M13; O6:Q24. Zależy nam żeby wyszukiwać danych w zakresie tych trzech  
tabel po indeksie z pierwszej kolumny. Żeby takie wyszukiwanie realizować przy pomocy f. WYSZUKAJ.PIONOWO trzeba by sprawdzać czy próba  
wyszukania danego indeksu w pierwszej tabeli nie zwróci błędu, jeżeli jednak zwróci błąd to przeszukujemy kolejną tabelę. Zadanie może i realne przy  
trzech tabelach, ale przy rosnącej liczbie tabel problem zrodzi się od strony ilości zagnieżdżeń f. JEŻELI czy nawet długości formuły. Można podejść  
do sprawy inaczej: tj. napisać funkcję UDF która zwróci tablicę, która będzie Unią zakresów podanych do naszej funkcji jako argumenty.  
Funkcja realizująca takie zadanie może wyglądać tak:  
 
Function tblUnionZakresówV(ParamArray Zakresy() As Variant) As Variant  
    Dim zakres As Variant  
    Dim wTbl As Long, kTbl As Integer  
    Dim tblWyniki() As Variant, w As Long  
    Dim temptbl As Variant, iTbl As Long, jTbl As Integer  
      
    For Each zakres In Zakresy  
        With zakres  
            wTbl = wTbl + .Rows.Count  
            If kTbl < .Columns.Count Then kTbl = .Columns.Count  
        End With  
    Next  
    ReDim tblWyniki(1 To wTbl, 1 To kTbl)  
    For Each zakres In Zakresy  
        temptbl = zakres  
        For iTbl = LBound(temptbl, 1) To UBound(temptbl, 1)  
            w = w + 1  
            For jTbl = LBound(temptbl, 2) To UBound(temptbl, 2)  
                tblWyniki(w, jTbl) = temptbl(iTbl, jTbl)  
            Next  
        Next  
    Next  
    tblUnionZakresówV = tblWyniki  
End Function  
 
wytłumaczę ją gdzie indziej - zainteresowani się doszukają :-).  
"Byle że..." możemy teraz wykorzystać wyniki zwracane przez tą funkcję do uproszczenia wyszukiwania danych poprzez WYSZUKAJ.PIONOWO w   
wielu zakresach. Na przykładzie naszych tabel:  
=WYSZUKAJ.PIONOWO(F26;tblUnionZakresówV(G4:I10;K5:M13;O6:Q24);2;0)  
zwróci wartość komórki z drugiej kolumny tej tabeli i w tym wierszu w którym odnaleziony został poszukiwany indeks - zapisany w komórce F26.  
     I teraz nasz główny temat. Jak w kom. F26 wstawić listę sprawdzania poprawności z indeksami z pierwszej kolumny naszych trzech tabel?  
Na wstępie zaznaczmy, że wprowadzenie do źródła danych takiej listy zakresu nieciągłego: =G4:G10;K5:K13;O6:O24 (w Excel2003) zwraca błąd:  
Nie można stosować unii, przecięć i stałych tablicowych dla kryteriów Sprawdzania danych  
a więc łatwo nie będzie :-)  
     W pomocy VBA dot. Add method as it applies to the Validation object. Można wyczytać że:  
     xlValidateList   
Formula1 is required, Formula2 is ignored. Formula1 must contain either a comma-delimited list of values or a worksheet reference to this list.   
A więc: przy typie xlValidateList - wybierając jako listę jako tym sprawdzania poprawności, Formula1 - zakres danych do listy, może być ciągiem  
danych rozdzielonych przecinkami lub odwołaniem do zakresu.  
Wiemy że odwołanie do zakresu odpada bo zakres jest nieciągły więc spróbujmy oprzeć się o ten ciąg danych rozdzielonych przecinkami. Oczywiście  
w polskiej wersji Excela musi być "po regionalnemu" więc: jeżeli wpiszemy w źródło danych ciąg danych rozdzielonych przecinkami to zostanie   
utworzona lista, ale tylko z jednym elementem którym będzie cały ciąg. Należy ciąg rozdzielić średnikami, jednak tworząc w ten sposób źródło danych  
w VBA ciąg należy rozdzielać przecinkami.  
    Funkcja która zwróciłaby taki ciąg mogła by wyglądać np.: tak  
 
Function strDaneDoListy(ParamArray Zakresy() As Variant) As String  
    Dim zakres As Variant, iTbl As Long, temptbl As Variant  
    Dim strList As String  
 
    For Each zakres In Zakresy  
        temptbl = zakres  
        For iTbl = LBound(temptbl, 1) To UBound(temptbl, 1)  
            strList = strList & temptbl(iTbl, 1) & ","  
        Next  
    Next  
    strDaneDoListy = Left(strList, Len(strList) - 1)  
End Function  
 
No i stwórzmy listę sprawdzania poprawnosci:  
 
Sub TworzListsSprawdzaniaPoprawnosci()  
    Dim wks As Excel.Worksheet  
 
    Set wks = ThisWorkbook.Worksheets("Arkusz1")  
    With wks.[F26].Validation  
        .Delete  
        .Add Type:=xlValidateList, _  
             AlertStyle:=xlValidAlertStop, _  
             Operator:=xlBetween, _  
             Formula1:=strDaneDoListy(wks.[G4:G10], wks.[K5:K13], wks.[O6:O24])  
    End With  
    Set wks = Nothing  
End Sub  
 
I mamy listę :-)  
    Nie jest to jeszcze szczyt szczęścia gdyż istnieje spore ograniczenie które musimy wziąć pod uwagę. Chodzi o max. długości formuły jaką możemy   Funkcje programu Office Excel 2007, które nie są obsługiwane we wcześniejszych wersjach programu Excel
zastosować jako źródło danych do listy spr. popraw. Ograniczenie to dotyczy wersji Excela wcześniejszych niż E2007 i jest to 255 znaków. Na stronie  
M$ na temat różnić dot. wersji E2003/E2007 w części dot. Formuł, w ostatniej pozycji można wyczytać:  
 
Formuła sprawdzania poprawności danych jest dłuższa niż 255 znaków.  
     Gdy długość formuły sprawdzania poprawności danych przekracza limit 255 znaków obsługiwany we wcześniejszych wersjach programu Excel,  
formuła będzie działać prawidłowo, jednak zostanie obcięta i nie będzie można jej edytować.  
     W funkcji sprawdzania zgodności kliknij przycisk Znajdź, aby zlokalizować komórki zawierające formuły sprawdzania poprawności danych,  
a następnie użyj w formule mniejszej liczby znaków, aby użytkownicy mogli edytować formuły we wcześniejszych wersjach programu Excel.  
 
To samo zjawisko napotkamy próbując wsadzić do param. Formula1 (metody Add obiektu Validation) ciąg dłuższy niż 255 znaków. Jak obejść problem?  
Zrezygnować z listy sprawdzania poprawności na korzyść kontrolki: niech to będzie ComboBox z Formularzy - a więc DropDown.  
 
Sub TworzDropDown()  
    Dim wks As Excel.Worksheet  
 
    Set wks = ThisWorkbook.Worksheets("Arkusz1")  
    On Error Resume Next  
    wks.DropDowns("mojDropDown").Delete  
    On Error GoTo 0  
 
    With wks.[F29]  
        With wks.DropDowns.Add(.Left, .Top, .Width, .Height)  
            .Name = "mojDropDown"  
            .List = tblUnionZakresówV(wks.[G4:G10], wks.[K5:K13], wks.[O6:O24])  
        End With  
    End With  
    Set wks = Nothing  
End Sub  
 
Powyższa procedura tworzy w kom.F29 taką właśnie kontrolkę. Za zakres danych takiej listy odpowiada właściwość List do której przypisujemy tablicę  
tworzoną z pierwszych kolumn naszych tabel. Tablicę taką tworzymy z użyciem f. tblUnionZakresówV.  
     Mamy już z czego wybierać ale jak teraz wskazać wybrany element do f. WYSZYKAJ.PIONOWO?  
PPM na utworzoną kontrolkę / Formatuj Formant / Zakładka: Formant / Łącze Komórki - wskazać F29 (komórkę "nad którą" stworzyliśmy kontrolkę)  
Zauważmy co do tej komórki zwraca wybranie czegoś na liście. Okazuje się że nie zostaje zwrócony wybrany indeks a jego pozycja (numer) na liście  
Jak więc uzyskać wskazany element a nie jego nr? Po prostu - f. INDEKS  
=INDEKS(tblUnionZakresówV(G4:G10;K5:K13;O6:O24);F29)  
i to można wykorzystać do f. WYSZUKAJ.PIONOWO  
=WYSZUKAJ.PIONOWO(INDEKS(tblUnionZakresówV(G4:G10;K5:K13;O6:O24);F29);tblUnionZakresówV(G4:I10;K5:M13;O6:Q24);2;0)  
albo krócej, wykorzystując f. INDEKS  
=INDEKS(tblUnionZakresówV(G4:I10;K5:M13;O6:Q24);F29;2)  
 
i po zadaniu :-)