Sumuj wartości z kolorowych komórek

Dzisiaj prosta wprawka z użyciem pętli For Each
Zaznaczamy zakres a makro zsumuje wartości z komórek wypełnionych dowolnym kolorem

Sub SumujKolorowe()
    Dim Kom As Range
    Dim Wynik As Double
    Dim Nic As String
    For Each Kom In Selection
        If Kom.Interior.ColorIndex <> xlNone Then
            Wynik = Wynik + Kom.Value
        End If
    Next
    Nic = _
        InputBox("W polu poniżej jest suma wartości znalezionych w kolorowych komórkach :)" _
        & vbNewLine & _
        "Możesz skopiować ten wynik (Ctrl+C) aby później go gdzieś wkleić", , Wynik)
End Sub

Warunkowe usuwanie wierszy

Spory kawałek kodu jak na tak prosty w sumie problem a to dlatego, że aplikacja sprawdza sobie najpierw czy ma z czym pracować i na dodatek robi kopię skasowanych wierszy

Option Explicit
 
Public Const WERSJA = "Warunkowe usuwanie wierszy v.1.0 (Akademia-VBA.pl)"
Public Const KOL_SPRAWDZANA As Long = 3
'
' Usuwa wiersz zakresu z nagłówkami gdy w kolumnie 3 jest pusto
' Usunięte wiersze zapisuje na wszelki w dodatkowym arkuszu
' Wcześniej aplikacja sprawdza, czy są dane
' i czy użytkownik ustawił w nich komórkę aktywną
'
 
Sub WarunkoweUsuwanieWierszy()
    Dim Zakres As Range
    Dim LW As Long, W As Long
    Dim Wiersz As Range
    Dim x As Long
    Dim ArkUsuwanychWierszy As Worksheet
    Dim Naglowki As Range
 
    If NienormalnyArkusz Then Exit Sub
    If NienormalneOkno Then Exit Sub
    If BrakZakresu Then Exit Sub
 
    Set Zakres = ActiveCell.CurrentRegion
    Set Naglowki = Zakres.Rows(1)
    Set ArkUsuwanychWierszy = Sheets.Add()
    ArkUsuwanychWierszy.Name = "Skasowane" & StempelCzasowy
    Naglowki.Copy ArkUsuwanychWierszy.Range("A1")
 
    LW = Zakres.Rows.Count
 
    For W = LW To 2 Step -1
        Application.StatusBar = "Analiza wiersza " & W
 
        If Len(Zakres.Cells(W, KOL_SPRAWDZANA)) = 0 Then 'czyli gdy pusto
            x = x + 1
            Set Wiersz = Zakres.Rows(W)
            Wiersz.Copy ArkUsuwanychWierszy.Cells(x + 1, 1)
            Wiersz.Delete
        End If
    Next
    ArkUsuwanychWierszy.UsedRange.EntireColumn.AutoFit
    Application.StatusBar = False
    MsgBox "Poczatkowa liczba wierszy: " & LW & vbNewLine & _
            "Liczba wierszy usuniętych: " & x & vbNewLine & _
            "Pozostało wierszy: " & LW - x, vbInformation, WERSJA
End Sub
 
Private Function NienormalnyArkusz() As Boolean
    If ActiveSheet.Type <> xlWorksheet Then
        MsgBox "Ustaw się w arkuszu danych!", vbExclamation, WERSJA
        NienormalnyArkusz = True
    End If
End Function
 
Private Function NienormalneOkno() As Boolean
    If ActiveWindow.Type <> xlWorkbook Then
        MsgBox "Ustaw się w oknie danych!", vbExclamation, WERSJA
        NienormalneOkno = True
    End If
End Function
 
Private Function BrakZakresu() As Boolean
    If Len(ActiveCell) = 0 Then
        MsgBox "Ustaw się w niepustej komórce zakresu danych!", vbExclamation, WERSJA
        BrakZakresu = True
    End If
