VBA カッコはどういうときに付けるの？
こんにちは。
派犬事務員のコロ子です。
実は今ノンプロ研のVBA初級講座でティーチングアシスタント（TA）をしている。前回、中級講座のTAでもテンパったけど、初級講座でも受講生からの鋭い質問にテンパっている。
先日はカッコの付け方についての質問があった。
メッセージボックスで
MsgBox "派犬", vbOKOnly ←実行できる
MsgBox ("派犬",vbOKOnly) ←エラーになる
どうしてカッコを付けるとエラーになるのですか？うわ～（汗）めちゃめちゃいい質問。
なんとなく雰囲気で戻り値を受ければいいのは分かる。
でも、どうしてカッコが要らないのかは答えに詰まる。
似たようなので
Workbooks.Open
これもいつもカッコが要るのか要らないのかあやふやで、エラーが出たらカッコを取る、みないなことをしている。
長年なんとなく放置した問題についに向かい合う時がきた。
Office TANAKAのサイトでは下記のようなルールだといっている。
返り値を何かに使うときは、引数を括弧で囲まなければなりません。
返り値を何かに使わないときは、引数を括弧で囲ってはいけません。これが、VBAにおける括弧のルールです。
なるほどー。ルールなのかー。
でも、何でもカッコをつければ戻り値が戻るとは思えない。
いったいどんなときにカッコをつければいいのだろう。
オブジェクトブラウザを見てみる
VBEエディタでF2キーを押すとライブラリ一覧が表示される。
MsgBoxを検索して下のを見てみると説明が書いてある。
Functionとなっている。MsgBoxはFunctionプロシージャなのだ。
Functionプロシージャなので当然、戻り値がある。
他のプロシージャを呼び出すには
Callは他のSubプロシージャーやFunctionプロシージャーを呼び出すことができる。
ただし、Callを使ってFunctionプロシージャーを呼び出す場合は戻り値は破棄される。
ということはCallは呼び出すだけで戻り値はない。
Callを使って他のプロシージャを呼び出すには、引数をカッコで囲む。
Call プロシージャ名(引数1, 引数2,…)
Call は省略できる。その場合はカッコを外す。
プロシージャ名 引数1, 引数2,…
ということで、戻り値がない他のプロシージャを呼び出す書き方は上記の2通り。
MsgBoxはFunctionプロシージャだけど、vbOKOnlyなどボタンが1つで、あえて戻り値が必要ない場合は上記のどちらかの方法で呼び出す。
Callを付けるか、カッコを外すか。たまーにCallがついているのを見かけるけど、カッコを外すのが一般的なんだろう。
Call MsgBox("派犬", vbOKOnly)
MsgBox "派犬", vbOKOnlyちなみに
Workbooks.OpenのOpenもFunction
Application.RunのRunもFunction
UnloadはSub
Collectionオブジェクト.AddのAddはSub
VBA プロのコードに脱帽
こんにちは。
派犬事務員のコロ子です。
ある行政提出用の書類をダウンロードしたらマクロが入っていた。
どんなマクロが組まれているんだろ？と思いながらなんとなくVBEエディタを開いてみたら、なんとパスワードがかかっていなくてエディタ開けちゃった！
えっ！本当に！？と驚きつつも、やったー、ラッキーとコードを見てさらに驚いた。
「えっーーーー！！！」
こんなコードの書き方あり！？
シートの数が20枚なので、シート名とセルが違うだけの同じコードが20回並んでいる。
見た瞬間、「普通、セルとシート名を引数にして、プリント部分を別プロシージャにするでしょ！なにこのダッサいコード。誰が作ったの？？」と心の中で叫んでしばらく固まった。ど素人が作ったのか？市の職員とかでVBAちょっとできるんですよ～、みたいな人が作ったのか？と最初本気でそう思った。
でも、良く考えてみたら、世間に配布するようなものを素人が作るはずはない。きっとどこかに委託して作っているはず。ということは、プロの仕事。
マジか。
たしかに分かりやすい。見た瞬間に何をしているのかすぐに分かる。
また変更にも強い。例えば、あるシートだけ2部プリントするとか印刷設定を変えるなどの変更も容易だ。プリント部分を別プロシージャにしていたら、IF文で判別するか、それ専用の別プロシージャを作成しなければならない。
別プロシージャになっていたら、どうしても「引数なんだっけ？」とちょっと考えなければならない。でもこの場合は全く考えることなく脳の負荷がゼロだ。
作るのだってコピペで簡単だ。例えシート名が変更になったとしても一括置換で対応できる。
なんて優れているんだ！！
まとめられるところをあえてまとめない。
凄い勇気。
素人ほどムダに知っている知識を使いたがって複雑にしてしまうのか！
ど素人はコロ子の方だ。
さらに別のプロシージャでは
これも正直、本当に驚いた。
Withでまとめることもなく、ブックやシートをオブジェクト変数に入れることもなく、シートの書式が全て同じなのに別プロシージャにしてループすることもなく、こんな感じで永遠と続いている。
シートの書式の変更なんてザラにある。あるシートだけ書式が変わっても容易に変更できる。ブックをオブジェクト変数に入れたりしていないので、下の方に行っても、この変数なんだっけ？とならない。
さらに凄いのは、コメントが一つもない。だって見れば分かるから。
本当に凄い。凄すぎる。
これがプロの技なのかー！脱帽。
なんか考え方変わった。
VBA 布教活動 超初心者の反応
こんにちは。
派犬事務員のコロ子です。
ウサ子の悩み
ウサ子：「最近担当することになったExcel作業に時間がかかって大変。忙しいのにそんな事やってられないよ～」
コロ子：「どんな作業なの？」
ウサ子：「毎日同じ作業なんだけど、地味に面倒で時間がかかるんだよね。何かいい方法ない？」
コロ子：「あるよー！マクロを作れば解決だよ！」
ウサ子：「もしかして、コロ子がいつも言っているVBAってやつ？プログラミングとか全然やった事ないんだけど、初めてでもできるの？」
コロ子：「できるよ！教えてあげるから一緒に作ろうよ。マクロなら、その仕事一瞬で終わるよ！」
ウサ子：「じゃぁ、やってみようかな」
VBAを布教する
コロ子：「まずは、ウサ子がやってる作業を教えて」
ウサ子：「サンプルデータで説明するね」
なんと！For文とIf文とプラスαで作れる絵に描いたような初心者向け！
では早速始めよう。
コロ子：「先ずはFor文で最初の行から最後の行までループして、1行ずつIf文で判定して、処理をする。」
ウサ子：「えっ！１行づつ処理するの？まとめてポンって処理できるんじゃないんだ。超地味！！」
そう。プログラミングとは超地味な作業なのだ。根気よくコツコツやるしかない。
ウサ子：「あのさ、実はこれはサンプルデータで10行しかないけど、実際は100行くらいあるんだけど。」
コロ子：「うん。その為に最終行までループするようにしたじゃん？」
ウサ子：「そうだけど、大変じゃない？」
コロ子：「何が？」
ウサ子：「マクロが。100回もループしたら大変じゃないの？」
人がやるなら大変だけど、機械がやるから大変じゃないよ。その為のマクロだからｗ。
ウサ子なかなか筋がいい。ちょっと説明しただけで、どんどん自分で作ってる。
よしよし、良い感じ。
と思いきやエラーでつまずく。
ウサ子：「何でこれダメなの？」
コロ子：「これは、こうこうこういう理由で・・・」
ウサ子：「はぁ？意味わかんねーんだよ！！！！！」
ひぇ～。
ウサ子がキレた。
普段は超温厚なウサ子が。
ウサ子：「こんなのやってらんねーよ！！！」
超温厚なウサ子がマジギレさせるとは。VBA恐るべし。
マウスを投げつけそうになるウサ子を何とかなだめて、今日はここまで、とパソコンの電源を切って一息ついた。
と、しばらくして落ち着いたらウサ子、またパソコンをやっている。
覗いてみるとVBAやってるじゃない！
そう。プログラミングとは辛い物を食べて「もう無理、これ以上食べられない」と思っても落ち着いたらまたもう一口食べたくなる感じに似ている。
もう無理→もうちょっとやってみよう→もう無理→もうちょっとやってみよう、を繰り返さなければ最後まで辿り着かないものなのだ。
頑張れウサ子。VBAの素晴らしさ分かってくれたかな。
VBA Dictionaryオブジェクトのアイテムが複数欲しい（アイテム配列編）
こんにちは。
派犬事務員のコロ子です。
「Dictionaryオブジェクトのアイテムが複数欲しい！」場合、アイテムを配列にする方法が分かった。
1回目（クラス編）
koroko.hatenablog.com
2回目（Dictionaryオブジェクト配列編）
koroko.hatenablog.com
アイテムを配列にする
Dictionaryオブジェクトのアイテムに配列をいれてみたところ、早速つまずいた。
アイテムに配列をセットして、配列の0番目を書き換えたい。
Sub Sample()
Dim dic As Dictionary
Set dic = New Dictionary
dic.Add "test", Array(10, 20, 30)
dic("test")(0) = 100
Debug.Print dic("test")(0)
Debug.Print dic("test")(1)
Debug.Print dic("test")(2)
End Subイミディエイトウインドウ
10 20 30
dic("test")(0)に100を入れたはずなのに、なぜか10のまま・・・。
どうやらアイテムに入れた配列を書き換えるには、一度外に出して編集して、再度格納する必要があるらしい。
Dim dic As Dictionary
Set dic = New Dictionary
dic.Add "test", Array(10, 20, 30)
dic("test") = Array(100, 200, 300)
Debug.Print dic("test")(0)
Debug.Print dic("test")(1)
Debug.Print dic("test")(2)イミディエイトウインドウ
100 200 300
おお！できた！
しかし、こんなの絶対に分からないよー。どこが間違っているか分からなくて相当悩んだ。（もちろん自力解決できずにノンプロ研で教えてもらった）
理由は「参照してる時は配列のコピーが取得されていて、更新ができないのだと思われる」とのこと。この辺は理解できないので「アイテムの配列はまるごと入れ替える」と覚えよう。
やり方が分かったので、前回の表で集計してみよう。
この表で会社ごとに各商品の金額を合計する。
キー：社名
アイテム（0）：商品A
アイテム（1）：商品B
アイテム（2）：商品C
Enum 項目名
en社名 = 1
en商品A
en商品B
en商品C
End Enum
Sub TEST()
Dim arr() As Long
Dim dic As Dictionary
Set dic = New Dictionary
Dim i As Long
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
Dim dickey As String
dickey = Cells(i, en社名).Value
If dic.Exists(dickey) Then
Dim sampleA As Long
Dim sampleB As Long
Dim sampleC As Long
'アイテムが配列の場合は一度外に出して計算し、再度格納する
sampleA = dic(dickey)(0) + Cells(i, en商品A)
sampleB = dic(dickey)(1) + Cells(i, en商品B)
sampleC = dic(dickey)(2) + Cells(i, en商品C)
dic(dickey) = Array(sampleA, sampleB, sampleC)
Else
dic.Add dickey, Array(Cells(i, en商品A), Cells(i, en商品B), Cells(i, en商品C))
End If
Next i
'確認
Dim buf As Variant
For Each buf In dic.Keys
Debug.Print dic(buf)(0), dic(buf)(1), dic(buf)(2)
Next
End Subもしかしたら、この方法が一番すっきりして分かりやすいかも！
Infomentさん、ExcelVBAerさん、Tanaka_Hiroakiさん、みなさんどうもありがとうございました！
VBA Dictionaryオブジェクトのアイテムが複数欲しい（Dictionaryオブジェクト配列編）
こんにちは。
派犬事務員のコロ子です。
前回「Dictionaryオブジェクトのアイテムが複数欲しい！」という記事を書いたら「Dictionaryオブジェクトを配列にすればよい」と教わった。
前回はクラスで変数を作る方法でしのいだ↓
koroko.hatenablog.com
Dictionaryオブジェクトを配列？？にするのですか！！！
む、むずい。
頭の中ぐるぐる混乱。
社名をキーにして、各商品の金額をアイテムに入れて合計を集計したい。
Dictionaryオブジェクトを3個作って列ごとに集計するイメージでいいのでしょうか？
Infomentさんのブログを見ながら作成してみた。
infoment.hatenablog.com
Enum 列名
社名 = 1
商品A
商品B
商品C
End Enum
Sub test1()
Dim dic(列名.商品A To 列名.商品C) As Dictionary
Dim c As Long
For c = 列名.商品A To 列名.商品C
Set dic(c) = New Dictionary
Dim r As Long
For r = 2 To Cells(Rows.Count, 1).End(xlUp).Row
Dim dickey As String
dickey = Cells(r, 列名.社名)
dic(c)(dickey) = dic(c)(dickey) + Cells(r, c)
Next r
Next c
'確認
For c = 列名.商品A To 列名.商品C
Dim buf As Variant
For Each buf In dic(c).Keys
Debug.Print Cells(1, c), buf, dic(c)(buf)
Next buf
Next c
End Sub
せっかく配列なので、Infomentさんのブログをまねして、元のシートも配列に入れてみよう。
Enum 列名
社名 = 1
商品A
商品B
商品C
End Enum
Sub TEST2()
Dim arr As Variant
arr = UsedRange.Resize(UsedRange.Rows.Count - 1, UsedRange.Columns.Count).Offset(1, 0)
Dim dic(列名.商品A To 列名.商品C) As Dictionary
Dim c As Long
For c = 列名.商品A To 列名.商品C
Set dic(c) = New Dictionary
Dim r As Long
For r = 1 To UBound(arr)
dic(c)(arr(r, 列名.社名)) = dic(c)(arr(r, 列名.社名)) + arr(r, c)
Next r
Next c
'確認
For c = 列名.商品A To 列名.商品C
Dim buf As Variant
For Each buf In dic(c).Keys
Debug.Print Cells(1, c), buf, dic(c)(buf)
Next buf
Next c
End Sub
元のシートも配列にするとさらにぐるぐる度が上がる～。
自力では絶対考えつかないなー。
Infomentさん、ExcelVBAerさん、どうもありがとうございました！
【次回】アイテムを配列にする方法↓
koroko.hatenablog.com
VBA DictionaryオブジェクトのItemが複数欲しい！（クラス編）
こんにちは。
派犬事務員のコロ子です。
以前に書いたブログのコードを見て「なんだコレ？」っていう変なコードがいっぱいある。なんか変だけど、そのときは真剣に書いていたから、それはそれでそのままにしておこう。「それは違ーーーう！！」と指摘してもらえたら嬉しいし。なんだコレ？のついでに、最近思いついたコードを載せてみよう。
DictionaryのItemが複数欲しい
Dictionaryオブジェクトのアイテムが複数欲しい！と思う事ない？
例えばこんなとき。
社名をキーにして、各商品の金額をアイテムに入れて合計を集計したい。
キー1つに対してこんなイメージで複数アイテムが欲しい。
Dictionaryオブジェクト.Add Key, Item1, Item2, Item3・・・ （あくまでもイメージ）
そうは言っても、キー1つに対してアイテム1つ。
アイテムにはオブジェクト型を入れる事も可能なので、複数の変数を用意したクラスを入れよう。
作り方
①クラスモジュールを作る
クラス名：Variable
②VariableクラスにPublic変数を3つ用意する
Public SampleA As Long
Public SampleB As Long
Public SampleC As Long
③コード（Sheetモジュール）
Sub Sample()
Dim dic As Dictionary
Set dic = New Dictionary
Dim i As Long
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
Dim dickey As String
dickey = Cells(i, 1).Value
If dic.Exists(dickey) = False Then
'クラスのインスタンスを生成
dic.Add dickey, New Variable
End If
'生成した変数に金額を足していく
dic(dickey).SampleA = dic(dickey).SampleA + Cells(i, 2).Value
dic(dickey).SampleB = dic(dickey).SampleB + Cells(i, 3).Value
dic(dickey).SampleC = dic(dickey).SampleC + Cells(i, 4).Value
Next i
'確認
Dim buf As Variant
For Each buf In dic.Keys
Debug.Print buf, dic(buf).SampleA, dic(buf).SampleB; dic(buf).SampleC
Next buf
とりあえず急場はしのげた。
普通はこうするとか、もっと良い方法ありますか？
↓
Dictionaryオブジェクトを配列にする方法を教わった！
koroko.hatenablog.com
↓
アイテムを配列にする方法
koroko.hatenablog.com
VBA 結合セルを扱う（正しい方法）
こんにちは。
派犬事務員のコロ子です。
前回の記事を書いたら、MergeAreaプロパティを使えばいいんじゃない？
と教えて頂いた。
MergeAreaプロパティ、こんな便利なものがあったなんて！！！
「練習のために○○は使わないでコードを書いてみましょう」的な事をしているのではなくて、素で知らなかった。
知らないと「VBA道場で修行的なコード」「体育会系に鍛錬的なコード」になっちゃう。修行も鍛錬も好きじゃないからー！！
MergeAreaプロパティは「結合されているセル範囲のRangeオブジェクトを返す」とのことなので、例えば
Range("B6").MergeArea(1).Value
と書くとB6セルを含む結合範囲の1番目のセルの値が取得できる。
ちなみに
Range("B4:B10").MergeArea.Value
とか
Range("B4:B10").MergeArea(1).Value
と書きたくなるけど
Range("B6").MergeArea(1).Value
Rangeの中身は単独セルを書かないといけない。
では、早速MergeAreaプロパティを使って書き直そう。
Sub Test1()
Dim ran As Range
For Each ran In Range("D4:H17")
Debug.Print "日付：" & _
Cells(2, ran.Column).MergeArea(1).Value & _
Cells(3, ran.Column).Value
Debug.Print "サンプル名：" & _
Cells(ran.Row, 2).MergeArea(1).Value
Debug.Print "ロット番号：" & _
Cells(ran.Row, 3).MergeArea(1).Value
Debug.Print "データ：" & ran.Value
Next ran
End Subえっつ！！これだけ？超簡単じゃん！
めちゃめちゃスッキリ！
