SUM関数の中身（VBA)

質問

エクセルVBA初心者です。
SUM関数と全く同じものをVBAでつくるとどのような式（？）になるのでしょうか？

回答No.6

Wendy02 · ベストアンサー

こんばんは。

>ずうずうしい質問で申し訳ないのですが

次はないと思って、聞けるときに、聞いてください。
勉強のためだとしたら、本当に、良い教材かもしれません。VBAで必要な基礎は全部含まれていますし、結構、難しい内容です。

なお、#4のマクロコードに関しては、たぶん、半年とか１年では出来ません。ところが、私自身の経験ですが、ある日突然分かるようになるのですね。これを書いた人のは、こんなことを考えていたのかって、ずっと後に知ることになります。最初、さっぱり分からないものがあります。

>kei=kei+c.Valueとありますが
>.Valueがなぜ必要なのでしょうか

最初に、この件からです。実は、結論自体は、あまり書かれていないはずです。もちろん、.Value なしでも、合計値は変わりません。

標準のプロパティ
http://officetanaka.net/excel/vba/speed/s8.htm

#標準のプロパティであるValueプロパティを省略せずに書くと約20%も高速化します。
#今回あらためて検証を行った結果、高速化と言えるほどの差が出ないことがわかりました。

この書いた人は、有名なインストラクターですが、本来、この問題は、速さの違いではありません。
「プロパティなし」ですと、暗黙のプロパティから、.Value 値になりますが、デフォルト・プロパティを書くべきか、しなくてよいのか、という問題に対して、私は、「必ず入れる」というのを原則としています。曖昧にはしないほうがよいと思います。

