Tag Archives: Excel

Loading
loading..

Dziś napiszemy o tworzeniu makr w skoroszycie makr osobistych.
Po co to robić?
Odpowiedź: aby nasze makra były dostępne w całym Excelu na danym komputerze (dla wszystkich skoroszytów Excela).

Skoro najlepiej uczyć się poprzez praktykę, to działamy… 🙂

Najpierw znajdźmy plik Personal.xlsb (czyli skoroszyt makr osobistych).

Plik Personal.xlsb powinien znajdować się w lokalizacji:

..\Users\NAZWA_UŻYTKOWNIKA\AppData\Roaming\Microsoft\Excel\XLSTART

Jeśli nie możesz znaleźć folderu AppData to włącz pokazywanie ukrytych plików i folderów:
http://windows.microsoft.com/pl-pl/windows/show-hidden-files#show-hidden-files=windows-7

Nie masz pliku PERSONAL?
Jeśli w powyżej lokalizacji nie ma pliku Personal.xlsb to należy najpierw nagrać makro. Plik ten powstaje w momencie zarejestrowania makra. Podczas rejestracji musimy wskazać miejsce przechowywania makr na „skoroszyt makr osobistych””.

W tym celu otwórzmy sobie nowy skoroszyt Excela.

W tym momencie powinniśmy mieć otwartego Excela i aby nagrać makro należy kliknąć w lewym dolnym rogu na przycisk nagrywanie makr.

nagrywanie

W tym momencie powinno nam się otworzyć okienko Rejestrowanie makra

Rejestrowanie

W Przechowuj makro w: wybieramy opcję Skoroszyt makr osobistych. Tak jak jest to zrobione na poniższym obrazku. Ponadto możemy nadać skrót wpisując w Klawisz skrótu np. dużą literę C. Następnie klikamy OK.

W tym momencie możemy nagrywać makro, więc wszystko co teraz zrobimy w Excelu będzie nagrywane aż do momentu zatrzymania rejestrowania makra. Przykładowo zmieńmy kolor zaznaczonej komórki na czerwono i zatrzymajmy nagrywanie makra klikając w to samo miejsce gdzie włączaliśmy nagrywanie makra.

zatrzym

Udało nam się nagrać makro w skoroszycie makr osobistych, teraz możemy zamknąć plik excela. Przy zamykaniu powinno nam się pojawić okno:

okno

Kliknijmy Zapisz

W tym momencie powinien utworzyć się plik Personal.xlsb. Możemy to sprawdzić poprzez ponowne otwarcie poniższej lokalizacji (oczywiście na waszym komputerze początek ścieżki może się różnić).

..\Users\NAZWA_UŻYTKOWNIKA\AppData\Roaming\Microsoft\Excel\XLSTART

Co więcej teraz gdy otworzymy jakikolwiek plik Excela to podczas pracy możemy użyć skrótu Ctrl+Shift+C.

Właśnie zostaliśmy developerem Excela 🙂
Pozdrawiamy i do następnego wpisu 🙂

Tutaj możecie ściągnąć plik ze stworzoną dwuetapową listą wybieralną.

Lista jest stworzona tak, że w 7 wierszu (zaznaczony na jasno zielony kolor) możemy dodawać kolejne regiony, a w polu zaznaczonym szarym kolorem możemy dodawać kolejne wydziały w tych regionach.
W zależności od tego ile dodamy pól, to lista wybieralna będzie odpowiednio dostosowana.

Lista wybieralna opiera się na poprawności danych. Klikamy na komórkę E2, wchodzimy w Dane/Poprawność danych i na karcie Ustawienia wybieramy opcję Lista. Różnica między standardową listą wybieralną polega na tym, że jako źródło nie podajemy zwykłej listy a formułę.

karta developer

Jako źródło listy wybieralnej regionu nie jest podany po prostu zakres $A$7:$D$7 tylko odpowiednia formuła z wykorzystaniem funkcji PRZESUNIĘCIE – kliknij tutaj aby przejść do wpisu gdzie opisujemy funkcję.

