Excel VBA配列をFunctionに渡す

こんばんは、引数について教えてください。
Excel　VBAの関数を作っていましたが、

1.Function ColumnArrayの部分でコンパイルエラーが発生し、
　「配列がありません」と表示されます。

　引数を配列のみで渡した場合、問題なく渡せるようですが、
　他の引数と、CriteriaArrsの配列と一緒に渡せないのでしょうか。
　すべて配列として１つにまとめて渡さなければならないのでしょうか。

2.CriteriaArrs = Array("田中", "鈴木")の部分は、文字列の増減が発生しますので
　配列はParamArray 　CriteriaArrs()とした方がよいのでしょうか


説明が不足している点があるかもしれませんが宜しくお願いいたします。


Function ColumnArray(SheetName As Worksheet, _
StartCell As Range, _
FieldColumn As Long, _
CountColumn As Long, _
CriteriaArrs As Variant _
) As Long
・・・
End Function
-------------------------------------
sub test()
Dim CriteriaArrs() As Variant
Dim SheetA As WorkSheet
DIm RangeA range
CriteriaArrs = Array("田中", "鈴木")

set SheetA =Worksheet(1)
set RangeA=Range("B3")
FilterCount = ColumnArray(SheetA, RangeA, 3, 2, CriteriaArrs)

end sub

No.1 回答者： keithin 回答日時： 2014/06/23 02:49

＞１

ご相談に掲示されたマクロ（のちょっとした不具合を修正した）では、ご質問のトラブルは再現しません。

正常に作動：
Function ColumnArray(SheetName As Worksheet, _
StartCell As Range, _
FieldColumn As Long, _
CountColumn As Long, _
CriteriaArrs As Variant _
) As Long
ColumnArray = UBound(CriteriaArrs)
End Function

Sub test()
Dim CriteriaArrs() As Variant
Dim SheetA As Worksheet
Dim RangeA As Range
Dim Filtercount As Long

CriteriaArrs = Array("田中", "鈴木")

Set SheetA = Worksheets(1)
Set RangeA = Range("B3")
Filtercount = ColumnArray(SheetA, RangeA, 3, 2, CriteriaArrs)
Debug.Print Filtercount
End Sub

ご相談の状況が実際に再現するマクロを情報提供してみて下さい。



＞２

ParamArrayを宣言した場合、今のようにバリアント型のCriteriaArrsを渡すと「その配列全体が１つの要素」になったちょっとメンドクサイ格好で引き渡されてしまいます。あまりお勧めではありません。


敢えて書くと：
Function ColumnArray(SheetName As Worksheet, _
StartCell As Range, _
FieldColumn As Long, _
CountColumn As Long, _
ParamArray CriteriaArrs() As Variant _
) As Long
ColumnArray = UBound(CriteriaArrs)
End Function

Sub test()
Dim CriteriaArrs() As Variant
Dim SheetA As Worksheet
Dim RangeA As Range
Dim Filtercount As Long

'CriteriaArrs = Array("田中", "鈴木")

Set SheetA = Worksheets(1)
Set RangeA = Range("B3")
Filtercount = ColumnArray(SheetA, RangeA, 3, 2, "田中","鈴木","吉田","秋山")
Debug.Print Filtercount
End Sub