Excel揭秘8：看看方括弧在VBA中的妙處——有用的Evaluate方法

在《Excel VBA解讀77：Evaluate方法》中，我們簡單介紹了該方法的語法及使用。在本文中，我們將詳細剖析這個方法。

在許多程序中，我們經常會看到像圖1所示的代碼片段，即出現了帶方括弧的字元串。

圖1

這些方括弧裡面的字元串為工作簿中定義的名稱，方括弧的作用就是告訴VBE編譯器將名稱轉換成相應的單元格或單元格區域。

其實，這是Evaluate方法的簡寫格式。

根據VBA幫助文件指出，Evaluate方法將Excel名稱轉換為對象或值。這樣的解釋確實有點模糊。

其語法為：

expression.Evaluate(Name)

expression可以省略，即簡寫為：

Evaluate(Name)

參數不只限於名稱，還可以是公式表達式字元串。

如果參數中沒有變數，還可以更簡略地寫為：

[Name]

使用Evaluate加上帶引號的參數的方法的優點是，可以在表達式中使用變數。而使用括弧簡寫的優點是，不僅簡短，而且在引用對象時會出現對象的屬性和方法的智能提示且不會導致失敗，例如代碼：

Evaluate("Sheet2").Activate

運行失敗。而代碼：

[Sheet2].Activate

成功運行，並且在輸入[Sheet2].後會出現屬性和方法的提示。

其參數可以是：

下面是VBA幫助中給出的一些示例代碼。

下列表達式是等價的：

[A1].Value=25

Evaluate(「A1」).Value=25

trigVariable=[SIN(45)]

trigVariable=Evaluate[「SIN(45)」]

Set firstCellInSheet =Workbooks("BOOK1.XLS").Sheets(4).[A1]

Set firstCellInSheet = Workbooks("BOOK1.XLS").Sheets(4).Evaluate("A1")

下面的代碼演示了在Evaluate方法中使用變數，將工作表Sheet1中單元格A1變為粗體格式：

Worksheets("Sheet1").Activate

boldCell = "A1"

Application.Evaluate(boldCell).Font.Bold = True

為什麼要使用Evaluate方法？

下面是幾個使用Evaluate方法的優點：

1. 代碼更簡短

大多數情形下，Evaluate方法可以使用方括弧的簡寫形式，使代碼更簡短。

下面的代碼在單元格A1中輸入數值100：

[A1].Value = 100

[A1] = 100

2. 可以在VBA中使用更多的工作表函數

在VBA中，通過WorksheetFunction對象可以使用很多在VBA中沒有與之等價的工作表函數，但仍有一些工作表函數不能在VBA中使用。然而，通過Evaluate方法卻可以在VBA中使用這些函數，或者是工作表數組公式。

例如，由於VBA有等效的IsEmpty函數提供了工作表函數ISBLANK相同的功能，因此不能通過WorksheetFunction對象使用ISBLANK函數。但是，如果需要在VBA使用ISBLANK函數，可以使用代碼：

Evaluate("=ISBLANK(A1)")

[ISBLANK(A1)]

如果工作表單元格A1為空，則返回TRUE，否則返回FALSE。

3. 公式更直觀

使用Evaluate方法比使用Application.WorksheetFunction更簡單且更直觀，其外觀與工作表函數相同，就像在單元格中編寫的公式一樣。

例如，在工作表中使用VLOOKUP函數：

=VLOOKUP(D1,A1:B4,2,FALSE)

通常，在VBA中的等效代碼為：

Application.WorksheetFunction.VLookup(Range("D1"),Range("A1:B4"), 2, False)

而如果使用Evaluate方法並忽略「=」號，在工作表中的公式可以直接複製到代碼中：

Evaluate("VLOOKUP(D1,A1:B4,2,FALSE)")

或者：

[VLOOKUP(D1,A1:B4,2,FALSE)]因此，對於很複雜的公式來說，使用Evaluate方法將其轉換到VBA代碼中將更方便，否則要修改為符合VBA語法的形式就需要大量的工作。

4. 代碼更有效率

下面的代碼將101至200的數值輸入到單元格區域A1:A100：

[A1:A100] = [ROW(101:200)]

下面的代碼將101至200的數值賦給一個Variant數組：

varArray = [ROW(101:200)]

它們都比使用循環更有效率。

5. 調用在運行時才創建的子過程

如果想要調用在運行時才創建子過程，由於這個子過程在編譯時不存在，因此會導致編譯時錯誤。雖然Run(「子過程名」)不會導致編譯時錯誤，但如果在運行時代碼還不存在，就會產生運行時錯誤。

而[子過程名]不會導致編譯時或者運行時錯誤。

因此，如果僅當滿足某種條件時才創建某子過程的情形下，並且另一子過程使用的值要從調用該子過程中獲取。如果該子過程存在則調用它，如果不存在則繼續運行，此時，Evaluate方法是調用該子過程的唯一選擇。