End Function
 
Function StempelCzasowy() As String
    StempelCzasowy = Format(Now(), "_yyyymmdd_hhmmss")
End Function

Usuwanie fragmentów tekstu ograniczonego parą znaków

Ostatnio dwukrotnie zostałem poproszony o rozwiązanie problemu usuwania części tekstu pomiędzy parą znaków
Np „Ala (hihi) ma kota (hehe) a kot to” – powinno zostać zamienione na „Ala ma kota a kot to”
Oto rozwiązanie, pisane jak zwykle na szybko, więc pewnie ktoś już to kiedyś zrobił lepiej 🙂

' Wymaga biblioteki Excela
' ze względu na obiekt WorksheetFunction
Function PominTekst( _
    Tekst As String, Ogr1 As String, Ogr2 As String, _
    BezZbednychSpacji As Boolean)
 
    Dim Pocz As Long, Wynik As String
    Dim PozOgr1 As Long
    Dim PozOgr2 As Long
    Pocz = 1
    Do
        PozOgr1 = InStr(Pocz, Tekst, Ogr1)
        If PozOgr1 = 0 Then Exit Do
        PozOgr2 = InStr(PozOgr1 + 1, Tekst, Ogr2)
        Wynik = Wynik & Mid(Tekst, Pocz, PozOgr1 - Pocz)
        Pocz = PozOgr2 + 1
    Loop
    Wynik = Wynik & Mid(Tekst, Pocz)
    If BezZbednychSpacji Then
        PominTekst = WorksheetFunction.Trim(Wynik)
    Else
        PominTekst = Wynik
    End If
End Function

Wartość minimalna z tablicy

Ponieważ pytanie o minimalną wartość z tablicy pojawia się na forach VBA z częstotliwością semestralną, postanowiłem umieścić kawałek kodu, pomagający w rozwiązaniu problemu:

Function MinTablicy(Tablica As Variant) As Variant
    Dim WartMin As Variant
    Dim IndeksDolny As Long, IndeksGorny As Long, i As Long
    IndeksDolny = LBound(Tablica)
    IndeksGorny = UBound(Tablica)
    WartMin = Tablica(IndeksDolny)
    For i = IndeksDolny To IndeksGorny
      If Tablica(i) < WartMin Then WartMin = Tablica(i)
    Next
    MinTablicy = WartMin
End Function
 
Sub TestFunkcji()
    Dim Tablica() As Variant
    Tablica = Array(7, 3, 5, 2, 6)
    MsgBox "Minimalana wartość z tablicy to: " & MinTablicy(Tablica)
End Sub

Sprawdzenie dostępności pliku

Czasem chcielibyśmy wiedzieć, czy plik na którym chcemy wykonać jakąś operację, jest dla nas dostępny. Np gdy jest otwarty przez innego użytkownika próba jego użycia może powodować błąd. Ponadto wcześniej należy sprawdzić czy dany plik w ogóle istnieje. Można to sprawdzić przy pomocy poniższych funkcji:

Public Const WERSJA as String = "Dostępność pliku v.0.1"
Sub TestFunkcji()
    Const NAZWA_PLIKU As String = "C:\EX04\Baza.xls"
    If BrakPliku(NAZWA_PLIKU) Then Exit Sub
    If PlikNiedostepny(NAZWA_PLIKU) Then Exit Sub
    MsgBox "Plik jest dostępny, można coś z nim robić ;)", vbInformation
End Sub
 
Function PlikNiedostepny(NazwaPliku As String) As Boolean
    Dim NrPliku As Long
 
    On Error GoTo Obsluga
 
    NrPliku = FreeFile()
    Open NazwaPliku For Binary Access Read Write Lock Read Write As #NrPliku
    Close #NrPliku
    Exit Function
