Autmoatyczne Scalanie i rozcalanie komórek za pomocą VBA

post przeniesiony na http://heap.pl/scalanie_rozcalanie_vba/

MAKRO - Usuwanie zbędnych spacji z zaznaczonych komórek

Często zdarza mi się dostać od kogoś plik w którym ktoś na początku lub końcu komórki w tekście wpisał spacje.
Jak się ich pozbyć?

KOD

Sub usunZbedneSpacje()

On Error Resume Next

   Dim komorka As Range          ' zmienna która będzie wskazywała aktualną komórkę

   For Each komorka In Selection ' dla każdej zaznaczonej komórki rób:

                                 ' trim - usuwa białe znaki z obu lewej i prawej tekstu.

      komorka = Trim(komorka)    ' wynik zapisujemy z powrotem do tej samej komórki

   Next komorka                  ' koniec for. Czyli przejdź do następnej komórki

End Sub

KONIEC! Widzisz jakie proste?

A jeśli chcemy coś więcej....

Modyfikacje:

Potrzebujemy usunąć również podwójne spacje ze środka tekstu

wystarczy zamiast funkcji Trim użyć funkcji WorksheetFunction.Trim

czyli linijka może wyglądać tak:

        komorka = WorksheetFunction.Trim(komorka)    

Tutaj użyliśmy funkcji która dodatkowo wszystkie podwójne spacje zamienia na pojedyncze.

Jeśli chcesz usunąć wszystkie spacje z komórek.

        komorka = replace(komorka, " ", "")

Funkcja Replace działa jak znajdź i zamień. Tutaj chcemy, żeby w komórce znalazł wszystkie spacje (" ") i je zlikwidował, czyli zamienił na nic ("")

Jeśli potrzebujemy usunąć spacje z konkretnego zakresu (a nie z zaznaczenia)

Pętla For each jest tak fajna, że możesz użyć jej również w tym przypadku.

Poniżej przykład pętli, która iteruje po wszystkich komórkach z zakresu od A1 do B20 oraz w kolumnie C:

For Each komorka In Range("A1:B20,C:C")

Jeśli masz pytania / uwagi / propozycje jakie makra byłyby Ci potrzebne to pisz w komentarzach

Excel - Formatowanie Warunkowe - zadanie i rozwiązanie

ZADANIE

Zadanie polega na wyróżnieniu wszystkich osób o wieku poniżej 30 lat.
a) chcemy pokolorować tylko ich wiek
b) chcemy pokolorować cały wiersz w którym jest taka osoba

Do zadania będzie potrzebna baza osób kliknij tu (xlsx) lub tu (zip)

Najwygodniej rozwiązać to zadanie używając formatowania warunkowego.
Dzięki temu po zmianie danych kolorowanie komórek również się zmieni. (będzie aktualizowało się na bieżąco)

Rozwiązanie pkt a)

To do dzieła!

1. Zaznaczamy wszystkie komórki zawierające wiek.
2. z karty Narzędzia Główne wybieramy Formatowanie Warunkowe -> Nowa Reguła -> Formatuj komórki zawierające
3. wpisujemy kryteria w kolejnych kratkach (konkretnie: wartość, mniejsza niż, 30)
4. musimy jeszcze wybrać kolor na który mają sformatować się komórki spełniające kryteria, czyli Formatuj -> karta Wypełnienie -> wybieramy kolor
5. Klikamy OK

Możesz zobaczyć to tutaj

Rozwiązanie pkt b)

1. Zaznaczamy całą tabelę (bez nagłówków) a nie tylko wiek.
   (bo jeśli wszyscy mieliby poniżej 30 lat, to chcemy kolorować wszystkie komórki)
2. z karty Narzędzia Główne wybieramy Formatowanie Warunkowe -> Nowa Reguła -> Użyj formuły do określenie komórek...
3. wpisujemy formułę: =$E2 < 30
   Formułę piszemy tak, żeby była prawdziwa jeśli komóreka ma być kolorowana, w przeciwnym razie ma dać w wyniku fałsz.
   Formuła ma być napisana dla lewej górnej komórki (aktywnej) czyli w naszym przypadku dla A2
   A po co znaczek $ ?
   musimy wstawić go przed E, ponieważ Excel kopiując komórki powinien zawsze sprawdzać wiek (kolumnę E). Jeśli zabrakło by $ to Excel Dla liczb porządkowych działałoby prawidłowo, ale dla kolejnych kolumn przesuwałby kryteria w prawo (dla imion sprawdzałby zawód (kolumna F), a dla nazwisk kolumnę G)