「明示的（.Valueを入れる）」か「暗黙的(いれないで使う）」かということとは、私の経験則からですが、「プロパティなし＝暗黙的」の場合、外部からオートメーション(OLE)として使うと、うまくいきません。理由はよく分かりませんが、おそらく、Office のVBEの中でしか、自動的に、暗黙のプロパティが取れないからだと思います。

そういう理屈は別にしても、Microsoft から出されるサンプルなどには、そうした暗黙のプロパティのまま、つまり「Cells(1,1)/Range("A1")」のままに書いているコードは、ないからなのです。

>If TypeName(v) = "Range" Then
>の部分がよくわかりません

これは、v という変数が、どういう種類のものかを分けて処理する必要があるからです。
TypeName(変数）とすると、どういう型なの、返ります。

セルを参照すると、"Range"となって、数字を入れれば、ワークシートからのものは、すべて、"Double"型になります。文字を入れれば、"String"が出てきます。

'実験
Function TYN(a As Variant)
　TYN = TypeName(a)
End Function

話を元に戻すと、

　　For Each v In ar　'←パラメータ配列（配列）
　　'v は、配列の中のひとつ
　　　　If TypeName(v) = "Range" Then
　　　　'Range型なら、通常のループで出来ます。
　　　　　　For Each c In v　'←ここにセルの範囲が入れば、そのまま取れません。
　　　　　　　　kei = kei + c.Value
　　　　　　Next
　　　　Else
　　　　　'単純な値なら、そのまま足し算すればよいです。
　　　　　　kei = kei + v
　　　　End If
　　Next v

つまり、パラメータ配列という、ひとつひとつの中身は、Range型のように二重の構造になっているものがあります。それは、そのまま、「kei + v」のようには計算できませんので、もう一度、ループしてあげる必要があります。

お礼

解りやすい解説ありがとうございます
この一連のやりとりは
この先何度も見直すとおもいます。
貴重な時間を割いていただいて本当にありがとうございました。

回答No.5

Wendy02

補足：
本当は、#1さん、#2さんのコードを組みあせるのでしたら、こんな感じです。最初は、なかなか、こういうことも思いつきません。

Function kei(ParamArray ar() As Variant) As Variant
　　Dim v As Variant
　　Dim c As Variant
　　For Each v In ar
　　　　If TypeName(v) = "Range" Then
　　　　　　For Each c In v
　　　　　　　　kei = kei + c.Value
　　　　　　Next
　　　　Else
　　　　　　kei = kei + v
　　　　End If
　　Next v
End Function

出力型は、本来は、Double型ですが、エラー値の出力はありませんが、今後のエラー値を予想して、Variant 型になります。
kei(ParamArray ar() As Variant) As Variant　←この部分。

ParamArray　は、「パラメータ配列」と呼びます。バラメータ配列は、何でも入るので、Variant 型のみです。

ちなみに、変数の　v は、Value の頭の１文字
ar は、Array の二文字。
c は、cell の意味です。

時々、自分で関数をVBAで作ってみると、とても勉強になります。特に、私が作って不思議さを感じるのは、RANK関数です。RANK関数は、初期の頃のままになっています。なぜ、もう少し便利に作らなかったのかなって思うことがあります。SUMIF などは比較的に簡単です。ただ、実際の内部の仕様は公開はされていませんので、試してみるまで分からないものもあります。INDEX 関数などは、変わっていて、戻り値が、値が返っているように見えますが、RANGE型が返っています。

補足

何度もありがとうございます
ずうずうしい質問で申し訳ないのですが
If TypeName(v) = "Range" Then
の部分がよくわかりません
またそのとき
kei=kei+c.Valueとありますが
.Valueがなぜ必要なのでしょうか
「ｖが複数範囲であるならそれぞれの数値に対して加算しなさい」
ということでしょうか？

回答No.4

Wendy02

#3です。

補足：
>SUM関数と全く同じものをVBAでつくるとどのような式（？）になるのでしょうか？

私は、読み落としていたけれども、「まったく同じもの」というのは、遥か先の話ですね。
少なくとも、「初心者」という銘を打っている限りは、難しいです。以下ですと、私が、何年か前に書いたもので、中級レベルの技術で書かれたものです。

例えば、このような計算が可能です。

　A1～A9 まで、1,2,3,4,5,6,7,8,9 と入れておいて、

　=MYSUM(A1:A9+1)
とし、配列の確定（F2を押して、『ShiftとCtrlを押しながらEnterキー』を押す）で、「54」という答えが出せます。中身は、2,3,4,5,6,7,8,9,10 と計算したものと同じです。

VBAのプログラミングの本質的なところは、初級も上級も変わらないのですが、エラー対策を施せるようになると技術的に上になっていきます。エラー対策など必要がないと思っている限りは、なかなか上に上がっていきません。

SUM関数と同じようなエラーが出で来るはずです。ただ、完全に、エラーを排出できるわけではありません。例えば、[循環参照ダイアログ]の呼び出しが出来ません。#VALUE! エラーになるだけです。

このコードは、最初に、関数を入れて、入れた状態で、Ctrl + A のショートカットで、SUMと同じように出てくるかどうか、というところから始まっています。それから、[チップ・テキスト（クリーム色のヘルプメッセージ）]は出てきません。

それと、少しVBAやExcelを詳しい人ですと、揮発性(Volatile)とか不揮発性とか言う人がいます。簡単に、Volatile メソッドを入れればよいと思っている人もいるようですが、それは、少し考え違いをしています。それは、実際、引数の変化によって、値が変わればよいのですから、必ずしも、Volatile メソッドは必要がないのです。

'------------------------------------------------------

Public Function MySum(数値1 As Variant, ParamArray 数値2() As Variant) As Variant
Dim dblTotal As Double
Dim ar As Variant
Dim c As Variant
Dim Arg As Variant
Dim Args() As Variant
Dim fml As String
Const vbMyError As Integer = 513
On Error Resume Next
fml = Application.Caller.FormulaR1C1
On Error GoTo ErrHandler
If InStr(fml, "RC") > 0 Then
　　Err.Raise vbMyError　'すべての循環参照は検出できない
ElseIf InStr(fml, "R") = 0 And InStr(fml, "C") = 0 Then
　　Err.Raise vbMyError + 1　
End If

If TypeName(数値1) = "Range" Then
　　Set Arg = 数値1
　　For Each c In Arg
　　　　If VarType(c) = vbDouble Then
　　　　　　dblTotal = dblTotal + c.Value
　　　　End If
　　Next c
ElseIf TypeName(数値1) = "Double" Then
　　Arg = 数値1
　　dblTotal = dblTotal + Arg
ElseIf UBound(数値1, 1) > -1 Or UBound(数値1, 2) > -1 Then
　　Arg = 数値1
　　'配列時の計算
　　For Each c In Arg
　　　　dblTotal = dblTotal + c
　　Next c
　　MySum = dblTotal
End If
If Not IsMissing(数値2) Then
　　Args() = 数値2
ElseIf UBound(数値2) = -1 Then
　　'配列が確保されていない時の離脱条件
　　MySum = dblTotal
　　Exit Function
End If
For Each ar In Args()
　　If TypeName(ar) = "Range" Then
　　　　For Each c In ar
　　　　　　If VarType(c) = vbDouble Then
　　　　　　　　dblTotal = dblTotal + c.Value
　　　　　　End If
　　　　Next c
　　ElseIf TypeName(ar) = "Double" Then
　　　　dblTotal = dblTotal + ar
　　End If
Next ar
MySum = dblTotal
Exit Function
ErrHandler:
'エラー処理
If Err.Number = vbMyError Then
　　MySum = CVErr(xlErrValue)
ElseIf Err.Number = vbMyError + 1 Then
　　MySum = CVErr(xlErrName)
ElseIf IsError(数値1) Then
　　MySum = CVErr(数値1) '←この部分は検証されていない
End If
End Function

お礼

SUM関数、実は奥が深いんですね。全く知りませんでした。
お門違いもいいところですね、私。。
　SUM関数に似たものを作ろうとしていたので、SUM関数の
中身をいじくってマクロを作成しようとしてました。
　今回はマクロとセル中の式で強引に作って完成させました。
　今後Wendy02様の貼り付けていただいたコードをじっくり勉強し（半年くらいかけて、、）ユーザー定義関数いっぱつで計算結果がでるようにしたいと思います。
みなさんありがとう御座いました。

回答No.3

Wendy02

こんにちは。

それは、練習のためですか？
もし、そうなら、自分なりに、少しお考えになって、レスを付けてください。こちらが解答を書いても、そうなんだ、というぐらいで、良いことではないと思います。たかがSUMでも、かなりハイレベルです。自分の実力に合わせてということになると思います。入門編を終えても、実際のSUM関数には程遠いです。

私は、VBA入門時に、ほとんど何も知らないまま、いきなり、SUMの関数と同じものを作ることになり、数時間考えて、なんとか考えて作りました。しかし、その後、SUMを観察すると、いろんなことが分かり、そのたびに修正をしてきました。

作る前には、よくSUM関数を観察しないとダメです。

SUMの挙動というのは、ダン・ブリックリン氏が作ったものだといわれますが、その後、Lotus社を経て、再び、Microsoft に戻ってきましたが、最初の挙動だけは、伝統的に残っています。

なぜ、SUM関数は、「文字列を0と数えるか？」というコラムを本で読んだことがあります。同系列の関数には、SUMPRODUCT関数 やPRODUCT 関数があります。

SUM関数の引数は、Range型ではなく、Variant 型で、おまけに、パラメータ配列となっています。だから、受けた引数をは最初に、どんな型になっているか判定しなければなりません。そして、その型によって、計算方法が変わります。単に、パラメータ配列にしても、その中に、Range型が入っていれば、そのままではエラーが発生してしまいます。また、循環参照を抜けるためには、どうしたらよいのか、配列数式の場合とか、難問がいくつかあります。

練習でなかったら、WorksheetFunction.Sum でよいと思います。

回答No.2

n-jun

ANo.1さんのコードをお借りすると、

Function kei(ParamArray r() As Variant) As Long
Dim c As Variant
For Each c In r
kei = kei + c
Next
End Function
でいけるかと。
でも普通にワークシート関数でもいいような。

お礼

こんどは=kei(a1:b2)等に対応できなくなってしまいました。
ご回答ありがとうございました。
No１様とNo2様の回答を組み合わせると
望みのマクロができそうです。

回答No.1

marbin

Worksheet.Function.Sum()
ではだめですか？

自作するなら、
セル範囲の値をFor～Nextで合計していくだけです。

Function kei(r)
Dim c As Range
For Each c In r
kei = kei + c.Value
Next
End Function

補足

早速ありがとうございます

ためしてみたのですが、セル範囲が飛ぶと、、、つまり
=kei(a1,b2)
とするとエラーがでてしまいした。

それも対応させるためにはどうすればよいのでしょうか？