VBAでの配列について

質問

VBA
で、
Dim pow(23) As String
Dim temp As Double

temp = WorksheetFunction.Average(pow)

としたいのですが、状況に応じて、powには、文字列が入ったり、0や空白だったりもします。
0や空白の場合、平均値に影響させたくありません。

現状、文字列等が入ると、
平均できません
見たいなエラーが出ます。
どういう風にするのが良いでしょうか

回答No.4

KenKen_SP

すみません。動作確認しないで投稿してしまいました。引数に単一
セルを渡してもダメだし、ゼロ除算のトラップもダメでした。
#3 の myAverage 関数を差し替えさせて下さい。

' // 0や空白を平均値に影響させない Average 関数
Public Function myAverage(ParamArray Args() As Variant) As Double

　　Dim lCount As Long
　　Dim dTotal As Double
　　Dim arg　　As Variant
　　Dim v　　　As Variant
　　Dim vTmp　 As Variant

　　For Each arg In Args
　　　　vTmp = arg
　　　　If Not IsArray(vTmp) Then
　　　　　 vTmp = Array(arg)
　　　　End If
　　　　For Each v In vTmp
　　　　　　If IsNumeric(v) And v <> 0 Then
　　　　　　　 dTotal = dTotal + CDbl(v)
　　　　　　　 lCount = lCount + 1
　　　　　　End If
　　　　Next v
　　Next arg

　　If lCount > 0 Then
　　　　myAverage = dTotal / lCount
　　Else
　　　　myAverage = 0
　　End If

End Function

回答No.3

KenKen_SP

> 0や空白の場合、平均値に影響させたくありません。

平均値を求めるならゼロ除算チェックがあった方が良いのでは？

下記は、引数で渡されたデータの内、平均値計算に係る該当データが存在
しなければ 0 を返すようにしてます。エラーを返してもいいと思いますが、
この辺は仕様の問題でしょう。

ワークシートでも使える関数として書いてみました。

Sub SampleProc()

　　' // Test1
　　Dim a(6) As String ' // Variant
　　a(0) = ""
　　a(1) = "Test"
　　a(2) = 1
　　a(3) = 2
　　a(4) = 6
　　a(5) = 0
　　a(6) = 1
　　MsgBox myAverage(a)

　　' // Test2
　　MsgBox myAverage(Range("A1:A10"))

End Sub

' // 0や空白を平均値に影響させない Average 関数
Public Function myAverage(ParamArray Args() As Variant) As Double

　　Dim lCount As Long
　　Dim dTotal As Double
　　Dim arg　　As Variant
　　Dim v　　　As Variant
　　Dim vTmp　 As Variant

　　For Each arg In Args
　　　　If TypeOf arg Is Range Then
　　　　　　vTmp = arg.Value
　　　　ElseIf IsArray(arg) Then
　　　　　　vTmp = arg
　　　　Else
　　　　　　vTmp = Array(arg)
　　　　End If
　　　　For Each v In vTmp
　　　　　　If IsNumeric(v) And v <> 0 Then
　　　　　　　 dTotal = dTotal + CDbl(v)
　　　　　　　 lCount = lCount + 1
　　　　　　End If
　　　　Next v
　　Next arg
　　myAverage = IIf(lCount > 0, dTotal / lCount, 0)

End Function

回答No.2

Wendy02

こんにちは。

>powには、文字列が入ったり、0や空白だったりもします。

平均値の計算自体を、マクロで作るのが早いと思います。
Avarage の引数は、パラメータ配列で、配列ではありません。

Excelだったら、配列に入れないで、そのまま、Range を、ワークシート関数で計算させたほうが速いです。

temp = myFunction(pow)
'----------------------------------
Function myFunction(ParamArray arg() As Variant)
　　Dim cnt As Double
　　Dim mTotal As Currency
　　Dim i As Long, j As Long
　　Dim v As Variant
　　For Each v In arg()
　　If IsArray(v) Then
　　　　For i = LBound(v) To UBound(v)
　　　　　　If IsNumeric(v(i)) And v(i) <> "" And v(i) <> 0 Then
　　　　　　　　mTotal = mTotal + v(i)
　　　　　　　　j = j + 1
　　　　　　End If
　　　　Next i
　　Else
　　　 If IsNumeric(v) Then
　　　　　 mTotal = mTotal + v
　　　　　　j = j + 1
　　　 End If
　　End If
　　Next v
　　　　myFunction = mTotal / j
End Function

回答No.1

FEX2053

VBAのコードは、後で見た時に「何をやってるか」分かるほうがいいです。
ウルトラＣみたいな解決方法はあると思いますが、むしろ、pow内部のデータを
明示的にチェックしたほうが正解なんじゃないかと思います。例えば

FOR I=0 TO 23
IF POW(I)<>"" THEN
X_AVG=X_AVG+POW(I)
X_CNT=X_CNT+1
ENDIF
NEXT
temp=X_AVG/X_CNT

みたいに、平均値計算の基本に戻ってコードを書き出しちゃったほうが
後で分かりやすくて良いんじゃないかと。VBA内のコードなら、多少長く
なっても演算時間は知れたものですし。