Moje doświadczenia z Excelem, Accesem i programowaniem w VBA

Wpisy z tagiem: funkcje UDF

poniedziałek, 11 kwietnia 2016

Jak zainstalować dodatek w Excelu:

 

A tu dodatek pokazany jako przykład czy funkcja zamieniająca liczbowy zapis kwoty na zapis słowny:

Kwota słownie - dodatek Excel

 

piątek, 05 kwietnia 2013

Dziś przykład bardzo praktyczny - jak w arkuszu Excela dodać ułamki zwykłe?W jednej komórce jest np. 1/3, w drugiej - 1/2 - jak dodać je do siebie, aby w trzeciej komórce otrzymać wynik 5/6?
Nie da się zrobić tego bezpośrednio - ani w Excelu ani w kodzie VBA. Można jednak stworzyć sobie własną funkcję użytkownika , która będzie wykonywała takie obliczenia tak samo jak wszystkie standardowe funkcje Excela. Taką funkcję nazywamy funkcją UDF (User-Defined Function).

Przykład takiej funkcji to

Public Function SumaUlamkow(Ulamek1, Ulamek2)
          'Autor marzatela   http://mojezmaganiainformatyczne.blox.pl

Dim Mianownik1
Dim Mianownik2
Dim Licznik1
Dim Licznik2
Dim Mianownik
Dim Licznik
Dim i As Integer

Ulamek1 = Replace(Ulamek1, " ", "")
i = InStr(1, Ulamek1, "/")
Licznik1 = Left(Ulamek1, i - 1)
Mianownik1 = Right(Ulamek1, Len(Ulamek1) - i)
Ulamek2 = Replace(Ulamek2, " ", "")
i = InStr(1, Ulamek2, "/")
Licznik2 = Left(Ulamek2, i - 1)
Mianownik2 = Right(Ulamek2, Len(Ulamek2) - i)
Licznik1 = CInt(Licznik1)
Mianownik1 = CInt(Mianownik1)
Licznik2 = CInt(Licznik2)
Mianownik2 = CInt(Mianownik2)
Mianownik = WorksheetFunction.Lcm(Mianownik1, Mianownik2)
Licznik = Licznik1 * (Mianownik / Mianownik1) + Licznik2 * (Mianownik / Mianownik2)
i = WorksheetFunction.Gcd(Licznik, Mianownik)
If i > 1 Then
  Licznik = Licznik / i
  Mianownik = Mianownik / i
End If
SumaUlamkow = Licznik & "/" & Mianownik
End Function


 

SumaUlamkow jest funkcją dodająca 2 ułamki zwykłe - określone poprzez argumenty wejściowe Ulamek1 i Ulamek2. Wprawdzie typ danych nie jest tu określony, ale należy się spodziewać, że jest to typ tekstowy czyli String. Wynika to głównie  z tego, że wpisując ciąg znaków np. 1/3 do komórki - po wyjściu z niej Excel automatycznie przekształci do w datę. Musimy więc zadeklarować to jako tekst czyli wpisać na początku apostrof - oznacza to że wpisujemy tekst. Szerzej pisałam o tym w notce:
Ułamek zwykły w komórce Excela
Można także wstawić w dowolnym miejscu znak spacji. 

Na początku funkcji SumaUlamkow następuje przekształcenie tych zmiennych czyli wyodrębnienie licznika i mianownika ułamków będących argumentami funkcji oraz przekształcenie ich w liczby całkowite. Kolejny krok to oszukanie najmniejszej wspólnej wielokrotności mianowników, rozszerzenie ułamków oraz dodanie liczników. Na końcu jeszcze tylko sprawdzenie największego wspólnego podzielnika i ewentualne skrócenie ułamka sumy. 

W samej funkcji użyłam funkcji VBA: Replace, InStr, Left, Right.

Efekt końcowy wygląda tak:

suma ułamków zwykłych

 

A każdego kto ma ochotę nauczyć się VBA zapraszam tu:

VBA w Excelu - pierwsze kroki

VBA w Excelu dla tych, którzy już coś wiedzą


wtorek, 09 marca 2010

Pierwsza własna funkcja już napisana (notka: Piszemy funkcję VBA) , teraz pora na jej wykorzystanie w arkuszu Excela. Ja kto zrobić? Identycznie jak wstawianie każdej innej funkcji, wybierając z listy rodzajów funkcji - funkcje użytkownika:

wstawianie funkcji użytkownika

 Po wybraniu kategorii Użytkownika pojawia się lista wszystkich funkcji tej kategorii:

funkcje użytkownika

Lista jest alfabetyczna. W tym przypadku na jej końcu znajduje się funkcja PodwojonaSumaKomorek, której kod VBA opisywałam tu:
Piszemy funkcję w VBA

Wybieramy tę funkcję i wstawiamy dane jak do każdej zwykłej funkcji

