Akademia VBA – Oferta

Wyróżnione

Opublikowany przez
Odpowiedz

Proponuję Państwu utworzenie i utrzymanie aplikacji dedykowanych pod konkretne potrzeby lub naprawę czy napisanie od nowa aplikacji już istniejących. Mogę też audytować kod pod względem bezpieczeństwa i wydajności. Często wraz z odejściem pracownika ginie wiedza jak dana aplikacja działa. Tu także mogę pomóc.

Moje aplikacje wyróżnia:

  • Dopasowanie do potrzeb klienta
  • Otwarty i dobrze skomentowany kod źródłowy
  • Instrukcja obsługi aplikacji
  • Szkolenia umożliwiające rozwijanie aplikacji własnymi siłami.

Mam 25 lat doświadczenia w tworzeniu aplikacji dla biznesu. Duże sieci handlowe, stacja telewizyjna, koncern górniczy ale także mniejsze firmy jak produkcja piekarnicza czy ogrodnicza, są od lat moimi klientami. Tworzę aplikacje głownie w oparciu o środowisko Excel i VBA ale także SQL (SQL Server), Access i Power BI.

Pozdrawiam i zapraszam do współpracy

Dariusz Kolasa

Proste w użyciu operacje na plikach

Opublikowany przez
1

Mało kto wie, że VBA ma fajne i proste w użyciu funkcje do odczytywania podstawowych informacji o pliku. Poniżej przykłady wykonania z debuggera:

?FileDateTime("C:\Users\dariu\Desktop\kotek.png")
2018-10-30 21:14:47

Oczywiście zwraca datę ostatniej modyfikacji pliku

?FileLen("C:\Users\dariu\Desktop\kotek.png")
135590

Zwraca wielkość pliku w bajtach.

Także znajomość niezwykle praktycznej funkcji FileCopy jest mocno ograniczona. Jej działanie jest chyba jasne 🙂

FileCopy "C:\Users\dariu\Desktop\kotek.png", _
        "C:\Users\dariu\Desktop\kotek2.png"

Podstawy SQL – Kolejność klauzul w SELECT

Opublikowany przez
Odpowiedz

Poważna aplikacja VBA z często korzysta z jakiejś bazy danych aby odczytać, dopisać a czasem zaktualizować lub usunąć jakieś dane. Dlatego warto poznać co najmniej podstawy języka SQL. Jest to język dość prosty jeżeli chodzi o podstawy, ale oczywiście jeżeli mamy nietrywialne potrzeby, zapytania też się komplikują. Co musisz wiedzieć na pewno to jak odczytać dane z bazy. Z artykułu o ADO wiesz, że z VBA można nawiązać połączenie z Access i SQL Server. Tam jako źródło zapytania jest po prostu podana nazwa tabeli. Ale oczywiście zamiast tego możemy podać dowolne wyrażenie typu SELECT. I tu na początek trzeba wiedzieć kilka rzeczy. Po pierwsze kolejność klauzul jest ściśle określona i jest następująca:

 SELECT pole1, pole2, poleN...
 FROM tabela (tabela1 JOIN tabela2 ON klucz1 = klucz2)
 WHERE warunek1 AND/OR warunek2...

Tak wygląda najprostszy select bez grupowania i wyliczeń. Zasadą jest, że piszemy najpierw FROM a potem wypisujemy pola w SELECT, bo tylko wtedy dobry edytor kodu SQL może nam podpowiedzieć nazwy pól. We FROM podajemy nazwę pojedynczej tabeli lub wiele tabel ale wtedy musimy wiedzieć po jakich polach kluczowych trzeba je połączyć. Sytuacja komplikuje się gdy chcemy w zapytaniu grupować dane i coś wyliczać dla tych grup:

 SELECT pole1, pole2, SUM(pole3) AS Razem
 FROM tabela (tabela1 JOIN tabela2 ON klucz1 = klucz2)
 WHERE warunek1 AND/OR warunek2...
 GROUP BY pole1, pole2
 HAVING SUM(pole3) > 1000
 ORDER BY Razem DESC

Jak widać użyliśmy w SELECT funkcji SUM, która współpracuje z klauzulą GROUP BY definiującą dla jakich grup chcemy policzyć sumy. Dodatkowo w klauzuli HAVING informujemy, że interesują nas tylko sumy przekraczające 1000. ORDER BY to oczywiście sortowanie, w tym wypadku po sumach w porządku malejącym (DESC jak descending). W SELECT za polem trzecim mamy as Razem co oznacza jak chcemy nazwać to wyliczane pole. I tu ciekawostka. Skoro już nazwaliśmy to pole Razem to czemu wykorzystaliśmy tą nazwę tylko w ORDER BY Razem DESC ale już nie w HAVING gdzie ponownie użyliśmy wyrażenia SUM(pole3) > 1000?. Otóż wynika z ważnej zasady, że kolejność pisania klauzul w SELECT nie odpowiada kolejności przetwarzania ich przez serwer! Okazuje się, że klauzula SELECT jest przetwarzana dopiero jako piąta!

 SELECT pole1, pole2, SUM(pole3) AS Razem              --5!!!
 FROM tabela (tabela1 JOIN tabela2 ON klucz1 = klucz2) --1
 WHERE warunek1 AND/OR warunek2...                     --2
 GROUP BY pole1, pole2                                 --3
 HAVING SUM(pole3) > 1000                              --4
 ORDER BY Razem DESC                                   --6

I dlatego tylko klauzula ORDER BY jako jedyna przetwarzana po SELECT może użyć zdefiniowanej w niej nazwy pola!

