Najbardziej przydatne metody obiektu Range

HintMine 2021-09-15

Trochę mi zajęło zabranie się do tego wszystkiego ale jak to się mówi lepiej późno niż wcale 😉

Czego dowiesz się z poniższego artykułu:

1. Co to jest metoda / funkcja wbudowana?

Pisząc kod makra w VBA, na pewno zetknęliście się z metodami, choć niekoniecznie musieliście wiedzieć że to akurat tak właśnie się nazywa. Chodzi o te elementy z zielonymi ikonkami zwane też potocznie funkcjami.

Czy możemy w takim razie używać nazwy metoda i funkcja zamiennie? Jasne że tak, choć słowo metoda odnosi się bardziej do tych funkcji „wbudowanych” w obiekty Excela czy VBA ale na tym etapie będę używał tych nazw zamiennie :)

Oczywiście nieco później gdy nauczysz się więcej o klasach, bibliotekach, obiektach itd. będzie można rozgraniczyć, że metoda działa jedynie w obrębie konkretnego obiektu i bla bla bla… ale w tym momencie nie ma to dla Ciebie kompletnie żadnego znaczenia. Czyli na ten moment naszej wiedzy metoda i funkcja to to samo :)

metody obiektu Range w makrach VBA

Jeżeli chcesz się dowiedzieć jak napisać swoją własną funkcję, zapraszam do osobnego posta tutaj. W tym artykule mam się skupić na tym, czym są metody obiektu Range, czyli tak na prawdę funkcje wbudowane w obiekt Range.

Wiemy już, że zielona ikonka w kodzie VBA oznacza taką właśnie wbudowaną funkcję, która działa podobnie tak jak formuła w Excelu. Najważniejsze jest to, że jej głównym zadaniem jest wykonywanie jakiejś czynności np.: zaznaczenia, skopiowania, wklejania, wyświetlenia komunikatu itd.

Różnica między Excelem a kodem jest taka, że w Excelu pisanie formuły zaczynasz od znaku „=” w komórce, natomiast w makrze najpierw podajemy nazwę obiektu (w naszym wypadku Range), potem naciskamy kropkę i wybieramy z listy potrzebną metodę. Tak jak zdjęciu poniżej:

wybieranie metody podczas pisania makra VBA

Dalsze kroki też są podobne jak w Excelu tj. po nazwie funkcji otwieramy nawias i podajemy w nim wymagane przez tą funkcję parametry oddzielając je przecinkami.

argumenty w metodach makra Visual Basic

Ale uwaga bo nie jest tak zawsze znaczy przecinki są zawsze :) Ale nawiasy już nie…

2. Kiedy dajemy nawiasy a kiedy spacje?

To pytanie bardzo często zadają mi kursanci na szkoleniach VBA :)

W najprostszych słowach: nawias po nazwie funkcji oznacza, że chcę, żeby ta funkcja zwróciła mi wartość, brak nawiasu oznacza, że interesuje mnie tylko wykonanie przez tą funkcję jakiejś czynności.

Yyyy czyli co? Już postaram się wyjaśnić :)

W Excelu, przy używaniu formuły w arkuszu nie ma z tym żadnego problemu. Każda formuła ma nawias po swojej nazwie i koniec. Bo każda formuła w arkuszu zwraca wartość.

W VBA jest inaczej,

nawias trzeba dać gdy używamy metody, która zwraca wartość np. left, right, mid, trim itd.
nawiasu nie dajemy dla metody, która nie zwraca wartości (a jedynie wykonuje czynność) np. kill, FileCopy itd.

Dobrze obrazuje to poniższy przykład, co prawda odnosi się on do kolekcji Workbooks ale fajnie pokazuje to o czym mówię :)

Sub kokoko()
    
    Dim sciezka As String
    
    sciezka = ThisWorkbook.Path & "\plik.xlsx"
    
    Workbooks.Open sciezka
    
End Sub

Sub kokoko()

Dim sciezka As String

sciezka = ThisWorkbook.Path & "\plik.xlsx"

Workbooks.Open sciezka

End Sub

Program kokoko nie ma po zielonej metodzie .Open nawiasu co oznacza, że chcę jedynie wykonać czynność – otworzyć plik. Do dalszego korzystania z tego otwartego pliku będę musiał używać obiektu ActiveWorkbook (np. ActiveWorkbook.Close)

