Zrozumienie typów danych Excel VBA (zmienne i stałe)

W Excel VBA często będziesz musiał używać zmiennych i stałych.

Podczas pracy z VBA zmienna jest miejscem w pamięci komputera, w którym można przechowywać dane. Typ danych, które można przechowywać w zmiennej, zależy od typu danych zmiennej.

Na przykład, jeśli chcesz przechowywać liczby całkowite w zmiennej, twoim typem danych będzie „Integer”, a jeśli chcesz przechowywać tekst, twoim typem danych będzie „String”.

Więcej o typach danych w dalszej części tego samouczka.

Podczas gdy wartość zmiennej zmienia się, gdy kod jest w toku, stała przechowuje wartość, która nigdy się nie zmienia. W ramach dobrej praktyki kodowania należy zdefiniować typ danych zarówno - zmienną, jak i stałą.

Dlaczego warto używać zmiennych w VBA?

Kiedy kodujesz w VBA, potrzebujesz zmiennych, których możesz użyć do przechowywania wartości.

Zaletą używania zmiennej jest możliwość zmiany wartości zmiennej w kodzie i dalszego jej używania w kodzie.

Na przykład poniżej znajduje się kod, który dodaje pierwsze 10 liczb dodatnich, a następnie wyświetla wynik w oknie komunikatu:

Sub AddFirstTenNumbers() Dim Var As Integer Dim i As Integer Dim k as Integer For i = 1 do 10 k = k + i Next i MsgBox k End Sub

W powyższym kodzie są trzy zmienne - Var, i, oraz k.

Powyższy kod wykorzystuje pętlę For Next, w której wszystkie te trzy zmienne są zmieniane po zakończeniu pętli.

Przydatność zmiennej polega na tym, że można ją zmienić w trakcie tworzenia kodu.

Poniżej znajduje się kilka zasad, o których należy pamiętać podczas nazywania zmiennych w VBA:

