Kurs VBA – cz. 9 – Obiekt Range

Większość pracy z Excelem polega na modyfikowaniu komórek. Prawie każdy element Excela ma odpowiadający sobie obiekt, w efekcie dużą część pracy w VBA wykonujemy przy pomocy zakresów (obiekt Range). Range może reprezentować pojedynczą komórkę, wiersz, kolumnę, grupę komórek z zakresu ciągłego lub nie albo nawet zakres z kilku skoroszytów jednocześnie.

Właściwość Range obiektu zwraca obiekt typu Range. Może ona być używana następująco.

ObiektNadrzedny.Range(komorka1)
ObiektNadrzedny.Range(komorka1, komorka2)

Pierwszy przykład zwraca zakres wyznaczony przez jedną komórkę, drugi natomiast zakres między dwoma komórkami, W powyższych przykładach ObiektNadrzedny ozbacza obiekt Application, Range lub Worksheet. Jeżeli nie podamy go, zostanie tam domyślnie wstawiony ActiveSheet.

Tyle w telegraficznym skrócie. Przerobimy teraz kilkanaście przykładów różnego dostępu do komórek. VBA udostępnia wiele możliwości, z których można zawsze wybrać akurat najwygodniejszą.


Przypisywanie wartości do zakresu.

Przypisywanie wartości do komórki jest proste.

 

Podobnie możemy dokonać przypisania do zakresu nazwanego. Poniższy przykład zadziała niezależnie od tego, na którym arkuszu znajduje się zakres nazwany,

 

Następny przykład przypisuje wartość do wszystkich komórek podanego zakresu w aktywnym arkuszu.

 

Jak już wspominałem wcześniej właściwość Value jest domyślna i najczęściej jest pomijana.

 

Łatwo jest wymienić kilka komórek.

 

Do wstawianiu formuły posługujemy się atrybutem Formula. Niestety musimy użyć wtedy angielskiej wersji formuły.

 

Zaznaczanie komórek

Do zaznaczania komórek używamy metody Select.

 

Po wykonaniu powyższego przykładu zakres B1:B15 zostaje podstawiony pod obiekt Selection. Obiekt Selection jest typu Range, ma więc jego wszystkie atrybuty i metody.

Odniesienia względne

Często chcemy znaleźć komórkę odpowiednio przesuniętą względem innej.

Range.Offset(przesuniecie w wierszu, przesunięcie w kolumnie)

Dodatnie argumenty przesuwają zakres w dół i w prawo, negatywne w górę i w lewo. Poniższe zaznacza komórkę A2.

 

Teraz ustawimy 1 w komórce C2.

 

Po wykonaniu poniższej instrukcji pojawi się błąd, ponieważ nie ma nic powyżej ani po lewej stronie od A1.

 

Ostatni przykład zaznacza komórkę bezpośrednio pod aktualnie zaznaczoną.

 

Powyższy atrybut Offset oczywiście zwraca range. Jest on często wykorzystywany w programach przy okazji pętli. Pętli jeszcze nie omawialiśmy, ale istotne jest tutaj, że dziesięć razy zaznaczamy komórkę poniżej aktualnie zaznaczonej.

 

Zmiana rozmiaru zakresu

Przy pomocy parametru Resize możemy stworzyć zakres, który ma lewy, górny róg w tym samym miejscu, ale inną ilość kolumn i wierszy. Zaznaczę zakres A1:A2.

 

Pierwszy argument może być pominięty, jeżeli chcemy, aby ilość wierszy została bez zmian
 

lub drugi, w przypadku kolumn.
 

Poniższy przykład zmienia rozszerza zakres nazwany Baza o jeden wiersz.

Odwołanie do poszczególnych komórek w zakresie

Do pojedynczych komórek możemy zwracać się przy pomocy atrybutu Cells. Poniższym zaznaczamy komórkę C2 (1 wiersz, 2 kolumna zakresu).

 

Inne komórki w tym zakresie możemy zaznaczać zgodnie z przykładem:

Cells jest też atrybutem obiektu Worksheet. Zaznaczmy komórkę C1.

 

Jeżeli używamy zapisu w stylu Range(„A1”) trzeba pamiętać, że jest to A1 zakresu zwróconego przez obiekt nadrzędny. Poniżej zaznaczam komórkę B2. Zwykle A1 odnosi się do skrajnie lewej i górnej komórki. Poniżej jest to właśnie B2.

 