Z kolei ten kod robi dokładnie to samo ale zwróćcie uwagę na nawiasy po .Open oraz deklarację zmiennej moj_plik as Workbook

Sub mumumu()
    
    Dim sciezka As String
    Dim moj_plik As Workbook
    
    sciezka = ThisWorkbook.Path & "\plik.xlsx"
    
    Set moj_plik = Workbooks.Open(sciezka)
    
    moj_plik.Close
    
End Sub

Sub mumumu()

Dim sciezka As String

Dim moj_plik As Workbook

sciezka = ThisWorkbook.Path & "\plik.xlsx"

Set moj_plik = Workbooks.Open(sciezka)

moj_plik.Close

End Sub

Dzięki nawiasom po metodzie .Open moja metoda nie tylko wykona czynność polegającą na otwarciu pliku ale dodatkowo zwróci wartość, którą będzie cały ten otwarty plik i przypisze mi go do zmiennej moj_plik. To z kolei sprawi, że dalej w kodzie mogę się odnosić do tego otwartego pliku nie przez ActiveWorkbook tylko przez zmienną moj_plik.

Jeżeli dalej macie mętlik w głowie, możecie kierować się zasadą, że jeżeli daliście nawias, a nie powinno go być, zobaczycie na ekranie taki komunikat błędu. Najczęściej jest to informacja, że wystarczy skasować nawias i będzie OK :)

vba błąd "expected ="

Czyli jak już wszystko (mam nadzieję) rozumiemy z tymi nawiasami to… trochę jeszcze zamotam ;) Bo… edytor VBA nie czepia się tych nawiasów aż tak bardzo jeżeli metodzie przekazujesz tylko 1 parametr.

Dla sprawdzenia o czym mówię spróbuj skopiować i uruchomić poniższy kod:

Sub bebebe()
    
   'ta linia dziala ok
    MsgBox "czy lubisz placki?"
    
   'ta linia dziala dokladnie tak samo. Jak jest tylko 1 parametr
   'to edytor az tak bardzo sie nie czepia o te nawiasy
    MsgBox ("czy lubisz placki")
    
End Sub

Sub bebebe()

'ta linia dziala ok

MsgBox "czy lubisz placki?"

'ta linia dziala dokladnie tak samo. Jak jest tylko 1 parametr

'to edytor az tak bardzo sie nie czepia o te nawiasy

MsgBox ("czy lubisz placki")

End Sub

A teraz spróbuj uruchomić ten program:

Sub kwakwakwa()
    
   'ta linia nadal dziala ok, bo MsgBox TYLKO wykonuje czynnosc
   'czyli wyswietla komunikat na ekranie i nie ma dla niego znaczenia
   'w ktory guzik klikniesz
    MsgBox "czy lubisz placki?", vbYesNo
    
   'ta linia zwraca blad, bo dodajac nawiasy chcesz, zeby MsgBox
   'zwrocil Ci wartosc kliknietego przycisku (6 dla "Yes" lub 7 dla "No")
    MsgBox ("czy lubisz placki?",vbYesNo)
    
End Sub

Sub kwakwakwa()

'ta linia nadal dziala ok, bo MsgBox TYLKO wykonuje czynnosc

'czyli wyswietla komunikat na ekranie i nie ma dla niego znaczenia

'w ktory guzik klikniesz

MsgBox "czy lubisz placki?", vbYesNo

'ta linia zwraca blad, bo dodajac nawiasy chcesz, zeby MsgBox

'zwrocil Ci wartosc kliknietego przycisku (6 dla "Yes" lub 7 dla "No")

MsgBox ("czy lubisz placki?",vbYesNo)

End Sub

Jak widzisz przy więcej niż 1 parametrze i nawiasach, edytor wyrzuca błąd :)

Żeby ten błąd poprawić dla drugiego MsgBoxa powinniśmy dopisać coś takiego:

Sub chrumchrumchrum()
    
    Dim odpowiedz As Integer
    
   'teraz jest jak trzeba, bo MsgBox ma zmienna, do ktorej
   'moze mi zwrocic wartosc kliknietego przycisku (6 dla "Yes" lub 7 dla "No")
    odpowiedz = MsgBox("czy lubisz placki?", vbYesNo)
    
