Obiekt Range

HintMine 2017-02-21

Na pierwszy ogień weźmy obiekt Range.

W tym poście dowiesz się:

Do czego służy Range?
Jak używać obiektu Range, żeby odwołać się do komórki?
Użycie obiektu Range w pętli
Najczęściej używane właściwości obiektu Range
Najbardziej przydatne metody (funkcje) obiektu Range
(ten temat jest na tyle obszerny, że napisałem o nim osobny artykuł)

1. Do czego służy obiekt Range ?

Obiekt Range służy do odwołania się w kodzie makra do komórki, zakresu komórek, kolumn i wierszy. Moim zdaniem to jeden z najważniejszych obiektów w Excelu.

2. Jak używać obiektu Range, żeby odwołać się do komórki?

Najlepiej pokaże to poniższe makro :)

Sub range_zastosowanie()

'wpisz cyfrę 3 w komórkę A1
 Range("A1") = 3

'wpisz cyfrę 3 każdej komórce zakresu od A1 do B10
 Range("A1:B10") = 3

'wpisz cyfrę 3 w każdej komórce zakresów od A1 do B10 i D2 do F5 i J1 do K20
 Range("A1:B10, D2:F5, J1:K20") = 3

'wpisz cyfrę 3 w całej kolumnie A (czyli w 1048576 komórkach)
 Range("A:A") = 3

'zaznacz wszystkie komórki "przylegające" tutaj najlepiej opisze to obrazek w dalszej części artykułu
 Range("A1").CurrentRegion.Select

'Range może być również przedstawiony za pomocą 2 komórek cells() wtedy
'pierwsza komórka jest lewym górnym a druga prawym dolnym rogu zakresu
'wpisz cyfrę 3 każdej komórce zakresu od A1 do J10
 Range(Cells(1, 1), Cells(10, 10)) = 3

'moje ulubione zastosowanie Range tzn. w połączeniu z funkcją SpecialCells
'pokolorujemy wszystkie komórki w zakresie A1:J10, które zawierają formułę na żółto
'najpierw wypełnimy zakres komórek formułą
 Range("A1:J10").Formula = "=1+1"

'teraz zamiast części formuł wpiszemy wartości
 Range("C1:H2, C9:H10,A3:B8,i3:j8")=2

'i wreszcie linia kodu o którą od początku chodziło tj. wyszukamy bez żadnej pętli
'tylko te komórki w zakresie, które zawierają formuły
 Range("A1:J10").SpecialCells(xlCellTypeFormulas).Interior.ColorIndex=6

End Sub

Sub range_zastosowanie()

'wpisz cyfrę 3 w komórkę A1

Range("A1") = 3

'wpisz cyfrę 3 każdej komórce zakresu od A1 do B10

Range("A1:B10") = 3

'wpisz cyfrę 3 w każdej komórce zakresów od A1 do B10 i D2 do F5 i J1 do K20

Range("A1:B10, D2:F5, J1:K20") = 3

'wpisz cyfrę 3 w całej kolumnie A (czyli w 1048576 komórkach)

Range("A:A") = 3

'zaznacz wszystkie komórki "przylegające" tutaj najlepiej opisze to obrazek w dalszej części artykułu

Range("A1").CurrentRegion.Select

'Range może być również przedstawiony za pomocą 2 komórek cells() wtedy

'pierwsza komórka jest lewym górnym a druga prawym dolnym rogu zakresu

'wpisz cyfrę 3 każdej komórce zakresu od A1 do J10

Range(Cells(1, 1), Cells(10, 10)) = 3

'moje ulubione zastosowanie Range tzn. w połączeniu z funkcją SpecialCells

'pokolorujemy wszystkie komórki w zakresie A1:J10, które zawierają formułę na żółto

'najpierw wypełnimy zakres komórek formułą

Range("A1:J10").Formula = "=1+1"

'teraz zamiast części formuł wpiszemy wartości

