Kategoria: Przykłady rozwiązań

Suma czasu

Dawno nie było nic o czasie, pora na przykładowe rozwiązanie.  Tym bardziej, że zawsze warto przypominać, że Data i czas to liczba

Załóżmy, że mamy taką tabelkę:

kliknij, aby powiększyć

Kolumna B – to czas Start, kolumna C– czas Stop. 
Obie kolumny są sformatowane tak:

kliknij, aby powiększyć

Kolumna D to formuła:
D2=JEŻELI(C2<B2;1+C2-B2;C2-B2) 
formatowanie tej kolumny:

kliknij, aby powiększyć

Takie formatowanie pozwala na wyświetlanie czasu w formie godzin i minut, ale pozwala na wyświetlenie również czasu powyżej 24 godzin (widać to w wierszu Sumy).

Kolumna E to formuła:
E2=JEŻELI(C2<B2;24*(1+C2-B2);24*(C2-B2))
formatowanie tej kolumny:

Tu mamy po prostu czas w liczbie godzin. 

Kolumny D i E to kolumny, w których uwzględniono przypadek, gdy czas Stop jest mniejszy od czasu Start (czyli jest przejście przez północ). Gdyby nie było sprawdzenia tego warunku – czas byłby w tych przypadkach ujemny czyli w kolumnie E widoczna byłaby liczba ujemna, a w kolumnie D – widoczne były tylko „krzaczki” – czas nie może być ujemny. 

A tu możesz mi postawić kawę: 

buycoffee.to/marzatela

Ukrywanie i odkrywanie arkuszy w skoroszycie

Jak można zabezpieczyć skoroszyt, aby różni użytkownicy mieli dostęp tylko do przeznaczonych dla siebie arkuszy? 
Możemy to zrobić na przykład korzystając z właściwości Visible arkusza. 

Załóżmy, że mamy skoroszyt z takimi arkuszami:

Każdy użytkownik może mieć dostęp tylko do arkusza Start – gdzie może się zalogować oraz do własnego arkusza. Admin widzi wszystkie arkusze, w tym arkusz Loginy. 

Arkusz Start może wyglądać np.tak: 

Wybór loginu z listy rozwijalnej w komórce F5. Hasło wpisywane do komórki F7. 

Do tego kilka procedur VBA: 

Procedura sprawdzająca prawidłowość wpisanego hasła i odkrywająca arkusz przypisany do użytkownika. 

Sub OdkryjArkusz()
Dim Login As String
Dim Haslo As String
Dim ZapisaneHaslo As String
Dim i As Integer
With Worksheets(„Start”)
Login = .Range(„F5”)
Haslo = .Range(„F7”)
End With
ZapisaneHaslo = WorksheetFunction.VLookup(Login, Worksheets(„Loginy”).Range(„A:B”), 2, False)
If Haslo = ZapisaneHaslo Then
   If Login = „admin” Then
     For i = 1 To Worksheets.Count
        Worksheets(i).Visible = xlSheetVisible
     Next i
  Else
       Worksheets(Login).Visible = xlSheetVisible
  End If
End If
End Sub

Procedura ta może być uruchamiana albo zdarzeniem arkusza albo przyciskiem w arkusz Start. Oczywiście powinno być także sprawdzenie, czy wybrano Login z listy, czy pole Hasło nie jest puste i ewentualnie jakieś komunikaty o błędnym wpisanym haśle – to tylko wersja podstawowa. 

Dodatkowo jeszcze w pliku powinna być procedura ukrywająca wszystkie arkusze z wyjątkiem Start i czyszcząca pola na Login i Hasło. 
Może to wyglądać np. tak:

Sub UkryjArkusze()
Dim i As Integer
For i = 1 To Worksheets.Count
If Worksheets(i).Name = „Start” Then
     Worksheets(i).Range(„F5”).ClearContents
     Worksheets(i).Range(„F7”).ClearContents
  Else
     Worksheets(i).Visible = xlSheetVeryHidden
End If
Next i
End Sub

Ta procedura powinna być uruchamiana zdarzeniem skoroszytu przed zamknięciem. 

A tu możesz mi postawić kawę: 

buycoffee.to/marzatela

Ostatnia niepusta komórka w wierszu

Jak wyznaczyć ostatnią niepustą komórką w wierszu?  Bardzo podobnie jak w przypadku ostatniego wiersza w kolumnie. 
Wykorzystywana jest  właściwość obiektu Range czyli Range.End. 

Na przykładzie takiego arkusza:

kliknij, aby powiększyć