End Sub

Sub chrumchrumchrum()

Dim odpowiedz As Integer

'teraz jest jak trzeba, bo MsgBox ma zmienna, do ktorej

'moze mi zwrocic wartosc kliknietego przycisku (6 dla "Yes" lub 7 dla "No")

odpowiedz = MsgBox("czy lubisz placki?", vbYesNo)

End Sub

3. Najważniejsze i najczęściej stosowane metody obiektu Range

Omówienie najważniejszych funkcji wbudowanych w obiekt Range jest o tyle trudne, że po pierwsze jest ich na prawdę sporo a po drugie co to znaczy najważniejszych? Dla każdego pewnie coś innego. Dlatego poniższe zestawienie jest takim moim wyborem, elementów z których korzystam najczęściej :)

Każdą metodę starałem się omówić jak najbardziej ludzkim językiem i najkrócej jak potrafię :)

Polecam też zerknąć na artykuł opisujący obiekt Range i jego właściwości który znajduje się tutaj

Dla ułatwienia w poruszaniu się po opisach, przygotowałem spis treści. Nie przerażamy się ilością tekstu ;) Tego nie czytacie jak powieść, bardziej korzystamy z tego jak ze słownika.

No to jedziemy :)

Activate

Select i Activate to ciekawe metody o których kiedyś napiszę w ogóle osobny artykuł. Ale póki co wystarczy zapamiętać, że Activate „kliknie” nam w jedną komórkę ale… TYLKO NA aktywnym arkuszu a co w takim razie robi select? Zobaczcie poniżej tutaj :)

Czyli taki kod zadziała:

Sub Activate_v1()
    Range("A1").Activate
End Sub

Sub Activate_v1()

Range("A1").Activate

End Sub

A w sytuacji, gdy mamy w naszym pliku arkusze o nazwie „Arkusz1” oraz „Arkusz2” i aktywnym arkuszem jest „Arkusz2”, to taki kod nie zadziała:

Sub Activate_v2()
    Sheets("Arkusz1").Range("A1").Activate
End Sub

Sub Activate_v2()

Sheets("Arkusz1").Range("A1").Activate

End Sub

A w tej samej sytuacji to makro będzie działać poprawnie :)

Sub Activate_v3()
    Sheets("Arkusz2").Range("A1").Activate
End Sub

Sub Activate_v3()

Sheets("Arkusz2").Range("A1").Activate

End Sub

AddComment

Dodaje komentarz do jednej komórki czyli program poniżej wykona się poprawnie:

Sub AddComment_v1()
    
    Range("A1").AddComment "jakis tam komentarz"
    
End Sub

Sub AddComment_v1()

Range("A1").AddComment "jakis tam komentarz"

End Sub

ale ten już nie:

Sub AddComment_v1()
    
    Range("A1:A10").AddComment "jakis tam komentarz"
    
End Sub

Sub AddComment_v1()

Range("A1:A10").AddComment "jakis tam komentarz"

End Sub

AdvancedFilter

Bardzo przydatna i niedoceniana metoda. Używam jej głównie do szybkiego (jedną linią) generowania listy unikatowych elementów z jakiegoś zakresu.

Poniższy kod stworzy nam listę unikatowych elementów z kol A i wypisze ją w kolumnie C począwszy od komórki C1 (pamiętajcie, pierwszy wiersz kolumny A jest traktowany jako nagłówek a nie jako część danych)

Sub AdvancedFilter_v1()
    Range("a:a").AdvancedFilter xlFilterCopy, , Range("c1"), True
End Sub

Sub AdvancedFilter_v1()

Range("a:a").AdvancedFilter xlFilterCopy, , Range("c1"), True

End Sub

AutoFill

Pozwala uzyskać z poziomu kodu, funkcjonalność jaką w Excelu mamy podczas przeciągania komórki ze zwiększaniem wpisanej w nią wartości.

Czyli taki program wpisze słowo „Kolumna1” w komórce A1 a następnie „przeciągnie” to słowo do kolejnych kolumn, zwiększając jednocześnie numer przy słowie kolumna (czyli w B1 będzie „Kolumna2”, w C1 „Kolumna3” itd.)