4. nie zapomnij o zdefiniowaniu formatowania komórek (ja wybrałem kolor niebieski)

Możesz zobaczyć to tutaj

Excel - Zamiana dat na prawdziwe daty - rozwiązanie

Zadanie

Często spostyjanmy się z sytuacją, gdy osoba która miała wpisać daty wprowadza je nieprawidłowo do Excela.
Problem w tym, że format daty do którego jesteśmy przyzwyczajeni np. 27.02.2013 nie jest akceptowany przez Excela. Program traktuje takie daty jako tekst, czyli nie różni się niczym od ab.cd.efgh

Chcielibyśmy znaleźć sposób na szybką zamianę takich dat na prawdziwe daty.

Rozwiązanie

Posłużymy się tutaj narzędziem tekst jako kolumny z karty Dane.
W tym celu: Zobacz film

1. zaznaczamy kolumnę z datami
2. Z karty Dane wybieramy narzędzie tekst jako kolumny
3. Zaznaczamy opcję rozdzielany i klikamy Dalej
4. Odznaczamy wszystkie opcje ( bo nie chcemy rozdzielać tej kolumny) i klikamy dalej
5. Zaznaczamy opcję Data i wybieramy DRM (czyli kolejność w jakiej zapisane są nasze "prawie daty")
6. Jeżeli chcemy mieć daty w innej kolumnie to w polu miejsce docelowe zaznaczamy komórkę w której mają się zacząć pojawiać daty
7. Klikamy Zakończ.
   Prawdziwe daty powinny wyrównać się do prawej strony komórki, a tekst do lewej

Kurs VBA cz.0 - Jak zacząć? Pierwsze makro. Instrukcja dla zielonych.

Przygotowania do tworzenia makr i proste makra w MS Excel

Przygotowania, czyli pokazanie karty developer na wstążce

Po pierwsze będzie nam potrzebna karta delevoper na wstążce( zakładam, że używamy wersji 2007 lub 2010).
Znajdują się na niej polecenia pomocne przy tworzeniu makr i kontrolek na Arkuszu.

W wersji 2007: wybieramy Przycisk Office(ten okrągły) -> opcje programy Excel -> Zaznaczamy haczyk przy "pokaż kartę developer na wstążce"
W wersji 2010: wybieramy Plik -> opcje -> dostosowywanie wstążki -> po prawej stronie zaznaczamy haczyk przy karcie developer
Powinniśmy uzyskać efekt:

Do dzieła! Piszemy nasze pierwsze makro.

Napiszemy makro, które wyświetli nam komunikat z przywitaniem.
W tym celu klikamy wstążkę developer -> Makra

W polu nazwa makra wpisujemy "pierwszeMakro"
Nazwa makra musi być nazwą kwalifikowaną, czyli taką która składa się z samych liter, liczb i znaków podkreślenia oraz musi zaczynać się od litery.
Warto pisać nazwy makr bez polskich liter ( np. Excel w niemieckiej czy angielskiej wersji nie zawsze dobrze radzi dobie z polskimi nazwami).
Po wpisaniu prawidłowej nazwy klikamy przycisk UTWÓRZ.

Otworzy nam się nowe okno VBE (Visual Basic Editor).

Standardowe okno dzieli się na 3 części. W górnej prawej części ekranu znajduje się okno PROJECT - jest to okno nawigacji po projekcie. Tym projektem jest nasz plik Excelowy.
Tutaj widzisz wszystkie arkusze (tak, to są te arkusze z Excela). Każdy arkusz ma dwie nazwy. Ta w nawiasie to nazwa Excelowa. (Spróbuj w Excelu zmienić nazwę arkusza zobacz, że zmieniła się także nazwa nawiasie w oknie Project).
W dolnym lewy oknie arkusza znajduje się okno Properties. W tym oknie znajdziesz właściwości elementu który zaznaczysz w oknie Project.
Większą część okna zajmuje edytor.

W oknie edytora wpiszemy nasze pierwsze marko:

sub pierwszeMakro
    msgbox("Witaj świecie")
end sub

