派遣事務員の迷走

派遣事務員コロ子。会社の犬。顔出しNG。常に迷走している。

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

おお!できた!
しかし、こんなの絶対に分からないよー。どこが間違っているか分からなくて相当悩んだ。(もちろん自力解決できずにノンプロ研で教えてもらった)
理由は「参照してる時は配列のコピーが取得されていて、更新ができないのだと思われる」とのこと。この辺は理解できないので「アイテムの配列はまるごと入れ替える」と覚えよう。

やり方が分かったので、前回の表で集計してみよう。

f:id:SNegishi:20210328151846p:plain

この表で会社ごとに各商品の金額を合計する。

キー:社名
アイテム(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個作って列ごとに集計するイメージでいいのでしょうか?

f:id:SNegishi:20210330223631p:plain

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が複数欲しい!(クラス編)

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

f:id:SNegishi:20210316221229j:plain
新しい首輪 Amazfit Band 5

以前に書いたブログのコードを見て「なんだコレ?」っていう変なコードがいっぱいある。なんか変だけど、そのときは真剣に書いていたから、それはそれでそのままにしておこう。「それは違ーーーう!!」と指摘してもらえたら嬉しいし。なんだコレ?のついでに、最近思いついたコードを載せてみよう。

DictionaryのItemが複数欲しい

Dictionaryオブジェクトのアイテムが複数欲しい!と思う事ない?
例えばこんなとき。
社名をキーにして、各商品の金額をアイテムに入れて合計を集計したい。

f:id:SNegishi:20210328151846p:plain

キー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

f:id:SNegishi:20210328155532p:plain

③コード(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プロパティを使えばいいんじゃない?
と教えて頂いた。

koroko.hatenablog.com


MergeAreaプロパティ、こんな便利なものがあったなんて!!!

「練習のために○○は使わないでコードを書いてみましょう」的な事をしているのではなくて、素で知らなかった。
知らないと「VBA道場で修行的なコード」「体育会系に鍛錬的なコード」になっちゃう。修行も鍛錬も好きじゃないからー!!

f:id:SNegishi:20210209223635p:plain

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 結合セルを扱う

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

あるシステムからデータをダウンロードすると、こんな感じのエクセルの表になって落ちてくる。

f:id:SNegishi:20210209223635p:plain
表1

見やすいようにセルが結合されている。
わざわざそういう風に作ってくれているのはありがたいけど、これを加工するとなるとちょっと扱いにくい。

結合されたセルの扱いについて考える

A1~A4が結合されている場合
f:id:SNegishi:20210209215905p:plain

結合セル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 ←空が取得

値は結合の一番上のセルでのみ取得可能。
確かにセルの結合を解除すると一番上のセルに値が入って他のセルは空欄になる。
なるほど、こういう仕様かぁ。

ちなみに、
f:id:SNegishi:20210211181934p:plain
①のように横に結合されている場合は一番左のセル(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 Sub
Function セルの値(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 Function
Function セルの値_日付(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

【恐怖】VBA Workbooks.Openでファイルが消える!

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

何年か前に作ったマクロで事件が起きた。

Aさん:「コロ子!大変!!!ファイルが無くなっちゃった!!!」
コロ子:「え!?どうしたんですか?」
Aさん:「間違った入力をしてボタン押したら、ファイルが無くなっちゃったの!」

え!?そんな事ある??
確認してみると、フォルダ内にあるはずのファイルが消えている。
コードを見てもファイル削除の処理は入っていない。
ファイルが無くなったのはマクロのせいじゃないよ、と思いながらフォルダの中に適当なファイルを入れて同じように実行してみたら、ファイルが消えている!
ファイル削除の処理は入ってないのに!何で!?

どうやら
入力が間違っていたことが原因でファイル名が取れず、ファイル名が空の状態でファイルオープンするとエラーが起こり(これは当然)、そしてフォルダ名の配下のファイルが削除される、らしい。
更に、削除されるファイルとそうでないファイルがある。

Dim fileName As String

fileName = ファイル名を取得する処理 ←何らかの原因で””(空)が入る

Workbooks.Open フォルダ名 & "\" & fileName ←ファイル名が空で開く

原因究明のために自分のPCに同じような環境を作ってマクロを実行してもファイルが消えない。
どうやっても再現できない。

分かった事

①ファイル名が空("")でWorkbooks.Openを実行するとパス配下のファイルが削除される
②ネットワーク上で起こる(ローカル環境では起こらない)
③ファイル名の先頭文字がアルファベットのものは削除されない

確かに雑なマクロを作ったコロ子が悪い。
でもファイルが削除されるなんて酷過ぎる!Workbooks.Openでファイルが削除されるなんてどこにも書いてないよ!
たまたま重要なファイルがアルファベットから始まっていたので今回は助かった。でもそうじゃなかったら本当にヤバイ!!
これって、本当に死んじゃうやつだよ!

Microsoftさん、こんなのありですか!!!!

使うの自分だけだからとか、限られたグループで使うだけだからエラーが出てもまあいいや、みたいな作り方してると超危険。
必ず、ファイル名がある事を確認してからファイルを開くべし!

Dim fileName As String

fileName = ファイル名を取得する処理

If fileName <> "" Then

    Workbooks.Open フォルダ名 & "\" & fileName
    
End If

1年を振り返る(ノンプロ犬、エセプロ犬になる)

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

f:id:SNegishi:20201202152311p:plain

今年もノンプロ研アドベントカレンダーに参加しました!
この記事は「ノンプロ研 Advent Calendar 2020」の3日目です。

adventar.org


今年は「派犬事務員」として大きな転機があった。
18年間所属した部署から新しい部署に異動になった。

異動の経緯

実は「辞める」と言った事が発端だった。

仕事や会社に不満というより(いやいやめっちゃ不満あったけど)、18年勤めて派犬という立場ではもうこれ以上どうにもならないと思った。
このままずーっと派犬のままなのか。何も変わらず、このまま一生この会社に飼われるのか。
そんなの嫌だ。派犬、派犬、派犬って毎日犬扱い(犬だけど)にはもううんざりだ。
もう嫌だ!!もう辞める!
次は決まってないけど、別れなければ出会いはない!!
勢いよく飛び出した。
つもりだった。


所長(以前上司だった):「コロ子、辞めるんだって?」
コロ子:「はい。長い事お世話になりました。」
所長:「次はどうするの?」
コロ子:「・・・まだ決まってません・・・」
所長:「まだ次決まってないなら、コロ子に頼みたい仕事があるんだけど。」
と言って分厚いファイルを見せてくれた。
所長:「こういうのアナログに手作業でやっていて大変なんだ。なんとか上手いことシステム化できないか考えてくれないか?外注するのも大変なんだ。他にもこういうの沢山あるんだ。」

えっ。本当に?
そういうの考えるの好き。面白そう。
でも、できるかな?できるかも?やってみたい!

あっけなく元さやに戻る。

一人部署

ということで部署異動で、総務の一角に机を置くことになった。
総務だからいろんな人が来る。
「あれ?コロ子何でここにいるの?えっ?異動?マジで?何で何で?前の部署イヤになっちゃったの?」
前の部署イヤになったのバレてる・・・。
一度辞める宣言しちゃった後だから気まずい。
総務にいるのに総務の仕事できない役立たず感が半端ない。めっちゃ浮いてる状態で新しい仕事は始まった。

なぜかモチベーションが下がる

コードを書いている時は楽しい。以前のようなルーチンワークを持っていないので集中して作れる。憧れのVBA三昧。
しかし、以前も孤独に悩んでいたけど、結局は一人部署なので誰も相談できる人がいない。
ノンプロ研で相談できるけど、具体的な仕事の内容はなかなか相談できない。
行き詰るとそこから一歩も進まなくなる。
また、「ここはどうしますか?」とメールをしても、なかなか返事が返ってこない。微妙な待ち時間が多い。
空いた時間に勉強しようと本を見るも、授業中に隠れて漫画を読んでるみたいな気分になって落ち着かない。

エセプロになった事が原因か?

理想の環境のはずなのに毎日モヤモヤ。精神的な疲労が続く。
今までは事務仕事でお給料を貰っていた。
異動になってからは「こーゆーの作って」を作る事が仕事になった。
ということは、
お金を貰う=プロ
もしかして、プロになってしまったのか?
えっ、どうしよう。
プロとしてやっていけるほどの技量がないのは分かりきっている。
お給料が少ないのに不満がありつつも、プロとしてお金を貰うのはおこがましい。

なんとかしてプロとしてやっていけるほどの技量をつけなければ、と焦れば焦るほど、いろいろ手を出して何もかも中途半端。
いったいこの先どこに向かっているのだろう。目標が見当たらない。自分がどうなりたいのか分からない。
環境変われどやっぱり迷走。