| |
Excel2003を使用しています。
以下の、ユーザー定義関数の第二引数を可変長引数にしたいです。
例：=Sabutotal(109,A1,D1,G1....)のように使いたいです。
rngを配列にしてParamArray宣言する所まではわかるのですが、
動作しなくなります。
宜しくお願いします。
▼
Public Function Sabutotal(cN As Integer, rng As Range)
Application.Volatile
Dim C As Range
Dim mA As Variant
ReDim mA(0)
If cN > 100 Then
For Each C In rng
If Not C.EntireColumn.Hidden And Not C.EntireRow.Hidden And _
Not C.Formula Like "=Sabutotal(*" Then
ReDim Preserve mA(UBound(mA) + 1)
mA(UBound(mA)) = C.Value
End If
Next
Else
For Each C In rng
If Not C.Formula Like "=Sabutotal(*" Then
ReDim Preserve mA(UBound(mA) + 1)
mA(UBound(mA)) = C.Value
End If
Next
End If
Select Case cN
Case 1, 101: Sabutotal = Application.WorksheetFunction.Average(mA)
Case 2, 102: Sabutotal = Application.WorksheetFunction.Count(mA)
Case 3, 103: Sabutotal = Application.WorksheetFunction.CountA(mA)
Case 4, 104: Sabutotal = Application.WorksheetFunction.max(mA)
Case 5, 105: Sabutotal = Application.WorksheetFunction.min(mA)
Case 6, 106: Sabutotal = Application.WorksheetFunction.Product(mA)
Case 7, 107: Sabutotal = Application.WorksheetFunction.StDev(mA)
Case 8, 108: Sabutotal = Application.WorksheetFunction.StDevP(mA)
Case 9, 109: Sabutotal = Application.WorksheetFunction.Sum(mA)
Case 10, 110: Sabutotal = Application.WorksheetFunction.Var(mA)
Case 11, 111: Sabutotal = Application.WorksheetFunction.VarP(mA)
Case Else: Sabutotal = "種類がありません。"
End Select
End Function
|
|
![[未読]](./data/excel/image/read_icon.gif){kind=icon}