W komórce E2 (gdzie wybieramy region), w poprawności danych jako źródło listy wybieralnej mamy wpisaną formułę:

=PRZESUNIĘCIE($A$7:$I$7;0;0;;ILE.NIEPUSTYCH($A$7:$I$7))



karta developer

Taka formuła powoduje, że gdy dopiszemy kolejne regiony w zakresie A7:I7 to pojawią się one na liście wybieralnej. Gdyby jako źródło był podany bezpośrednio zakres $A$7:$I$7, to na liście pojawiłyby się również puste pozycje. Co więcej, dzięki powyższej formule zakres ma tyle kolumn ile jest niepustych komórek w zakresie $A$7:$I$7. W funkcji PRZESUNIĘCIE jako piąty argument jest wpisana formuła ILE.NIEPUSTYCH($A$7:$I$7), dzięki czemu szerokość zakresu jest zmieniona w naszym przykładzie ma on 4 kolumny (mamy wpisane 4 regiony). Czyli zwrócony przez funkcję zakres to: $A$7:$D$7 i takie jest źródło naszej listy wybieralnej.

W kolejnej komórce, gdzie mamy wydziały (komórka E3) jako źródło listy wybieralnej wpisaliśmy formułę:

=PRZESUNIĘCIE($A$8:$A$102;0;PODAJ.POZYCJĘ(E2;$A$7:$I$7;0)-1;ILE.NIEPUSTYCH(PRZESUNIĘCIE($A$8:$A$102;0;PODAJ.POZYCJĘ(E2;$A$7:$I$7;0)-1)))



karta developer

Funkcja PODAJ.POZYCJĘ(E2;$A$7:$I$7;0) zwraca pozycję wybranego przez nas wyżej regionu w zakresie a7:I7. I tak np. jeśli wybierzemy Południe, to podaj pozycje zwróci nam wartość 4. Dzięki temu wiemy, że interesująca nas lista wydziałów znajduje się w kolumnie 4, czyli musimy przesunąć zakres $A$8:$A$102 o 3 kolumny (czyli pozycja Południe – 1) w prawo. Właśnie w tym celu zastosowaliśmy funkcję PRZESUNIĘCIE. Powoduje ona, że zakres $A$8:$A$102 jest przesunięty o 0 wierszy w dół i odpowiednią ilość kolumn w prawo (w przypadku południe 3 kolumny). Znów formuła ILE.NIEPUSTYCH zapewnia nam, że na liście nie będą widoczne puste elementy, czyli wielkość zakresu będzie miała tyle wierszy ile jest niepustych komórek. Ostatecznie funkcja PRZESUNIĘCIE powoduje, że w źródle listy wybieralnej odwołujemy się do zakresu $D$8:$D$11.

Mam nadzieję, że to Wam pomogło. Zapraszamy za tydzień po kolejny wpis. 🙂

Plik Excela, w którym zastosowano funkcję PRZESUNIĘCIE znajdziecie tutaj:

Funkcja PRZESUNIĘCIE zwraca odwołanie do zakresu, który jest podaną liczbą wierszy lub kolumn począwszy od komórki lub zakresu komórek. Zwrócone obszar może być zarówno komórką jak i zakresem komórek.

Funkcja PRZESUNIĘCIE potrzebuje następujących parametrów:

odwołanie – komórka lub zakres od którego zaczynamy (od którego będziemy się przesuwać)
wiersze – o ile wierszy chcemy się przesunąć od komórki początkowej (lub odwołania)
kolumny – o ile kolumn chcemy się przesunąć od komórki początkowej (lub odwołania)
wysokość – jaka ma być wysokość zwróconego obszaru w ilości wierszy (możemy zmniejszyć lub zwiększyć zakres podany w odwołaniu) – argument opcjonalny
szerokość – analogicznie jak wysokość – jaka ma być szerokość zwróconego obszaru w ilości wierszy – argument opcjonalny

PRZYKŁAD 1:

Arkusz Przesuniecie1 w pliku do ściągnięcia.

karta developer

W prezentowanym powyżej przykładzie w komórce B6 wpisaliśmy formułę:

= PRZESUNIĘCIE(A2;1;1)