Możemy odwołać się też poza pierwotny zakres, dopóki mieścimy się w arkuszu. Zaznaczę komórkę C1 w aktywnym arkuszu.

 

Dzięki powyższemu możemy jeszcze raz napisać wcześniejszy program, unikając zaznaczania komórek, dzięki czemu będzie działał szybciej.Option Explicit

 

Podsumowanie

Oczywiście obiekt Range ma mnóstwo innych metod i atrybutów. Wszystkie można znaleźć na stronie MSDN i w pomocy VBA. W poprzedniej części kursu pokazałem jak znaleźć informacje na temat obiektów.

Żeby podsumować chciałbym jeszcze raz podkreślić, że wszystkie powyższe atrybuty najczęściej przynależą do obiektu Range i zawsze zwracają obiekt Range. Można więc z nich tworzyć „łańcuchy” wedle potrzeb. Umiejętność tworzenia takich zestawów przychodzi z doświadczeniem.

Ostatni przykład należy potraktować z przymrużeniem oka. Przypisuje on po prostu wartość 1 do komórki A1.

 

Jako ostateczny dowód elastyczności VBA w omawianym zakresie pokażę trzy instrukcje, które robią dokładnie to samo, czyli przesuwają zaznaczenie jedną komórkę niżej.

 

Subskrybuj RSS, lub polub blog na Facebooku aby otrzymywać najnowsze informacje o rozwoju kursu.

