VBA 罫線で囲まれた範囲を取得する（田んぼRange）

こんにちは。
派犬事務員のコロ子です。

よくありがちなこんな表。

罫線で囲まれた表の外に自由に書き込みがされている。
表の中に空白行があるけど、区切りの意味があるらしく、勝手に削除できない。
この状態に手は加えず、表だけを取得したい。

UsedRange　だと、枠の外も使用範囲が全部になってしまう。

Range("C3").CurrentRegion　だと、連続している範囲になってしまう。

罫線で囲まれた範囲だけ取得する関数とかあっても良さそうだけどなー。
ないっぽいので自作してみよう。

Sub Test()

    田んぼRange(Sheet1).Select

End Sub


Function 田んぼRange(ws As Worksheet) As Range
    
    Dim linRange As Range

    Dim ran As Range
    For Each ran In ws.UsedRange
    
        If ran.Borders.LineStyle = xlContinuous Then
        
            If linRange Is Nothing Then
                Set linRange = ran
            Else
                Set linRange = Union(linRange, ran)
            End If

        End If
    
    Next ran

    Set 田んぼRange = linRange

End Function

枠全てに罫線が引かれているセルをUnion関数で繋げて範囲を取得する。

できた！
罫線で囲まれた範囲を田んぼRangeと命名しよう。
（ネーミングがダサい？でも分かりやすいでしょ！ちゃんとした名前あるのかな？）
田んぼRange、もしかしたらUsedRangeやCurrentRegionよりもニーズがあるかも！？

おしゃべりがうるさーい！（悩み）

こんにちは。
派犬事務員のコロ子です。

現在「こーゆーの作って」を作る謎の一人部署で毎日VBAを書いている。
一人なので専用の部屋はなく、総務に間借りをしている。

そんな状況で困った事がある。
それは「うるさい」ことだ。
総務なのでいろいろな人が出入りする。その度におしゃべりが盛り上がって非常にうるさい。めちゃめちゃ考えている時に集中できなくて困る。
しかも話の内容丸聞こえなんですけど！
時々「うるさーい！」って叫びそうになるけど、さすがに間借りしている分際でそれは言えない。
昔から音楽を聴きながら、ラジオを聴きながら勉強とかできないタイプだった。
だって一緒に歌っちゃうから。
そんなわけで、聞き耳を立ててるわけでもないのに（できれば聞きたくない話が多い）仕事にならなくて困っている。

何度も上司に相談しようと思い、でもそんな相談されても困るよなぁ、と思いとどまる、を繰り返していた。
が、先日ついに我慢ができなくなり上司に相談に行った。さすがに小学生じゃないからおしゃべりを注意してもらう、とかはないけど、あわよくば在宅勤務とかもちょっと期待しながら。

上司：「ヘッドフォンしていいよ」
ええっーーー！！マジですかー！音楽聞いていいんですか！！
それ、海外ドラマとかで見た事あるー！
ヘッドフォンしながらオフィスで仕事。超憧れるけど、さすがに日本企業じゃおかしくない？しかもかなり古いタイプの会社だし。
コロ子：「ヘッドフォンですか？防音の意味でですよね？」
上司：「そう。それで対処してくれる？」
コロ子：「ちょっとさすがに、角が立たないですか？」
上司：「何か言われたら、私が許可した、って言っていいよ。」
そうは言われても・・・。

ってなわけで、職場でのヘッドフォンを許可された。
でも突然仕事中にヘッドフォンするのもおかしいよな・・・。
まずは毎日ヘッドフォンをして出社し、仕事中は首から下げてファッションアイテムとして定着させるべきか・・・？

ヘッドフォンは大げさだから、ワイヤレスのイヤフォンをするべきか？髪（耳）で隠せるし。
でも、そんなコソコソした感じにしたら、周りから「派犬のくせに仕事中隠れて音楽聞いてる」って言われるよな・・・。派犬は周りからの評価が仕事に響くんだよ・・・。

ちょっとウサ子に相談してみよう。
ウサ子：「いいな～。仕事中ヘッドフォンしていいんだ～」
コロ子：「全然良くないよ～。いくらうるさいからって、そんな嫌味なことしたら角が立つよ」
ウサ子：「そんなことないよー。とびっきりいいヘッドフォン買いなよー。派犬なんて仕事上のヒエラルキーは最下位なんだから、ヘッドフォンヒエラルキーで最上位になりなよー。」

意味不明。いまだ悩み中。

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を布教する機会がやってきた！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