Range("C1:H2, C9:H10,A3:B8,i3:j8")=2

'i wreszcie linia kodu o którą od początku chodziło tj. wyszukamy bez żadnej pętli

'tylko te komórki w zakresie, które zawierają formuły

Range("A1:J10").SpecialCells(xlCellTypeFormulas).Interior.ColorIndex=6

End Sub

3. Użycie obiektu Range w pętli

Zmienną która jest licznikiem pętli musimy „dokleić” do litery kolumny na zasadzie Range(„A” & wiersz)

Sub range_w_petli()    
 'deklaracja zmiennej/licznika pętli
  Dim wiersz As Integer   

 'pętla ma się wykonać 10 razy
  For wiersz = 1 To 10

      'sklej literę kolumny czyli "A" z wartością wiersza czyli 1, 2, 3...
      'w nawiasie pojawi się więc wartość "A1", "A2", "A3"...
      'a więc do komórki "A1" wypisze się wartość 1
      'do komórki "A2" wypisze się 2, do "A3" wypisze się 3 itd...
       Range("A" & wiersz) = wiersz
 'zamykam pętlę
  Next

'koniec programu
End Sub

Sub range_w_petli()

'deklaracja zmiennej/licznika pętli

Dim wiersz As Integer

'pętla ma się wykonać 10 razy

For wiersz = 1 To 10

'sklej literę kolumny czyli "A" z wartością wiersza czyli 1, 2, 3...

'w nawiasie pojawi się więc wartość "A1", "A2", "A3"...

'a więc do komórki "A1" wypisze się wartość 1

'do komórki "A2" wypisze się 2, do "A3" wypisze się 3 itd...

Range("A" & wiersz) = wiersz

'zamykam pętlę

'koniec programu

End Sub

4. Najczęściej używane właściwości obiektu Range

.Address

Zwraca adres podanej komórki w postaci $kolumna$wiersz, czyli poniższym przykładzie zobaczysz „$A$1”.

Sub prz01()
    MsgBox Range("A1").Address
End Sub

Sub prz01()

MsgBox Range("A1").Address

End Sub

.Characters

Pozwala na odwołanie się do konkretnych znaków w wartości komórki i np. pokolorowanie ich, odczytanie, pobranie.

Sub prz02()
    Range("A1") = "Ala ma kota"
    Range("A1").Characters(8, 4).Font.ColorIndex = 4
End Sub

Sub prz02()

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

Range("A1").Characters(8, 4).Font.ColorIndex = 4

End Sub

.Column

Zwraca numer kolumny, bardzo przydatne np. przy znajdowaniu numeru ostatniej wypełnionej kolumny w danym wierszu. Możemy to zrobić np. tak: od najbardziej na prawo wysuniętej kolumny Arkusza czyli XFD wykonujemy „ctrl + ” co spowoduje przeniesienie zaznaczenia do ostatniej wypełnionej w tym wierszu kolumny

Sub prz03()
   MsgBox Range("XFD1").End(xlToLeft).Column
End Sub

Sub prz03()

MsgBox Range("XFD1").End(xlToLeft).Column

End Sub

.Columns

Kolekcja zawierająca wszystkie kolumny danego zakresu, przykładowe zastosowanie:

Sub prz04()
   MsgBox Range("A1").CurrentRegion.Columns.Count
End Sub

Sub prz04()

MsgBox Range("A1").CurrentRegion.Columns.Count

End Sub

.CurrentRegion

Zbiór przylegających do siebie komórek, w Excelu można je zaznaczyć skrótem Ctrl+* Najlepiej wyjaśnią to obrazki poniżej.

.End

Używam tej właściwości gdy trzeba przejść szybko na początek lub koniec zakresu. Excelu korzystamy z tej właściwości naciskając „CTRL + <-” lub „CTRL + ->” itd. W kodzie CTRL zastępuje właśnie END. Tej właściwości bardzo często używa się do wyliczenia ostatniej wypełnionej kolumny lub wiersza:

Sub prz05()
  'ostatni wypełniony wiersz w kolumnie "A" 
   MsgBox Range("A1048576").End(xlUp).Row
  'ostatnia wypełniona kolumna w wierszu 1
   MsgBox Range("XFD1").End(xlToLeft).Column
End Sub

Sub prz05()

'ostatni wypełniony wiersz w kolumnie "A"

MsgBox Range("A1048576").End(xlUp).Row

'ostatnia wypełniona kolumna w wierszu 1

MsgBox Range("XFD1").End(xlToLeft).Column

End Sub

Zanim uruchomisz przykład prz06 najpierw zapełnij dowolnymi wartościami zakres od komórki A1 do J10 a następnie kliknij w komórkę A1 i teraz wykonaj kod procedury prz06 KROKOWO (czyli za pomocą F8)

Sub prz06()
  'przenieś się do ostatniej wypełnionej (bez przerw) komórki w dół
   Range("A1").End(xlDown).Select
  'przenieś się do ostatniej wypełnionej (bez przerw) komórki w prawo
   Range("A1").End(xlToRight).Select
  'przenieś się do ostatniej wypełnionej (bez przerw) komórki w górę
   Range("A1000").End(xlUp).Select
  'przenieś się do ostatniej wypełnionej (bez przerw) komórki w lewo
   Range("J1").End(xlToLeft).Select
End Sub

Sub prz06()

'przenieś się do ostatniej wypełnionej (bez przerw) komórki w dół

Range("A1").End(xlDown).Select

'przenieś się do ostatniej wypełnionej (bez przerw) komórki w prawo

Range("A1").End(xlToRight).Select

'przenieś się do ostatniej wypełnionej (bez przerw) komórki w górę

Range("A1000").End(xlUp).Select

'przenieś się do ostatniej wypełnionej (bez przerw) komórki w lewo

Range("J1").End(xlToLeft).Select

End Sub

.EntireColumn

Cała kolumna w której znajduje się dana komórka. Używamy tej właściwości np. żeby dopasować szerokość kolumny do jej zawartości

Sub prz07()
   Range("J1").EntireColumn.AutoFit
End Sub

Sub prz07()

Range("J1").EntireColumn.AutoFit

End Sub

.EntireRow

Cały wiersz w którym znajduje się dana komórka. Używamy np. do sprawdzenia ile komórek jest wypełnionych w danym wierszu

Sub prz08()
   MsgBox WorksheetFunction.CountA(Range("J1").EntireRow)
End Sub

Sub prz08()

MsgBox WorksheetFunction.CountA(Range("J1").EntireRow)

End Sub

.Font

Ustawienia czcionki dla zakresu lub komórki.

Sub prz09()
   'pogrubienie tekstu w komórce
    Range("a1").Font.Bold = True

   'pokolorowanie tekstu w komórce na kolor czerwony
    Range("a1").Font.ColorIndex = 4

   'zmiana wielkości czcionki w komórce
    Range("a1").Font.Size = 14
End Sub

Sub prz09()

'pogrubienie tekstu w komórce

Range("a1").Font.Bold = True

'pokolorowanie tekstu w komórce na kolor czerwony

Range("a1").Font.ColorIndex = 4

'zmiana wielkości czcionki w komórce

Range("a1").Font.Size = 14

End Sub

.Formula

Bardzo przydatna właściwość. Korzystam z niej gdy chcę na szybko zobaczyć jak po angielsku nazywa się formuła która została użyta w komórce dzięki czemu wiem pod jaką nazwą szukać tej funkcji w kodzie np. w komórce B1 piszę formułę =FRAGMENT.TEKSTU(A1;1;5). Potem uruchamiamy program prz10

Sub prz10()
   Debug.Print Range("a1").Formula
End Sub

Sub prz10()

Debug.Print Range("a1").Formula

End Sub

I po wykonaniu powyższego programu, pojawi się nam w oknie immediate window (skrót ctrl+g) taki tekst:
=MID(A1,1,5) stąd wiem, że funkcja fragment tekstu to w kodzie makra funkcja Mid :)

.HasFormula

Sprawdza, czy komórka zawiera formułę, jeżeli tak, zwraca True jeżeli nie zwraca False

Sub prz11()
   MsgBox Range("A1").HasFormula
End Sub

Sub prz11()

MsgBox Range("A1").HasFormula

End Sub

.Height

Zwraca wysokość komórki (wiersza). Często mylnie próbuje używać tej właściwości do ustalenia wysokości wiersza ale ta właściwość jest read only. Ustawianie wysokości wygląda nieco inaczej

Sub prz12()
   'wyświetla wysokość wiersza
    MsgBox Range("A1").Height
   
   'poniższa linia ustawia wysokość wiersza 
    Range("A1").RowHeight = 60
End Sub

Sub prz12()

'wyświetla wysokość wiersza

MsgBox Range("A1").Height

'poniższa linia ustawia wysokość wiersza

Range("A1").RowHeight = 60

End Sub

.Hidden

Właściwość która pozwala na ukrycie wiersza. Nie da się ukryć samej komórki, można ukryć jedynie wiersz lub kolumnę czyli

Sub prz13()
   'ukrywamy wiersz
    Range("A1").EntireRow.Hidden
   'lub ukrywamy kolumnę
    Range("A1").EntireColumn.Hidden
End Sub

Sub prz13()

'ukrywamy wiersz

Range("A1").EntireRow.Hidden

'lub ukrywamy kolumnę

Range("A1").EntireColumn.Hidden

End Sub

.Interior

Służy do zarządzania tłem komórki. Najczęściej używam jej do kolorowania, bo kolorowanie nagrane makrem jest za bardzo skomplikowane z nagrania makra kolorowanie wyjdzie nam takie:

Sub prz14()
  'tradycyjne kolorowanie komórki, kod nagrany rejestratorem makr
   Range("A1").Select
   With Selection.Interior
           .Pattern = xlSolid
           .PatternColorIndex = xlAutomatic
           .Color = 65535
           .TintAndShade = 0
           .PatternTintAndShade = 0
   End With
  'a tutaj prostsza forma
   Range("A1").Interior.ColorIndex = 6
End Sub

Sub prz14()

'tradycyjne kolorowanie komórki, kod nagrany rejestratorem makr

Range("A1").Select

With Selection.Interior

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

.Color = 65535

.TintAndShade = 0

.PatternTintAndShade = 0

End With

'a tutaj prostsza forma

Range("A1").Interior.ColorIndex = 6

End Sub

.Item

Dla większego zakresu zwraca jedną z komórek tego zakresu. Kolejność biegnie przez pierwszy wiersz od lewej do prawej, następnie w drugim wierszu od lewej i prawej itd. Żeby dobrze zobaczyć działanie tej właściwości zanim uruchomisz program prz15, wypełnij zakres komórek od A1 do E1 liczbami od 1 do 5 i komórki od A2 do E2 liczbami od 6 do 10. Dopiero wtedy uruchom poniższy kod

Sub prz15()
  'poniższa linia powinna zwrócić cyfrę 4
   MsgBox Range("A1:E2").Item(4)
End Sub

Sub prz15()

'poniższa linia powinna zwrócić cyfrę 4

MsgBox Range("A1:E2").Item(4)

End Sub

.Left

Określa pozycję lewej krawędzi komórki lub kontrolki ActiveX odległość podawana jest w punktach (points) 1 pixel = 0.75 punktu

Sub prz16()
   MsgBox Range("C1").Left
End Sub

Sub prz16()

MsgBox Range("C1").Left

End Sub

.MergeArea

Zwraca zakres, składający się ze scalonych komórek dzięki temu wiemy gdzie się scalony zakres zaczyna, gdzie się kończy i z ilu składa się komórek