Sub AutoFill_v1()
   
    Range("A1") = "Kolumna1" 'wpisz pierwszy naglowek w komorke A1
    Range("A1").AutoFill Range("A1:D1") 'wypelnij naglowki w kolejnych kolumnach
   
End Sub

Sub AutoFill_v1()

Range("A1") = "Kolumna1" 'wpisz pierwszy naglowek w komorke A1

Range("A1").AutoFill Range("A1:D1") 'wypelnij naglowki w kolejnych kolumnach

End Sub

AutoFilter

To „zwykły” filtr czyli ten „lejek”, którego na co dzień używamy w Excelu :) Super przydatny, tylko trzeba pamiętać o kilku rzeczach:
– filtry nakładają się na siebie, czyli zanim zaczniesz filtrować wyczyść wyniki poprzednich filtrowań
– pierwszy argument tej metody oznacza numer kolumny w zakresie który chcemy filtrować (czyli 1 w moim przykładzie poniżej, nie oznacza kolumny A tylko kolumnę D :)

przykład:

Sub AutoFilter_v1()
    Range("d:h").AutoFilter 'najpierw czyszcze wyniki poprzednich filtrowan
    Range("d:h").AutoFilter 1, ">=2021-01-01", xlAnd, "<=2021-12-31" 'i dopiero teraz filtruje wszystkie daty z roku 2021
End Sub

Sub AutoFilter_v1()

Range("d:h").AutoFilter 'najpierw czyszcze wyniki poprzednich filtrowan

Range("d:h").AutoFilter 1, ">=2021-01-01", xlAnd, "<=2021-12-31" 'i dopiero teraz filtruje wszystkie daty z roku 2021

End Sub

AutoFit

Dopasowuje szerokość kolumny do zawartości komórki. Najczęściej używam tego w połączeniu z .EntireColumn

Sub AutoFit_v1()
    Range("D1").EntireColumn.AutoFit
End Sub

Sub AutoFit_v1()

Range("D1").EntireColumn.AutoFit

End Sub

BorderAround

Bardzo fajna metoda, pozwalająca na ustawienie obramowania dla komórki lub zakresu za pomocą tylko jednej linii. Kto kiedykolwiek nagrywał ustawianie obramowania komórki wie jak ogromna ilość kodu się wtedy generuje. Przy pomocy BorderAround możemy zrobić to znacznie prościej.

Pamiętajcie że w argumencie ColorIndex nie jesteście ograniczeni do tylko tych dwóch pojawiających się opcji ale możecie wpisać tam dowolny nr koloru z palety ColorIndex (czyli liczbę od 1 do 56).

Czyli taki kod obramuje komórkę D2 i kolor obramowania ustawi na czerwony

Sub BorderAround_v1()
    Range("D1").BorderAround , xlMedium, 3
End Sub

Sub BorderAround_v1()

Range("D1").BorderAround , xlMedium, 3

End Sub

Z kolei ten program wklejony w kodzie arkusza sprawi że kliknięcie w dowolną komórkę tego arkusza obramuje/zaznaczy cały wiersz w którym ta komórka jest na niebiesko:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Cells.Borders.ColorIndex = xlNone 'czyszcze poprzednie obramowania
    Target.EntireRow.BorderAround , xlMedium, 5
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Cells.Borders.ColorIndex = xlNone 'czyszcze poprzednie obramowania

Target.EntireRow.BorderAround , xlMedium, 5

End Sub

Clear

Czyści komórkę nie tylko z zawartości ale również formatowania, reguł poprawności, obramowań itd.

Sub Clear_v1()
    Range("D5").Clear
End Sub

Sub Clear_v1()

Range("D5").Clear

End Sub

ClearComments

Jak sama nazwa wskazuje, czyści/kasuje tylko komentarze w danej komórce. Jeżeli komentarzy nie ma, metoda ClearComments nie zwraca błędu.

Sub ClearComments_v1()
    Range("D5").ClearComments
End Sub

Sub ClearComments_v1()

Range("D5").ClearComments

End Sub

ClearContents

Czyści zawartość komórki. Jeżeli komórka jest pusta, metoda ClearContents nie zwraca błędu.