1. Możesz używać liter, cyfr i znaków interpunkcyjnych, ale pierwsza liczba musi być alfabetem.
2. W nazwie zmiennej nie można używać spacji ani kropki. Możesz jednak użyć znaku podkreślenia, aby nazwy zmiennych były bardziej czytelne (takie jak Interest_Rate)
3. Nie możesz używać znaków specjalnych (#, $, %, & lub !) w nazwach zmiennych
4. VBA nie rozróżnia wielkości liter w nazwie zmiennej. Tak więc „Oprocentowanie” i „Oprocentowanie” są takie same dla VBA. Możesz użyć mieszanej wielkości liter, aby zmienne były bardziej czytelne.
5. VBA ma kilka zarezerwowanych nazw, których można użyć dla nazwy zmiennej. Na przykład nie możesz użyć słowa „Dalej” jako nazwy zmiennej, ponieważ jest to nazwa zarezerwowana dla pętli For Next.
6. Twoja nazwa zmiennej może mieć do 254 znaków.

Typ danych zmiennych

Aby jak najlepiej wykorzystać zmienne, dobrą praktyką jest określenie typu danych zmiennej.

Typ danych przypisany do zmiennej będzie zależał od typu danych, które ta zmienna ma przechowywać.

Poniżej znajduje się tabela przedstawiająca wszystkie dostępne typy danych, których można używać w Excel VBA:

Typ danych	Wykorzystane bajty	Zakres wartości
Bajt	1 bajt	0 do 255
Boole'a	2 bajty	Prawda czy fałsz
Liczba całkowita	2 bajty	-32 768 do 32 767
Długa (długa liczba całkowita)	4 bajty	-2 147 483 648 do 2 147 483 647
Pojedynczy	4 bajty	-3,402823E38 do -1,401298E-45 dla wartości ujemnych; 1.401298E-45 do 3.402823E38 dla wartości dodatnich
Podwójnie	8 bajtów	-1,79769313486231E308 do -4,94065645841247E-324 dla wartości ujemnych; 4.94065645841247E-324 do 1.79769313486232E308 dla wartości dodatnich
Waluta	8 bajtów	-922 337 203 685 477,5808 do 922 337 203 685 477,5807
Dziesiętny	14 bajtów	+/-79228162514264337593543950335 bez przecinka; +/-79228162514264337593543950335 z 28 miejscami po przecinku
Data	8 bajtów	1 stycznia 100 do 31 grudnia 9999
Obiekt	4 bajty	Wszelkie odniesienia do obiektów
Ciąg (o zmiennej długości)	10 bajtów + długość ciągu	0 do około 2 miliardów
Ciąg (o stałej długości)	Długość sznurka	1 do około 65 400
Wariant (z numerami)	16 bajtów	Dowolna wartość liczbowa do zakresu Double
Wariant (ze znakami)	22 bajty + długość ciągu	Taki sam zakres jak w przypadku String o zmiennej długości
Określony przez użytkownika	Różne	Zakres każdego elementu jest taki sam, jak zakres jego typu danych.

Kiedy określasz typ danych dla zmiennej w kodzie, mówi on VBA, jak przechowywać tę zmienną i ile miejsca na nią przydzielić.

Na przykład, jeśli musisz użyć zmiennej, która ma przechowywać numer miesiąca, możesz użyć typu danych BYTE (który może pomieścić wartości od 0 do 255). Ponieważ numer miesiąca nie będzie większy niż 12, będzie to działać dobrze, a także zarezerwować mniej pamięci dla tej zmiennej.

Wręcz przeciwnie, jeśli potrzebujesz zmiennej do przechowywania numerów wierszy w programie Excel, musisz użyć typu danych, który może pomieścić liczbę do 1048756. Najlepiej więc użyć typu danych Long.

Deklarowanie zmiennych typów danych

Dobrą praktyką kodowania jest deklarowanie typu danych zmiennych (lub stałych) podczas pisania kodu. Dzięki temu VBA przydzieli do zmiennej tylko określoną pamięć, co może przyspieszyć działanie kodu.

Poniżej znajduje się przykład, w którym zadeklarowałem różne typy danych do różnych zmiennych:

Sub DeclaringVariables() Dim X As Integer Dim Email As String Dim Imię As String Dim RowCount As Long Dim TodayDate As Date End Sub

Aby zadeklarować zmienny typ danych, należy użyć instrukcji DIM (skrót od Dimension).

W 'Dim X jako liczba całkowita‘, zadeklarowałem zmienną X jako typ danych Integer.

Teraz, kiedy używam go w swoim kodzie, VBA wiedziałby, że X może przechowywać tylko typ danych całkowitych.

Jeśli spróbuję przypisać do niego wartość, która nie jest liczbą całkowitą, otrzymam błąd (jak pokazano poniżej):

Uwaga: Możesz także nie deklarować typu danych, w którym to przypadku VBA automatycznie uwzględnia zmienną wariantowego typu danych. Wariant typu danych może pomieścić dowolny typ danych. Chociaż może to wydawać się wygodne, nie jest najlepszym rozwiązaniem korzystanie z wariantu typu danych. Zajmuje więcej pamięci i może spowolnić działanie kodu VBA.

Wykonywanie deklaracji zmiennej obowiązkowej (opcja jawna)

Chociaż możesz kodować bez deklarowania zmiennych, jest to dobra praktyka.

Oprócz oszczędzania pamięci i zwiększania wydajności kodu, deklarowanie zmiennych ma jeszcze jedną ważną zaletę - pomaga wyłapywać błędy spowodowane błędnie wpisanymi nazwami zmiennych.

Aby upewnić się, że jesteś zmuszony do deklarowania zmiennych, dodaj następujący wiersz na górze swojego modułu.

Opcja Wyraźna

Kiedy dodasz „Option Explicit”, będziesz musiał zadeklarować wszystkie zmienne przed uruchomieniem kodu. Jeśli istnieje jakakolwiek zmienna, która nie została zadeklarowana, VBA wyświetli błąd.

Korzystanie z Option Explicit daje ogromne korzyści.

Czasami może się zdarzyć, że popełnisz błąd podczas pisania i wprowadzisz nieprawidłową nazwę zmiennej.

Zwykle VBA nie może wiedzieć, czy jest to błąd, czy celowy. Jednak gdy użyjesz „Opcji Explicit”, VBA zobaczy błędnie napisaną nazwę zmiennej jako nową zmienną, która nie została zadeklarowana i wyświetli błąd. Pomoże to zidentyfikować te błędnie zapisane nazwy zmiennych, które mogą być dość trudne do zauważenia w długim kodzie.

Poniżej znajduje się przykład, w którym użycie „Option Explicit” identyfikuje błąd (który nie mógłby zostać uwięziony, gdybym nie użył „Option Explicit”)

Sub CommissionCalc() Dim CommissionRate jako Double If Range("A1").Value > 10000 Then CommissionRate = 0,1 Else CommissionRtae = 0,05 End If MsgBox "Total Commission: " & Range("A1").Value * CommissionRate End Sub

Zauważ, że raz błędnie napisałem słowo „CommissionRate” w tym kodzie.

Jeśli nie użyję Option Explicit, ten kod zostanie uruchomiony i poda mi nieprawidłową całkowitą wartość prowizji (w przypadku, gdy wartość w komórce A1 jest mniejsza niż 10000).

Ale jeśli użyję Option Explicit na górze modułu, nie pozwoli mi to uruchomić tego kodu, dopóki nie poprawię błędnie napisanego słowa lub nie zadeklaruję go jako innej zmiennej. Wyświetli się błąd, jak pokazano poniżej:

Chociaż możesz wstawić wiersz „Opcja Explicit” za każdym razem, gdy kodujesz, oto kroki, aby wyświetlić go domyślnie:

1. Na pasku narzędzi Edytora VB kliknij Narzędzia.
2. Kliknij Opcje.
3. W oknie dialogowym Opcje kliknij zakładkę Edytor.
4. Zaznacz opcję - „Wymagaj deklaracji zmiennej”.
5. Kliknij OK.

Po włączeniu tej opcji, za każdym razem, gdy otworzysz nowy moduł, VBA automatycznie doda do niego wiersz „Opcja jawna”.

Uwaga: ta opcja będzie miała wpływ tylko na każdy moduł, który utworzysz po włączeniu tej opcji. Nie dotyczy to wszystkich istniejących modułów.

Zakres zmiennych

Do tej pory widzieliśmy, jak deklarować zmienną i przypisywać do niej typy danych.

W tej sekcji omówię zakres zmiennych i sposób, w jaki można zadeklarować zmienną, która będzie używana tylko w podprogramie, w całym module lub we wszystkich modułach.

Zakres zmiennej określa, gdzie zmienna może być użyta w VBA,

Istnieją trzy sposoby określania zakresu zmiennej w Excel VBA:

1. W ramach jednego podprogramu (zmienne lokalne)
2. Wewnątrz modułu (zmienne na poziomie modułu)
3. We wszystkich modułach (zmienne publiczne)

Przyjrzyjmy się szczegółowo każdemu z nich.

W ramach jednego podprogramu (zmienne lokalne)

Kiedy deklarujesz zmienną w ramach podprogramu/procedury, ta zmienna jest dostępna tylko dla tego podprogramu.

Nie możesz go używać w innych podprogramach w module.

Zaraz po zakończeniu podprogramu zmienna zostaje usunięta, a używana przez nią pamięć jest zwalniana.

W poniższym przykładzie zmienne są zadeklarowane w podprogramie i zostaną usunięte po zakończeniu tego podprogramu.

W module (zmienne na poziomie modułu)

Jeśli chcesz, aby zmienna była dostępna dla wszystkich procedur w module, musisz zadeklarować ją na górze modułu (a nie w podprogramie).

Gdy zadeklarujesz ją na górze modułu, możesz użyć tej zmiennej we wszystkich procedurach w tym module.

W powyższym przykładzie zmienna „i” jest zadeklarowana na górze modułu i jest dostępna do użycia przez wszystkie moduły.

Zauważ, że kiedy podprogram się kończy, zmienne poziomu modułu nie są usuwane (zachowują swoją wartość).

Poniżej przykład, gdzie mam dwa kody. Kiedy uruchamiam pierwszą procedurę, a następnie drugą, wartość „i” wynosi 30 (ponieważ zawiera wartość 10 z pierwszej procedury)

We wszystkich modułach (zmienne publiczne)

Jeśli chcesz, aby zmienna była dostępna we wszystkich procedurach w skoroszycie, musisz zadeklarować ją za pomocą słowa kluczowego Public (zamiast DIM).

Poniższy wiersz kodu u góry modułu udostępni zmienną „CommissionRate” we wszystkich modułach skoroszytu.

 Podwójna stawka prowizji publicznej

Możesz wstawić deklarację zmiennej (za pomocą słowa kluczowego Public) w dowolnym module (na górze przed jakąkolwiek procedurą).

Zmienne statyczne (które zachowują wartość)

Podczas pracy ze zmiennymi lokalnymi, gdy tylko procedura się zakończy, zmienna straci swoją wartość i zostanie usunięta z pamięci VBA.

Jeśli chcesz, aby zmienna zachowała wartość, musisz użyć Statyczny słowo kluczowe.

Pozwól, że najpierw pokażę, co dzieje się w normalnym przypadku.

W poniższym kodzie, gdy wielokrotnie uruchamiam procedurę, za każdym razem wyświetli się wartość 10.

Sub Procedura1() Dim i As Integer i = i + 10 MsgBox i End Sub

Teraz, jeśli użyję słowa kluczowego Static zamiast DIM i uruchomię procedurę wiele razy, będzie ona nadal wyświetlać wartości w przyrostach co 10. Dzieje się tak, ponieważ zmienna „i” zachowuje swoją wartość i używa jej w obliczeniach.

Sub Procedura1() Statyczna i As Integer i = i + 10 MsgBox i End Sub

Deklarowanie stałych w Excel VBA

Chociaż zmienne mogą się zmieniać podczas wykonywania kodu, jeśli chcesz mieć stałe wartości, możesz użyć stałych.

Stała umożliwia przypisanie wartości do nazwanego ciągu, którego można użyć w kodzie.

Zaletą używania stałej jest to, że ułatwia ona pisanie i rozumienie kodu, a także pozwala kontrolować wszystkie stałe wartości z jednego miejsca.

Na przykład, jeśli naliczasz prowizje, a stawka prowizji wynosi 10%, możesz utworzyć stałą (CommissionRate) i przypisać jej wartość 0,1.

W przyszłości, jeśli zmieni się stawka prowizji, wystarczy dokonać zmiany w jednym miejscu, zamiast ręcznie zmieniać ją wszędzie w kodzie.

Poniżej znajduje się przykład kodu, w którym przypisałem wartość stałej:

Sub CalculateCommission() Dim CommissionValue As Double Const CommissionRate As Double = 0.1 CommissionValue = Zakres("A1") * CommissionRate MsgBox Wartość prowizji End Sub

Poniższy wiersz służy do deklarowania stałej:

Stała stawka prowizji jako podwójna = 0,1

Deklarując stałe, musisz zacząć od słowa kluczowego „Stała‘, po którym następuje nazwa stałej.

Zauważ, że w tym przykładzie określiłem typ danych stałej jako Double. Ponownie, dobrą praktyką jest określenie typu danych, aby kod działał szybciej i był bardziej wydajny.

Jeśli nie zadeklarujesz typu danych, zostanie on uznany za wariantowy typ danych.

Podobnie jak zmienne, stałe mogą również mieć zasięg zależny od miejsca i sposobu ich zadeklarowania:

1. W ramach jednego podprogramu (stałe lokalne): Są one dostępne w podprogramie/procedurze, w której są deklarowane. Po zakończeniu procedury stałe te są usuwane z pamięci systemu.
2. W module (stałe na poziomie modułu): Są one deklarowane na górze modułu (przed jakąkolwiek procedurą). Są one dostępne dla wszystkich procedur w module.
3. We wszystkich modułach (stałe publiczne): Są one deklarowane przy użyciu słowa kluczowego „Publiczne” na górze dowolnego modułu (przed każdą procedurą). Są one dostępne dla wszystkich procedur we wszystkich modułach.

Możesz również polubić następujące samouczki VBA:

• Jak nagrać makro w programie Excel
• Praca z komórkami i zakresami w Excel VBA
• Praca z arkuszami roboczymi za pomocą Excel VBA
• Praca ze skoroszytami w Excel VBA
• Wydarzenia VBA
• Pętle Excel VBA
• Jak uruchomić makro w programie Excel
• Instrukcja If Then Else w Excel VBA.