Jako pierwszy argument podajemy odwołanie do komórki A2, jako drugi i trzeci wpisujemy 1, czyli przesuwamy podany w odwołaniu zakres o jeden wiersz w dół i jedną kolumnę w prawo. W konsekwencji formuła zwróci wynik Nowak, czyli odwoła się do wartości znajdującej się w komórce B3. Dzieje się tak ponieważ komórka B3 znajduje się o jeden wiersz w dół i jedną kolumnę w prawo od komórki A2.

PRZYKŁAD 2

W arkuszu Przesuniecie2 tym razem bardziej zaawansowany przykład wykorzystania funkcji PRZESUNIĘCIE, który może być przydatny podczas tworzenia raportów. Za pomocą funkcji PRZESUNIĘCIE możemy stworzyć dynamiczny wykres.

W komórce D2 stworzyliśmy listę wybieralną (DANE/POPRAWNOŚĆ DANYCH), gdzie jako źródło podajemy zakres poszczególnych miesięcy podanych w tabeli.

W D2 wybieramy odpowiedni miesiąc, dla którego chcemy zobaczyć dane i odpowiedni wykres.

karta developer

Jednak najważniejsza w naszym przykładzie jest funkcja PRZESUNIĘCIE, która jest wykorzystana w zakresie B5:B11.

=PRZESUNIĘCIE($D$31:$D$37;0;MIESIĄC($D$2))

W pierwszym argumencie podaliśmy zakres $D$31:$D$37, czyli listę nazwisk (pierwsza kolumna w tabeli danych), drugi argument jest równy 0, ponieważ nie chcemy przesuwać zakresu w dół a jedynie w prawo, aby znaleźć dane na odpowiedni miesiąc. Jako argument 3 podaliśmy funkcję Miesiąc, która zwraca numer miesiąca, który wybraliśmy z listy wybieralnej znajdującej się w komórce D2. Jeżeli wybierzemy styczeń 15, to funkcja miesiąc zwraca 1, jak marzec 15 to funkcja miesiąc zwraca 3 itd. W związku z tym w trzecim argumencie funkcji PRZESUNIĘCIE mamy wartość zależną od wybranego przez nas miesiąca, i tak jeśli wybierzemy styczeń to przesuniemy się o 1 kolumnę w prawo i odwołamy się do zakresu $E$31:$E$37, czyli do wartości ze stycznia.

Co ważne formuła jest formuła tablicową. W praktyce od zwykłej formuły różni się to tylko tym, że przed wpisaniem formuły zaznaczamy cały zakres D5:D11 a nie jedną komórkę, a przy zatwierdzaniu funkcji wciskamy Ctrl + Shift + Enter zamiast samego Entera.

W ten sposób w komórkach D5:D11 uzyskujemy odpowiednie wartości dla wybranego przez nas miesiąca.

Stworzony przez nas wykres odwołuje się do danych z zakresu C5:D11, dzięki czemu osiągnęliśmy dynamiczny wykres, który zmienia się gdy zmienimy miesiąc w komórce D2

Dla zainteresowanych w arkuszu Przesuniecie + Sortowanie znajduje się przykład gdzie dane są automatycznie sortowane

Czasami pojawiały się pytania o funkcję „oczyszczającą”, która w tekście zostawiałaby wskazane przez nas znaki a pozostałe usuwała. Po tych pytaniach postanowiliśmy umieścić taką funkcję na blogu, aby ułatwić Wam pracę.:)

Przedstawiona poniżej funkcja pozostawia w tekście znaki, które są wskazane w drugim argumencie funkcji. Jako argument możemy podać ciąg znaków i tak np. jeśli wpiszemy poniższą formułę:

=Pozostaw(„Ala ma kota!”; „ma „)

to funkcja usunie wszystkie znaki poza m, a, oraz spacją. Wynikiem działania będzie „Aa ma a”


