VBA - 冷門函數 Evaluate 的一些有趣用法

08-16

4 人贊了文章

Application.Evaluate 和 Worksheet.Evaluate函數

Evaluate函數是一個相對比較冷門的函數。

它的用處簡單來說是用來將一個變體數據類型（Variant）轉換成對象或者數值。

使用:

Application.Evaluate(Name) 或者 Worksheet.Evaluate(Name)

因為Name的數據類型是Variant，EVALUATE基本接受所有數據種類，如文本，數據單元格等等。注意的是它有255個位元組的限制。

估計上面你看的還是一頭暮水，下面先列舉些常見的用法：

1. 將字元串轉換成計算公式並執行

這應該是Evaluate最常見的用法比如算 1+2，你可以直接evaluate（1+2）

如果字元串前面和後面有多出來一個加運算符合它會自動忽略，比如Evaluate(+1+2+3).

應用方面如果你的代碼中間生成一些字元串你知道是公式,你想很快的將它轉換並計算, Evaluate就相當方便.

下面這個例子將一個數字裡面每一個數加起來求和, 只需要一行代碼.

Evaluate(Format("123456", Replace(Space(Len("123456")), " ", "+@")))

2.用於執行函數

下面直接算出A1：A10數據範圍的和（注意這裡A1：A10不需要雙引號引用）

MsgBox EVALUATE("SUM(A1:A10)")

EVALUATE 還可以簡寫成 [ ], 上面可以直接寫成

MsgBox [SUM(A1:A10)]

由Application.WorksheetFunction.Sum（ActiveWorksheet.Range("A1:A10") 直接到 [SUM(A1:A10)]. 是不是已經覺得有點爽啊？

特別是Debug的時候, 在Immediate窗口，你想算個數值來驗證一下。你可以直接這樣

Print [(SUM(C4:C10)+123)*0.5]

是不是很方便？！下面例子可以直接執行Vlookup函數.

Evaluate("VLOOKUP(""Andrew"",A1:B5,2,FALSE)")

Evaluate幾乎可以執行所有Excel表具有的函數。例如NA（）函數，在VBA的worksheetFunction是沒有這個函數的，但用Evaluate你可以這樣用它：

[c1]= [if(a1=a2,na(),""]

如果A1=A2，輸出#Value到C1.

注意:簡寫默認的表是ActiveSheet, 如果你要指定表, 可以這樣:[Sheet1!c1*Sheet2!c2].

另外一個常用到Evaluate 函數的情景是, 我們可以用它來代替For... Each的循環計算.

如下面例子, 需要計算數據區每一個單元格的數值乘以自己本身並把結果存回去原單元格.

先看一個常見的For... Each循環的解決方法.

Sub Test() Dim rngData As Range Dim rngCell As Range Set rngData = ThisWorkbook.Worksheets("Sheet1").Range("A1:B10") For Each rngCell In rngData rngCell = rngCell * rngCell NextEnd Sub

用Evaluate函數像下面這樣直接一行就可以了, 像使用數組函數一樣,非常方便. 速度也比上面的Loop要稍微快一點.

Sub Test() Dim rngData As Range Set rngData = ThisWorkbook.Worksheets("Sheet1").Range("A1:B10") rngData = Evaluate(rngData.Address & "*" & rngData.Addres)End Sub

數組函數也可以使用Evaluate函數. 比如:

[G5][=SUM(G5:G10-F5:F10)][A1:A10]=[A1:A10+SUM(B1:B10)]

因為Evaluate自動Evaluate 字元串來轉換成公式. 原則上你可以將字元串裡面任何一個字元變成變數作為參數. 只要保證最後輸出的文字公式是正確的就可以了.

如下面這個例子, 用Evaluate一行就寫出一個能接受參數的IF混合函數:

確定Zzz這個字元串是不是數字, 如果是就求絕對值,不是就直接輸出.

Sub RangeNumbersToAbsolute(Zzz As String) .Value = Evaluate("If(IsNumber(" & Zzz & "),Abs(" & Zzz & ")," & Zzz & ")") End Sub

3. 將字元串轉化成數組然後計算

比如將{1,2,3}變成數組你可以直接 Array = [{1,2,3}]

用表裡面一行數據直接轉換成一列的數組,不用transpose後再寫到表裡面然後存數組裡面. 列到行數組同理:

Array = [transpose(A1:A20)]Array = [transpose(transpose(A1:K1))]

單元格局域到數組,再進行數據運算: 下面所有單元格乘以12.

Array =[transpose(12*A1:A20)]

從今天起生成365天的數組

Array =[transpose(text(today()+row(1:365),"dd-mm-yyyy"))]

算乘冪都可以: 第幾行的行數乘以對應第幾列的列數乘冪

Array = [row(A1:D10)^column(a1:D10)]

to be continued......