Elektroda.pl
Elektroda.pl
X
Proszę, dodaj wyjątek dla www.elektroda.pl do Adblock.
Dzięki temu, że oglądasz reklamy, wspierasz portal i użytkowników.

Prosty sposób na organizację części i projektów w Excelu za pomocą makra

p.kaczmarek2 29 Kwi 2020 20:36 2463 12
  • Prosty sposób na organizację części i projektów w Excelu za pomocą makra
    (Miniaturka przedstawia zrzut ekranu z mojego prywatnego pliku Excela z projektami; zrealizowany jest on metodą którą opiszę poniżej)
    Witajcie moi drodzy
    Zapraszam do lektury mojego nietypowego tematu przeznaczonego dla elektroników amatorów i hobbystów wykonujących własne projekty.
    Przedstawię dzisiaj prosty sposób na organizację naszego zbioru części i listy projektów które planujemy w programie Microsoft Excel za pomocą makra VBA.
    (AKTUALIZACJA: teraz w temacie jest też działająca wersja dla Open Office!).
    Przedstawiony sposób oprócz oczywistych rzeczy takich jak lista posiadanych części/planowanych projektów będzie posiadać automatyczne sprawdzanie czy posiadany daną część w naszym zbiorze.
    Na końcu dam do pobrania Excelowy plik (skoroszyt .xlsm) gotowy do użycia.

    Motywacja projektu
    Przy większej ilości projektów i części trudno jest spamiętać co już posiadamy, a co musimy kupić. Przedstawiony tu skoroszyt Excela będzie stanowić rozwiązanie tego problemu i będzie automatycznie podświetlać na czerwono nazwy części których jeszcze nie mamy, i na zielono nazwy części, które już posiadamy. Dzięki niemu będzie można było jednym rzutem oka sprawdzić czy mamy wszystko co potrzebne i będzie dało się uniknąć sytuacji w której okazuje się, że zapomnieliśmy o jednym tranzystorze i musimy przez niego zwlekać tydzień z projektem.

    Organizacja arkuszy
    Moją prostą bazę danych w Excelu oparłem o dwa osobne arkusze, dwie listy. Oba należą do jednego skoroszytu (jednego pliku) Excela.
    Pierwszy arkusz nazywa się "Parts".
    Arkusz "Parts" zawiera listę części jakie posiadamy. W kolumnie A wpisujemy nazwę. W kolumnie B możemy wpisać ilość, ale na ten moment nie jest ona używana przez skrypt gdyż uznałem, że i tak większość osób kupuje po kilka części i warto jest mieć elementy zapasowe. Dodatkowo w kolumnie C warto wpisać gdzie mamy te części, w jakiej szufladce, itp.
    Zrzut ekranu:
    Prosty sposób na organizację części i projektów w Excelu za pomocą makra
    Na zrzucie ekranu wpisane jest, że posiadam tranzystory: BC547, BC557 oraz układ NE555. Ilości nie uzupełniałem, gdyż kupuję hurtowo.
    Drugi arkusz nazywa się "Schematics".
    Arkusz "Schematics" zawiera listę projektów jakie nas interesują i jakie zamierzamy zrobić w przyszłości.
    Jego forma jest dość dowolna, jedynie "magiczna" jest w nim pierwsza kolumna, gdyż ona automatycznie sprawdza czy posiadamy dany element w naszej bazie, tzn. na liście z arkusza Parts.
    Zrzut ekranu:
    Prosty sposób na organizację części i projektów w Excelu za pomocą makra
    Na zrzucie ekranu widać dwa projekty które planuję. Do jednego potrzeba BC548, którego nie posiadam (program podświetla go na czerwono), a do drugiego projektu potrzeba NE555, który posiadam (program podświetla go na zielono).
    (Ja wiem, że można by tego BC548 zastąpić, to tylko przykład)
    Do arkusza Schematics polecam wpisywać nazwę projektu, wklejać link do opisu projektu który chcemy wykonać (ale nie do kolumny A) oraz też skopiować tam sam schemat, by mieć do niego dostęp szybko i też w trybie offline.
    Przydaje się też tam dać opis projektu oraz różne adnotacje nasze i wnioski.
    I co najważniejsze - potem należy wypisać tam nazwy elementów które chcemy wiedzieć czy mamy. By makro mogło nam je kolorować na zielono i czerwono.

    Działanie makra od wewnątrz
    Jeśli tylko chcecie skorzystać z mojego szablonu to nie musicie tego wiedzieć, ale jeśli ktoś jest zainteresowany jak to się dzieje że Excel poznaje czy mamy daną część, to proszę spojrzeć na zrzut ekranu:
    Prosty sposób na organizację części i projektów w Excelu za pomocą makra
    Całość sprowadza się tylko do obsługi jednego zdarzenia (OnChange) a potem sprawdzenia czy dana wartość jednej komórki znajduje się gdzieś w drugiej kolumnie. Na bazie tego ustawiany jest kolor tła.
    Te zdarzenie "OnChange" oraz cały skrypt jest przypisany dla całego skoroszytu, nie tylko dla jednego arkuszu - trzeba o tym pamiętać, bo chcemy odświeżać listę zarówno w przypadku modyfikacji spisu posiadanych elementów jak i spisu potrzebnych elementów.
    Warto też zwrócić uwagę, że nazwa arkuszu Parts jest wpisana w kodzie na sztywno, więc jak ją zmienicie, to wszystko się popsuje. No, chyba, że też zmienicie ją w kodzie.

    Co można by ulepszyć?
    Bieżącą wersję makra można by nieco usprawnić. Widzę przede wszystkim dwie możliwości które by ulepszyły system.
    - można by dodać wsparcie zamienników, np. w osobnym arkuszu dać listę co może zastąpić co. Ale ten problem można też rozwiązać "na około" po prostu poprzez... wypisywanie tych części które mamy oraz ich odpowiedników jako posiadane
    - można by dodać wsparcie ilości części (bo teraz system tylko patrzy po nazwie, gdyż ja zakładam, że i tak muszę mieć na wszelki wypadek nadmiarowe). System mógłby liczyć czy mamy wystarczająco części i to by mogło się przydać pewnie dla tych droższych kostek lub modułów, których kupuje się po jednej sztuce

    Szablon do pobrania
    Plik Excela z przykładowymi danymi do pobrania:
    PartsDatab...e.xlsm.zip Download (45.77 kB)Punkty: 0.5 dla użytkownika
    Zapraszam do użycia i ulepszania makra. Jeśli ktoś np. doda wsparcie liczby części to mógłby opublikować w tym temacie swoje poprawki.


    Wersja dla Open Office - do pobrania
    Początkowo tej wersji nie było, ale zrobiłem ją po sugestii użytkownika @Janusz_kk.
    Użyłem OpenOffice 4.1.7.
    Niestety 1:1 przepisane makro w Open Office nie działało ze względu na niekompatybilność funkcji Find w tamtejszej implementacji VBA, więc musiałem te Find rozpisać.
    Wyszło tak:
    Prosty sposób na organizację części i projektów w Excelu za pomocą makra
    Dodatkowo musiałem obniżyć poziom bezpieczeństwa makr, zgodnie z ostrzeżeniem które Open Office sam wyświetla:
    Prosty sposób na organizację części i projektów w Excelu za pomocą makra
    Ustawiłem to w Tools->Options->Open Office->Security. Włączyłem tam wszystkie makra:
    Prosty sposób na organizację części i projektów w Excelu za pomocą makra
    Dodatkowo w Options->Load/Save->VBA Properties włączyłem wykonywanie makr tzn 'executable':
    Prosty sposób na organizację części i projektów w Excelu za pomocą makra
    Filmik przedstawiający działanie mojego makra w Open Office:

    Przeportowana do Open Office baza z makrem w formacie .ods do pobrania:
    PartsDatab...ce.ods.zip Download (44.9 kB)Punkty: 1 dla użytkownika

    Podsumowanie
    Pokazałem tu sposób na organizację projektów z którego rzeczywiście korzystałem przez długi czas. Jak na moje potrzeby sprawdzał się idealnie. Nie wiem na ile Wam przypasuje, ale plik Excel dałem do pobrania więc zachęcam chociaż do wypróbowania go u Was.
    PS: Proszę pamiętać by włączyć makra po pobraniu, inaczej skrypt nie działa.
    A jakie Wy macie sposoby na organizację Waszego zbioru części i planowanych projektów?

    Fajne! Ranking DIY
    Potrafisz napisać podobny artykuł? Wyślij do mnie a otrzymasz kartę SD 64GB.
    O autorze
    p.kaczmarek2
    Poziom 24  
    Offline 
  • #3
    .Wiśnia
    Poziom 28  
    cena legalnego office na znanym portalu jest tak śmieszna że raczej każdego na nią stać.
    Swoją drogą ciekaw jestem ile projektów wykonałeś ,że opłacało się usiąść parę godzin nad skryptami w excelu
  • #4
    Baskhaal
    Poziom 14  
    Ciekawe podejście. Widać dowód na to, że ewolucja działa, świat nie lubi próżni. Proponowane rozwiązanie to nic innego jak system ERP ;) taki SAP czy IFS itd. Stany magazynowe + BOM (Bill Of Materials) projektów. Ważnym jest binarne podejście do kwestii - albo prowadzę tam wszystko albo tylko się tym bawię i coś wpisuję a cuś nie bo coś tam.
    Teraz pojawiają się kwestie mozolnej pracy z systemem, dlatego też życie wprowadziło coś takiego jak barcody, na podstawie których przyjmuje się na stan nowo zakupione komponenty, tak samo się wydaje 'na produkcję' zdejmując ze stanu i mnóstwo innych dodatkowych rozwiązań.
  • #5
    p.kaczmarek2
    Poziom 24  
    Janusz_kk napisał:
    No fajnie tylko nie każdy ma office a ma np open offica czy libre offica, tak makra będą działać?


    Te odpowiedniki mają wsparcie VBA, choć te wsparcie nie jest w pełni kompletne. Sprawdzę to i dam znać, ewentualnie dam zaktualizowaną wersję arkusza pod te programy.

    .Wiśnia napisał:
    że opłacało się usiąść parę godzin nad skryptami w excelu

    Ten zrzut ekranu z tematu to jest całość skryptu, to kilkanaście linijek, to jest do zrobienia o wiele szybciej jak się już zna Excela i VBA. Dzielę się by pokazać pomysł i bo mam świadomość, że są osoby które nawet o istnieniu VBA nie wiedzą.

    .Wiśnia napisał:
    Swoją drogą ciekaw jestem ile projektów wykonałeś

    Część moich konstrukcji możesz zobaczyć tutaj w dziale DIY:
    https://www.elektroda.pl/rtvforum/search.php?search_author_id=2529249&mode=onlytopics


    Baskhaal napisał:
    Ciekawe podejście. Widać dowód na to, że ewolucja działa, świat nie lubi próżni. Proponowane rozwiązanie to nic innego jak system ERP ;) taki SAP czy IFS itd. Stany magazynowe + BOM (Bill Of Materials) projektów.

    Tak, ale oczywiście taki bardzo prymitywny system, którego plusem jest użycie programu którego już wiele osób ma.

    Baskhaal napisał:

    Ważnym jest binarne podejście do kwestii - albo prowadzę tam wszystko albo tylko się tym bawię i coś wpisuję a cuś nie bo coś tam.

    Warto na bieżąco prowadzić, ja mam u siebie we własnym pliku jeszcze lokalizację w szufladkach Patrol opisaną:
    Prosty sposób na organizację części i projektów w Excelu za pomocą makra
    Na powyższym zrzucie ekranie to dokładniej mam lokację części zapisaną z pomocą numeru segmentu szafek ("Block"), numeru szufladki ("Drawer"), 'koordynatów' szufladki ("DrawerX" oraz "DrawerY") oraz. ew sekcji szufladki (ale ona już nie jest tak istotna)

    Jedyne o co nie dbam, to ta nieszczęsna liczba posiadanych elementów danego typu, bo po prostu kupuję po 5/10/50/100 sztuk i mi się czasowo nie opłaca liczyć co do jednego.... wpisują ją tylko dla tych części co wiem, że mam np. 1 lub 2 sztuki.
  • #6
    Piotrek1970
    Poziom 17  
    Szacun za takie poświęcenie dla hobby. W swoim czasie próbowałem skatalogować swoje elementy, płytki , projekty przy pomocy excela i mimo, że trochę tam skrobałem w VBA to sam proces wprowadzania danych na początku doprowadził mnie do wniosku, że to strata czasu i projekt został zarzucony. Jak pamiętam to na forum były jakieś próby zainteresowania dziełami typu program do katalogowania części, ale... Wszystko fajnie jak jest porządek w elementach i dokumentacja na bieżąco jest uzupełniana -ja za bardzo lubię zabawę w lutowanie niż statystyki co mam a czego mi brakuje. Dziś nie jest problemem zamówić nawet jeden element od każdego z dystrybutorów części -cena jest dyskusyjna ale możliwość jest. Jak brakuje mi czegoś robię listę na kartce lub ostatnio częściej notatkę w telefonie w miasto na zakupy ;-) Niemniej jak w w/w projekcie tak i ten pewnie znajdzie chętnych, którzy zaczną z niego korzystać lub co byłoby równie dobre zainteresują się samym VBA.
  • #8
    Baskhaal
    Poziom 14  
    speedy9 napisał:
    .Wiśnia napisał:
    ena legalnego office na znanym portalu jest tak śmieszna że raczej każdego na nią stać.

    Te oferty nie mają nic wspólnego z legalnym oprogramowaniem. Legalny Office kosztuje co najmniej 350zł.


    Oj nie jest trudno znaleźć znajomego, który pracuje w dużym korpo i ma dostęp do MS HUP (Home use program). Ja w ten sposób kupiłem najnowszy pakiet Office za 58 zł. Legal, na moje nazwisko itd.

    Potrzebny jest @ służbowy korporacji, która ma odpowiednią umowę z MS oraz prywatny. Podajesz dane z prywatnego, tam dopiero pyta o nazwisko itd. Na służbowym tylko potwierdzasz, że podany prywatny ma zostać obsłużony łaskawie linkiem do instalacji i rejestracji.
  • #9
    p.kaczmarek2
    Poziom 24  
    speedy9 napisał:
    .Wiśnia napisał:
    ena legalnego office na znanym portalu jest tak śmieszna że raczej każdego na nią stać.

    Te oferty nie mają nic wspólnego z legalnym oprogramowaniem. Legalny Office kosztuje co najmniej 350zł.


    Warto jeszcze wspomnieć, że niektóre uczelnie oferują dla swoich studentów darmowy dostęp do różnych programów bezpośrednio od Microsoftu z tzw. Microsoft Imagine (kiedyś: Dreamspark).
  • #10
    zipzap
    Poziom 15  
    Baskhaal napisał:
    Oj nie jest trudno znaleźć znajomego, który pracuje w dużym korpo i ma dostęp do MS HUP (Home use program). Ja w ten sposób kupiłem najnowszy pakiet Office za 58 zł. Legal, na moje nazwisko itd.


    Skoro nie jesteś pracownikiem tej korporacji to chyba nie jest to jednak legalne. Microsoft wprost pisze (https://www.microsoft.com/pl-pl/home-use-program/resources), że to jest program dla pracowników ważnych klientów. Z tego co piszesz to jesteś znajomym pracownika ważnego klienta.
  • #11
    Baskhaal
    Poziom 14  
    zipzap napisał:
    Baskhaal napisał:
    Oj nie jest trudno znaleźć znajomego, który pracuje w dużym korpo i ma dostęp do MS HUP (Home use program). Ja w ten sposób kupiłem najnowszy pakiet Office za 58 zł. Legal, na moje nazwisko itd.


    Skoro nie jesteś pracownikiem tej korporacji to chyba nie jest to jednak legalne. Microsoft wprost pisze (https://www.microsoft.com/pl-pl/home-use-program/resources), że to jest program dla pracowników ważnych klientów. Z tego co piszesz to jesteś znajomym pracownika ważnego klienta.


    No nie, byłem pracownikiem korporacji, która to zgodnie z umową z MS udostępniała takie opcje swoim pracownikom, wszystkim, którzy posiadali służbowy email (to jedyne forma weryfikacji). Tam podajesz prywatny adres email i tam przychodzi klucz. Nikt nie weryfikuje nic dalej. Zainstalowany, aktywowany Office jest zalinkowany do mojego prywatnego konta MS i nie ma tam żadnej wzmianki o mojej ex firmie ani fakcie, że to z HUP.
  • #12
    p.kaczmarek2
    Poziom 24  
    Youmound napisał:
    Janusz_kk napisał:
    No fajnie tylko nie każdy ma office a ma np open offica czy libre offica, tak makra będą działać?


    To zamiast głupio się pytać trzeba sprawdzić.


    Znalazłem wolną chwilę i przetestowałem plik z pierwszego tematu z OpenOffice 4.1.7. Na wstępie Office napisał mi co zrobić:
    Prosty sposób na organizację części i projektów w Excelu za pomocą makra
    Włączyłem tam wszystkie makra:
    Prosty sposób na organizację części i projektów w Excelu za pomocą makra
    Dodatkowo włączyłem wykonywanie makr tzn 'executable':
    Prosty sposób na organizację części i projektów w Excelu za pomocą makra

    Niestety szybko okazało się, że mimo wszystko skrypt nie działa. Funkcja Find w OpenOffice jest nieco inna, dostawałem jakiś błąd związany z argumentami, to samo co ktoś stąd:
    https://forum-test.openoffice.org/it/forum/viewtopic.php?f=26&t=9141
    Czyli: " RunTime BASIC. ‘1’ - Type: com.sun.star.uno.RuntimeExeption Message: Range::Find, missing params"
    Więc rozpisałem Find na pętlę i wyszło coś takiego:
    Prosty sposób na organizację części i projektów w Excelu za pomocą makra
    Teraz makro działa i w Open Office, a przynajmniej w tym co mam.
    Filmik z działania:

    Zaraz zaktualizuję pierwszy post i dam w załączniku plik .ods.
  • #13
    Janusz_kk
    Poziom 27  
    p.kaczmarek2 napisał:
    Wersja dla Open Office - do pobrania
    Początkowo tej wersji nie było, ale zrobiłem ją po sugestii użytkownika @Janusz_kk.

    Dziękuję bardzo, będę miał trochę czasu to przetestuję, lecą punkty dla Ciebie.