Tag: arkusz

Adresy komórek w kodzie VBA

Ostatnio kilka razy zdarzyło mi się korygować kod VBA w skoroszytach użytkowników, w których nagle coś przestawało działać.  Przyczyna błędu była prozaiczna, choć niekiedy bywa trudna do zlokalizowania. O co chodzi? O adresy komórek w kodzie VBA. 

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

kliknij, aby powiększyć

W kolumnie D jest prosta formuła sumująca wartości z komórek
B i C tego samego wiersza. Kolumna E to też suma tych komórek, ale wprowadzona jako wartość kodem VBA:

Public Sub Suma()
With Worksheets(„Arkusz8”)
.Range(„E2”) = .Range(„B2”) + .Range(„C2”)
.Range(„E3”) = .Range(„B3”) + .Range(„C3”)
.Range(„E4”) = .Range(„B4”) + .Range(„C4”)
.Range(„E5”) = .Range(„B5”) + .Range(„C5”)
.Range(„E6”) = .Range(„B6”) + .Range(„C6”)
End With
End Sub

Problem zaczyna się wtedy, gdy użytkownik końcowy zmieni układ arkusza  np. wstawi kolumny lub wiersze. Wszystkie formuły odwołujące się do komórek (także w innych arkuszach) – bez problemu same się zaktualizują, nawet gdy są wpisane jako adresy bezwzględne. 

kliknij, aby powiększyć

W kodzie VBA jednak nic się nie zmieniło, odwołania do poszczególnych komórek zostały takie same. Po wykonaniu makra efekt wygląda tak:

Procedura nadał wpisuje obliczone kodem wartości i wstawia je w kolumnę E, likwidując znajdującą się tam po przesunięciu formułę. W dodatku są tam dziwne wartości – sumowana jest liczba początkowa LP oraz wartość X. 
Problem może się pojawić także przy zmianie nazwy arkusza – w kodzie będzie zapisana stara nazwa i przy uruchomienie procedury pojawi się błąd wykonania 9.
Niestety, w takim przypadku trzeba ręcznie przerobić odwołania w kodzie VBA, dostosowując go do nowego układu danych. 

Ten przykład jest prosty, w bardziej skomplikowanych projektach znalezienie i   poprawienie takiego błędu może być zdecydowanie bardziej skomplikowane. 
Jak zabezpieczyć się przed nieprzewidzianą ingerencją użytkownika? 
Najprostsze sposoby to:

    • włączenie ochrony arkusza
      uniemożliwiające wstawienie wierszy/kolumn;
    • zastosowanie w kodzie VBA odwołań do nazw kodowych arkuszy
      uniezależniające odwołania do konkretnych arkuszy;

A tu możesz mi postawić kawę: 

buycoffee.to/marzatela

 

Wykorzystany zakres arkusza

UsedRange to jedna z właściwości obiektu Worksheet.  Zwraca wartość typu Range wyznaczony przez pierwszą i ostatnią niepustą komórkę w arkuszu. 

kliknij, aby powiększyć

Jeżeli np. pierwsza niepusta komórka to B2, a ostatnia to D10, to właściwość UsedRange to zakres Range(„B2:D10”). 
Trzeba pamiętać też o tym, że nawet jeżeli komórka wydaje się pusta, wcale nie musi taka być – może tam być np. formuła zwracającą wartość tekstu o zerowej długości. Także wstawienie zwykłej spacji w komórce sprawia, że nie jest ona już pusta. 

Do czego można ją wykorzystać? Możliwości tu jest wiele: do zaznaczania, formatowania, czyszczenia itp.  
Jedną z ciekawszych możliwości może być też sprawdzenie, czy w arkuszu nie ma np. jakichś niepotrzebnych śmieci. Mogą się pojawić m.in.  przy wielokrotnym kopiowaniu i wstawianiu różnych danych z innych plików. 
Może to w efekcie spowodować znaczne i zupełnie niepotrzebne zwiększenie rozmiaru pliku i spowolnienie pracy. 
Np.

Public Sub ZaznaczUsedRange()
ActiveSheet.UsedRange.Select
End Sub

Po uruchomieniu tej procedury – w arkuszu zostanie zaznaczony cały wykorzystywany zakres komórek. 
Np.

kliknij, aby powiększyć

Tu akurat wstawiłam spację w komórce K19. Normalnie jej nie widać, ale  dla zakresu UseRange ma znaczenie. Czasem warto sprawdzić. 

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

Widoczność arkusza

Visible czyli widoczność arkusza to jedna z właściwości obiektu Worksheet.  Określa, czy dany arkusz jest widoczny czy nie. 

kliknij, aby powiększyć

Właściwość ta może przybierać jedną z 3 wartości:

kliknij, aby powiększyć

Wartości te to:

    • xlSheetHidden
      arkusz jest ukryty
    • xlSheetVeryHidden
      arkusz jest bardzo ukryty, bez możliwości odkrycia w Excelu
    • xlSheetVisible
      arkusz jest odkryty

Po wykonaniu kodu takiej przykładowej procedury w skoroszycie z trzema arkuszami:

Public Sub Ukryte()
Arkusz1.Visible = xlSheetVisible
Arkusz2.Visible = xlSheetHidden
Arkusz3.Visible = xlSheetVeryHidden
End Sub

Efekt końcowy w arkuszu wygląda tak:

Arkusz1 – jest widoczny na liście zakładek

kliknij, aby powiększyć

Arkusz2 – niewidoczny na na liście zakładek, ale można go odkryć (klikniecie prawym przyciskiem myszy na pasku zakładek i wybór opcji Odkryj z menu podręcznego)

Następnie w formularzu wybieramy nazwę arkusza do odkrycia. Jeżeli jest kilka ukrytych arkuszy, widoczne są tu wszystkie:  

Arkusz3 – nie jest widoczny ani na pasku zakładek, ani w formularzu z ukrytymi arkuszami. Widać go jedynie w edytorze VBA w oknie obiektów Excela:

kliknij, aby powiększyć

 Właściwość Visible jest często stosowana i bardzo przydatna. 

Kurs Programowanie VBA w Excelu 365 od podstaw

 

Nazwa arkusza

Nazwa arkusza to ta nazwa, która jest widoczna na dolnym pasku Excela. 

Często też jest nazywana zakładką.
Każdy arkusz ma swoją własną, unikalna nazwę.
Chcąc zmienić nazwę arkusza, wystarczy kliknąć dwukrotnie na jego nazwie i wpisać nową nazwę:

W kodzie VBA nazwa arkusza to właściwość Name obiektu Worksheet.  

Public Sub Work()
Dim NazwaArkusza As String
’nazwa aktywnego arkusza: 
NazwaArkusza=ActiveSheet.Name
(…)
End Sub

W tym przypadku zmienna NazwaArkusza zwraca nazwę aktywnego arkusza (ActiveSheet), która w dalszym ciągu instrukcji może zostać wykorzystana lub zmieniona. 

Uwaga: jeżeli w skoroszycie są formuły odwołujące się do komórek z innych arkuszy, po zmianie nazwy arkusza zostaną one automatycznie zaktualizowane, nie trzeba nic dodatkowo zmieniać. 
W kodzie VBA nie ma niestety takiej automatycznej aktualizacji – tu po zmianie arkusza trzeba wszędzie ręcznie zmienić nazwę arkusza na nową.  

Kurs Makra w Excelu 365 od podstaw