Evaluate方法的使用

正如已經講述的內容，Evaluate方法主要用於兩種情形。

情形1：Evaluate( 公式 )

評估公式表達式或者值，並轉換為值。

實際上，Evaluate方法在用於計算時，允許創建「虛擬單元格」，作為VBA代碼與工作表公式之間的介面。這些虛擬單元格允許VBA訪問和直接處理當前工作簿中每個工作表函數，因此提供了一種允許僅用一兩行VBA代碼就替換成千行公式的方法。

情形2：Evaluate( 對象名 )

將代表對象的對象名轉換為對象。

下面是Evaluate方法的一些基本應用示例。

示例1：從關閉的工作簿中取值

下面的代碼從當前工作簿所在文件夾的工作簿test.xlsx中獲取值。

Sub GetValueFromClosedWB()

With [Sheet2!A1:A5]

.Value = "="" &ActiveWorkbook.Path & "[test.xlsx]Sheet1"!A1:A5"

.Value = .Value "移除對原工作簿的鏈接

End With

End Sub

示例2：提取名稱中的值

下面的代碼首先創建一個名為「我的公眾號」的名稱，其內容為「完美Excel」，然後在當前工作表的單元格A1中輸入值，最後使用Evaluate方法將A1中的值和名稱的內容連接並顯示。

Sub GetNameValue()

ThisWorkbook.Names.Add "我的公眾號","完美Excel"

Range("A1").Value = "我的公眾號是"

MsgBox Evaluate("A1 & 我的公眾號")

End Sub

或者將最後一行代碼替換為簡寫形式：

MsgBox [A1 & 我的公眾號]

運行後的結果如圖2。

圖2

示例3：調用函數過程並從中獲取值

下面的代碼演示了調用子函數過程，並將返回的值加上100。

Sub CallFunc()

MsgBoxEvaluate("testFunc(100) 100")

MsgBox [testFunc(100) 100]

End Sub

Function testFunc(i As Long)

testFunc = i 10

End Function

運行後的效果如下圖3。

圖3

示例4：使用變數

下面的簡單示例演示了在Evaluate方法中使用變數的基本方法。

下面的代碼顯示當前工作表單元格B1至B10中的值。

Sub testGetVarValue()

Dim i As Long

For i = 1 To 10

MsgBox Evaluate("B" & i)

Next i

End Sub

下面的代碼在當前工作表單元格A1至A10中的值。

Sub testEnterValue()

Dim rng As Range, i As Long

For i = 1 To 10

Set rng = Range("A" & i)

[rng] = "完美Excel"& i

Next

End Sub

示例5：引用圖表和工作表對象

下面的代碼設置當前工作表單元格背景色及圖表格式。

Sub testObject()

[圖表 1].Activate

With ActiveChart.ChartArea

.Interior.Color = vbRed

.Border.Color = vbYellow

End With

[Sheet6].Cells.Interior.Color = vbBlue

End Sub

運行後的效果如圖4。

圖4

下面的代碼依次激活當前工作簿中的工作表。

Sub testObject1()

Dim ws As Worksheet, i As Long

For i = 1 To Worksheets.Count

Set ws = Worksheets("Sheet"& i)

[ws].Activate

Next i

End Sub

示例6：數組

下面的代碼在虛擬單元格中創建數組，然後將其輸入到工作表單元格中。

Sub EvaluateArray()

Dim Array_1D, Array_2D

With Worksheets("Sheet8")

Array_1D =[{"A","B","C","D","E"}]

.[A1].Resize(1, UBound(Array_1D, 1)) =Array_1D

Array_2D = [{1,2;3,4;5,6}]

.[A3].Resize(UBound(Array_2D, 1),UBound(Array_2D, 2)) = Array_2D

End With

End Sub

運行代碼後的結果如圖5。

圖5

示例7：統計單元格數據的數量

下面的程序統計列A中，某單元格的值在其上面的單元格中出現的次數。

Sub CountCellNum()

Dim i As Long

For i = 2 To [COUNTA(A:A)]

Evaluate("B" & i) =Evaluate("COUNTIF(A1:A" & (i - 1) & ",A" & i& ")")

Next i

End Sub

運行後的結果如圖6。

圖6

小結

Evaluate是一個強大的命令，然而它往往並不為人所知。

Evaluate基本上可以表示：做任何所需要執行的計算，如果要匯總數據，那麼就匯總；如果要運行另一個程序，那麼就運行另一個程序；如果要做…那麼就做…，總之，無論需要什麼，立即完成。

然而，Evaluate沒有很多介紹文檔，也會在許多程序中看到它的使用。但基本上是，雖然知道它能做什麼但並沒有真正看到它的好處，而只是看到了它表面上的一些，它的強大功能還需要深入挖掘。

本文為原創文章，轉載請聯繫我（xhdsxfjy@163.com）或者註明出處。

歡迎在下面留言，完善本文內容，讓更多的人學到更完美的知識。