學習Excel技術，關注微信公眾號：

excelperfect

在前面的幾篇文章中，我們自定義的函數使用定義為Range的參數來從Excel工作表中獲取數據，例如：

Function VINTERPOLATEB(Lookup_Value As Variant, _

???Table_Array As Range, _

???Col_Num As Long)

如果在公式中使用單元格區域作為參數來調用該函數，運轉得非常好：

=VINTERPOLATEB($H1,$A$1:$C$10000,2)

但是，如果使用計算表達式或者一組常量作為參數，則返回的結果為#Value：

{=VINTERPOLATEB($H1,($A$1:$C$10000*1),2)}

這個公式是數組公式，因此輸入完后按Ctrl+Shift+Enter組合鍵，Excel自動在公式兩邊添加花括號。

=VINTERPOLATEB(4.5,{1,3,3.5;4,4,4.5;5,4.5,5},2)

這個公式使用了3行3列的數組常量。

Excel在調用函數之前會檢測到這些參數不是單元格區域。

通過將參數定義為Variant型而不是Range可以解決此問題：Variant型參數幾乎可以包含任何內容！但用戶自定義函數現在必須處理Variant可能包含的所有不同類型的數據。

一種簡單的方法是將參數聲明為Variant型變量：這會將所有內容強制轉換為值：

Function TestFunc(theParameter AsVariant)

???Dim vArr As Variant

???vArr = theParameter

???TestFunc = vArr

End Function

在VBE中，在賦值給函數的返回值的語句行設置斷點，如下圖1所示

圖1

輸入數組公式：

=TestFunc($A$1:$A$5*1)

本地窗口顯示如下圖2所示。

圖2

在本地窗口可以看到，vArr變量包含2維Variant型數組，子類型為Double。

輸入公式：

=TestFunc({1,2,3;5,6,7})

在本地窗口中可以看到其結果也是2維數組：

圖3

輸入公式：

=TestFunc({1,2,3})

在本地窗口可以看到結果為1維數組：

圖4

輸入公式：

=TestFunc({1;2;3})

在本地窗口可以看到結果為2維數組：

圖5

輸入公式：

=TestFunc(15)

可以看到結果為一個標量，而非數組：

圖6

如果提供單元格區域作為函數參數：

=TestFunc($A$1:$A$5)

則得到：

圖7

注意，theParameter變量包含對象子類型Range，意味著必須將它視為Range變量，而vArr包含從該Range變量中提取的值。

因此，在通用目的的用戶自定義函數中，希望使用Variant型參數，并且經常需要確定變體的類型以及上限和下限。

為了獲得最大效率，不能只使用vArr=theVariant，因為：

• 不能使用.Value2，因為它可能不是單元格區域。

• 在許多情況下，希望在強制轉換所有值之前操控Range對象或者操控Range對象而不是強制轉換所有值。

因此，這里有一個函數用來確定傳遞的內容以及它的大小：

Function Variant_Type(theVariantAs Variant)

???Dim jRowL As Long

???Dim jRowU As Long

???Dim jColL As Long

???Dim jColU As Long

???Dim jType As Long

???Dim varr As Variant

??? '

??? ' theVariant可以包含標量, 數組, 或單元格區域

??? ' 找到上限和下限以及類型

??? 'type=1:單元格區域, 2:2維variant數組,

??? ' 3：1-維variant數組(列的單行), 4:標量

??? '

???On Error GoTo FuncFail

???jType = 0

???jRowL = 0

???jColL = 0

???jRowU = -1

???jColU = -1

???If TypeName(theVariant) = "Range" Then

??????? jRowL = 1

??????? jColL = 1

??????? jRowU = theVariant.Rows.Count

??????? jColU = theVariant.Columns.Count

??????? jType = 1

???ElseIf IsArray(theVariant) Then

??????? jRowL = LBound(theVariant, 1)

??????? jRowU = UBound(theVariant, 1)

??????? On Error Resume Next

??????? jColL = LBound(theVariant, 2)

??????? jColU = UBound(theVariant, 2)

??????? On Error GoTo FuncFail

??????? If jColU < 0 Then

??????????? jType = 3

??????????? jColL = jRowL

??????????? jColU = jRowU

??????????? jRowL = 0

??????????? jRowU = -1

??????? Else

???????? ???jType = 2

??????? End If

???Else

??????? jRowL = 1

??????? jRowU = 1

??????? jColL = 1

??????? jColU = 1

??????? jType = 4

???End If

???Variant_Type = jType

???Exit Function

FuncFail:

???Variant_Type = CVErr(xlErrValue)

???jType = 0

???jRowU = -1

???jColU = -1

End Function

注意，首先測試變量是否包含Range，這是為了避免無意中將Range強制轉換為其值。在確定變體的子類型時，VBA還有幾種方法：

• If TypeOf theVariant Is Range Then

• If TypeName(theVariant) = “Range”Then

嘗試使用VarType(theVariant)時要特別小心，這會對Range的覆蓋范圍進行強制轉換，然后拋出結果值！

代碼的圖片版：

小結：在通用目的的用戶自定義函數中，必須使用Variant類型的參數而不是Range類型。可以通過在處理變量之前確定變體包含的內容來有效地處理出現的問題。

總結

以上是生活随笔為你收集整理的必须声明标量变量_Excel VBA解读（136）： 在用户定义函数中的变体、引用、数组、计算表达式、标量...的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。