Sub ClearContents_v1()
    Range("D5").ClearContents
End Sub

Sub ClearContents_v1()

Range("D5").ClearContents

End Sub

ClearFormats

Kasuje z komórki jedynie formatowanie.

Sub ClearFormats_v1()
    Range("D5").ClearFormats
End Sub

Sub ClearFormats_v1()

Range("D5").ClearFormats

End Sub

ClearHyperlinks

Kasuje link w komórce. Uwaga, może się wydawać, że ta metoda nie działa poprawnie ponieważ po jej zastosowaniu w komórce zostaje tekst linku i jego formatowanie (czyli niebieski tekst z podkreśleniem). Wydaje się więc że link nadal tam jest ale po kliknięciu w tekst okazuje się, że to sam tekst. Czyli metoda działa poprawnie – kasuje link.

Sub ClearHyperlinks_v1()
    Range("D5").ClearHyperlinks
End Sub

Sub ClearHyperlinks_v1()

Range("D5").ClearHyperlinks

End Sub

ColumnDifferences

Zwraca zakres składający się z wszystkich komórek (w kolumnie aż do ostatniej wypełnionej), które różnią się zawartością od innej podanej komórki tej kolumny.

Czyli żeby przetestować poniższy kod, wpisz jakiekolwiek wartości w kolejnych komórkach kolumny D. Po uruchomieniu programu, zobaczysz, że wszystkie komórki aż do ostatniej wypełnionej, które mają inną wartość niż komórka D1, są zaznaczone.

Sub ColumnDifferences_v1()
    Range("A1") = "bocian"
    Range("A2") = ""
    Range("A3") = "byk"
    Range("A4") = "baran"
    
    Range("A1").EntireColumn.ColumnDifferences(Range("A1")).Select
End Sub

Sub ColumnDifferences_v1()

Range("A1") = "bocian"

Range("A2") = ""

Range("A3") = "byk"

Range("A4") = "baran"

Range("A1").EntireColumn.ColumnDifferences(Range("A1")).Select

End Sub

Copy

Kopiowanie danej komórki lub zakresu do schowka. Najczęściej używam tej metody w połączeniu z .CurrentRegion. Czyli poniższe makro skopiuje wszystkie przylegające do siebie komórki począwszy od A1 (czyli tak jakby ustawi się w komórce A1 a następnie zrobi ctrl+a i ctrl+c)

Sub CurrentRegion_v1()
    Range("A1").CurrentRegion.Copy
End Sub

Sub CurrentRegion_v1()

Range("A1").CurrentRegion.Copy

End Sub

CopyFromRecordset

Rewelacyjna metoda pozwalająca na pobieranie do komórki wyniku zapytania SQLowego wykonanego z poziomu makra VBA. Warto pamiętać, że CopyFromRecordset nie zaczytuje nagłówków, trzeba je sobie pobrać z wyniku zapytania osobą pętlą.

Sub CopyFromRecordset_v1()
    Dim polaczenie As Object
    Dim zapytanie As Object
    Dim sciezka_do_pliku_bazy_danych As String
    
    sciezka_do_pliku_bazy_danych = ThisWorkbook.Path & "\baza.xlsx"
    
    Set polaczenie = CreateObject("ADODB.Connection")
    Set zapytanie = CreateObject("ADODB.Recordset")
    
polaczenie.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sciezka_do_pliku_bazy_danych & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    
    zapytanie.Open "SELECT * FROM [Sheet1$]", polaczenie
    Range("A2").CopyFromRecordset zapytanie 'Range("A2") a nie A1 bo CopyFromRecordset nie zwraca naglowkow wiec
                                            'zostawiam sobie miejce na naglowki w pierwszym wierszu
    
polaczenie.Close
End Sub

Sub CopyFromRecordset_v1()

Dim polaczenie As Object

Dim zapytanie As Object

Dim sciezka_do_pliku_bazy_danych As String

sciezka_do_pliku_bazy_danych = ThisWorkbook.Path & "\baza.xlsx"

Set polaczenie = CreateObject("ADODB.Connection")

Set zapytanie = CreateObject("ADODB.Recordset")

polaczenie.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sciezka_do_pliku_bazy_danych & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

zapytanie.Open "SELECT * FROM [Sheet1$]", polaczenie