UWAGA1: VBA nie rozróżnia wielkości liter. Najlepiej wszystko co nie jest w cudzysłowie wpisywać małymi literami.
Po naciśnięciu Enter, lub po przejściu do nowej linijki VBA poprawi nam literki na duże. (Jeśli nie zrobiliśmy literówki)
UWAGA2: nie bój się eksperymentować z programowaniem w VBA. Zmień fragment kodu i sprawdź czy zadziała.

linijka 1:
pierwsza linijka to słówko SUB.
Dzięki niemu VBA wie, że chcemy zacząć pisać procedurę.
po nim następuje nazwa makra. Może być to dowolna nazwa klalifikowana. (czyli w grę wchodzą tylko litery, cyfry i podkreślenia. Oraz dodatkowo musi rozpoczynać się literą).

linijka 2:
MSGBOX  (skrót od Message Box) to funkcja, dzięki której możemy wyświetlić komunikat w oddzielnym okienku z przyciskiem OK. Na pewno widziałeś już takie okna w Windowsie.
Oczywiście zawartość cudzysłowu możesz dowolnie zmieniać.

linijka 3: 
Tutaj komunikujemy VBA, że w tym momencie kończy się nasz procedura.

UWAGA3: makro możesz edytować/dodawać/usuwać bezpośrednio z edytora VBA.
Aby dodać makro pod już istniejącym wpisujesz nowe.
Np mój moduł wygląda tak:

sub pierwszeMakro
    msgbox("Witaj świecie")
end sub

sub aktualnaData
   msgbox("Dzisiaj jest " & date )
end sub

Znaczek & popularnie zwany AND (a tak na prawdę ampersand) łączy dwa napisy. Tak samo działa w Excelu! Sprawdź, co zrobi formuła =A1&"  --- "&dziś() wpisana w komórkę A2. I wpisz coś w komórkę A1.
Funkcja DATE w VBA podaje aktualną datę. Tak samo jak funkcja DZIŚ() w excelu

Makr w jednym module może być dowolna ilość. Każde rozpoczyna się od słówka SUB i kończy na END SUB. Pomiędzy tymi słowami kluczowymi może być dowolna liczba instrukcji.

JAK URUCHOMIĆ MAKRO?

Mamy na to kilka sposobów:

Bezpośrednio z edytora VBA. 

Wystarczy, że klikniesz wewnątrz makra, tak aby wskaźnik tekstu znajdował się wewnątrz makra i naciśniesz zielony trójkąt na pasku narzędzi ( w na górze okna, zaraz pod paskiem tytułu).
Zamiast klikać w trójkąt (Play) możesz również użyć klawisza F5.

Z poziomu Excela.

Jeśli zamkniesz VBA, to w Excelu możesz wywołać okno makr poprzez Developer -> Makra lub kombinację klawiszy Alt + F8.

Po pojawieniu się okna wybieramy interesujące nas makro i klikamy uruchom.

Jest jeszcze klika sposobów uruchamiania makr. Ale o tym przy bardziej zaawansowanych makrach;)

Ćwiczenia ( praca domowa):

1. Napisz makro które wyświetli Twoje imię.

2. Zmodyfikuj poprzednie makro tak, aby za Twoim imieniem pojawiła się aktualna data.

3. Sprawdź co się stanie jak zamiast funkcji DATE użyjesz funkcji TIME lub NOW

4. wypisz imię i datę w 2 oddzielnych msgboxach

POWODZENIA!

Jeśli masz pytania / uwagi / problemy z ćwiczeniami to bardzo proszę o komentarze.

Trochę z innej beczki, ale może ktoś będzie potrzebował opisu instrukcji COPY z Command Line Windowsa

Dzisiaj omówimy jak używać instrukcji copy i jak używać help

Nawias kwadratowy w opisach oznacza, że cos jest opcjonalne
plus w opisach oznacza, że może być tego dowolna ilość


najprostsze wywołanie to:
copy skąd dokąd
czyli np.
copy c:\1.txt D:\2.txt
Taka komenda kopiuje plik 1.txt z dysku C. i wrzuca go pod nazwą 2.txt na dysk D:

pomoc na temat polecenia copy podaje:

Copies one or more files to another location.

COPY [/D] [/V] [/N] [/Y | /-Y] [/Z] [/L] [/A | /B ] source [/A | /B] [+ source [/A | /B] [+ ...]] [destination [/A | /B]]