Chcąc znaleźć adres ostatniej komórki w wierszu 1 możemy skorzystać
np. z takiego kodu:

Public Sub OstatniWiersz()
Dim OstKom1
Dim OstKom2
OstKom1 = Range(„A1”).End(xlToRight).Address
OstKom2 = Cells(1, Columns.Count).End(xlToLeft).Address
End Sub

OstKom1 – od komórki A1 szukana jest ostatnia niepusta komórka w prawo
OstKom2- od ostatniej komórki w wierszu (wyznaczonej przez liczbę kolumn) w lewo. Ten sposób jest najczęściej stosowany i najbezpieczniejszy. 

A tu możesz mi postawić kawę: 

buycoffee.to/marzatela

Ostatnia niepusta komórka w kolumnie

W różnego rodzaju projektach VBA w Excelu bardzo często pojawia się potrzeba wyznaczenia ostatniej niepustej komórki w kolumnie. 
Wykorzystywana jest tu jedna właściwości obiektu Range czyli Range.End. 

Zobaczmy, jak to wygląda w praktyce na podstawie takiego arkusza:

kliknij, aby powiększyć

Są tu widoczne 2 różne tabelki, oddzielone kilkoma pustymi wierszami. Ostatnia niepusta komórka tabeli 1 to zaznaczona na żółto komórka A6, ostatnia z drugiej tabeli to A25 (zaznaczona na zielono). 
Ta zielona komórka jest jednocześnie ostatnią niepustą w całej kolumnie. 

Przykładowy kod wyznaczający nr wiersza to: 

Public Sub OstatniWiersz()
Dim OstWZolty As Long
Dim OstWZielony As Long
OstWZolty = Range(„A1”).End(xlDown).Row
OstWZielony = Range(„A” & Rows.Count).End(xlUp).Row
End Sub

Dla OstWZolty – od komórki A1 szukana jest ostatnia niepusta w dół. 
Dla OstWZielony – od ostatniej komórki w kolumnie (wyznaczonej przez liczbę wierszy) w górę. Ten sposób jest najczęściej stosowany i najbezpieczniejszy. 

A tu możesz mi postawić kawę: 

buycoffee.to/marzatela

 

Wyznaczanie dat w Excelu

W poprzedniej notce opisałam bardzo pożyteczną funkcję w VBA:
Funkcja VBA DateAdd()
Niestety, w Excelu nie mam jej odpowiednika, ale nie oznacza to, że za pomocą formuł nie można wyznaczyć daty wyznaczonej za lub przed określony czas od daty początkowej. 

Ponieważ:
Data i czas to liczba
można po prostu wykonać dodawanie daty początkowej oraz konkretnej liczby. Przelicznik wygląda tak:
1 – jeden dzień
1/24 – jedna godzina
1/(2460) – jedna minuta
1/(2460*60) – jedna sekunda

Załóżmy, że komórce A1 mamy pełną datę z godziną: 

formuły mogą wyglądać np. tak:

=A1+3 – termin za 3 dni
=A1+5 + 1/24 – termin za 5 dni i 1 godzinę
itp. 

Bardziej skomplikowana sytuacja może się pojawić, gdy odstęp czasu jest większy i policzenie np. liczby dni między kilkoma miesiącami (w ekstremalnych przypadkach w latach przestępnych) – liczenie dni wcale nie jest takie proste i skomplikowane. 
W takich przypadkach zdecydowanie łatwiej jest z wykorzystaniem funkcji Data(). 

Załóżmy, że chcemy wyznaczyć datę za 1 rok, 2 miesiące i 3 dni od daty wpisanej w komórce A1. Robimy to tak:
Zasada jest prosta: odczytujemy poszczególne elementy daty z komórki A1 i zwiększamy (można oczywiście też zmniejszać) je od żądaną wartość. 
Formuła wygląda tak:
=DATA(ROK(A1)+1;MIESIĄC(A1)+2;DZIEŃ(A1)+3)
Oczywiście można też zrobić przesunięcia również o godziny i minuty, dodając do formuły część związaną z czasem , np.
=DATA(ROK(A1)+1;MIESIĄC(A1)+2;DZIEŃ(A1)+3)+CZAS(GODZINA(A1)+4;
MINUTA(A1)+5;SEKUNDA(A1)+6)

Warto tu też pamiętać o tym, że jeżeli dodajemy np.50 dni (czyli więcej niż liczba dni miesiąca), to Excel sam doda miesiąc,a do liczby dni – dołoży tylko końcówkę. 

A tu możesz mi postawić kawę: 

buycoffee.to/marzatela