Obsluga:
    Select Case Err
    Case 70
        MsgBox "Plik jest obecnie otwarty przez inny proces", vbExclamation
    Case Else
        MsgBox "Przy próbie otwarcia pliku zgłaszany jest błąd: " _
            & Err & " - " & Err.Description, vbCritical
    End Select
    PlikNiedostepny = True
End Function
 
Function BrakPliku(PelnaNazwaPliku As String) As Boolean
    If Dir(PelnaNazwaPliku) = "" Then
        MsgBox "Brak pliku", vbCritical, WERSJA
        BrakPliku = True
    End If
End Function

Deklaracja zmiennych

Dla początkujących użytkowników VBA nieodmiennie zagadką jest deklarowanie zmiennych. Po pierwsze w ogóle nie wiadomo po co to pisać, skoro bez tego i tak działa, a po drugie nazwy typów zmiennych nie brzmią zbyt przyjaźnie.
Niestety brak stosowania i rozumienia deklaracji jest głównym powodem błędów, literówek, spowolnienia działania kodu i niewygody jego pisania. Temat nie jest bardzo prosty, ale bardzo ważny. Tu krótko wymienię zalety rozumienia i stosowania właściwych deklaracji:

  • Kod działa nawet do 4 razy szybciej
  • Łatwiej pisać kod bo zmienne zadeklarowane są wspomagane przez technologię podpowiadania słów kluczowych – Intellisense
  • Unikamy literówek
  • Piszemy kod wysokiej jakości pozwalający na szybsze wychwytywanie błędów – np niezgodność typu co przy przeoczeniu może prowadzić do negatywnych konsekwencji
  • Poprawiamy czytelność kodu

Problem polega na tym, że ta wiedza przychodzi z czasem, a tego jak zwykle wszystkim brakuje …

Bezpieczne wyłączenie odświeżania ekranu

Większość programistów VBA wie o tym, że aby przyspieszyć wykonanie procedury warto wyłączyć odświeżanie ekranu. Ma to kluczowe znaczenie np. przy generowaniu złożonych wykresów.
[cc lang=”vb”] Application.ScreenUpdating = False [/cc]
Ale już nie każdy potrafi napisać kod gwarantujący przywrócenie odświeżania. Brak odświeżania może drogo kosztować…
Należy to zrobić w sekcji czyszczenia obsługi błędów, wtedy wykona się zawsze, nawet po błędzie.

Sub GenerujWykres()
   'jakieś deklaracje
   On Error GoTo Obsluga
   Application.ScreenUpdating = False
   'jakiś kod
Czyszczenie:
   On Error Resume Next
   'jakieś inne sprzątanie
   Application.ScreenUpdating = True
   Exit Sub
Obsluga:
   MsgBox Err.Description
   Resume Czyszczenie
End Sub

Oczywiście w ten sposób powinniśmy obsłużyć wszystkie inne niebezpieczne zmiany dokonane przez aplikację na maszynie użytkownika. Na szczęście obsługi błędów nie trzeba pisać w podprocedurach, wystarczy ją napisać w procedurze głównej

Jak zacząć programowanie w VBA w Excelu?

Trzeba zacząć od dodania do Wstążki karty Deweloper. Karta Plik, zwana też dla zmylenia przeciwnika widokiem Backstage, pozwala oczywiście zarządzać plikiem (zapisywanie, drukowanie, właściwości itp.). Znajdziemy tu także opcje Excela. I właśnie te opcje nas teraz interesują.
Po wejściu w Opcje wybieramy Dostosowywanie Wstążki i zaptaszamy wybór karty Deweloper. Po zatwierdzeniu możemy obejrzeć nowo dodaną kartę, a na niej stare dobre przyciski ze starego dobrego paska Visual Basic (i komu on przeszkadzał?).

Tu przede wszystkim interesujemy się Bezpieczeństwem Makr, które w zdecydowanej większości przypadków powinniśmy zostawić w domyślnej opcji – Wyłącz wszystkie makra i wyświetl powiadomienie (dawny średni poziom zabezpieczeń). Pozwoli nam to na podjęcie decyzji czy włączyć makra po otwarciu pliku je zawierającego.

