Dziś w kolejnym spóźnionym o prawie półtora tygodnia odcinku zajmiemy się pętlami For i działaniami na komórkach arkusza.
Odczytywanie i zapis danych do komórki
Istnieje wiele sposobów odwołania się do komórki w arkuszu. Najczęściej wykorzystuje się odwołania poprzez zakres lub odpowiednie ustawienie wartości komórki. Aby odwołać się do konkretnej komórki piszemy:
Cells( nr_wiersza, nr_kolumny).Value
Gdzie numer wiersza i kolumny to kolejne liczby naturalne. Należy pamiętać, że odwołujemy się w ten sposób do aktywnej komórki akrusza. Poniższy przykład pokazuje podstawowe działania na komórkach.
Sub pr4() Dim a As Single, z As Single a = Cells(1, 1).Value Cells(1, 2).Value = a * 2 Cells(1, 3).Value = "To jest komórka C1" z = 2 Cells(2, z).Value = a End Sub
UWAGA! Zastosowane powyżej odwołanie się do komórek powoduje że wszystkie operacje będą wykonywały się na aktywnym arkuszu.
W praktyce w programie o wiele częściej niż do pojedynczej komórki odwołujemy się do zakresu komórek. W tym celu wykorzystuje się pętle i tablice. Powszechnym błędem, z którym bardzo często spotykam się w aplikacjach korzystających z akrusza, jest praca bezpośrednio na komórkach. Zapomina się przy tym, że odczyt i zapis do arkusza jest często najbardziej pracochłonną częścią programu. dlatego pracując na komórkach lepiej korzystać z tablic.
Tablice i praca z elementami w Excelu, For… Next
Problem: Chcemy przy pomocy VBA wczytać z Excela kolumnę liczb znajdującą się w kolumnie B, dodać do wszystkich liczb 3, a następnie wypisać wyniki dodawania do kolumny C.
Rozwiązanie tego problemu składa się z następujących kroków:
- kopiujemy liczby do pamięci,
- dodajemy do wszystkich skopiowanych do pamięci liczb 3,
- wypisujemy wyniki do Excela.
Aby skopiować zmienne z Excela musimy stworzyć tyle zmiennych, ile liczb chcemy skopiować z Excela. Zamiast tworzyć je ręcznie łatwiej zrobić to hurtowo przy pomocy tablicy. Tablica do uporządkowany zbiór zmiennych, które znajdują się obok siebie w pamięci. Jeżeli przykładowo chcemy stworzyć tablicę 8 zmiennych przechowujących liczby całkowite to piszemy:
Dim a(1 to 8 ) As Intege
W ten sposób w pamięci powstanie 8 zmiennych ponumerowanych od 1 do 8. Tak więc, jeżeli chcemy wpisać do 5 elementu tablicy cyfrę 12 to piszemy
a(5) = 12
Jeżeli chcemy do kolejnych elementów tablicy wpisać kolejne komórki Excela z kolumny B, to napiszemy
a(1) = Cells(1,2).Value a(2) = Cells(2,2).Value
Ogólnie możemy więc napisać, że do i-tego elementu tablicy wpisujemy zawartość komórki Bi, czyli
a(i) = Cells(i,2).Value
Możemy nakazać komputerowi aby podstawiał za i liczby od 1 do 8, w taki sposób
For i = 1 to 8 a(i) = Cells(i,2).Value Next i
Pętla For – uproszczona składnia
For licznik = wartoscPoczatkowa To wartosc_koncowa [STEP WartoscKroku] [Instrukcje] [Exit For] [Instrukcje] Next [licznik]
Jeżeli w dowolnym momencie funkcja napotka Exit For to natychmiast zostanie przerwana.
Rozwiązanie c.d.
W ten sposób wpisaliśmy zawartość komórek B1 do B8 do tablicy w pamięci komputera. Teraz chcemy, aby każdy element tablicy został zwiększony od 3. Wykorzystamy do tego znowu pętlę for
For i = 1 to 8 a(i) = a(i)+3 Next i
na koniec wypiszemy zawartość naszej tablicy do kolumny C
For i = 1 to 8 Cells(i,3).Value = a(i) Next i
Schemat działania całego programu oraz listing przedstawiłem na rysunku
Na koniec dwa przykłady wykorzystania pętli for
Przykłady wykorzystania pętli for
Jak zsumować komórki zapisane w arkuszu przy pomocy VBA
Sub sumowanie() Dim a(1 to 8 ) As Integer Dim i As integer, suma As integer suma = 0 For i = 1 to 8 a(i) = Cells(i,2).Value suma = suma + a(i) Next i End Sub
Jak obliczyć maksimum z komórek w arkuszu?
Sub maximum()
Dim a(1 to 8 ) As Integer
Dim i As integer, max As Integer
For i = 1 to 8
a(i) = Cells(i,2).Value
Next i
Dim max As integer
max = a(1)
For i = 2 to 8
If a(i) > max Then
max = a(i)
End if
Next i
Cells(9,2).Value = max
End Sub
Następnym razem przydatne własności komórek oraz więcej przykładów dla pętli for.