Excel Forum - Porady, Pomoc, Excel Help, Excel FAQ - Uzupełnienie/poprawienie kodu UDF

Staszek1406 · Wysłany: 06-04-2021, 18:21 **Uzupełnienie/poprawienie kodu UDF**

Witam,
na jednym z blogów znalazłem taki kod:

Kod:

Function NoweWyszukajPionwo(szukana_wart As Range, Szuk_zakres As Range, kolumna As Range, Optional Format_danych As Byte = 0)
'
Dim pozycja As Range
Set pozycja = Szuk_zakres.Find(What:=szukana_wart, MatchCase:=True)

Select Case Format_danych
Case 0
NoweWyszukajPionwo = Cells(pozycja.Row, kolumna.Column).Value
Case 1
NoweWyszukajPionwo = Int(Cells(pozycja.Row, kolumna.Column).Value)
Case 2
NoweWyszukajPionwo = Format(CSng(Cells(pozycja.Row, kolumna.Column).Value), "Fixed")
Case 3
NoweWyszukajPionwo = CStr(Cells(pozycja.Row, kolumna.Column).Value)
Case 4
NoweWyszukajPionwo = Format(CDate(Cells(pozycja.Row, kolumna.Column).Value), "dd.mm.yyyy")
End Select
End Function

Autor na tym przykładzie przedstawił tworzenie funkcji użytkownika.
Ten kod powstał poprzez przepisanie z bloga.
Niestety funkcja nie działa. Wyświetla błąd #ARG!.
Autor nie zamieścił listingu tej funkcji. Napisałem do autora, ale nie otrzymałem odpowiedzi.

Proszę o pomoc

Pozdrawiam

PS
Do którego z moderatorów można napisać na PW (dotyczy konta na forum) ?

Rafał B. · Wysłany: 06-04-2021, 19:11

Ta funkcja przyjmuje Range jako argumenty (nie licząc tego ostatniego). Przecież masz wszystko opisane w pierwszym wierszu, który przytoczyłeś.

Wywołujesz więc:

Kod:

=NoweWyszukajPionwo(A1;E4:G20;F2)

A1 - komórka z szukaną wartością
E4:G20 - dane, zakres (czyli tabela_tablica jak w wyszukaj.pionowo)
F2 - dowolna komórka wskazująca na kolumnę- w naszym przykładzie "F", czyli będzie szukać w drugiej kolumnie w tym zakresie wyżej zdefiniowanym. Może być też F1, F999, nie ma to znaczenia, tylko chodzi o kolumnę

Maciej Gonet · Wysłany: 06-04-2021, 19:21

Ta funkcja w zasadzie działa, ale trochę nietypowo, jest napisana pod czyjeś konkretne potrzeby.
Wprowadziłem drobne poprawki: usunąłem literówkę w nazwie funkcji,
usunąłem z deklaracji wymóg, żeby pierwszy argument był odwołaniem (adresem). W tej chwili może być zarówno odwołaniem jak i wartością.
Poza tym ostrożnie należy używać ostatniego argumentu. Na ogół wymagane jest by wartość zwracana była liczbą.

Szkoda, że nie pokazałeś własnego przykładu jak chciałeś tej funkcji używać.

Nie wiem, jaka była intencja piszącego kod tej funkcji. Jeśli myślał o jej użyciu w sposób pokazany przez Rafała, czyli z użyciem wielokolumnowego zakresu jako drugiego argumentu, to będzie się ona zachowywać inaczej niż znana nam funkcja WYSZUKAJ.PIONOWO, bo będzie szukać wartości z pierwszego argumentu gdziekolwiek w obszarze drugiego argumentu, a nie tylko w pierwszej kolumnie.
Wynik zostanie zaś zwrócony z pierwszej kolumny zakresu podanego w trzecim argumencie, z wiersza tego, w którym znaleziono wartość szukaną.

W sumie jest tu dużo możliwych wariantów użycia, niektóre nie są oczywiste. Trzeba dokładnie przeanalizować kod.

Nowe_Wyszuk_Pion.xlsm

Pobierz Plik ściągnięto 7 raz(y) 14.73 KB

Staszek1406 · Wysłany: 07-04-2021, 16:25

Witam,
Maciej Gonet napisał"

Cytat:

Ta funkcja w zasadzie działa, ale trochę nietypowo, jest napisana pod czyjeś konkretne potrzeby.

Post, z którego zaczerpnąłem kod funkcji NoweWyszukajPionowo był zatytułowany Jak stworzyć własną funkcję w Excelu? Tyle wyjaśnienia.

W załączniku przedstawiłem działanie tej funkcji i problemy, na jakie natrafiłem.
Funkcja działa prawidłowo, gdy w kol D są stałe. Natomiast, gdy w tej kolumnie są wyniki działania formuły wyświetla #ARG! (nieprawidłowy typ danych użytej w formule). Sprawdzałem funkcją CZY.TEKST wartości z kolumn D i Z, w obu przypadkach wynik PRAWDA. Próbowałem wynik formuły przekonwertować na tekst funkcją T. Niestety bez rezultatu. Jak spowodować, aby funkcja działała także, gdy wartości są wynikiem działania formuły?

Pozdrawiam

Pionowo.xlsm

Pobierz Plik ściągnięto 9 raz(y) 23.44 KB

Tajan · **Pomógł:** 4767 razy Posty: 10469

Zmień linię:

Kod:

Set pozycja = Szuk_zakres.Find(What:=szukana_wart, MatchCase:=True)

w taki sposób:

Kod:

Set pozycja = Szuk_zakres.Find(What:=szukana_wart, MatchCase:=True, LookIn:=xlValues, LookAt:=xlWhole)

