みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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


自分は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プロシージャを使ってうまく計算できる方法ありましたらご教示お願いします。

投稿日時 - 2013-10-19 22:26:24

QNo.8312529

困ってます

質問者が選んだベストアンサー

あまり難しく考えない基本形で、
セル範囲ループ版と二次元配列版、2種。
もしもオーバーフローで困るようなことあれば、あらためて相談で。

' ' =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

投稿日時 - 2013-10-19 23:07:52

お礼

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

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

投稿日時 - 2013-10-20 08:37:50

このQ&Aは役に立ちましたか?

14人が「このQ&Aが役に立った」と投票しています

回答(3)

ANo.3

#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

投稿日時 - 2013-10-20 10:45:58

ANo.2

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

投稿日時 - 2013-10-20 10:41:19