W jaki sposób zmienić kolor tła w wierszach arkusza Excel, w których w wybranej komórce pojawia się data?

Skrypciarze odpowiadają na Wasze pytania

W jaki sposób zmienić kolor tła w wierszach arkusza Excel, w których w wybranej komórce pojawia się data?

Cześć, Skrypciarze! W jaki sposób zmienić kolor wiersza, w którego komórkach znajduje się data?

-- GL

Cześć, GL. Być może bardziej spostrzegawczy z Was zauważą, że brakuje jednego artykułu – poprzedni miał numer 25, a ten od razu 29. Nie martwcie się, to wynika po prostu stąd, że wczoraj w Stanach mieliśmy wolne – był Dzień Pamięci. A że tym razem żaden z nas nie powtórzył wyczynu naszego kolegi sprzed paru lat, który przyszedł do pracy w święto i przesiedział w biurze jakieś cztery godziny, zanim się zorientował, że jest jedyną osobą w całym budynku, to żaden artykuł skrypciarski wczoraj nie powstał. No cóż, co dobre, zwykle szybko się kończy, więc dziś wracamy do pracy:

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True



Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")

Set objWorksheet = objWorkbook.Worksheets(1)



i = 1



Do Until objExcel.Cells(i, 1) = ""

    strValue = objExcel.Cells(i, 1)



    If IsDate(strValue) Then

        objExcel.Cells(i, 1).EntireRow.Interior.ColorIndex = 44

    End If

    

    i = i + 1

Loop

Jak to więc działa? Na początek zakładamy, że mamy arkusz taki jak ten:

Jak widać, to całkiem prosty plik. W niektórych komórkach w kolumnie A mamy zapisane różne dane: kilka dat, liczba, fragment tekstu. Nasze zadanie będzie polegało na programistycznym wybraniu tych wierszy w kolumnie A, w których znajduje się data, a następnie zmiana ich koloru tła. Wygląda na skomplikowane? Być może, ale wcale tak nie jest.

Jak się może domyślacie, na początek tworzymy wystąpienie obiektu Excel.Application i ustawiamy jego właściwość Visible na True; dzięki temu uzyskujemy wystąpienie programu Excel widoczne na ekranie. Następnie za pomocą poniższych dwóch wierszy kodu otwieramy plik C:\Scripts\Test.xls i łączymy się z jego pierwszym arkuszem:

Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")

Set objWorksheet = objWorkbook.Worksheets(1)

Teraz przypisujemy wartość 1 zmiennej licznika o nazwie i; posłuży nam ona do śledzenia naszej pozycji w arkuszu (a konkretnie – wiersza, którym się obecnie zajmujemy.)

Teraz możemy już zacząć zmieniać kolory wierszy. W naszym przykładowym arkuszu nie ma pustych wierszy pomiędzy wierszami z danymi. Dlaczego? Ponieważ dzięki temu łatwiej będzie stwierdzić, czy sprawdziliśmy już wszystkie wiersze zawierające dane: natknięcie się na pusty wiersz w kolumnie A („”) będzie oznaczało, że to już koniec. Tak więc możemy uruchomić pętlę Do Until, która będzie działać aż do napotkania pustej komórki w kolumnie A:

Do Until objExcel.Cells(i, 1) = ""

Uwaga:

Co zrobić, jeśli w naszej kolumnie są puste wiersze? Możemy użyć właściwości UsedRange, która umożliwi nam wskazanie, gdzie nasze dane się zaczynają, a gdzie kończą. Przykładowy skrypt używający właściwości UsedRange można znaleźć w artykule z serii Office Space (j.ang.).

Wewnątrz pętli najpierw pobieramy wartość z komórki o współrzędnych: i, 1; przy pierwszym przejściu pętli będzie to wiersz 1, kolumna 1 (przypominamy, że i to zmienna licznika odpowiadająca za numer bieżącego wiersza). Następnie przypisujemy wartość tej komórki zmiennej o nazwie strValue:

strValue = objExcel.Cells(i, 1)

Teraz używamy funkcji IsDate języka VBScript do określenia, czy wartość ta oznacza datę:

If IsDate(strValue) Then

Jeśli wynik działania IsDate to False, wiemy, że nie mamy do czynienia z datą. W takim razie podnosimy wartość i o 1, a następnie ponownie uruchamiamy pętlę i powtarzamy ten proces dla kolejnego wiersza w arkuszu (zanim ktoś zapyta – funkcja IsDate uwzględnia ustawienia regionalne i językowe podczas sprawdzania, czy dana wartość jest datą).

OK, co się jednak stanie, jeśli wynikiem działania IsDate będzie wartość True? Może to oznaczać tylko jedno – w kolumnie A znajduje się data. To z kolei znaczy, że musimy zmienić kolor tła danego wiersza. W tym celu użyjemy poniższego kodu:

objExcel.Cells(i, 1).EntireRow.Interior.ColorIndex = 44

olecenie to wygląda trochę niezgrabnie, ale tak naprawdę jest bardzo proste. Zaznaczamy tu bieżącą komórkę (współrzędne i, 1), a następnie rozszerzamy zaznaczenie na cały wiersz – służy do tego właściwość EntireRow. Potem zmieniamy kolor tła, ustawiając właściwość Interior.ColorIndex na 44. Po zakończeniu działania całego skryptu i przejrzeniu całego pliku, nasz arkusz będzie wyglądał tak:

To w sumie bardzo proste. Jeśli tylko w jakimś wierszu w kolumnie A znajdzie się data, to cały wiersz zmieni kolor na… taki śliczny, pomarańczowo-żółtawo-brązowy, czy jak to się tam nazywa.

Uwaga:

Nie, ten prześliczny kolor nie jest jedynym dostępnym. Informacje o innych kolorach i odpowiadających im wartościom numerycznym można znaleźć w tym artykule Office Space (j.ang.).

Do początku strony