Sub prz17()
   MsgBox Range("A1").MergeArea.Address
End Sub

Sub prz17()

MsgBox Range("A1").MergeArea.Address

End Sub

.Next

Zwraca następną komórkę po prawej stronie (odpowiednik naciśnięcia TAB) nie następuje zaznaczenie komórki a jedynie jej zwrócenie.

Sub prz18()
  'linijka poniżej wyświetli $B$1
   MsgBox Range("A1").Next.Address
End Sub

Sub prz18()

'linijka poniżej wyświetli $B$1

MsgBox Range("A1").Next.Address

End Sub

.NumberFormat

Właściwość odpowiadająca w Excelu ustawieniu formatowania/wyświetlania wartości w komórce.

Sub prz19()
  'wpisz do komórki A1 dzisiejszą datę 
   Range("A1") = Date
  
  'sformatuj wyświetlaną w komórce A1 datę żeby uzyskać 
  '2 cyfrowy dzień - 2 cyfrowy miesiąc - 4 cyfrowy rok
   Range("A1").NumberFormat = "dd-mm-yyyy"
End Sub

Sub prz19()

'wpisz do komórki A1 dzisiejszą datę

Range("A1") = Date

'sformatuj wyświetlaną w komórce A1 datę żeby uzyskać

'2 cyfrowy dzień - 2 cyfrowy miesiąc - 4 cyfrowy rok

Range("A1").NumberFormat = "dd-mm-yyyy"

End Sub

.Offset

Przesunięcie względem komórki z której offseta stosujemy pierwsza wartość określa przesunięcie dla wierszy a druga dla kolumny czyli

Sub prz20()
   MsgBox Range("B2").Offset(1, 0).Address 'zwróci $B$3
   MsgBox Range("B2").Offset(-1, 0).Address 'zwróci $B$1
   MsgBox Range("B2").Offset(0, 1).Address 'zwróci $C$2
   MsgBox Range("B2").Offset(0, -1).Address 'zwróci $A$2
End Sub

Sub prz20()

MsgBox Range("B2").Offset(1, 0).Address 'zwróci $B$3

MsgBox Range("B2").Offset(-1, 0).Address 'zwróci $B$1

MsgBox Range("B2").Offset(0, 1).Address 'zwróci $C$2

MsgBox Range("B2").Offset(0, -1).Address 'zwróci $A$2

End Sub

.Orientation

Pozwala na ustawienie kierunku tekstu w komórce, mamy do wyboru cztery wartości xlDownward, xlHorizontal, xlUpward, xlVertical

Sub prz21()
  'wpisz jakiś tekst do komórki A1
   Range("A1") = "Ala ma kota"

  'spraw żeby tekst został wyświetlony w pionie
   Range("A1").Orientation = xlDownward
End Sub

Sub prz21()

'wpisz jakiś tekst do komórki A1

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

'spraw żeby tekst został wyświetlony w pionie

Range("A1").Orientation = xlDownward

End Sub

.Row

Zwraca wartość wiersza w którym znajduje się komórka. Jeżeli używamy jej nie dla komórki a dla zakresu to zwróci numer pierwszego wiersza w zakresie. Używana często do znajdowania numeru ostatniego wypełnionego wiersza w zakresie.

Sub prz22()
  'tworzę zmienną
   Dim wierszyk

  'wylosuj numer wiersza i przypisz do zmiennej
   wierszyk = WorksheetFunction.RandBetween(10, 20)

  'wypełnij zakres wylosowanych komórek jakimś tekstem
   Range("A1:A" & wierszyk) = "Ala ma kota"

  'wypisz informację o numerze ostatniego wypełnionego wiersza w kolumnie A
   MsgBox Range("A" & Rows.Count).End(xlUp).Row
End Sub

Sub prz22()

'tworzę zmienną

Dim wierszyk

'wylosuj numer wiersza i przypisz do zmiennej

wierszyk = WorksheetFunction.RandBetween(10, 20)