Range("A2").CopyFromRecordset zapytanie 'Range("A2") a nie A1 bo CopyFromRecordset nie zwraca naglowkow wiec

'zostawiam sobie miejce na naglowki w pierwszym wierszu

polaczenie.Close

End Sub

CopyPicture

Kopiuje komórkę lub zakres do schowka jako zdjęcie, które możemy później wklejać w Paincie, Wordzie, PowerPoincie itd.

Sub CopyPicture_v1()
    Range("A1:D10").CopyPicture xlScreen, xlBitmap
End Sub

Sub CopyPicture_v1()

Range("A1:D10").CopyPicture xlScreen, xlBitmap

End Sub

Cut

Wycina komórkę lub zakres (wartości są wrzucane do schowka)

Sub Cut_v1()
    Range("A1:D10").Cut
End Sub

Sub Cut_v1()

Range("A1:D10").Cut

End Sub

DataSeries

Pozwala na wypełnienie „serią danych” komórek w podanym zakresie. Np. kod poniżej wpisze nam w komórce A1 datę a następnie wypełni zakres kolejnych 11 komórek w dół datą z tego samego dnia ale w kolejnym miesiącu (a jak miesiąc przekroczy grudzień to i z kolejnego roku).

Sub DataSeries_v1()

Range("A1") = #9/15/2021# 'ja tutaj wpisalem date "normalnie" :) czyli 2021-09-15
                                  'tylko jak sie "ubiera" daty w # # to VBA sam zamienia na taki swoj format

Range("A1:A12").DataSeries , xlChronological, xlMonth

End Sub

Sub DataSeries_v1()

Range("A1") = #9/15/2021# 'ja tutaj wpisalem date "normalnie" :) czyli 2021-09-15

'tylko jak sie "ubiera" daty w # # to VBA sam zamienia na taki swoj format

Range("A1:A12").DataSeries , xlChronological, xlMonth

End Sub

Dirty

Wymusza przeliczenie formuły w podanej komórce lub zakresie

Sub Dirty_v1()

Range("A1") = "=RANDBETWEEN(1,100)"
Range("A1").Dirty

End Sub

Sub Dirty_v1()

Range("A1") = "=RANDBETWEEN(1,100)"

Range("A1").Dirty

End Sub

Find

Ogromnie przydatne, jest to w okienko, które w Excelu pokazuje się po naciśnięciu ctrl+f Mega przydatne w kodzie! :)

Tylko ważna rzecz:
pamiętajcie, że używając w kodzie jakiegokolwiek okna Excela, które ma swoje opcje i różne ustawienia, nigdy nie możecie być pewni jak te opcje użytkownik sobie poprzestawiał. Dlatego w kodzie zawsze ustawiamy wszystkie potrzebne nam opcje okna, na wartości które są nam potrzebne.

Metoda Find jako wynik zwraca komórkę (czyli Range), w której znajduje się nasza szukana wartość a my możemy sobie potem z tej komórki wyciągnąć interesujące nas informacje (np. nr kolumny)

Np poniższy przykład znajdzie nam w całym pierwszym wierszu arkusza kolumnę która ma w nagłówku słowo „ilość” szukając go w „wartościach” (xlValues) z dopasowaniem „dokładnym” (xlWhole) i nie zwracając uwagi na wielkość znaków (False)

Sub Find_v1()

Dim znaleziona_komorka As Range

Set znaleziona_komorka = Range("A1").EntireRow.Find("ilość", , xlValues, xlWhole, , , False)

If znaleziona_komorka Is Nothing Then
   MsgBox "nie ma"
Else
   MsgBox "znalazlem w kolumnie " & znaleziona_komorka.Column
End If

End Sub

Sub Find_v1()

Dim znaleziona_komorka As Range

Set znaleziona_komorka = Range("A1").EntireRow.Find("ilość", , xlValues, xlWhole, , , False)

If znaleziona_komorka Is Nothing Then

MsgBox "nie ma"

Else

MsgBox "znalazlem w kolumnie " & znaleziona_komorka.Column

End If

End Sub

Insert

Pozwala na dodawanie komórek i przesuwa pozostałe komórki w dół lub w prawo :) To trochę tak jakby dodać wiersz, tylko że nie cały :)