Kolejny ruch to zapisanie pliku jako Skoroszyt programu Excel z obsługą makr, bo tylko w takim typie pliku możemy zapisać kod pod Excelem 2007/2019. I to z grubsza tyle, możemy sobie teraz nagrać i edytować jakieś makro, czyli procedurę publiczną VBA. Możemy też napisać jakąś własną funkcję publiczną i używać jej jak funkcji wbudowanej Excela. Co bardziej ambitni mogą oprogramować zdarzenia skoroszytu czy arkusza a najbardziej wytrwali mogą nawet zaprojektować i oprogramować własny formularz. Powodzenia!
Jeszcze jedna uwaga. Gdyby przyszło nam do głowy rozpowszechniać kod w postaci szablonu musimy pamiętać aby zapisać plik jako Szablon programu Excel z obsługą makr

Zabezpieczenia makr, poziomy zabezpieczeń

Na szczęście makrowirusy nie są ostatnio bardzo popularne, ale proszę zawsze uważać, lepiej dmuchać na zimne. Poziom zabezpieczeń ustawiamy z menu – Narzędzia – Makro – Zabezpieczenia.

Nie wolno ustawiać niskiego poziomu zabezpieczeń, ponieważ wtedy każdy uruchomiony plik zawierający makro, będzie mógł je bez pytania uruchomić. W większości przypadków należy użyć średniego poziomu zabezpieczeń, który gwarantuje zadanie pytania czy włączyć makra, przy uruchamianiu pliku. Proszę się dobrze zastanowić przed odpowiedzią na to pytanie!

Nigdy nie należy włączać makr w obcym nieznanym pliku!

Zabezpieczenia wysokie i bardzo wysokie stosujemy tylko gdy mamy wykupiony certyfikat bezpieczeństwa do projektu VBA co się raczej rzadko zdarza

Jak skorzystać z kodu VBA znalezionego w Internecie

Dla początkujących może być to poważny problem. Przede wszystkim trzeba ocenić czy przypadkiem kod nie jest szkodliwy. Na szczęście w przypadku kodu VBA jest to bardzo rzadko spotykane. Po drugie trzeba rozróżnić czy jest to kod do umieszczenia w module publicznym, czy prywatnym module formularza, raportu, skoroszytu czy arkusza.

Moduł publiczny wstawiamy z menu Instert – Module (wcześniej Alt-F11 aby otworzyć edytor VBA). Tu wklejamy funkcje, które mają być dostępne z poziomu funkcji użytkownika w Excelu czy też w każdej kwerendzie, raporcie czy formularzu w Accessie (np wrzucamy tu funkcję Slownie, jeżeli chcemy aby była powszechnie dostępna w całej bazie)

Moduł prywatny zawsze związany jest z jakimś obiektem. Tylko tu możemy umieścić kod automatycznie wykonujący się z powodu zdarzeń generowanych przez użytkownika takich jak otwarcie czy zamknięcie dokumentu, lub kliknięcie w przycisk. Jeżeli widzimy kod bezpośrednio odwołujący się do kontrolek na formularzu, jak np poniższe dwie procedury:

Private Sub UserForm_Initialize()
 
    przyciskUruchom.Enabled = False
    listaFunkcji.AddItem "Funkcja1"
    listaFunkcji.AddItem "Funkcja2"
 
End Sub
 
Private Sub listaFunkcji_Click()
    przyciskUruchom.Enabled = True
End Sub

to znaczy, że aby przetestować taki kod trzeba: po pierwsze trzeba utworzyć formularz, po drugie umieścić na nim pole listy i przycisk a po trzecie odpowiednio je ponazywać (właściwość Name kontrolki). Efektem powyższych procedur jest początkowe wyłączenie przycisku i załadowanie pola listy. Następnie gdy użytkownik kliknie coś na liście, przycisk zostanie włączony.