Comments 39

  1. Potrzebuję pomocy, poszukuję kodu, który będzie wybierał wolną komórkę ale zawsze w tym samej kolumnie A ale nie wiadomu w jakim wierszu bo zakres jest zmienny jak zrobić taki kod? Prosze o pomoc

    1. 'wybiera pierwszą wolną komórkę w kolumnie A
      Sub Pusta()
      [a1].Select
      Do While ActiveCell <> ""
      ActiveCell.Offset(1, 0).Select
      Loop
      End Sub

  2. witam może lepiej
    Sub test()
    Dim LastRow As Long
    LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
    if Not ISEmpty (Cells(LastRow,1))
    Msgbox "Wolne miejsce"
    End if
    End sub

    Makro będzie w miejscu pustym wyświetlać informację

  3. Dzień Dobry,
    podłączam się do pytania. Również poszukuję formuły, która zaznaczy mi zakres wszystkich aktywnych komórek w danej kolumnie.

    Przykładowo jeżeli w kolumnie A będzie wpisanych 10 liczb pod sobą to makro zaznaczy 10, jeżeli 8 to makro zaznaczy 8 itp.
    Coś w stylu skrótu klawiszowego ctrl+shift+(strzałka w dół)

    Z góry dziękuje za pomoc.

    1. Dzień dobry,
      Dziękuje za pytanie. W takich przypadkach przydaje się nagrywarka. W tym przypadku po naciśnięciu skrótu ctrl+shift+ strzałka w dół nagrywarka tworzy kod:

      Range(Selection, Selection.End(xlDown)).Select

      Wystarczy pod Selection wstawić zakres A1.

  4. Dzień dobry mam problem, a dla kogoś nie obeznanego jak ja to wielki problem 🙁
    Może ktoś pomoże mi z nim.
    Jak zarejestrować makro /makro wypełnia komórki wartościami – i to działa/ tak by ostatnim jego zadaniem było ustawić po wykonaniu makra została zaznaczona automatycznie kolejna komórka /i tak też się ustawia/ i po wciśnięciu kolejny raz tego makra zostały wypełnione pola na których makro skończył zadanie. tj. Wypełniło komórki G2:G4 i stanęło na H2. Teraz po wciśnięciu tegoż samego makro chciałbym by wypełniło H2:H4 i stanęło na I2. I tak dalej po każorazowym wciśnięciu tego makra.

    Poniżej to makro.

    Sub Makro5()
    '
    ' Makro5 Makro
    '

    '
    Range("A12:A15").Select
    Selection.Copy
    Range("G2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("H2").Select
    End Sub

    1. Dziękuję za szybką odpowiedź. Już sobie poradziłem. Dałem radę z pętlą. :))
      Co do kursu to super sprawa! na bierząco się tego uczę 🙂 Jak człowiek za młodu olał temat tak teraz musi nadrabiać tak proste sprawy.

      Po range.selection dodałem

      Do Until (ActiveCell.Value = "")

      ActiveCell.Offset(0, 1).Select

      Loop 'koniec pętli

  5. Worksheets(2).Range(„B1:B15”).Select – nie działa (nawet jeżeli nazwę arkusza umieścimy w cudzysłowiu)

    1. Post
      Author

      Ma Pan rację, przykład nie trafiony. Powinno to wyglądać tak:
      Worksheets(2).Select
      Worksheets(2).Range(„B1:B15”).Select

      Albo tak:

      ActiveSheet.Range(„B1:B15”).Select

      Jakkolwiek jednym poleceniem nie można zmienić zaznaczenia na obszar znajdujący się na innym arkuszu niż aktualnie zaznaczony.

  6. czy ktoś wie czy da się sparametryzować obiekt range np w ten sposób?
    For t = 51 To 10000 Step 50
    Range(„At”).Select
    Nie chce mi działać a chciałem rozpisać makro na kopiowanie pewnego zakresu komórek i zmiany ich zawartości z krokiem co 50 komórek. Pętla zatrzymuje się na linijce ze sparametryzowanym range. próbowałem nawet w ten sposób:
    Range(„A51+t”).Select ale nic nie daje.
    Ktoś a pomysł?

    1. Post
      Author

      Dziękuję za pytanie.
      Nie ma takiego zakresu jak "AT". Dlatego pojawia się błąd. Adres powinien być tworzony tak: Range("A" & t).

      1. A czemu kompilacja poniższego makra sypie sie na linijce ActiveCell.FormulaR1C1 = „=REJESTR!R[-52 & -Z)]C[-1]” ?
        Makro:
        Sub toWyjdzie()

        ’ toWyjdzie Makro

        For Z = 0 To 1000000 Step 50

        Range(„A1:C50” & Z).Select
        Range(„C50” & Z).Activate
        Selection.Copy
        Range(„A51” & Z).Select
        ActiveSheet.Paste
        Range(„B56” & Z).Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = „=REJESTR!R[-52 & -Z)]C[-1]”
        Range(„B57” & Z).Select
        ActiveCell.FormulaR1C1 = „=REJESTR!R[-(53 & z)]C”
        Range(„B58:B67” & Z).Select
        ActiveCell.FormulaR1C1 = „=REJESTR!R[-(54 & z)]C[1]”
        Range(„B68” & Z).Select
        ActiveCell.FormulaR1C1 = „=REJESTR!R[-(64 & z)]C[2]”
        Range(„B69” & Z).Select
        ActiveCell.FormulaR1C1 = „=REJESTR!R[-(65 & z)]C[3]”
        Range(„B71:B72” & Z).Select
        ActiveCell.FormulaR1C1 = „=REJESTR!R[-(67 & z)]C[4]”
        Range(„B73:B74” & Z).Select
        ActiveCell.FormulaR1C1 = „=REJESTR!R[-(69 & z)]C[5]”
        Range(„B75:B76” & Z).Select
        ActiveCell.FormulaR1C1 = „=REJESTR!R[-(71 & z)]C[6]”
        Range(„B77:B80” & Z).Select
        ActiveCell.FormulaR1C1 = „=REJESTR!R[-(73 & z)]C[7]”
        Range(„B87:C87” & Z).Select
        ActiveCell.FormulaR1C1 = „=REJESTR!R[-(83 & z)]C[8]”
        Range(„B88:C88” & Z).Select
        ActiveCell.FormulaR1C1 = „=REJESTR!R[-(84 & z)]C[9]”
        Range(„B89:C89” & Z).Select
        ActiveCell.FormulaR1C1 = „=REJESTR!R[-(85 & z)]C[10]”
        Range(„B90:B93” & Z).Select
        ActiveCell.FormulaR1C1 = „=REJESTR!R[-(86 & z)]C[11]”
        Range(„B94:C95” & Z).Select
        ActiveCell.FormulaR1C1 = „=REJESTR!R[-(90 & z)]C[12]”
        Range(„B101” & Z).Select
        Next

        End Sub

        1. Post
          Author

          Dzień dobry,
          Po pierwsze bardzo mocno polecam używać option explicit. Proponuję powtórzyć sobie rozdział o operatorach. Na przykład selekcję Pan tworzy tak:
          Range(„A1:C50” & z).Select
          czyli tworzy się adres:
          Range(„A1:C500”).Select
          Range(„A1:C5051”).Select
          Range(„A1:C50101”).Select
          Zapewnie chciał Pan zrobić:
          Range(„A1:C” & 50 + z).Select
          wtedy tworzą się adresy:
          Range(„A1:C50”).Select
          Range(„A1:C101”).Select
          Range(„A1:C151”).Select
          itd.
          Podobnie z kolejnymi instrukcjami.

          1. Poprawiłem kod. Zobaczyłem na operatory ale nadal nie wiem czemu kompilacja sypie się na tej ostatniej linijce (oczywiście są linijki kodu dalej analogiczne).
            Range(„A1:C50”).Select
            Range(„C50”).Activate
            Selection.Copy

            For Z = 50 To 100 Step 50
            Range(„A” & 1 + Z).Select
            ActiveSheet.Paste
            Range(„B” & 6 + Z).Select
            Application.CutCopyMode = False
            ActiveCell.FormulaR1C1 = „=REJESTR!R[-2 – Z]C[-1]”

  7. Teraz doszedłem że kompilacja zatrzymuje się na tym etapie ponieważ wpisując w komórkę =REJESTR!R[-2-z] system nierozpoznaje parametru z gdyż w arkuszu go nie ma. Czy da się to jakoś obejść?

    1. Post
      Author

      Zapewne chciał Pan to napisać tak:
      ActiveCell.FormulaR1C1 = „=REJESTR!R[” & -2 – z & „]C[-1]”

  8. Dzień Dobry,
    Bardzo proszę o pomoc
    Użytkownik zaznacza dowolny zakres komórek, konkretnie w pionie, wycinek kolumny.
    Chcę nadać zmiennej tablicowej:
    Dim tablica(n To 1) As Single 'kształt kolumny
    wartości z zaznaczonych (zawsze inna ilość) komórek w pionie.

    1. Post
      Author

      Dzień dobry,
      Elementy tablicy muszą być typu Variant.
      Dim tablica() As Variant
      tablica = Selection

  9. Witajcie,
    Będę wdzięczny za podpowiedź – chcę za pomocą Makra zaznaczać i kopiować do innego arkusza pewien zakres komórek z danymi, który jest zmienny. Zakres komórek (w formacie np: B2:E10) do zaznaczenia podany jest w komórce A1 i zakres ten ulega zmianie. Raz będzie to B2:E10 raz B4:E40 lub jeszcze inny. Liczę na podpowiedź i z góry dziękuję.

    1. Post
      Author

      Dzień dobry,
      Za pomocą nagrywarki tworzę taki kod:

      Range("A2:A17").Select
      Selection.Copy
      Sheets("Sheet2").Select
      ActiveSheet.Paste

      Żeby uniknąć zaznaczania modyfikuję kod:

      Range("A2:A17").Copy Sheets("Sheet2").Range("A1")

      Teraz wystarczy podmienić adres źródłowy:


      Range(Range("A1")).Copy Sheets("Sheet2").Range("A1")

        1. Post
          Author
      1. Dokładniej chodzi o to by adres obszaru zapisany w formacie: $A$2:$P$20 – który jest podany w komórce R1 w Arkuszu1 pobrać do VBA.
        Chcę utworzyć makro, które będzie kopiowało pewien obszar z danymi do drugiego arkusza. Obszar jest określony jak powyżej w komórce R1 Arkusza1 i chcę te dane skopiować do Arkusza2 w obszar np B2:R20

  10. Dzień dobry,
    mam pytanie jak odwołać się do zakresu gdy znam początek nazwy komórki startowej i końcowej tzn. na arkuszu są nazwane komórki „start_”&nazwa arkusza oraz „end_”&nazwa arkusza
    wiem jak pobrać nazwę arkusza -> a = ActiveWorkbook.ActiveSheet.Name mogę przypisać do zmiennej etykietę nazwanej komórki -> start_txt = „stat_” & a
    ale jak przejść od nazwy do zakresu Range(„start_txt”, „end_txt”) by VBA rozumiało że jest to zakres a nie teksty

    1. Post
      Author

      Dzień dobry,
      Range(„start_txt”, „end_txt”) przyjmuje dwa literały typu tekst.
      Range(start_txt, end_txt) w ten sposób przekazywane są dwie zmienne.

      1. Super, dzięki
        Mam jeszcze jeden problem, potrzebuję wskazać adresy komórek ze wskazanego zakresu w postaci listy tzn. na arkuszu AA jest zakres komórek tak jak tabliczka mnożenia, ma nagłówki wierszy np. wier1, wier2, wier3 itd oraz nagłówki kolumn kol1, kol2, kol3 itd, na następnym arkuszu potrzebuję wypisać listę komórek w stylu „nazwa arkusza”_”nagłówek wiersza”_”nagłówek kolumny” a w kolumnie obok rzeczywisty adres komórki tzn
        w kolumnie A nazwy w kolumnie B adresy
        AA_wier1_kol1 =AA!B3
        AA_wier2_kol1 =AA!B4
        jak połączyć nazwę z konkretną komórką i wypisać rzeczywiste adresy bez znaków $

  11. Cześć .
    Mam problem z funkcją select.

    Zaznaczam kilka lini arkusza i czyszczę je z kolorów

    Workbooks(„QMS-P MP7.xls”).Activate
    Sheet1.Activate
    Sheet1.Select
    With Sheet1.Range(„15:17,19:23,25:34,36:39,41:46”).Interior
    .Pattern = xlNone

    End With

    wszystko działa poprawnie pod warunkiem że arkusz działa samodzielnie.
    W przypadku gdy otworzę inny arkusz excela na lini
    With Sheet1.Range(„15:17,19:23,25:34,36:39,41:46”).Interior
    wysypuje się z komunikatem

    Mam ten problem też na innych arkuszach przy podobnym kodzie, tak jakby excel nie wiedział do którego pliku funkcja select ma się odnosić.

    Co ciekawe gdy definiujemy tylko jeden zakres problemu nie ma
    With Sheet1.Range(„15:46”).Interior

    Przekopywałem się przez wiele stron ale nie mogę nigdzie znaleźć rozwiązania.

    1. Post
      Author
  12. Witam!
    Mam takie pytanie. W arkuszu EXCEL w komórce A1 – Arkusz1 mam wpisaną wartość zakresu, np A3:D10.

    Jak wczytać taki zakres do kodu VBA, tak, aby na nim program wykonał jakąś operację? np skopiował i wkleił do Arkusz2 jako wartości? Chodzi mi o to, aby zakres mógł być zmienny, raz taki raz inny.

    1. Post
      Author

      Witam,
      Dziękuję za pytanie. Na przykład tak:

      Option Explicit

      Public Sub SelectRange()
      Dim aRange As Range

      On Error Resume Next
      Set aRange = Application.InputBox(prompt:=”Enter range”, Type:=8)
      If aRange Is Nothing Then
      MsgBox „Nic nie wybrano”
      Else
      aRange.Interior.Color = vbBlack
      End If
      End Sub

      1. A żeby wybrał konkretnie z A1 wpisanego na poziomie excela to tak:
        (nie mam kompa w domu z Excelem, a szukam rozw na jutro)

        Public Sub SelectRange()
        Dim aRange As Range

        On Error Resume Next
        ’—
        Set aRange = Range(„a1”).value
        ’—-
        If aRange Is Nothing Then
        MsgBox „Nic nie wybrano”
        Else
        aRange.Interior.Color = vbBlack
        End If
        End Sub

        1. Post
          Author
  13. Potrzebuję pewnej pomocy, a mianowicie mam stworzone makro które wykonuje pewne działania w aktywnym pliku. Ma być wykorzystywane też w innych plikach i tu zaczyna się problem. Chodzi o miejsce z którego będzie wykonywana kolejna część procedury. Najłatwiej by było aby użytkownik zaznaczył myszą komórkę. Wtedy za pomocą instrukcji vba pobiorę jej adres i zacznę działać dalej.
    Obecnie mam to zrobione za pomocą inputBox z „wklepaniem” adresu z klawiatury, lecz zastanawiam się czy można to zrobić inaczej.

    1. Post
      Author

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *