【VBA】１からマクロ組みます（事務系の自動化１）

目次

はじめに

事務系あるある事務系あるある～言いたいよ～🎵

こんちゃ！

今回は、１からマクロを組んでいきたいと思います😊

マクロに大した機能は付けないけど、記事は結構なボリュームになると思います😓

まず、どんなことを自動化するかだよねぇ？

自分がちょっと考えてみて、よくあるのが、、、

Excel関数の入ったテンプレートに、データを貼り付けて、特定の名前に変更して保存。そして元データの後処理。

こんなこと、ありますよね？

じゃあ、次であるある手作業の説明します。

 

あるある手動の手順

こんなデータを使って、仕事をすることは無いでしょうけど⇒

例えばデータ（前月今月データ）＆例えばテンプレート（テンプレ）を使って説明します。

 

 

如何でしょうか？似たようなことありますよね？

 

今回は、こんな感じの定例業務のマクロを組みます。

 

例えばデータと例えばテンプレートの準備

マクロを作成する前に、データとテンプレを準備しましょう。

データ類なしでマクロ組めって言ったって、「味噌なしで味噌汁作れ」と言っているようなものですからね😣

『で、どうやって準備するんだ？手動？』って思いました？

そんなこと、この僕がやらせるわけないじゃない。

まっかせなさーい💗

VBE開いて、適当にモジュール挿入して↓コピペ

1С (Запрос)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
Option Explicit
Sub junbi()
Dim GakePath As String
Dim WshNetworkObject As Object
    Set WshNetworkObject = CreateObject("WScript.Network")
    GakePath = "C:\Users\" & WshNetworkObject.UserName & "\Desktop\崖っぷち派遣社員フォルダ"
    Set WshNetworkObject = Nothing
    If Dir(GakePath, vbDirectory) = "" Then
        Application.ScreenUpdating = False
        MkDir GakePath
        MkDir GakePath & "\old"
    Else
        MsgBox "フォルダが準備されています", vbOKOnly, "確認"
        Exit Sub
    End If
    Call tenpure1(GakePath)
    Call tenpure2(GakePath)
    Application.ScreenUpdating = True
    MsgBox "デスクトップを確認してください", vbOKOnly, "準備完了"
End Sub
Private Sub tenpure1(ByVal Gake As String)
Dim NewBook As Workbook
Dim NewSheet As Worksheet
    Set NewBook = Workbooks.Add
    Set NewSheet = NewBook.Worksheets(1)
    NewSheet.Name = "月集計"
    NewSheet.Cells(1, 1) = "社名"
    NewSheet.Cells(1, 2) = "売り上げ"
    NewSheet.Cells(1, 3) = "日付"
    NewSheet.Cells(1, 4) = "年/月"
    NewSheet.Cells(1, 5) = "今月の売り上げ"
    NewSheet.Cells(3, 8) = "=TEXT(TODAY()," & """" & "yyyy年m月" & """" & ")"
    NewSheet.Cells(3, 9) = "の集計"
    NewSheet.Cells(5, 8) = "A社"
    NewSheet.Cells(6, 8) = "B社"
    NewSheet.Cells(7, 8) = "C社"
    NewSheet.Cells(8, 8) = "集計"
    NewSheet.Cells(2, 4) = _
        "=IF(C2<>" & """""" & ",TEXT(C2," & """" & "yyyy年m月" & """" & ")," & """""" & ")"
    NewSheet.Cells(2, 5) = "=IF(D2=$H$3,B2," & """""" & ")"
    NewSheet.Range("D2:E50") = NewSheet.Range("D2:E2").Formula
    NewSheet.Cells(5, 9) = "=SUMIF(A:E,H5,E:E)"
    NewSheet.Cells(6, 9) = "=SUMIF(A:E,H6,E:E)"
    NewSheet.Cells(7, 9) = "=SUMIF(A:E,H7,E:E)"
    NewSheet.Cells(8, 9) = "=SUM(I5:I7)"
    With NewSheet.Range("A1:E50").Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With NewSheet.Range("H5:I8").Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    NewSheet.Columns("E").AutoFit
    NewBook.SaveAs Filename:=Gake & "\テンプレ集計.xlsx"
    NewBook.Close
End Sub
Private Sub tenpure2(ByVal Gake As String)
Dim NewBook As Workbook
Dim NewSheet As Worksheet
Dim i As Long
Dim j As Long
Dim RndVal As Long
    Set NewBook = Workbooks.Add
    Set NewSheet = NewBook.Worksheets(1)
    NewSheet.Name = "集計表"
    NewSheet.Cells(1, 1) = "社名"
    NewSheet.Cells(1, 2) = "売り上げ"
    NewSheet.Cells(1, 3) = "日付"
    For i = 2 To 30
        For j = 1 To 3
            RndVal = Int(30 * Rnd + 1)
            If j = 1 Then
                Select Case RndVal
                    Case 1 To 10
                        NewSheet.Cells(i, j) = "A社"
                    Case 11 To 20
                        NewSheet.Cells(i, j) = "B社"
                    Case Else
                        NewSheet.Cells(i, j) = "C社"
                End Select
            ElseIf j = 2 Then
                NewSheet.Cells(i, j) = RndVal * 100
            Else
                If RndVal > 16 Then
                    NewSheet.Cells(i, j) = _
                        "2020/" & Format(DateAdd("m", -1, Date), "m") & "/" & NewSheet.Cells(i, j - 1) / 100
                Else
                    NewSheet.Cells(i, j) = _
                        "2020/" & Format(Date, "m") & "/" & NewSheet.Cells(i, j - 1) / 100
                End If
            End If
        Next
    Next
    With NewSheet.Range("A1:C50").Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    NewSheet.Columns("C").AutoFit
    NewBook.SaveAs Filename:=Gake & "\売上表" & Format(Date, "yyyymmdd") & ".xlsx"
    NewBook.Close
End Sub

プロシージャ名『junbi』を実行してください。

おそらく、デスクトップに『崖っぷち派遣社員フォルダ』が作成されたと思います。

これを材料にして、マクロを作成していきますね🎶

 

情報の整理

先ず、マクロを組む前に情報の整理しましょう！

何処に何があるとか、何のどれをどうするとかが大事！

まあ、仕様書だね✨

今回、『例えばデータ』は、どこかの誰かが毎日『崖っぷち社員フォルダ』に格納してくるって設定にします。

だから、フォルダやファイル類の場所は固定として作成します。

（固定じゃない場合はダイアログとか使うと便利かな）

ってことで、仕様は以下の感じで。

１、崖っぷち派遣社員フォルダ内の例えばファイル２つを開く

（フォルダの場所はデスクトップ）

２、例えばデータのA~C列をテンプレートのA～C列に転記

３、テンプレートのシート名を本日の日付に変更

４、テンプレートを名前を本日の日付に変更して保存

（保存先は崖っぷち派遣社員フォルダ内）

５、２ファイルを閉じる

６、例えばデータをoldへ移動

そして、これらに使う格納先やファイル名などは、Excelシートを参照することにします。

（フォルダ名等に変更があったときExcelシートを変更するだけでメンテ可能）

 

マクロファイル作成

まず、Excelを開き適当な名前を付けて、拡張子を『.xlsm』で保存しましょう。（マクロの保存先は、お任せします。）

 

次に、格納先やファイル名をExcelシートにまとめます。シート名は後で説明しやすいように『macro』にしておいて下さい。

B4～C9にフォルダやファイル名をまとめます。

日付絡みのところは、Excel関数を使いました。

↓　　　　　↓　　　　　↓

1С (Запрос)
1
2
3
4
5
6
C:\Users\○○\Desktop\崖っぷち派遣社員フォルダ
="売上表"&TEXT(TODAY(),"yyyymmdd") & ".xlsx"
テンプレ集計.xlsx
=TEXT(TODAY(),"yyyymmdd")  & "集計"
=C7 & ".xlsx"
C:\Users\○○\Desktop\崖っぷち派遣社員フォルダ\old

上から順に4行目→9行で目す。（○○のところはユーザー名）

フォルダ名は、直接フォルダ広げてコピーした方が早いし間違いが無いです。

そしたら、VBEを開いてモジュールを挿入し、適当な名前のプロシージャを作成してください。

次からマクロを組んでいきます( ^^) _旦~~

 

マクロを組む

はい、あとは仕様通りに組んでいくだけです😛

組んでいく上で、分からないことがあれば、その都度調べる！

これでマクロは組めるはず👍

では、順番にいきますね。

仕様１

まず、２つのブックを開くコードを記述します。

で、組んだことがない人はここで固まりますよね？

ブックを開くときはフルパスで指定します。

フォルダ名とファイル名はExcelシートにあるので、ここではパス名の代わりにセルを指定します。

 

それで、組もうと思って調べても、、

MS DOS
1
Workbooks.Open Filename:=ThisWorkbook.Worksheets("macro").Cells(4, 3) & "\" & ThisWorkbook.Worksheets("macro").Cells(5, 3)

↑こんな感じじゃない？挫折するよね😴

僕もね、こんなの書いたの何年ぶり。吐きそう。

変数使いましょう！

今回は、ブックとシートが３つずつ、合計６つの変数を用意します。

マクロブック（ファイル名参照のため）、元ブック、テンプレブックね。

それと、１ブックにつき１シート。

先ず、マクロブックから変数に代入し、そのマクロブックを参照に残りのブックを開き、そして変数に代入します。

・開いているブックは ⇒ Set 変数 ＝ Workbooks(“ファイル名")

・ブックを開く場合は ⇒ Set 変数 ＝ Workbooks.Open(“フルパス")

・シートは ⇒ Set 変数 = ブック名（変数）.Worksheets(“シート名")

といった感じで、１行で変数に代入できます。

（何のこっちゃって人は、もう少し下にソースコードあるので我慢してください。）

参照先の確認には、Debug.Printを使ってイミディエイトウィンドウで計算結果を出すと良いです。

 

仕様1のコード↓

1С (Запрос)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Sub tenki()
Dim MacBook, MotoBook, TmpBook As Workbook '左からマクロ、元データ、テンプレ
Dim MacSheet, MotoSheet, TmpSheet As Worksheet 'ブック同様
    'マクロブックとシートを変数に格納
    Set MacBook = ThisWorkbook 'ThisWorkbookでブックを指定して変数に格納しました
    Set MacSheet = MacBook.Worksheets("macro") 'MacBookのシート名を指定
    
    '元データのブックを開いて変数に格納を1行で済ませる
    Set MotoBook = Workbooks.Open(MacSheet.Cells(4, 3) & "\" & MacSheet.Cells(5, 3)) 'フルパスで指定
    Set MotoSheet = MotoBook.Worksheets(1) '左から１つめのシート指定
    
    'テンプレの処理
    Set TmpBook = Workbooks.Open(MacSheet.Cells(4, 3) & "\" & MacSheet.Cells(6, 3)) 'フルパスで指定
    Set TmpSheet = TmpBook.Worksheets("月集計") 'シート名を指定
 
End Sub

仕様１が、一番大変かもしれません。

流れを分かりやすく説明すると、、

セルに入ったパス名を参照してブックを開き、そして変数と紐づける。

次に、そのブックの変数を使って、今度はシートを変数と紐づける。

といった感じでしょうか。

 

仕様２

元データのA~C列をテンプレのA~C列に転記するわけですが、、

おそらく、これを調べると、、、

○○.select

○○.Copy

○○.select

○○.Paste

なんて出て来るんじゃないかな？怠くないですか？

これも1行で終わらせましょう。シートが変数に代入されているので、楽に記述できます。

MS DOS
1
MotoSheet.Columns("A:C").Copy TmpSheet.Cells(1, 1)

↑元データのA～C列をコピーして、テンプレのA1セルにペースト。

もしくは

MS DOS
1
TmpSheet.Columns("A:C").Value = MotoSheet.Columns("A:C").Value

↑テンプレのA～C列は、元データのA～Cという意味。

列のコピペなら、上のが良さそうだね。

自分は下のコピペを頻繁に使います。セル×セルの時、よく使います。

仕様２は簡単でしたね。次に行きましょう👍

 

仕様３

仕様３は、テンプレシートの名前変更ですが、シート名自体はExcel関数で作成済み。

なので、シート名も単純にセルを指定してやればいいです。

Visual Basic
1
TmpSheet.Name = MacSheet.Cells(7, 3)

↑テンプレシートの名前はマクロシートのC3ってこと。

仕様の半分まできたので、コードの確認をしてみましょう。

1С (Запрос)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Option Explicit
Sub tenki()
'仕様１
Dim MacBook, MotoBook, TmpBook As Workbook '左からマクロ、元データ、テンプレ
Dim MacSheet, MotoSheet, TmpSheet As Worksheet 'ブック同様
    'マクロブックとシートを変数に格納
    Set MacBook = ThisWorkbook 'ThisWorkbookでブックを指定して変数に格納しました
    Set MacSheet = MacBook.Worksheets("macro") 'MacBookのシート名を指定
    
    '元データのブックを開いて変数に格納を1行で済ませる
    Set MotoBook = Workbooks.Open(MacSheet.Cells(4, 3) & "\" & MacSheet.Cells(5, 3)) 'フルパスで指定
    Set MotoSheet = MotoBook.Worksheets(1) '左から１つめのシート指定
    
    'テンプレの処理
    Set TmpBook = Workbooks.Open(MacSheet.Cells(4, 3) & "\" & MacSheet.Cells(6, 3)) 'フルパスで指定
    Set TmpSheet = TmpBook.Worksheets("月集計") 'シート名を指定
    
'仕様２
    '元データをテンプレに転記
'    TmpSheet.Columns("A:C").Value = MotoSheet.Columns("A:C").Value
    MotoSheet.Columns("A:C").Copy TmpSheet.Cells(1, 1)
    
'仕様３
    'テンプレシートの名前変更
    TmpSheet.Name = MacSheet.Cells(7, 3)
    
End Sub

まだ仕様の半分ですが、後半のボリュームは少な目です。あと少し頑張りましょう。

 

仕様４

仕様４は、テンプレートの名前を変更して保存。

こちらも仕様３同様に、Excel関数でファイル名を用意してあるので、セルを指定して名前の変更をします。

MS DOS
1
TmpBook.SaveAs Filename:=MacSheet.Cells(4, 3) & "\" & MacSheet.Cells(8, 3)

↑ブックを開くときと同じ要領です。『ブックの変数.Saveas Filename:="フルパス“』でセーブします

今回は、崖っぷちフォルダに日付入りのファイル名で保存します。

 

仕様５

仕様５は、マクロファイル以外を閉じます。

MS DOS
1
2
MotoBook.Close
TmpBook.Close

↑『ブックの変数.Close』で簡単にブックを閉じることができます。

 

仕様６

最後に元データをoldフォルダに移動します。

MS DOS
1
Name MacSheet.Cells(4, 3) & "\" & MacSheet.Cells(5, 3) As MacSheet.Cells(9, 3) & "\" & MacSheet.Cells(5, 3)

↑『Name “フルパス" As “フルパス"』でファイルの移動ができます。（左のパスが現在で右のパスが移動後）

今回、ファイル名はcells(5,3)のままで、格納先をcells(4,3)からcells(9,3)に書き換えます。

 

ソースコード

例としてソースコード載せます。

1С (Запрос)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
Option Explicit
Sub tenki()
'仕様１
Dim MacBook, MotoBook, TmpBook As Workbook '左からマクロ、元データ、テンプレ
Dim MacSheet, MotoSheet, TmpSheet As Worksheet 'ブック同様
    'マクロブックとシートを変数に格納
    Set MacBook = ThisWorkbook 'ThisWorkbookでブックを指定して変数に格納しました
    Set MacSheet = MacBook.Worksheets("macro") 'MacBookのシート名を指定
    
    '元データのブックを開いて変数に格納を1行で済ませる
    Set MotoBook = Workbooks.Open(MacSheet.Cells(4, 3) & "\" & MacSheet.Cells(5, 3)) 'フルパスで指定
    Set MotoSheet = MotoBook.Worksheets(1) '左から１つめのシート指定
    
    'テンプレの処理
    Set TmpBook = Workbooks.Open(MacSheet.Cells(4, 3) & "\" & MacSheet.Cells(6, 3)) 'フルパスで指定
    Set TmpSheet = TmpBook.Worksheets("月集計") 'シート名を指定
    
'仕様２
    '元データをテンプレに転記
'    TmpSheet.Columns("A:C").Value = MotoSheet.Columns("A:C").Value
    MotoSheet.Columns("A:C").Copy TmpSheet.Cells(1, 1)
    
'仕様３
    'テンプレシートの名前変更
    TmpSheet.Name = MacSheet.Cells(7, 3)
    
'仕様４
    'ブックの名前を変更して保存
    TmpBook.SaveAs Filename:=MacSheet.Cells(4, 3) & "\" & MacSheet.Cells(8, 3) 'フルパスで指定します
 
'仕様５
    'ブックを閉じます
    MotoBook.Close
    TmpBook.Close
    
'仕様６
    'ファイルの移動
    Name MacSheet.Cells(4, 3) & "\" & MacSheet.Cells(5, 3) As MacSheet.Cells(9, 3) & "\" & MacSheet.Cells(5, 3)
    
    MsgBox "完了しました"
    
End Sub

これが正解ってわけじゃ無いです。他にもやり方はあります。

 

ボタン作成

最後に、以下の手順でExcelシートにボタンを作成します。

 

 

こうすることによって、ボタンを押すだけでマクロが実行されます。

ボタン作成後に、ボタンを押してみましょう😝

仕様通りに動きましたか？

 

まとめ

変数の記事～今回まで？も？ザックリ説明してきましたけど、如何でしたか？簡単？難しい？

『何だよ。マクロ組むところまでザックリじゃないか。』

って思いました？でもね、

自動化するには、何が必要で、それは誰が何時何処にとか、そういった情報の整理から、、

じゃあ、どういった手順で組んだら最短で最速なのか、バグを無くすにはどうしたらいいとか、、、

そういった『アルゴリズム』が一番大事だと思います。

今回の記事で、少しでもアルゴリズム（手順みたいなもの）が、わかってもらえたら十分かなと思います。

関数だのAPIだの細かいことは、その都度調べればいいだけ。

やってみてダメだったら、また考えればいい。

フルオートにでできないのなら、セミオートにしたらいい。

とりあえず、実現に向けて１歩でも先に。と踏み出すことが大事なわけで。

自分が１番最初に作ったマクロは、１ヶ月も掛かった割にバグが多かったです。

でも、それから２つ３つ先のことを考えれるようになって、バグも少ないし仕様の変更にも強いマクロを組めるようになりました。

毎日コツコツ少しずつ成長していけば、1年もしないうちにサクサク組めるようになると思いますよ👍

 

 

んなわけで、今回はここまで。

長い記事になってしまいました。高覧おつぴょん😆

んじゃまた✋

 

VBA,プログラミングVBA,仕事

Posted by jim