a po tej linii dodaj jeszcze:

Kod:

If pozycja Is Nothing Then
NoweWyszukajPionowo = CVErr(xlErrNA)
Exit Function
End If

aby funkcja zwracała prawidłowy kod błędu w przypadku nieodnalezienia wartości.
Chciałbym jeszcze zwrócić uwagę, że funkcja nie zawsze wyszuka pierwszą wartość. Zwróć uwagę na wynik wyszukania wartości "ES". Aby tego uniknąć konieczna jest dalsza rozbudowa funkcji. Ale nie wiem, czy jest to ci potrzebne.

Maciej Gonet · Wysłany: 07-04-2021, 19:11

Trzeba było zacząć od przykładu jak chcesz tej funkcji używać, bo ona bazuje na metodzie Range.Find, która działa w określony sposób i ma wiele parametrów, które mogą modyfikować sposób jej działania. Te parametry (poza jednym) nie zostały określone, więc przyjęto ich wartości domyślne. A w przypadku tej funkcji wartości domyślne mogą wynikać z poprzedniego użycia tej funkcji (funkcja pamięta ostatnio używane wartości niektórych parametrów), więc to jeszcze komplikuje sytuację.
Dlatego pisałem, że trzeba dokładnie przeanalizować kod w kontekście potrzeby jego użycia.
Jeżeli funkcja miała być tylko przykładem to jest, bo w określonych sytuacjach działa.
Natomiast to nie oznacza, że w tej formie będzie działać w każdej sytuacji.
Dlatego w zależności od tego jak chcemy jej używać musimy zmodyfikować wartości parametrów, które wpływają na jej działanie. Pierwszą rzeczą, jaką powinieneś był zrobić, to przeczytać opis działania metody Find i znaczenie jej parametrów.
Pierwszą ważną rzeczą jest w czym funkcja ma szukać poszukiwanej wartości. Może jej szukać w wartościach lub w formułach w obszarze przeszukiwanym. Domyślnie szuka w formułach. Jeśli obszar przeszukiwany zawiera stałe, to nie ma to znaczenia, natomiast gdy zawiera formuły, a Ty chcesz szukać w wartościach, to trzeba dodać argument

Kod:

LookIn:=xlValues

Druga kwestia wiąże się z dopasowaniem. Czy wartość szukana ma pasować dokładnie do wartości w obszarze przeszukiwanym, czy wystarczy, żeby była w nim zawarta?
Określa to parametr LookAt, który może przyjmować wartość xlWhole albo xlPart. On nie jest tu określony, co oznacza, że przyjęte będzie xlPart, zatem wystarczy jak wartość szukana mieści się w wartości znalezionej.
W Twoim przypadku oznacza to, że mógłbyś szukać również w kolumnie X, niekoniecznie Z. A jeśli zależy Ci na dopasowaniu dokładnym, trzeba dodatkowo zdefiniować ten parametr. Nota bene wyszukiwanie dokładne/przybliżone znaczy co innego w przypadku funkcji Excela WYSZUKAJ.PIONOWO, a co innego w metodzie Find.
Trzeci aspekt wiąże się z duplikatami, które występują w Twoich danych. Z przykładu rozumiem, że oczekujesz, że funkcja znajdzie pierwsze dopasowanie. Żeby tak było potrzebny jest jeszcze jeden parametr After. Metoda Find domyślnie zaczyna szukać od drugiej komórki przeszukiwanego zakresu. Jeśli więc dopasowanie jest już w pierwszej komórce, a występuje inne dopasowanie (duplikat), to to pierwsze zostanie pominięte (w pierwszym obiegu funkcji Find). Jeśli taka sytuacja może wystąpić trzeba wskazać, że poszukiwanie ma się zacząć za ostatnią komórką zakresu.

Kod:

After:=Szuk_zakres(Szuk_zakres.Count)

Jest jeszcze więcej parametrów opcjonalnych, które mogą modyfikować działanie metody Find, więc nie należy stosować jej bezmyślnie.
Ale tych wszystkich aspektów nie poruszyłeś w swoim pierwszym poście, więc trudno się było domyślić, jak chcesz używać tej funkcji.
Sprawdź czy teraz jest jak chciałeś, może jeszcze trzeba coś doprecyzować.

Pionowo1.xlsm

Pobierz Plik ściągnięto 5 raz(y) 20.6 KB

Staszek1406 · Wysłany: 07-04-2021, 20:55

Witam,
dziękuję wszystkim za rady, wskazówki i za znalezienie czasu na rozwiązanie problemu.
Przepraszam, że nie zamieściłem załącznika przy poście inicjującym temat. Wydawało mi się, że problem leży w braku jakieś linijki kodu.
Przeczytałem jeszcze raz post, z którego pobrałem kod funkcji. O metodzie FIND autor pisze bardzo niewiele. Dziękuję Maciejowi za wyjaśnienia dotyczące metody FIND.

Pozdrawiam

Stanisław

ąćęłńóś · Excel Expert **Pomógł:** 327 razy Posty: 1615

Należy jeszcze pamiętać o pewnych niuansach/"właściwościach" 'Find' w przypadku wyszukiwania w zakresach, w których są ukryte wiersze/kolumny - w takim przypadku z wyszukiwaniem poradzi sobie tylko 'Find' z parametrem "LookIn:=xlFormulas", "xlValues" zawodzi - tak więc w sumie, to należałoby zastosować wyszukiwanie zarówno z "xlFormulas", jak i z "xlValues", aby być "pewnym", że coś "może" znajdziemy.

Staszek1406 · Wysłany: 08-04-2021, 19:35

Witam,
dzięki za dodatkowe uwagi o metodzie Find.

Pozdrawiam
Stanisław