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えっつ！！これだけ？超簡単じゃん！
めちゃめちゃスッキリ！
VBA 結合セルを扱う
こんにちは。
派犬事務員のコロ子です。
あるシステムからデータをダウンロードすると、こんな感じのエクセルの表になって落ちてくる。
見やすいようにセルが結合されている。
わざわざそういう風に作ってくれているのはありがたいけど、これを加工するとなるとちょっと扱いにくい。
結合されたセルの扱いについて考える
A1～A4が結合されている場合
結合セルA1：A4を選択するには、セルの範囲のどこを指定しても結合セルが選択される。
Cells(1, 1).Select
Cells(2, 1).Select
Cells(3, 1).Select
Cells(4, 1).Select
上記4つは全部同じ動きをする。
値を取るのはどうだろう。
Cells(1, 1).Value ←このときだけ値が取れる
Cells(2, 1).Value ←空が取得
Cells(3, 1).Value ←空が取得
Cells(4, 1).Value ←空が取得
値は結合の一番上のセルでのみ取得可能。
確かにセルの結合を解除すると一番上のセルに値が入って他のセルは空欄になる。
なるほど、こういう仕様かぁ。
ちなみに、
①のように横に結合されている場合は一番左のセル（B2セル）
②のように縦横に結合されている場合は一番左上のセル（B4セル）
で値が取れる。
仕組みが分かったので、表1からサンプル名を取得してみる。
Sub SampleName()
Dim i As Long
For i = 4 To 17
If Cells(i, 2).Value <> "" Then
Debug.Print Cells(i, 2).Value
End If
Next i
End Sub
次に、表1より「日付」「サンプル名」「ロット」「データ」を取得してみる。
Selectionで結合セルを選択して、アドレスから値を取得する方法でやってみる。
Sub データ()
Dim ran As Range
For Each ran In Range("D4:H17")
Debug.Print "日付：" & セルの値_日付(ran, 2)
Debug.Print "サンプル名：" & セルの値(ran, 2)
Debug.Print "ロット番号：" & セルの値(ran, 3)
Debug.Print "データ：" & ran.Value
Next ran
End SubFunction セルの値(ran As Range, c As Long) As String
'引数cは列番号
Cells(ran.Row, c).Select
Dim myAdress As Variant
myAdress = Split(Selection.Address, "$")
Dim r As Long
'単独セルの場合
If InStr(Selection.Address, ":") = 0 Then
r = myAdress(2)
'結合されている場合「：」を除く
Else
r = Left(myAdress(2), Len(myAdress(2)) - 1)
End If
セルの値 = Cells(r, c).Value
End FunctionFunction セルの値_日付(ran As Range, r As Long) As String
'引数rは行番号
Cells(r, ran.Column).Select
Dim myAdress As Variant
myAdress = Split(Selection.Address, "$")
Dim c As String
c = myAdress(1)
セルの値_日付 = Cells(r, c).Value & Cells(r + 1, ran.Column).Value
End Function・セルのアドレスは$B$4:$B$10のような形になっているので、＄で区切って配列に入れる。
・縦に結合されている場合は、配列の2番目が行番号。「：」が入っていないなければ単独セル。「：」が入っている場合は：を除く。
・横に結合されている場合は配列の1番目が列番号。列番号は英文字で取得。
この場合Selectionでセルを選択しているのでマクロ実行中は絶対にエクセルを触らないように注意。
******************************************
この記事を公開したら正しい方法を教えてもらった。
こちらをチェック
↓
koroko.hatenablog.com