Function Pozostaw(PrzeszukiwanyTekst As String, Znak As String) As String
    'Funkcja pozostawia w tekście znaki, które są podane w drugim argumencie
    Dim Z As String
    Dim i As Long
    Dim NumerZnaku As Long
    For i = 1 To Len(PrzeszukiwanyTekst)
        NumerZnaku = NumerZnaku + 1
        Z = Mid(PrzeszukiwanyTekst, NumerZnaku, 1)
        If Not UCase(Znak) Like "*" & UCase(Z) & "*" And Z <> "" Then
            PrzeszukiwanyTekst = Replace(PrzeszukiwanyTekst, Z, "")
            NumerZnaku = NumerZnaku - 1
        End If
    
    Next
    
    Pozostaw = PrzeszukiwanyTekst
End Function

Jeśli nie wiesz jak wkleić powyższą funkcję to zapraszamy do wpisu jak zacząć pierwsze makro.
Wystarczy skopiować i wkleić powyższy kod a już będziemy mogli korzystać z formuły Podstaw.


Inne przykłady wykorzystania funkcji Pozostaw

Funkcja pozostaw może nam się np. przydać jeśli mamy podany serię i numer dowodu, a chcemy je oddzielić.
Przykładowo: ABS123456

Chcemy jednak wyciągnąć tylko cyfry z podanego ciągu. Wystarczy, że wywołamy funkcję

Pozostaw(„ABS123456″;”0123456789”)

Oczywiście jako argumenty funkcji możemy podawać odwołania do komórek.

Omówimy sposób pobierania nazwy pliku ze ścieżki z wykorzystaniem różnych opcji.

W ostatnim wpisie poruszyliśmy kwestię pobierania ścieżki za pomocą metod: GetOpenFilename oraz GetSaveAsFilename, ale co zrobić jeśli następnie chcemy pobrać samą nazwę pliku?

Link do poprzedniego wpisu: http://heap.pl/pobieranie-sciezki-pliku/

Istnieje kilka możliwości, aby pobrać nazwę pliku ze ścieżki. Proponowane przeze mnie w tym wpisie to:

1) użycie funkcji InStrRev, wyszukanie za jej pomocą ukośnika (ostatniego od prawej strony)
2) wykorzystanie funkcji Split (podział po ukośnikach) a następnie znalezienie ostatniego elementu
3) użycie funkcji DIR

W naszym przykładzie wpisaliśmy ścieżkę do pliku postaci: „C:\Users\Documents\makro\makro.xlsx”.


1) – użycie funkcji InStrRev

Sub PobierzNazwePliku()
    Dim NazwaPliku As String
    Dim Sciezka As String
    Sciezka = "C:\Users\Documents\makro\makro.xlsx"
    NazwaPliku = Right(Sciezka, Len(Sciezka) - InStrRev(Sciezka, "\"))
End Sub

Funkcja Right() zwraca określoną liczbę znaków od końca ciągu tekstowego. Jako argument podajemy ścieżkę, oraz wykorzystujemy funkcję InStrRev, aby określić liczbę znaków od prawej strony. InStrRev zwraca pozycję na której znajduje się ostatni ukośnik.

Jeśli chcemy usunąć rozszerzenie, to wystarczy użyć funkcji Mid()

Sub PobierzNazwePliku()
    Dim NazwaPliku As String
    Dim Sciezka As String
    Sciezka = "C:\Users\Documents\makro\makro.xlsx"
    NazwaPliku = Mid(Sciezka, InStrRev(Sciezka, "\") + 1, InStrRev(Sciezka, ".") - InStrRev(Sciezka, "\") - 1)
End Sub


opcja 2) – wykorzystanie funkcji Split

Funkcja VBA Split jest funkcją rozdzielającą łańcuch znaków.

Argumenty wejściowe Split() to:

  • Expression – wyrażenie do rozdzielenia
  • Delimiter – separator – znak rozdzielający (argument opcjonalny, wartość domyślna to spacja).
  • Limit – ilość pojedynczych łańcuchów wydzielonych z wyrażenia. Argument opcjonalny (wartość domyślna -1, zwracając wszystkie).
  • Compare – tryb porównania ciągów. Dla porównania tekstowego – wartość 1. (dla wartości domyślnej 0 – porównanie binarne).

Funkcja zwraca wartość tablicową. Poszczególne elementy tablicy indeksowane są począwszy od wartości. Podobny efekt jak funkcja

Zwracana przez funkcję Split wartość jest wartością tablicową. Poszczególne elementy są indeksowane począwszy od wartości zero.

Sub PobierzNazwePliku()
    Dim NazwaPliku As String
    Dim TabElementy() As String
    Dim Sciezka As String
    Sciezka = "C:\Users\Documents\makro\makro.xlsx"
    
    TabElementy() = Split(Sciezka, "\")
    NazwaPliku = TabElementy(UBound(TabElementy()))
End Sub

W powyższym przykładzie funkcja split() rozdziela ścieżkę po ukośniku. W efekcie zwraca nam tablicę, gdzie ostatnim elementem jest nazwa pliku wraz z rozszerzeniem. Dlatego użyliśmy polecenia Ubound() aby sprawdzić wielkość tablicy i zwrócić jej ostatni element.


opcja 3) – użycie funkcji DIR.

Jest jedną z bardziej popularnych metod, ale nie jest to wcale najlepszy sposób. Dlaczego?
1. Działa wolno, bo zawsze przeszukuje dysk.
2. Plik musi istnieć, aby działała poprawnie.
3. Jest niestabilna (jeśli tworzymy pętlę po wszystkich plikach danego folderu, to takie wywołanie dir zniszczy tę pętle)
4. Nie zadziała poprawnie jeśli plik jest ukryty

Jako argument funkcji Dir wpisujemy ścieżkę do pliku.
Przykład:

Sub PobierzNazwePliku()
    Dim NazwaPliku As String
    NazwaPliku = Dir("C:\Users\Documents\makro\makro.xlsx")
End Sub

Co ważne polecenie Dir zwróci nazwę pliku wraz z rozszerzeniem tylko wtedy gdy plik istnieje w podanym katalogu. Dlatego aby powyższy przykład zadziałał musimy najpierw stworzyć plik makro.xlsx w katalogu makro (C:\Users\Documents\makro\). Wtedy funkcja zwróci łańcuch marko.xlsx. Jeżeli plik nie istnieje lub w podanej ścieżce jest błąd to funkcja zwróci pusty łańcuch znaków.

Przykład z poprzedniego wpisu
Link do poprzedniego wpisu: http://heap.pl/pobieranie-sciezki-pliku/

Poniżej prezentujemy przykład z poprzedniego wpisu, ale tym razem dodamy linijkę gdzie wykorzystany będzie sposób 1 aby pobrać nazwę pliku.

Sub PobierzNazwePliku4()
    Dim NazwaPliku As Variant
    Dim FileFilter  As String
    Dim FilterIndex  As Integer
    Dim Title As String
    
    FileFilter = "Pliki Excel (*.xlsx;*.xls),*xlsx;*.xls," & _
    "Pliki Excel XLSX (*.xlsx),*.xlsx," & _
    "Pliki Excel 97-2003 (*.xls),*.xls," & _
    "Wszystkie pliki (*.*),*.*"
    
    FilterIndex = 4 'Wybieramy domyślny filtr. 4 oznacza że domyślnie wybieramy filtr Wszystkie pliki (*.*)
    Title = "Otwórz plik Excela"
    
    NazwaPliku = Application.GetOpenFilename(FileFilter, FilterIndex, Title)
    
    'Wyświetlenie  MessageBox, jeśli w oknie dialogowym zostanie naciśnięty przycisk Anuluj
    If NazwaPliku = False Then
        MsgBox "Nie wybrano żadnego plikU."
        Exit Sub
    End If
    
    NazwaPliku = Right(NazwaPliku, Len(NazwaPliku) - InStrRev(NazwaPliku, "\"))
End Sub

To tyle na dziś. 🙂

Layout mode
Predefined Skins
Custom Colors
Choose your skin color
Patterns Background
Images Background

Zapisz się na nasz newsletter dotyczący nauki Excela

Wysyłaj mi newsletter (możesz się wypisać w każdym dowolnym momencie).

FreshMail e-mail marketing free online marketing software