ExcelVBAで次のコードを説明して欲しい

質問

教えてください。下の表があります。
コード 数量
327 3
327 2
332 5
332 8
332 9
336 5
336 6

標準モジュールに次のようにコードが書いてあります。

Sub コード別集計とグループ化()
Dim d As Long
Dim s As String

d = Cells(1, 1).CurrentRegion.Rows.Count
s = "a1:b" & d
Range(s).Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2)
End Sub
他人が作ったものです。動作は支障ありません。VBAを勉強中なのですが、なぜ、このように記述するのか分かりません。できるだけ詳しく解説していただけないでしょうか。
特に分からないのが s = から始まるところと、Range(s).～のところです。賢者の皆様よろしくお願いします。

回答No.3

BlueRay · ベストアンサー

>なぜコードの変わり目を取得できるのでしょう。
「327,332,336」をそれぞれに集計って事ですね。
>なぜコード+合計と表示できるのでしょう。
「327 計,332 計,336 計」を表示って事ですね。
>想像するに、ExcelVBAに定型的な機能としてプログラム化してある。…省略

↓↓↓

この「コード別集計とグループ化」という処理は、対象範囲の自動検索と
メニューバーの「データ」→「集計」をの処理を行っています。

対象範囲の自動検索の処理は、
>d = Cells(1, 1).CurrentRegion.Rows.Count
>s = "a1:b" & d
です。

集計の処理は、
>Range(s).Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2)
です。

集計には、コード別にと言う処理を行う事ができる(定型的な)機能が元々あるんです。

>勉強すれば誰でも簡単に作れるものなんでしょうか。
これをですよね？
↓
>Range(s).Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2)

もちろん、勉強すればご自分で作る（マクロでですよね？）事も可能ですし、
もっと高機能なもの（？）まで作る事も可能です。

お礼

いろいろとお教えいただきありがとうございました。
私にとって、このサイトはとても重宝しています。過去にも多くの賢者の皆様に教えをいただきました。私には VBA をいとも簡単に作ってしまう方々が天才か魔法使いのように思えます。しかも無料というのがうれしいです。また、お伺いしますのでよろしくお願い申し上げます。

回答No.2

BlueRay

Sub コード別集計とグループ化()
　　Dim d As Long
　　Dim s As String

　　'コメント(1)
　　d = Cells(1, 1).CurrentRegion.Rows.Count

　　'コメント(2)
　　s = "a1:b" & d

　　'コメント(3)
　　Range(s).Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2)
End Sub

コメント
(1)セルA1から下方に空白が見つかるまで検索。空白が見つかった時点でその前行を最終行としてdへ格納。ここでは7
(2)処理対象範囲をsへ格納。左上[a1]から右下[b?]を処理対象範囲として定義。ここでは、7行なのでb7となる。
(3)2で確定した範囲を集計対象として、グループ化を行う。
　グループ化する対象の列は、1(A列)で集計方法はxlSum(合計算出)で集計される列は2(B列)となる。
　(※GroupBy:対象列は1(A列)　Function:集計方法はxlSum(合計算出)　TotalList:集計される列は2(B列))

以上で如何でしょうか。

お礼

先日につづいてのご教示、感謝いたします。
>※GroupBy:対象列は1(A列)　Function:集計方法はxlSum(合計算出)、TotalList:集計される列は2(B列))
少し分かったような気がします。それにしても VBA ってすごいですね。私の能力ですとある程度理解するまで３年以上かかりそうです。ありがとうございました。

補足

なぜコードの変わり目を取得できるのでしょう。
なぜコード+合計と表示できるのでしょう。
想像するに、ExcelVBAに定型的な機能としてプログラム化してある。でしょうか。このプロシージャを作った人は天才なんでしょうか。それとも勉強すれば誰でも簡単に作れるものなんでしょうか。

回答No.1

MSZ006

d = Cells(1, 1).CurrentRegion.Rows.Count
これはデータの入っている行数を数えてｄに格納しています。ご質問の例では７になります。

s = "a1:b" & d
これはSにデータの範囲を格納しています。ご質問の例では"a1:b7"という範囲になります。

Range(s).Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2)
先ほど取得した範囲S（"a1:b7")でsubtotalをしています。
GroupBy:=1　１列目(a列)をキーにする
Function:=xlSum　合計する
TotalList:=Array(2)　二列目(b列)を合計する要素とする

あまり詳しく判っていませんので間違っていたらすみません。

お礼

早速のご回答感謝いたします。
S="a1:b" & d までは分かりました。質問の例は簡略化してありますがこのプロシージャを実行すると、コードの変わり目ごとに行が挿入されて、327合計、5 と表示されます。最後の行に総計と表示されます。しかもコードごとにグループ化されます。行の挿入や合計、総計の記述をした覚えがないのに…、たった１行でこんなことできるなんて VBA ってほんとにすごいと思いました。また、教えてください。