ADOX

Opublikowany przez
Odpowiedz

Bardzo stara i bardzo fajna biblioteka do obsługi struktury bazy. Na liście referencji nazywa się Microsoft ADO Ext. XX for DDL and Security. Potrzebna jest też biblioteka ADO do nawiązania połączenia. Kluczowy jest ADOX.Catalog, który pozwala na iterowanie po tabelach, polach itp.
Poniższy kod wymaga tzw. connection string (stała CN_STR), których przykłady są w artykule o ADO – i wszystkie bazy są nasze

Sub Lista_Tabel_Kolumn()
    Dim cn As New ADODB.Connection
    Dim cat As New ADOX.Catalog
    Dim tb As ADOX.Table
    Dim k As ADOX.Column
 
    cn.ConnectionString = CN_STR 'dodaj swoje ustawienia!
    cn.Open
 
    Set cat.ActiveConnection = cn
 
    For Each tb In cat.Tables
        For Each k In tb.Columns
            Debug.Print tb.Name, k.Name
        Next
    Next
End Sub

Funkcja do sprawdzania sprawdzania poprawności

Opublikowany przez
Odpowiedz

Czasem możemy chcieć sprawdzić czy w danej komórce ustawiono sprawdzanie poprawności (walidację), np. aby jej nie popsuć wklejaniem
Poniżej funkcja logiczna, która to sprawdza

Function JestSprawdzaniePoprawnosci(KomSprawdzania As Range) As Boolean
    Dim Typ As Long
 
    On Error GoTo Obsluga
 
    Typ = KomSprawdzania.Validation.Type
    JestSprawdzaniePoprawnosci = True
    Exit Function
 
Obsluga:
    JestSprawdzaniePoprawnosci = False
 
End Function

Wysyłanie wiadomosci e-mail z VBA

Opublikowany przez
1

Collaboration Data Objects (CDO) to biblioteka umożliwiająca wysyłanie wiadomosci e-mail. Była dedykowana dla Windows 2000 ale działa do dziś 🙂

Kod oczywiście wymaga poprawek w zakresie adresów e-mail jak i danych serwera wysyłającego pocztę. Niektóre serwery (np Gmail) mogą wymagać dodatkowych ustawień dla użycia przez CDO

'Wymaga biblioteki Microsoft CDO for Windows 2000 Library
 
Sub WyslijMail_CDO()
    Dim Wiadomosc As CDO.Message
    Dim Konfiguracja As CDO.Configuration
 
    Set Wiadomosc = New CDO.Message
    Set Konfiguracja = New CDO.Configuration
 
    Const ADRES_KONFIGURACJI As String = "http://schemas.microsoft.com/cdo/configuration/"
 
    Konfiguracja.Load cdoDefaults
 
    With Konfiguracja.Fields
        .Item(ADRES_KONFIGURACJI & "sendusername") = "login"          'login
        .Item(ADRES_KONFIGURACJI & "sendpassword") = "hasło"          'hasło
        .Item(ADRES_KONFIGURACJI & "smtpserver") = "poczta.o2.pl"     'serwer SMTP
        .Item(ADRES_KONFIGURACJI & "smtpserverport") = 465            'port
        .Item(ADRES_KONFIGURACJI & "sendusing") = cdoSendUsingPort    'metoda wysyłania
        .Item(ADRES_KONFIGURACJI & "smtpauthenticate") = cdoBasic     'metoda autentykacji
        .Item(ADRES_KONFIGURACJI & "smtpusessl") = 1                  'kodowany kanał
        .Update
    End With
 
    Set Wiadomosc.Configuration = Konfiguracja
 
    Wiadomosc.From = "adres1@o2.pl"
    Wiadomosc.To = "adres2@gmail.com"
    Wiadomosc.Subject = "Test CDO"
    Wiadomosc.TextBody = "Hej" & vbNewLine & "To jest test biblioteki CDO" & _
        vbNewLine & vbNewLine & "Pozdrawiam" & vbNewLine & "Darek"
 
    Wiadomosc.Send
 
End Sub

Sumuj wartości z kolorowych komórek

Opublikowany przez
2

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

Usuwanie niedozwolonych znaków w nazwie pliku

Opublikowany przez
2

Jest to oczywiście nawiązanie do napisanej wcześniej funkcji sprawdzającej czy takie znaki występują (dostępna tutaj). Tym razem wersja konwertująca niedozwolony znak na podkreślenie, bez żadnych komunikatów, wygodna do użycia w pętli itp.

Function SkonwertowanaNazwaPliku(SprawdzanaNazwa As String) As String
    Dim tbl As Variant
    Dim NrZn As Long
    Dim Dlugosc As Long
    Dim Znak As String * 1
    Dim tblZnak As Variant
    Dim NiedozwolonyZnak As Boolean
 
    tbl = Array("#", "%", "&", "*", ":", "<", ">", "?", "/", "\", "{", "|", "}")
    Dlugosc = Len(SprawdzanaNazwa)
    For NrZn = 1 To Dlugosc
        Znak = Mid(SprawdzanaNazwa, NrZn, 1)
        NiedozwolonyZnak = False
        For Each tblZnak In tbl
            If tblZnak = Znak Then
                NiedozwolonyZnak = True
                Exit For
            End If
        Next
        If NiedozwolonyZnak Then
            SkonwertowanaNazwaPliku = SkonwertowanaNazwaPliku & "_"
        Else
            SkonwertowanaNazwaPliku = SkonwertowanaNazwaPliku & Znak
        End If
    Next
End Function