excel rządzi, excel radzi, excel nigdy cię nie zdradzi

Wpisy

  • wtorek, 22 czerwca 2010
  • sobota, 19 czerwca 2010
    • Excel VBA: jak przyspieszyć aplikację

      Mozolnie piszesz linijki kodu w VBA, testujesz - wszystko pięknie działa. Przerzucasz się z danych testowych na rzeczywiste i... zonk: okazuje się, że gdy aplikacja ma pracować ze 150 arkuszami, na każdym z nich jest 255 kolumn i kilkadziesiąt tysięcy wierszy - staje się bardzo powoooolnaaaa, komputer czasem przestaje reagować, a wyniki dostajesz po 3 godzinach.

      Może to oznaczać trzy rzeczy:

      1. Pora zmienić komputer. Lub:
      2. Pora przemyśleć swoją aplikację. Lub:
      3. Pora przerzucić się na jakiś język programowania "porządny" ;)

       

      W tym ostatnim punkcie trochę żartuję - VBA jest dość przyjazny, a korzystanie z arkuszy i zakresów, z możliwością umieszczania dowolnych formantów (guziki, pola kombi, itp.) przy budowaniu prostego interfejsu użytkownika jest nie do pobicia. Komputer możesz zmienić oczywiście, ale punkt nr 2 jest w tej chwili najistotniejszy.

      Co możesz zmienić w aplikacji?

      Po pierwsze - jeśli dużo danych znajduje się w arkuszach, a Ty przełączasz się między arkuszami, by je pobrać/zmienić/itp. - zastanów się, czy na pewno musisz skakać z miejsca w miejsce bez powodu. Można przecież odwoływać się do odpowiednich arkuszy podając ich numer albo nazwę:

      ' bardzo słaby kod, serio - widziałem taki
      sheets("dane").Activate
      ActiveSheet.range("a1").Select
      ActiveCell.value = 8

      ' wersja poprawiona (niekoniecznie najlepsza)
      sheets("dane").range("a1").value = 8

      Po drugie - jeśli nie potrzebujesz w czasie rzeczywistym wyświetlać wyników działania programu - wyłącz odświeżanie ekranu Excela. Znam przypadki, w których spowodowało to ponad dwukrotne przyspieszenie działania! Kod jest prosty:

      ' żeby wyłączyć odświeżanie ekranu
      Application.ScreenUpdating = false

      ' tutaj Twój kod

      ' żeby włączyć odświeżanie ekranu
      Application.ScreenUpdating = true

      Można to wrzucić w każdą procedurę, która dużo miesza na arkuszach i w zakresach. Efekt powinien być natychmiastowy. Aby nie bawić się w ciągle pisanie - przygotowałem sobie procedurę, która włącza bądź wyłącza odświeżanie ekranu - bez mojego zastanawiania się, czy mam wpisać true czy false. No i mniej pisania:

      ' przełącza ekran w tryb aktualizacji bądż wyłącza ją
      ' opcjonalnie można podać parametr Boolean - nazwa z miennej
      ' w której będzie aktualny stan screenupdating podany
      Public Sub ekran(Optional co As Boolean = False)
      Application.ScreenUpdating = Not Application.ScreenUpdating
      co = Application.ScreenUpdating
      End Sub

      ' przykład poprzedni teraz by wyglądał tak:

      ' żeby wyłączyć odświeżanie ekranu
      ekran

      ' tutaj Twój kod

      ' żeby włączyć odświeżanie ekranu
      ekran

      Proste i przydatne.

      Po trzecie - jeśli dużo pracujesz na zakresach (Range) zmieniając w nich wartości, pobierając dane i w ogóle - dużo Range masz albo Cells - zastanów się nad wczytywaniem całego zakresu do tablicy dwuwymiarowej, obróbką danych w tablicy i dopiero na koniec z powrotem wypluj to do zakresu. Częste odwołania do Range w arkuszach bardzo spowalniają aplikacje excelowe. Praca z tablicami jest równie łatwa, a przyspieszenie - cóż, zauważysz je na pewno. Zresztą, wystarczy, że uruchomisz poniższy przykład:

      Sub test()
      Dim t() As Variant
      t = activesheet.Range("a1:z6000").Value
      For i = 1 To UBound(t)
      For j = 1 To UBound(t, 2)
      t(i, j) = i * j
      Next j
      Next i

      Activesheet.Range("a1:z6000") = t
      MsgBox "już"

      ' teraz standardowy sposób
      ' jak komputer nie reaguje, to go zostaw na kilka
      ' sekund albo minut...
      For i = 1 To UBound(t)
      For j = 1 To UBound(t, 2)
      activesheet.Range("a1:z6000").Cells(i, j) = i * j
      Next j
      Next i
      MsgBox "już?"
      End Sub

      Czas to pieniądz, jak to mówią. Pamietaj tylko, że zmienna, do której wczytujesz dane musi być tablicą Variant. A druga rzecz - zawsze, nawet jeśli wczytujesz tylko jeden wiersz (np. za pomocą .EntireRow) zmienna będzie dwywymiarową tablicą i do jej elementów odwołujesz się: zmienna(x, y).

      W jednej z firm, z którymi współpracowałem, aplikacja w VBA liczyła coś od 2 do 6 godzin, a komputera nie dało się wtym czasie używać. Zastosowanie połączenia opisanych w tym artykule metod przyniosło efekt w postaci skrócenia czasu pracy do góra... 6 minut.

      Szczegóły wpisu

      Komentarze:
      (2) Pokaż komentarze do wpisu „Excel VBA: jak przyspieszyć aplikację”
      Tagi:
      Kategoria:
      Autor(ka):
      originalhmm
      Czas publikacji:
      sobota, 19 czerwca 2010 14:03
  • poniedziałek, 14 czerwca 2010
    • Excel VBA: zdarzenie onPaste? (onPaste event?)

      Niestety w VBA Excela, przynajmniej tych w wersjach <=2003, nie ma zdarzenia onPaste lub podobnego, choć czasem by się przydało. Ostatnio brakowało mi go nawet bardziej niż zwykle - potrzebowałem kopiować pojedyncze wiersze z tabeli w MS Access i wklejać je do Excela. Problem polegał na tym, że po zaznaczeniu wiersza do skopiowania w tabeli i skopiowaniu go do schowka oraz po wklejeniu go w Excelu - na arkuszu lądowała zarówno zawartość wiersza jak i nagłówki kolumn (choć ich nie zaznaczałem).

      Zdarzenia onPaste lub podobnego nie udało mi się znaleźć i już prawie się poddałem, gdy przypomniałem sobie o rzeczy, na którą zwykle nie zwracam uwagi: przecież można pod skróty klawiaturowe CTRL+coś podpiąć makra! Pod CTRL+V (standardowy skrót dla "wklej") również.

      No to jestem w domu - w edytorze VBA piszę sobie procedurę:

      Sub FakeOnPaste () 
       ActiveSheet.Paste
        Selection.Rows(1).Delete shift:=xlUp
      End Sub

      która działa tak: w aktywną komórkę wkleja zawartość schowka, po wklejeniu - domyślnie - cały wklejony zakres zostaje zaznaczony przez Excela, w zaznaczonym zakresie usuwam pierwszy wiersz - ten z nagłówkami, który jest mi niepotrzebny, a pozostałe wiersze zostają przesunięte o 1 do góry. Pozostaje tylko przypisać skrót do tej procedury - w Excelu: Narzędzia->Makro->Makra. Wybieram makro FakeOnPaste i klikam Opcje, w których przypisuję klawisz skrótu - CTRL+V.

      Teraz każde naciśnięcie CTRL+V wkleja zawartość schowka do arkusza, po czym usuwa pierwszy, zbędny, wiersz. Oczywiście metoda działa tylko dla wklejania za pomocą klawiatury - użycie menu albo ikonki na pasku narzędzi niestety nie uruchomi makra.

      Sama metoda .Paste może się przydać do wielu innych rzeczy, tu akurat 2 linijki kodu znacznie ułatwiły mi pracę. Może więc i Wam się do czegoś pomysł na moje FakeOnPaste przyda.

       

       

      Szczegóły wpisu

      Komentarze:
      (0)
      Tagi:
      Kategoria:
      Autor(ka):
      originalhmm
      Czas publikacji:
      poniedziałek, 14 czerwca 2010 16:14
  • wtorek, 08 czerwca 2010
    • Excel funkcja: wyszukaj.pionowo

      Zobaczyłem dziś rzecz straszną. Siedzi pani i ma Excela włączonego - w jednym arkuszu tabelka do wypełnienia, w drugim - jakieś wartości. Pani przegląda wiersz po wierszu pierwszą tabelkę i sprawdza w kolumnie C wartości, po czym przełącza się na drugi arkusz, szuka wartości i przepisuje na poprzedni arkusz inną wartość, zależną od tej pierwszej.

      Na moje pytanie ile czasu jej to zajmuje i jak często to robi - odparła "Co tydzień, no ze 2-3 godziny schodzą na to, zależy ilu klientów mieliśmy".

      Kto ją zatrudnił nie zapytam, ale czy można inaczej? Można. Jest taka funkcja, która się nazywa WYSZUKAJ.PIONOWO (vlookup) i która robi w mgnieniu oka to, co tamta pani robi co tydzień przez kilka godzin.

      Funkcja wyszukaj.pionowo ma 4 argumenty (parametry), o nieco nietrafionych i mylących nazwach:

      1. szukana_wartość - wymagany - to jest coś, co tak na prawdę mamy i znamy, od czego zależy jakaś inna wartość (której nie znamy, ale mamy w innej tabelce)
      2. tabela_tablica - wymagany - zakres komórek, gdzie w pierwszej kolumnie tego zakresu znajdują się wartości, wśród których możemy odnaleźć szukana_wartość, a w pozostałych kolumnach znajdują się jakieś inne wartości.
      3. nr_indeksu_kolumny - wymagany - liczba - numer kolumny z tabela_tablica, z której chcemy żeby funkcja wyszukaj.pionowo zwróciła wartość
      4. przeszukiwany_zakres - opcjonalny - prawda/fałsz albo 1/0 - jeśli ten parametr jest pusty albo 1 albo PRAWDA, to funkcja będzie szukała szukana_wartość, ale jeśli jej nie znajdzie - weźmie najbliższą mniejszą; czyli takie wyszukiwanie "przybliżone"; natomiast gdy FAŁSZ albo 0 - funkcja będzie szukała tylko szukana_wartość, nic innego, a jak nie znajdzie - zwróci błąd "wartość niedostępna"

      Przykład

      Mam tabelkę (A1:D6)

      Produkt Ilość Cena Do zapłaty
      A 3 ? ?
      B 2

      C 4

      D 5

      E 6

      Oraz drugą tabelkę, cennik: (G5:H10) - tabelka powinna być posortowana wg. pierwszej kolumny (dotyczy głównie przypadku z ostatnim parametrem równym 1 lub PRAWDA).

      Produkt Cena
      A 10
      B 20
      C 15
      D 7
      E 8

      Jak policzyć cenę i do zapłaty:

      cena: =WYSZUKAJ.PIONOWO(A2 ; $G$5:$H$10 ; 2 ; 0)
      do zapłaty: = B2*C2

      Prawda, że proste? Małe wyjaśnienie słowne. Cena zależy od nazwy produktu, więc skoro wypełniam kolumnę "cena", to szukana_wartość to znana mi nazwa produktu z komórki A2. Gdzie szukam tej nazwy? W pierwszej kolumnie (tego się nie da zmienić) tabelki w zakresie $G$5:$H$10. Dlaczego z $? Ponieważ za chwilę będę kopiował moją formułę liczącą cenę do kolejnych wierszy i chcę, by cennik był taki sam w kolejnych wierszach. Wypełniam kolumnę cena, więc z której kolumny w cenniku chcę pobierać wartości? Oczywiście z drugiej, więc wpisuję jako trzeci parametr 2. Czy gdyby produktu Z nie było na liście, to może on kosztować tyle, co produkt X? Nie powinien - wtedy chcę zobaczyć błąd. Dlatego ostatni parametr to zero.

      Wynik działania:

      Produkt Ilość Cena Do zapłaty
      A 3 10 30
      B 2

      B 4

      C 5

      E 6

      Teraz mogę zaznaczyć komórki C2:D2 i skopiować je w dół do kolejnych wierszy. Jeśli wszystko poszło ok - ostateczny wynik:

      Produkt Ilość Cena Do zapłaty
      A 3 10 30
      B 2 20 40
      B 4 20 80
      C 5 15 75
      E 6 8 48

      Zagadka: do czego w takim razie można użyć wersji tej funkcji z ostatnim parametrem pustym albo równym 1 bądź PRAWDA.

      PS. tabelki z wpisu można zaznaczyć, skopiować do schowka po czym wkleić w Excelu. Ale uwaga - zwykłe wklejanie może nie działać - trzeba użyć "wklej specjalnie" i wybrać "tekst".

       

      Szczegóły wpisu

      Komentarze:
      (0)
      Tagi:
      Kategoria:
      Autor(ka):
      originalhmm
      Czas publikacji:
      wtorek, 08 czerwca 2010 12:13
    • Blox i google w jednym stali domu

      Ok, to jest już przegięcie... Jeden wpis na nowym blogu i 20 minut później jestem w googlu. Sprawdź, wpisując excel rządzi. Też masz mnie na 1 miejscu? (daj znać w komentarzach)

      Czyżby więc wszystko zależało nie od tego, co piszę, ale GDZIE piszę?

      Dobranoc

      PS. no to mam wyzwanie - excel vba na 1 miejscu wyników wyszukiwania ;)

      Szczegóły wpisu

      Komentarze:
      (0)
      Tagi:
      Kategoria:
      Autor(ka):
      originalhmm
      Czas publikacji:
      wtorek, 08 czerwca 2010 00:41
    • Excel VBA: potrzebny mi cały wiersz na podstawie wartości z jednej kolumny

      Zadanie

      Mam tabelkę z różnymi dziwnymi danymi. Chcę móc wyszukać w dowolnej kolumnie dowolną wartość i po jej znalezieniu - mieć dostęp do danych z całego wiersza, w którym jest ta wartość. Funkcja/procedura ma być dostępna z posiomu VBA, nie musi być dostępna z arkusza.

      Rozwiązanie

      Idziemy na łatwiznę i robimy procedurę. Jeden z jej parametrów będzie odwołaniem do zmiennej (w uproszczeniu), w której będziemy trzymać wynik. Jeśli nie zostanie znaleziona komórka z tekstem szukanym - zwracamy wartość Nothing. Do działania użyjemy metody Find.

      Public Sub getRowWith(co As String, _
      zakres As Range, wynik As Range)
      Dim tmp As Range

      Set tmp = zakres.Find(What:=co, _
      After:=zakres.Cells(zakres.Cells.Count), _
      LookIn:=xlValues, _
      LookAt:=xlWhole, _
      SearchOrder:=xlByRows, _
      SearchDirection:=xlNext, _
      MatchCase:=False)
      If Not tmp Is Nothing Then
      Set wynik = tmp.EntireRow
      Else
      Set wynik = Nothing
      End If

      End Sub

      Prawda, że proste?

      Zastosowanie

      Przypuśmy, że mam na arkuszu tabelkę w komórkach A1:C3

      id komunikat PL EN
      1 Pole musi być wypełnione to se wypełnij!
      spik inglisz!
      2 Arkusz zablokowany dla zmian no to co z tego!
      so what!

      i z poziomu VBA chcę znaleźć cały wiersz, w którym kolumna "komunikat" zawiera tekst "Pole musi być wypełnione". Mam już procedurę i nie zawaham się jej użyć! Przykładowe makro, które da się uruchomić z powyższą procedurą:

      sub makro1()
      dim tuchce as range

      getRowWith "Pole musi być wypełnione", Range("b1:b3"), tuchce

      msgbox tuchce.cells(1,1)
      msgbox tuchce.cells(1,2)
      msgbox tuchce.cells(1,3)
      end sub

      Takie trochę wyszukaj.pionowo (vlookup) tylko trochę inaczej. Oczywiście nic nie stoi na przeszkodzie, by przerobić procedurę na funkcję. Mi akurat była potrzebna taka, bo w wyniku dostaję wiersz, który jest typu Range. Używam tej procedury z inną - getColumnWith, co pozwala mi na łatwe znalezienie komórki znajdującej się na przecięciu określonego wiersza i kolumny. Ale o tym następnym razem.




      Szczegóły wpisu

      Komentarze:
      (0)
      Tagi:
      Kategoria:
      Autor(ka):
      originalhmm
      Czas publikacji:
      wtorek, 08 czerwca 2010 00:07

Archiwum

Kategorie

Kanał informacyjny

tak, błąd jest zamierzony ;)