Poniższy kod dodaje jedną komórkę nad komórką A1 a to co było w komórce A1 przesuwa w dół.

Sub Insert_v1()

Range("A1").Insert xlShiftDown

End Sub

Sub Insert_v1()

Range("A1").Insert xlShiftDown

End Sub

Merge

Pozwala nam zrobić najgorszą możliwą rzecz w Excelu czyli… scalić komórki. Jak ktoś czytał „Boską komedię” to pamięta, że ludzie którzy scalają komórki w Excelu mają swój własny krąg piekieł. Gorsi od nich są tylko Ci, którzy wklejają do maila screeny z danymi zamiast tabelki, z której można by skopiować dane ;)

No ale niech będzie, taki kod scali wam komórki w arkuszu (ale jakby ktoś pytał to, ja go Wam nie pokazałem ;)

Sub Merge_v1()

Range("A1:A2").Merge

End Sub

Sub Merge_v1()

Range("A1:A2").Merge

End Sub

PasteSpecial

Kolejna metoda, której używam praktycznie co dziennie. Obiekt Range nie ma on wbudowanej metody Paste (ma ją Worksheet) dlatego jak chcemy wklejać coś bezpośrednio w konkretną komórkę używamy właśnie PasteSpecial dodatkową zaletą tej metody jest to, że możemy wklejać np. tylko wartości lub tylko formuły lub formatowanie lub co tam chcemy z listy dostępnych opcji.

Poniższy kod wpisze formułę w komórce A1, następnie skopiuje ją i wklei same wartości w komórce B1

Sub PasteSpecial_v1()

Range("A1") = "=3+4" 'wpisz formule w komorce A1
Range("A1").Copy    'skopiuj ja
Range("B1").PasteSpecial xlPasteValues 'wklej wartosci do komorki B1

End Sub

Sub PasteSpecial_v1()

Range("A1") = "=3+4" 'wpisz formule w komorce A1

Range("A1").Copy 'skopiuj ja

Range("B1").PasteSpecial xlPasteValues 'wklej wartosci do komorki B1

End Sub

RemoveDuplicates

Znane z Excela, cenione, lubiane narzędzie do usuwania duplikatów :)

Sub RemoveDuplicates_v1()

    Range("a1").EntireColumn.RemoveDuplicates 1, xlYes

End Sub

Sub RemoveDuplicates_v1()

Range("a1").EntireColumn.RemoveDuplicates 1, xlYes

End Sub

Replace

Kolejna przydatna metoda, w Excelu dostępna jako okienko ctrl+h czyli znajdź i zamień. Możemy poustawiać w nim ciekawe opcje, np. żeby zamienił nam tylko pewien fragment tekstu w komórce na jakiś inny tekst.

Wykonaj poniższy kod krokowo:

Sub Replace_v1()
    
    Range("a1") = "Ala ma kota"
    Range("a1").Replace "ta", "nia", xlPart, , False

End Sub

Sub Replace_v1()

Range("a1") = "Ala ma kota"

Range("a1").Replace "ta", "nia", xlPart, , False

End Sub

RowDifferences

To samo co ColumnDifferences tylko działa dla wierszy. Czyli zwraca zakres składający się z wszystkich komórek (w wierszu) które różnią się zawartością od innej podanej komórki tego wiersza.

Sub RowDifferences_v1()
    Range("A1") = "kot"
    Range("B1") = "kura"
    Range("C1") = ""
    Range("D1") = "kogut"
    
    Range("A1").EntireRow.RowDifferences(Range("A1")).Select
End Sub

Sub RowDifferences_v1()

Range("A1") = "kot"

Range("B1") = "kura"

Range("C1") = ""

Range("D1") = "kogut"

Range("A1").EntireRow.RowDifferences(Range("A1")).Select

End Sub

Select

Zaznacza komórkę lub zakres komórek. Czyli inaczej niż w przypadku Activate (który z założenia ma zaznaczać jedną komórkę)

Sub Select_v1()
   
    Range("A1").Select
   
End Sub

Sub Select_v1()

Range("A1").Select

End Sub

Show

