這裡介紹如何在 Excel VBA 中宣告、初始化與操作各種變數。

一般在程式設計上,變數在使用前都要經過宣告(declare)與定義(define)兩個步驟,宣告就是讓電腦知道我們要使用變數,而定義則是將變數的內容指定為一個特定的值,在 Excel VBA 中也是一樣有這些步驟,以下是一些教學與範例。

宣告與初始化變數

Excel VBA 中的變數宣告是使用 DimAs 兩個關鍵字,分別指定變數名稱以及變數的類型,例如宣告一個整數類型的變數 x

Dim x As Integer

這樣就會建立一個變數 x,不過這時候該變數並沒有儲存任何的資料,若要設定變數一開始的值,就要對變數進行定義,而定義變數的語法很單純,只是使用簡單的等好而已:

x = 5

這樣就可以將變數 x 指定成 5,隨後就可以開始使用這個新建立好的變數了:

Range("A1").Value = x

這是浮點數的例子:

Dim x As Double
x = 5.5
MsgBox "value is " & x

布林值:

Dim x As Boolean
x = True
MsgBox "value is " & x

字串:

Dim x As String
x = "G.T.Wang"
MsgBox "My name is " & x

Excel VBA 中的變數類型有好多種,完整的變數類型列表請參考 MSDN 網頁

萬用類型變數

上面介紹的那些是屬於一般性的變數類型,而 VBA 中種特殊的萬用類型變數,也就是一種可以儲存任何資料的類型,其稱為 Variant,其使用方式與一般的類型類似:

Dim x As Variant
x = "G.T.Wang"
MsgBox "My name is " & x
x = 123
MsgBox "The value is " & x

宣告為 Variant 的變數可以儲存任何類型的資料。

如果在宣告變數時不指定變數類型,則 VBA 預設會將變數視為 Variant 類型,所以這兩種寫法的效果是相同的。

Dim x As Variant
Dim x ' 預設為 Variant

Variant 類型的變數通常可用於從 Excel 儲存格中讀取使用者輸入的資料,由於使用者輸入的資料變異性很大,可能會是任何類型,所以直接使用 Variant 來儲存會比較方便。

雖然 Variant 非常方便,但它的缺點就是程式執行效能較差,所以除非必要,在一般的情況下還是使用固定的變數類型較佳。

未經宣告的變數

Excel VBA 也允許使用者省略變數宣告,所以其實未經宣告也可以直接定義變數:

MyVar = 35

未經宣告的變數預設會是 Variant 萬用類型。

雖然不宣告就使用變數非常方便,但明確宣告變數可以讓程式執行效率較好,而且比較不容易因為打錯字造成 bugs,所以建議在寫程式時都明確加入變數宣告。如果想避免自己在寫程式時,不小心在沒有宣告的情況下就使用變數,或是有宣告變數,但是在使用時打錯又沒發現,可以在程式碼的開頭加上:

Option Explicit ' 強迫變數宣告

Sub Hello()
  Dim MyVar As Integer
  MyVar = 10
  MyInt = 10 ' 未宣告
End Sub

這樣只要遇到變數未宣告就使用的狀況,編譯時就會出現錯誤訊息:

excel-vba-programming-variable-1

變數未宣告錯誤

變數範圍

在一般的 Sub 副程式中宣告的變數,其範圍僅限於該副程式,有就是所謂的區域變數:

Sub Hello1()
  Dim MyVar As Integer
  MyVar = 12
  MsgBox MyVar
End Sub

Sub Hello2()
  Dim MyVar As Integer
  MyVar = 34
  MsgBox MyVar
End Sub
excel-vba-programming-variable-2

區域變數

若希望一個變數可以在整個模組內的副程式中使用,則可將變數宣告於副程式之外,建立模組層級的變數:

Dim MyVar As Integer

Sub Hello1()
  MyVar = 12
  MsgBox MyVar
End Sub

Sub Hello2()
  MsgBox MyVar
End Sub

這樣一來,在 Hello1 執行完之後,再繼續執行 Hello2 時,其所取得的 MyVar 變數值就會是 Hello1 中所設定的 12

excel-vba-programming-variable-3

模組層級變數

如果在程式比較複雜時,一個應用程式中可能會有好多個模組,一般模組層級的變數只能在該變數隸屬的模組之內被存取,若要在其他模組之內也可以存取,就必須將變數宣告為公開(public)的變數,宣告方式是將 Dim 改為 Public。假設應用程式中有兩個模組,分別為 Module1Module2,而我們想要在 Module1 中宣告一個 MyVar 變數,並且讓這個變數在 Module2 模組中也可以使用,則 Module1 會是這樣寫:

Public MyVar As Integer

Sub Hello1()
  MyVar = 12
  MsgBox MyVar
End Sub

Module2 則為:

Sub Hello2()
  MsgBox MyVar
End Sub

這樣 Module1Module2 兩個模組就可以同時存取 MyVar 這個變數的內容了。

常數

如果希望變數的內容在程式的執行過程中都是固定的,不會不小心被更改,可以把這種固定的變數加上 Const,宣告為常數,例如:

Const MyInteger As Integer = 42

這樣一來 MyInteger 這個整數就是一個不變的常數,無法被更改。

各種類型的變數都可以宣告為常數,用法都類似:

Const myDate As Date = #2/2/2020#
Const myDay As String = "Sunday"

參考資料:Power Spreadsheets