source Specifies the file or files to be copied.
/A Indicates an ASCII text file.
/B Indicates a binary file.
/D Allow the destination file to be created decrypted destination Specifies the directory and/or filename for the new file(s).
/V Verifies that new files are written correctly.
/N Uses short filename, if available, when copying a file with a non-8dot3 name.
/Y Suppresses prompting to confirm you want to overwrite an existing destination file.
/-Y Causes prompting to confirm you want to overwrite an existing destination file.
/Z Copies networked files in restartable mode.
/L If the source is a symbolic link, copy the link to the target instead of the actual file the source link points to.

To append files, specify a single file for destination, but multiple files
for source (using wildcards or file1+file2+file3 format).

Wysyłanie maila z Excela przy użyciu VBA. cz1

Pierwszy merytoryczny post:)

Na początku muszę powiedzieć, że jest kilka sposobów wysyłania maili z poziomu VBA.

Tutaj omówimy 2:

• Pierwszy to użycie mechanizmu Excela i domyślnego klienta pocztowego.
  Minusem tego rozwiązania jest to, że mail który wysyłamy jest zawsze z załącznikiem ( w tym załączniku jest jakiś plik Excela). I niestety nie można dodać treści maila
• 
  Drugim sposobem jest wywołanie odpowiedniego polecenia Windowsa za pomocą VBA.
  Tutaj niestety nie wyślemy załącznika, ale za to mamy wpływ na treść i temat
  
• Sposób trzeci to użycie programu MS Outlook.
  Tym sposobem możemy zrobić wszystko, a minusem jest niestety posiadanie takowego programu.

W tym poście zajmiemy się pierwszymi dwoma

Przykład 1.
Chcemy napisać makro, który wyśle nam cały plik pod konkretny adres mailowy.

Sprawa jest prosta, wystarczy napisać:


Powyższy kod wysyła maila pod wskazany adres o temacie "Bardzo ważny plik".
Treść maila jest pusta, a w załączniku znajduje się plik w którym jest napisane makro.

Jeśli chcemy wysłać inny plik, to:

ten kod wyśle aktywny skoroszyt (plik)

a ten kod wyśle plik o nazwie Warszawa.xls pod wskazany adres:

Uwaga, aby odwołać się do skoroszytu poprzez obiekt Workbook plik musi być wcześniej otwarty, a po wysłaniu najlepiej go zamknąć.


Parametr

false

metody Close mówi, żeby nie zapisywać zmian.

true

wymusiłoby zapisanie zmian
A metoda bez argumentu sprawi, że Excel zapyta użytkownika czy zapisać zmiany.

Przykład 2.
Wysyłamy jeden arkusz na adres "jakis_adres@gmail.com"

Przyjmijmy, że arkusz do wysłania nosi nazwę kalkulator i znajduje się on w pliku Warszawa.xls który to plik jest otwarty.
W tym przypadku musimy skopiować arkusz "kalkulator" do nowego pliku.
Wysłać mailem nowy plik
zamknąć nowy plik bez zapisywania

Przykład 3.
Tak jak w przykładzie 2, ale chcemy, żeby plik nazywał się "raport.xls"

Musimy zapisać plik na dysku. (Użyję do tego katalogu C:\tmp\)
wysłać maila
skasować plik


Przykład 4

Wysyłanie maila bez załącznika

Na górze modułu umieszczamy import z biblioteki shell32.dll
Dokumentację tej funkcji możecie znaleźć tutaj ShellExecute


Korzystamy z mechanizmu, który został wymyślony w celu łatwego umieszczenia hiperłącza do wysłania maila.

%0a to kod ASCII nr 10, który umieści zrobi nam enter(linuksowy) w miejscu gdzie go urzyjemy
czyli "Nie ma załącznika" wyświetli się w drugiej linii

Zaczynamy.

Witam.
Raz dwa trzy...
raz dwa trzy..
próba posta...

Chyba działa:)

Witam wszystkich na swoim blogu o Excelu, Accessie i programowaniu w VBA.
Zobaczymy jak często uda się opublikować ambitny ( lub mniej ambitny tekst).

Kilka słów o mnie.
Na co dzień prowadzę szkolenia z programowania w VBA, JAVA, C++, Python, SCALA.
Oprócz tego piszę programy w wyżej wymienionych językach.
Zapraszam do współpracy.