エクセルVBAのFunctionプロシージャの質問

エクセルのFunctionプロシージャを使って以下のユーザー定義関数を作りたいのですが上手くいきません。
アドバイスお願いしますm(＿_)m

やりたいこと
エクセルのA列とB列に複数の（たとえば10行の）数字が並んでいます。
各行ごとに　0.5×[A列の値]×[B列の値の二乗]
を算出し、その合計を求めたいです。
別の列（例えばC列）を使って上記の計算式を入力し、その合計を求めればよいのですが
どうしてもFunctionプロシージャを使って１セルだけを使って求めたいのです。


自分はVBA（エクセルマクロ）はあまり詳しくないのですが
色々ネットで調べた結果、以下のようなプロシージャを書いてみたのですが
上手く計算できません。

Function KE(element As Range)
Dim e As Range
Dim m() As Variant
Dim v() As Variant
Dim ans As Integer
i = 0
j = 0
For Each e In element
If i = j Then
m(i) = e.Value
i = i + 1
Else
m(j) = e.Value
j = j + 1
End If
Next e
k = i
i = 0
j = 0
While i < k + 1
ans = ans + 0.5 * m(i) * v(i) * v(i)
i = i + 1
Wend
KE = ans
End Function

もちろん、この形に拘りません
Functionプロシージャを使ってうまく計算できる方法ありましたらご教示お願いします。

No.1 ベストアンサー 回答者： cj_mover 回答日時： 2013/10/19 23:07

あまり難しく考えない基本形で、

セル範囲ループ版と二次元配列版、２種。
もしもオーバーフローで困るようなことあれば、あらためて相談で。

' ' =ffKE(A1:B10)　　'　　 QNo.8312529
Function ffKE(element As Range)
　　Dim v As Variant
　　Dim i As Long
　　For i = 1 To element.Rows.Count
　　　　v = v + element(i, 1) * element(i, 2) ^ 2
　　Next i
　　ffKE = 0.5 * v
End Function


' ' =ffKE9(A1:B10)　　'　　QNo.8312529
Function ffKE9(element As Range)
　　Dim mtxV() As Variant
　　Dim v As Variant
　　Dim i As Long
　　mtxV() = element.Value
　　For i = 1 To UBound(mtxV)
　　　　v = v + mtxV(i, 1) * mtxV(i, 2) ^ 2
　　Next i
　　ffKE9 = 0.5 * v
End Function

この回答へのお礼

ありがとうございます！！
どちらもうまく行きました！
本当にありがとうございます。

二つ目の
MtxV() = element.value
の記述は、この記述で、MtxVという配列に
各セルの値が格納されるという認識でよろしいでしょうか？
もし、お時間ありましたらご教示いただけると幸いです。

お礼日時：2013/10/20 08:37

No.3 回答者： mitarashi 回答日時： 2013/10/20 10:45

#2です、変な沼から抜け出せた嬉しさに（言い訳）推敲不足で投稿してしまいました。

最初のFunctionのbufは使用しておらず、消し損ないのゴミです。申し訳ありません。

Function KE(element As Range)
KE = 0.5 * WorksheetFunction.SumProduct(element.Columns(1).Value, element.Columns(2).Value, element.Columns(2).Value)
End Function

No.2 回答者： mitarashi 回答日時： 2013/10/20 10:41

VBAを使うまでもなく、配列数式や、SUMPRODUCTの本来の使用法を用いれば、セル一個で算出できます。

下記は、暇つぶしで実用的な意味はありません。
KE2という関数名にしたら、#REF!のエラーになって、はまってしまいました。数字を含むユーザー定義関数名は使えないのでしょうか？調べてみると、KEEEEEE2と8文字以上なら良いらしいです。ご参考まで。
Function KE(element As Range)
Dim buf As Variant

buf = element.Value
KE = 0.5 * WorksheetFunction.SumProduct(element.Columns(1).Value, element.Columns(2).Value, element.Columns(2).Value)
End Function

VBA中でSUMPRODUCTに基本形以外の事をさせるには、Evaluateを用いる必要があるそうです。
Function KEa(element As Range)
Dim buf As String

buf = "0.5*SumProduct(myformula1,myformula2^2)"
buf = Replace(buf, "myformula1", element.Columns(1).Address)
buf = Replace(buf, "myformula2", element.Columns(2).Address)
KEa = Application.Evaluate(buf)
End Function