wstawianie funkcji własnej użytkownika

Argumentami funkcji są PierwszySkladnik i DrugiSkladnik - czyli te zmienne, które w kodzie funkcji były wpisane w w pierwszej linii, przy deklaracji funkcji:

Public Function PodwojonaSumaKomorek(PierwszySkladnik As Integer, DrugiSkladnik As Integer) As Integer

Wstawiamy komórki w poszczególne pola funkcji i już - funkcja działa i oblicza.
Warto przy tym zwrócić uwagę, że w ramach kodu funkcji PodwojonaSumaKomorek - argumenty funkcji zostały zdeklarowane jako liczby typu Integer czyli całkowite. Jeżeli w komórkę wstawioną do funkcji wpiszemy liczbę rzeczywista - Excel odrzuci to co po przecinku i obliczy wartość funkcji tylko dla części całkowitej. Jeżeli pojawią się jednak np. wartość typu String - wynikiem działania funkcji będzie błąd.
Ale wszsytko przed nami - na obsługę błędów i zabezpieczenia przed nieprzewidywalnymi działaniami użytkowników też przyjdzie pora.....

poniedziałek, 08 marca 2010

Ja zacząć pisać funkcję w VBA? Przykład będzie bardzo prosty, ale chcę na nim pokazać podstawowe zasady. O wejściu do edytora VBA i wstawieniu nowej funkcji pisałam tu:
Excel - edytor Visual Basic

Zaprojektujmy prostą funkcję, która będzie sumowała wartości dwóch komórek, a nastepnie mnożyła wynik przez 2. .

Public Function PodwojonaSumaKomorek(PierwszySkladnik As Integer, DrugiSkladnik As Integer) As Integer              'linia 1

Dim Suma As Integer                                       'linia 2

Suma = PierwszySkladnik + DrugiSkladnik  'linia 3
PodwojonaSumaKomorek = 2 * Suma          'linia 4

End Function                                                      'linia 5

W oknie powyżej jest dokładnie skopiowany kod programu, łącznie z kolorami, które też mają swoje znaczenie. Można je zmienić, ale to już osobny temat na inną okazję.
kolor niebieski - słowa kluczowe VBA 
kolor zielony - wszystkie dodatkowe informacje, opisy - jednym słowem tekst własny programisty, w którym możemy wpisywac wszystkie uwagi. Zaczynają sie one od górnego apostrofu ' , kończą naciśnięciem Enter czyli przejściem do następnej linii. 
A teraz po kolei, linijkami:

linia 1 
Public Function  - to oznaczenie początku kodu funkcji. PodwojonaSumaKomorek - nazwa własna funkcji (wymyślona przez mnie). PierwszySkładnik i DrugiSkladnik - to nazwy zmiennych wymaganych (będzie to widoczne z poziomu arkusza Excel) . Ponieważ przy każdej z nich jest dopisek As Integer - oznacza to, że zmienne muszą być typu Integer.  Podobnie w przypadku wyniku całej funkcji - za nawiasem tez jest ten dopisek. Nie jest to warunek obowiązkowy, można nie deklarować typu zmiennych - wówczas będa one traktowane jako zmienne typu Variant.

linia 2
Blok (w tym przypadku tylko jedna linia) deklaracji zmiennych o charakterze lokalnym i pomocniczym, widocznych tylko w ramach kodu. Każda kolejna zmienna jest deklarowana w nowej linii, zaczynajacej się słowem kluczowym Dim.

linia 3
Zmienna Suma przyjmuje wartość PierwszySkladnik + DrugiSkladnik

linia 4
PodwojonaSumaKomorek (czyli wartość funkcji) przyjmuje wartość podwójnej Sumy.
Tak naprawdę - to można było to zapisać dużo prościej:
PodwojonaSumaKomorek=2*(PierwszySkladnik+DrugiSkladnik)
chodziło mi jednak o pokazanie róznicy pomiędzy zmiennymi wymaganymi, a blokiem zmiennych w ramach kodu.

linia 5
End Function - słowo kluczowe kończące kod funkcji.

Teraz wystarczy tylko zdebugować funkcję w edytorze VBA (czyli sprawdzić poprawność wpisanego kodu) i funkcję można wykorzystać.

kompilacja kodu VBA

Jeżeli w kodzie są błędy - pojawi się komunikat z zaznaczeniem miejsca, w którym błąd się pojawił. Jeżeli nie - komunikatu nie będzie, ale zaznaczona na obrazku opcja "Compile VBA Projekt" - stanie się nieaktywna. Przynajmniej do czasu, gdy nie wprowadzimy zmian w kodzie (nawet tych nieistotnych).

| < Listopad 2017 > |
Pn Wt Śr Cz Pt So N
    1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30      


Książki warte polecenia
zobacz szczegóły...


A tu oferta na dziś:





ministat liczniki.org



Napisz do mnie!