Przenosi ekran Excela w taki sposób żebyśmy mogli zobaczyć dany zakres czy komórkę ale uwaga nie zaznacza jej. Czyli tylko tak jakby scrolluje nam ekran tak żebyśmy widzieli na nim podaną komórkę

Sub Show_v1()

    Range("iv5567").Show

End Sub

Sub Show_v1()

Range("iv5567").Show

End Sub

Sort

Jak sama nazwa wskazuje służy do szybkiego posortowania zakresu (ale po max 3 kryteriach).

Warto pamiętać żeby podać wartość parametru Header, bo bez tego czasem może się zdarzyć, że nagłówki naszego zakresu zostaną wyrzucone gdzieś daleko na dół (metoda Find może je potraktować jako część danych do sortowania). Jeżeli natomiast dorzucimy na końcu linii sortującej parametr Header:=xlYes mamy pewność, że nagłówki zostaną nietknięte w pierwszym wierszu :)

Sub Sort_v1()
    Range("A1") = "Klumna1"             'wpisz naglowek w kolumnie A
    Range("A1").AutoFill Range("A1:D1") 'wypelnij naglowki w kolejnych kolumnach
    Range("A2:D12") = "=RandBetween(1, 100)" 'wypelnij obszar danych losowymi liczbami
    Range("A2:D12").Copy 'skopiuj
    Range("A2:D12").PasteSpecial xlPasteValues 'wklej jako wartosci
    
   'posortuj dane po kolA rosnąco, przy czym 1wszy wiersz jest traktowany jako naglowek
    Range("A1").CurrentRegion.Sort Range("A1"), xlAscending, Header:=xlYes
     
End Sub

Sub Sort_v1()

Range("A1") = "Klumna1" 'wpisz naglowek w kolumnie A

Range("A1").AutoFill Range("A1:D1") 'wypelnij naglowki w kolejnych kolumnach

Range("A2:D12") = "=RandBetween(1, 100)" 'wypelnij obszar danych losowymi liczbami

Range("A2:D12").Copy 'skopiuj

Range("A2:D12").PasteSpecial xlPasteValues 'wklej jako wartosci

'posortuj dane po kolA rosnąco, przy czym 1wszy wiersz jest traktowany jako naglowek

Range("A1").CurrentRegion.Sort Range("A1"), xlAscending, Header:=xlYes

End Sub

Speak

Śmieszna metoda, która sprawia, że zawartość komórki zostanie wypowiedziana na głos przez Wasz komputer :)

Sub Speak_v1()

Range("A1").Speak

End Sub

Sub Speak_v1()

Range("A1").Speak

End Sub

Jeżeli chcecie żeby komp, powiedział dowolny tekst (nawet taki, którego nie ma w komórce) użyjcie metody speak obiektu Speach (z Application) czyli:

Sub Application_Speach_Speak()

    Application.Speech.Speak "lubie placki", True

End Sub

Sub Application_Speach_Speak()

Application.Speech.Speak "lubie placki", True

End Sub

Ustawienia lektora i języka w którym mówi teoretycznie zależy od wersji językowej Waszego Windowsa ale… w kodzie wszystko można zmienić i ustawić ;)

SpecialCells

Czyli Excelowe okienko przejdź do–> specjalnie. Korzystam z tego bardzo często np, żeby pozbyć się pustych wierszy pomiędzy danymi, bo np. SAP wyrzucił dane w jakiś głupawy sposób. Dzięki metodzie SpecialCells mogę sprytnie posprzątać dane jedną linią (bez filtrowania itd. :) )

Sub SpecialCells_v1()

    Range("A1").EntireColumn.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

Sub SpecialCells_v1()

Range("A1").EntireColumn.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

UnMerge

Jeżeli ktoś gdzieś kiedyś w arkuszu użył scalania komórek, możecie łatwo to naprawić właśnie dzięki tej metodzie :)

Np. ten kod rozscali/odscali (nie wiem jak się to poprawnie mówi) wszystkie scalone komórki w całej kolumnie A

Sub UnMerge_v1()

Range("A:A").UnMerge

End Sub

Sub UnMerge_v1()

Range("A:A").UnMerge

End Sub

A jakie są Wasze ulubione metody obiektu Range? Może pominąłem coś z czego często korzystacie? Jeżeli tak napiszcie do mnie to zaktualizuję artykuł :)