Jak napisać funkcję UDF, która zwraca rozlany wynik?

W VBA dla Excela można tworzyć własne funkcje zwracające wynik. Przykładowo kod funkcji zwracającej nazwę dnia tygodnia może wyglądać następująco:

Function DZIEN_TYGODNIA() As String
    Dim nr_dnia As Long
    Dim dni(1 To 7)
    dni(1) = "poniedziałek"
    dni(2) = "wtorek"
    dni(3) = "środa"
    dni(4) = "czwartek"
    dni(5) = "piątek"
    dni(6) = "sobota"
    dni(7) = "niedziela"

    nr_dnia = Weekday(Date, vbMonday)
    If nr_dnia >= 1 And nr_dnia <= 7 Then DZIEN_TYGODNIA = dni(nr_dnia)
End Function

Funkcja o nazwie DZIEN_TYGODNIA zwraca nazwę dnia tygodnia (np. sobota). Jest to funkcja bezargumentowa, która zwraca wartość typu string.

Aby wywołać funkcję należy w dowolnej komórce wpisać jej nazwę wraz z parą nawiasów tj. =DZIEN_TYGODNIA()

Czasami potrzebujemy napisać funkcję, która „rozleje” wartości na kilka kolejnych komórek. Załóżmy, że chcę napisać funkcję, która zwraca w kolejnych pięciu komórkach jakieś wartości.

Nasza funkcja po prostu musi zwracać dwuwymiarową tablicę. I tyle! Cała magia. Numery indeksów nie są ważne (tablica może się zaczynać od 0 lub od 1, albo od dowolnej wartości) ale ja ustaliłem, że 1 to najwygodniejsza wartość. Dlatego na początku modułu warto dodać dyrektywę

Option Base 1

Która mówi, że deklarując tablicę w formie Dim Ret(5) tablica będzie mieć elementy od 1 do 5 a nie jak to domyślnie bywa od 0 do 4.

 Oto przykładowy kod funkcji zwracającej rozlany wynik (funkcja w 5 kolejnych komórkach zwraca kolejne liczby, pierwsza zwracana liczba to liczba podana jako argument funkcji, każda kolejna jest większa o 1 od poprzedniej).

Function ROZLEWAJACA_PION(Start As Long) As Variant
    Dim Ret(5, 1) As Long
    Dim x As Long
    Const y As Long = 1
    
    For x = 1 To 5
        Ret(x, y) = Start + x - 1
    Next x
    
    ROZLEWAJACA_PION = Ret
End Function

A oto efekt

Funkcja rozlewająca w poziomie wygląda bardzo podobnie

Function ROZLEWAJACA_POZIOM(Start As Long) As Variant
    Dim Ret(1, 5) As Long
    Const x As Long = 1
    Dim y As Long
    
    For y = 1 To 5
        Ret(x, y) = Start + y - 1
    Next y
    
    ROZLEWAJACA_POZIOM = Ret
End Function

A oto efekt:

Możemy też zwrócić wynik w postaci rozlania się i w pionie i w poziomie. Takim szkolnym przypadkiem będzie tabliczka mnożenia. Oto przykładowy kod generujący tabliczkę mnożenia:

Function TABLICZKA_MNOZENIA(Ile As Long) As Variant
    Dim Ret() As Long
    Dim x As Long, y As Long
    
    ReDim Ret(Ile, Ile)
    
    For x = 1 To Ile
        For y = 1 To Ile
            Ret(x, y) = x * y
        Next y
    Next x
    
    TABLICZKA_MNOZENIA = Ret
End Function

A oto efekt:

A jak to się ma w starszych wersjach Excela?

W starszych wersjach Excela, które nie obsługują rozlewania wyników funkcji domyślnie jest zwracany pierwszy element tablica:

Ale jeśli zaznaczę np. 5 kolejnych komórek i nacisnę kombinację klawiszy CTRL+SHIFT+ENTER (czyli wywołam tę funkcję tak jakby to była funkcja tablicowa) to efekt będzie taki jak na poniższym rysunku:

Oczywiście zarówno w starszych jak i nowszych wersjach Excela możemy nasze funkcje, które zwracają tablice „wsadzić” do środka dowolnej funkcji agregującej np. funkcji SUMA.

Wynikiem jest 35 gdyż funkcja ROZLEWAJACA_PION zwraca kolejno wartości 5, 6, 7, 8 i 9 a suma tych liczb to 35.