'wypełnij zakres wylosowanych komórek jakimś tekstem

Range("A1:A" & wierszyk) = "Ala ma kota"

'wypisz informację o numerze ostatniego wypełnionego wiersza w kolumnie A

MsgBox Range("A" & Rows.Count).End(xlUp).Row

End Sub

.Rows

Zbiór wszystkich wierszy w zakresie/w arkuszu. Przy współpracy z CurrentRegion może być używane do zwrócenia numeru ostatniego wypełnionego wiersza w zakresie. Tylko przy założeniu, że nasz zakres zaczyna się od pierwszego wiersza.

Sub prz23()
   MsgBox Range("A1").CurrentRegion.Rows.Count
End Sub

Sub prz23()

MsgBox Range("A1").CurrentRegion.Rows.Count

End Sub

.Top

Zwraca podaną w punktach odległość komórki od górnej krawędzi arkusza.

Sub prz24()
   MsgBox Range("A3").Top
End Sub

Sub prz24()

MsgBox Range("A3").Top

End Sub

.UseStandardHeight

Pozwala ustawić czy też przywrócić wysokość wiersza do wartości domyślnej/standardowej.

Sub prz25()
   Range("A1").EntireRow.UseStandardHeight = True
End Sub

Sub prz25()

Range("A1").EntireRow.UseStandardHeight = True

End Sub

.UseStandardWidth

To samo co powyżej tylko że dla kolumny, ustawia jej szerokość na wartość domyślną / standardową.

Sub prz26()
   Range("A1").UseStandardWidth = True
End Sub

Sub prz26()

Range("A1").UseStandardWidth = True

End Sub

.Value i Value2

Domyślna właściwość obiektu Range. Zwracają lub pozwalają na ustawienie wartości która pojawia się w komórce .Value różni się od .Value2 tym, że jeżeli komórka jest sformatowana jako currency to w .value wartości po przecinku mogą nie być wyświetlane poprawnie (może nam obciąć cyfry po 2 miejscu po przecinku). Z kolei w przypadku daty np. 2000-01-01 właściwość .Value wyświetli ładnie sfotmatowaną datę czyli 2000-01-01 natomiast .value2 liczbę odpowiadającą tej dacie czyli 36526.

Sub prz27()
     Range("A1") = 4  
   
    'jest tym samym co
     Range("A1").Value = 4  

    'i tym samym co
     Range("A1").Value2 = 4
End Sub

Sub prz27()

Range("A1") = 4

'jest tym samym co

Range("A1").Value = 4

'i tym samym co

Range("A1").Value2 = 4

End Sub

.WrapText

Właściwość tylko do odczytu. Zwraca prawdę jeżeli tekst w komórce jest zawinięty lub Fałsz jeżeli nie jest :)

Sub prz28()
   MsgBox Range("A1").WrapText
End Sub

Sub prz28()

MsgBox Range("A1").WrapText

End Sub

5. Najbardziej przydatne metody obiektu Range

Tak jak wspominałem jest tego trochę więc, napisałem o tych metodach osobnego posta który znajduje się tutaj :)

Obiekt Range

1. Do czego służy obiekt Range ?

2. Jak używać obiektu Range, żeby odwołać się do komórki?

3. Użycie obiektu Range w pętli

4. Najczęściej używane właściwości obiektu Range

.Address

.Characters

.Column

.Columns

.CurrentRegion

.End

.EntireColumn

.EntireRow

.Font

.Formula

.HasFormula

.Height

.Hidden

.Interior

.Item

.Left

.MergeArea

.Next

.NumberFormat

.Offset

.Orientation

.Row

.Rows

.Top

.UseStandardHeight

.UseStandardWidth

.Value i Value2

.WrapText

5. Najbardziej przydatne metody obiektu Range

Zobacz też:

2 sposoby na wyłączający się dodatek Excela

Zapytania SQL i baza danych Excel czy to da się połączyć?

Ustawienia modułu