Go to Qiita Advent Calendar 2024 Top
LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@amonyumas

初心者 練習 Excel VBA

Posted at

練習のため作成したExcel VBAのコード

練習内容を備忘録として残します

使用技術

Excel VBA
Access

作成内容

・入力した値を別シートに転記、Accessに自動的に追加

・CSV出力し、別DBにバックアップを作成

完成後イメージ

入力画面

一覧画面

入力画面コード

カレンダーから入力時ボタンのコード

直感的に日付を入力できるようにカレンダーを作成

まずは [開発タブ]→[Visual Basic] また Alt + F11で開く
カレンダーを追加したいシートを左クリックし、 [標準モジュール] を選択
下記のコードを入力

Private Sub CreateCalendarForm()
    Dim myForm As Object
    Set myForm = ThisWorkbook.VBProject.VBComponents.Add(ComponentType:=3)  '' vbext_ct_MSForm
    With myForm
        .Name = "CalendarForm"
        .Properties("Height") = 310
        .Properties("Width") = 310
        .Properties("Caption") = "日付を選択してセルに入力"
    End With
    Dim myFormDesign As Object
    Set myFormDesign = myForm.Designer
    With myFormDesign.Controls.Add("Forms.TextBox.1")
        .Name = "TXT日付"
        .Width = 144
        .Height = 24
        .Top = 6
        .Left = 78
        .BackColor = 16777215
        .BackStyle = 1
        .ForeColor = 0
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 0
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .MaxLength = 10
        .IMEMode = 3
    End With
    With myFormDesign.Controls.Add("Forms.CommandButton.1")
        .Name = "CMD先月"
        .Width = 30
        .Height = 24
        .Top = 6
        .Left = 42
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 1
        .Caption = "<"
    End With
    With myFormDesign.Controls.Add("Forms.CommandButton.1")
        .Name = "CMD翌月"
        .Width = 30
        .Height = 24
        .Top = 6
        .Left = 228
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 2
        .Caption = ">"
    End With
    With myFormDesign.Controls.Add("Forms.CommandButton.1")
        .Name = "CMD今日"
        .Width = 48
        .Height = 24
        .Top = 252
        .Left = 126
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 10.2
        .Font.Name = "MS UI Gothic"
        .TabIndex = 3
        .Caption = "今日"
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label43"
        .Width = 36
        .Height = 20
        .Top = 40
        .Left = 24
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = 255
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 4
        .BorderColor = -2147483633
        .BorderStyle = 1
        .SpecialEffect = 0
        .TextAlign = 2
        .Caption = "日"
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label44"
        .Width = 36
        .Height = 20
        .Top = 40
        .Left = 60
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 5
        .BorderColor = -2147483633
        .BorderStyle = 1
        .SpecialEffect = 0
        .TextAlign = 2
        .Caption = "月"
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label45"
        .Width = 36
        .Height = 20
        .Top = 40
        .Left = 96
        .BackColor = -2147483633
        .BackStyle = 0
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 6
        .BorderColor = -2147483633
        .BorderStyle = 1
        .SpecialEffect = 0
        .TextAlign = 2
        .Caption = "火"
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label46"
        .Width = 36
        .Height = 20
        .Top = 40
        .Left = 132
        .BackColor = -2147483633
        .BackStyle = 0
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 7
        .BorderColor = -2147483633
        .BorderStyle = 1
        .SpecialEffect = 0
        .TextAlign = 2
        .Caption = "水"
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label47"
        .Width = 36
        .Height = 20
        .Top = 40
        .Left = 168
        .BackColor = -2147483633
        .BackStyle = 0
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 8
        .BorderColor = -2147483633
        .BorderStyle = 1
        .SpecialEffect = 0
        .TextAlign = 2
        .Caption = "木"
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label48"
        .Width = 36
        .Height = 20
        .Top = 40
        .Left = 204
        .BackColor = -2147483633
        .BackStyle = 0
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 9
        .BorderColor = -2147483633
        .BorderStyle = 1
        .SpecialEffect = 0
        .TextAlign = 2
        .Caption = "金"
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label49"
        .Width = 36
        .Height = 20
        .Top = 40
        .Left = 240
        .BackColor = -2147483633
        .BackStyle = 0
        .ForeColor = 16711680
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 10
        .BorderColor = -2147483633
        .BorderStyle = 1
        .SpecialEffect = 0
        .TextAlign = 2
        .Caption = "土"
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label1"
        .Width = 36
        .Height = 30
        .Top = 66
        .Left = 24
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = 255
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 13
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label2"
        .Width = 36
        .Height = 30
        .Top = 66
        .Left = 60
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 14
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label3"
        .Width = 36
        .Height = 30
        .Top = 66
        .Left = 96
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 15
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label4"
        .Width = 36
        .Height = 30
        .Top = 66
        .Left = 132
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 16
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label5"
        .Width = 36
        .Height = 30
        .Top = 66
        .Left = 168
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 17
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label6"
        .Width = 36
        .Height = 30
        .Top = 66
        .Left = 204
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 18
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label7"
        .Width = 36
        .Height = 30
        .Top = 66
        .Left = 240
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = 16711680
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 19
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label8"
        .Width = 36
        .Height = 30
        .Top = 96
        .Left = 24
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = 255
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 20
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label9"
        .Width = 36
        .Height = 30
        .Top = 96
        .Left = 60
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 21
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label10"
        .Width = 36
        .Height = 30
        .Top = 96
        .Left = 96
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 22
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label11"
        .Width = 36
        .Height = 30
        .Top = 96
        .Left = 132
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 23
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label12"
        .Width = 36
        .Height = 30
        .Top = 96
        .Left = 168
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 24
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label13"
        .Width = 36
        .Height = 30
        .Top = 96
        .Left = 204
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 25
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label14"
        .Width = 36
        .Height = 30
        .Top = 96
        .Left = 240
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = 16711680
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 26
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label15"
        .Width = 36
        .Height = 30
        .Top = 126
        .Left = 24
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = 255
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 27
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label16"
        .Width = 36
        .Height = 30
        .Top = 126
        .Left = 60
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 28
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label17"
        .Width = 36
        .Height = 30
        .Top = 126
        .Left = 96
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 29
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label18"
        .Width = 36
        .Height = 30
        .Top = 126
        .Left = 132
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 30
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label19"
        .Width = 36
        .Height = 30
        .Top = 126
        .Left = 168
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 31
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label20"
        .Width = 36
        .Height = 30
        .Top = 126
        .Left = 204
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 32
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label21"
        .Width = 36
        .Height = 30
        .Top = 126
        .Left = 240
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = 16711680
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 33
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label22"
        .Width = 36
        .Height = 30
        .Top = 156
        .Left = 24
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = 255
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 34
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label23"
        .Width = 36
        .Height = 30
        .Top = 156
        .Left = 60
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 35
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label24"
        .Width = 36
        .Height = 30
        .Top = 156
        .Left = 96
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 36
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label25"
        .Width = 36
        .Height = 30
        .Top = 156
        .Left = 132
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 37
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label26"
        .Width = 36
        .Height = 30
        .Top = 156
        .Left = 168
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 38
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label27"
        .Width = 36
        .Height = 30
        .Top = 156
        .Left = 204
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 39
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label28"
        .Width = 36
        .Height = 30
        .Top = 156
        .Left = 240
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = 16711680
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 40
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label29"
        .Width = 36
        .Height = 30
        .Top = 186
        .Left = 24
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = 255
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 41
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label30"
        .Width = 36
        .Height = 30
        .Top = 186
        .Left = 60
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 42
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label31"
        .Width = 36
        .Height = 30
        .Top = 186
        .Left = 96
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 43
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label32"
        .Width = 36
        .Height = 30
        .Top = 186
        .Left = 132
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 44
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label33"
        .Width = 36
        .Height = 30
        .Top = 186
        .Left = 168
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 45
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label34"
        .Width = 36
        .Height = 30
        .Top = 186
        .Left = 204
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 46
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label35"
        .Width = 36
        .Height = 30
        .Top = 186
        .Left = 240
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = 16711680
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 47
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label36"
        .Width = 36
        .Height = 30
        .Top = 216
        .Left = 24
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = 255
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 48
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label37"
        .Width = 36
        .Height = 30
        .Top = 216
        .Left = 60
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 49
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label38"
        .Width = 36
        .Height = 30
        .Top = 216
        .Left = 96
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 50
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label39"
        .Width = 36
        .Height = 30
        .Top = 216
        .Left = 132
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 51
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label40"
        .Width = 36
        .Height = 30
        .Top = 216
        .Left = 168
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 52
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label41"
        .Width = 36
        .Height = 30
        .Top = 216
        .Left = 204
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 53
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label42"
        .Width = 36
        .Height = 30
        .Top = 216
        .Left = 240
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = 16711680
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 54
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
End Sub

入力後、実行をすることで下記のフォームができているか確認

スクリーンショット 2024-03-29 151224.png

左のプロジェクトエクスプローラーの [フォーム]→[CalenderForm]
右クリックで コードを表示 を選択
コード入力画面表示がされたら下記のコードを記入

' カレンダーコントロールの配列と現在の日付を定義
Private CalendarParts(1 To 42) As CalendarControl
Private CurrentDate As Date

' 色の定数を定義
Private Const GRAY As Long = -2147483633
Private Const LIGHTBLUE As Long = 16763070

' ユーザーフォームが初期化されたときに実行
Private Sub UserForm_Initialize()
    Dim i As Long
    ' 各カレンダーコントロールを初期化
    For i = LBound(CalendarParts) To UBound(CalendarParts)
        Set CalendarParts(i) = New CalendarControl
        Call CalendarParts(i).Bind(Me.Controls("Label" & i))
    Next i
    ' 現在の日付を設定
    CurrentDate = Date
    ' 日付を作成
    Call CreateDays
End Sub

' ユーザーフォームが終了したときに実行
Private Sub UserForm_Terminate()
    Erase CalendarParts
End Sub

' 日付テキストボックスが変更されたときに実行
Private Sub TXT日付_Change()
    If IsDate(Me.TXT日付.Value) Then
        CurrentDate = Me.TXT日付.Value
        Call CreateDays
    End If
End Sub

' 日付テキストボックスからフォーカスが外れたときに実行
Private Sub TXT日付_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Not IsDate(Me.TXT日付.Value) Then
        Me.TXT日付.Value = CurrentDate
    End If
End Sub

' 先月ボタンがクリックされたときに実行
Private Sub CMD先月_Click()
    CurrentDate = DateAdd("m", -1, CurrentDate)
    Me.TXT日付.Value = Format(CurrentDate, "yyyy/mm")
    Call CreateDays
End Sub

' 翌月ボタンがクリックされたときに実行
Private Sub CMD翌月_Click()
    CurrentDate = DateAdd("m", 1, CurrentDate)
    Me.TXT日付.Value = Format(CurrentDate, "yyyy/mm")
    Call CreateDays
End Sub

' 今日ボタンがクリックされたときに実行
Private Sub CMD今日_Click()
    ActiveCell.Value = Date
    Call CalendarForm.Hide
End Sub

' 日付を作成
Private Sub CreateDays()
    Me.TXT日付.Value = Format(CurrentDate, "yyyy/mm")
    Dim TargetDate As Date
    TargetDate = Format(CurrentDate, "yyyy/mm") & "/1"
    Dim WeekDayCode As Long
    WeekDayCode = 1
    Dim Ctrl As Control
    Dim i As Long
    ' 各ラベルに日付を設定
    For i = 1 To 42
        Set Ctrl = Me.Controls("Label" & i)
        Ctrl.Caption = ""
        Ctrl.BackColor = GRAY
        If Month(TargetDate) = Month(CurrentDate) And WeekDayCode >= Weekday(TargetDate) Then
            Ctrl.Caption = Day(TargetDate)
            If TargetDate = Date Then
                Ctrl.BackColor = LIGHTBLUE
            End If
            TargetDate = DateAdd("d", 1, TargetDate)
        End If
        WeekDayCode = WeekDayCode + 1
    Next i
End Sub

' アクティブセルに日付をコピー
Public Sub CopyToActiveCell(ByVal xDate As String)
    If xDate = "" Then Exit Sub
    ActiveCell.Value = Format(CurrentDate, "yyyy/mm/") & xDate
    Call CalendarForm.Hide
End Sub

[標準モジュール] を右クリックし、
[挿入]→[クラスモジュール] を選択
下記のコードを記入

Option Explicit

Public Sub カレンダーから選択_Click()
    Call CalendarForm.Show
End Sub

コードをコピペしたら、プロパティウィンドウのオブジェクト名（Class1と書かれているところ）をダブルクリック
CalendarControl に変更

カレンダーを使用したい場合はボタンを設置し、
マクロを カレンダーから入力_Click を選択する

転記ボタンのコード

転記先のシート名を 「一覧」 と設定しているが、名前は任意

Sub ボタン1_Click()
    Dim idcount As Long
    Dim lastrow As Long
    Dim ws As Worksheet
    Dim check(2) As Boolean
    Dim i As Integer
    Dim dbPath As String
    Dim adoCn As Object
    Dim strSQL As String
    Dim CurDate, CurCount, CurTotal

    ' "一覧" シートの最終行を検索
    lastrow = Worksheets("一覧").Cells(Rows.Count, 1).End(xlUp).row
    Set ws = ThisWorkbook.Sheets("入力")

    ' バリデーションチェック
    check(0) = IsDate(ws.Range("B1"))
    check(1) = IsValidCell(ws.Range("B2"))
    check(2) = IsValidCell(ws.Range("B3"))
    
    For i = 0 To 2
        If Not check(i) Then
            MsgBox ("入力エラーを検出しました。確認してください")
            Exit Sub
        End If
    Next i
    
    ' ID番号が存在しない場合は1を代入
    If Not IsNumeric(idcount) Then
        idcount = 1
    ElseIf Worksheets("一覧").Cells(lastrow, 1).Value = "ID" Then
        idcount = 1
    Else
        idcount = Int(Worksheets("一覧").Cells(lastrow, 1).Value) + 1
    End If
    
    ' Accessデータベースに接続
    Set adoCn = CreateObject("ADODB.Connection")
    dbPath = "C:\Excel\Database2.accdb"
    adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath & ";"

    ' 入力シートから値を取得
    CurDate = ws.Cells(1, 2).Value
    CurCount = ws.Cells(2, 2).Value
    CurTotal = ws.Cells(3, 2).Value

    ' 新しいレコードを追加するためのSQL文
    strSQL = "INSERT INTO テーブル1(ID,日付,数量,金額) VALUES('" & idcount & "','" & CurDate & "','" & CurCount & "','" & CurTotal & "')"
    adoCn.Execute strSQL

    ' 後処理
    adoCn.Close
    Set adoCn = Nothing

    ' 代入後データの値をクリア
    ws.Range(ws.Cells(1, 2), ws.Cells(3, 2)).ClearContents

    ' ワークシート「入力」の「セルB1」を選択
    ws.Cells(1, 2).Select
End Sub

Function IsValidCell(rng As Range) As Boolean
    ' セルが空欄かどうかをチェック
    If rng.Value = "" Then
        IsValidCell = False
    ElseIf IsNumeric(rng.Value) And rng.Value >= 1 And rng.Value = Int(rng.Value) Then
        IsValidCell = True
    Else
        IsValidCell = False
    End If
End Function

一覧シートの各ボタンのコード

おさらいスクリーンショット 2024-03-29 143827.png

データの削除

下記のコードのAccessのパスは各自確認し、入力してください

Sub ボタン2_Click()
   ' 変数の定義
   Dim lastrow As Long
   Dim dbPath As String
   Dim adoCn As Object
   Dim strSQL As String
   Dim ws As Worksheet

   ' ワークシートの設定
   Set ws = ThisWorkbook.Sheets("入力")

   ' 一覧シートの最終行を取得
   lastrow = Sheets("一覧").Cells(Rows.Count, 1).End(xlUp).row

   ' 一覧シートの内容をクリア
   Sheets("一覧").Range(Sheets("一覧").Cells(2, 1), Sheets("一覧").Cells(lastrow + 1, 4)).ClearContents

   ' Accessファイルへの接続
   Set adoCn = CreateObject("ADODB.Connection")
   dbPath = "C:\Excel\Database2.accdb"
   adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath & ";"

   ' レコード削除のSQL文
   strSQL = "DELETE FROM テーブル1"

   ' SQLの実行（レコードの削除）
   adoCn.Execute strSQL

   ' 後処理：Accessへの接続を解除
   adoCn.Close
   Set adoCn = Nothing
End Sub

CSVに出力

ファイルパス、Accessパスは各自が確認、入力

Sub 一覧_ボタン1_Click()
    ' 変数の定義
    Dim ws As Worksheet, lastrow As Long, Conn1 As Object, Conn2 As Object
    Dim tableName As String, rs As Object, rng As Range, row As Range
    Dim arrTemp As Variant, objWB As Workbook, strFilePass As String

    ' ワークシートと最終行の設定
    Set ws = ThisWorkbook.Sheets("一覧")
    lastrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).row

    ' 記入する値があるか確認
    If lastrow <= 1 Then
        MsgBox "ファイルを作成するための値がないため終了します"
        Exit Sub
    End If

    ' Accessデータベース1と2に接続
    Set Conn1 = CreateObject("ADODB.Connection")
    Conn1.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Excel\Database2.accdb"
    Set Conn2 = CreateObject("ADODB.Connection")
    Conn2.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Excel\backup.accdb"

    ' テーブル名を指定
    tableName = Format(Date, "yyyy/mm/dd") & "作成"

    ' Conn2で新しいテーブルを作成
    Conn2.Execute "CREATE TABLE [" & tableName & "] (ID long, 日付 date, 数量 long, 金額 long);"

    ' テーブルにデータを追加
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open "[" & tableName & "]", Conn2, 1, 3  ' 1 (adOpenKeyset) はカーソルタイプ、3 (adLockOptimistic) はロックタイプを表します
    Set rng = ws.Range(ws.Cells(2, 1), ws.Cells(lastrow, 4))
    For Each row In rng.Rows
        rs.AddNew
        rs.Fields("ID").Value = row.Cells(1).Value
        rs.Fields("日付").Value = row.Cells(2).Value
        rs.Fields("数量").Value = row.Cells(3).Value
        rs.Fields("金額").Value = row.Cells(4).Value
        rs.Update
    Next row
    rs.Close

    ' Conn1のテーブル1のデータを削除
    Conn1.Execute "DELETE FROM テーブル1"

    ' Accessへの接続を解除
    Conn1.Close
    Conn2.Close

    ' CSV出力
    strFilePass = "C:\Excel\" & ws.Range("I3").Value & ".csv"
    lastrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).row
    arrTemp = ws.Range(ws.Cells(1, 1), ws.Cells(lastrow, 4))

    ' 新規ブックを作成
    Set objWB = Workbooks.Add

    ' CSVファイルが存在しない場合は作成
    If Dir(strFilePass) = "" Then
        objWB.Sheets(1).Range("A1").Resize(UBound(arrTemp, 1), UBound(arrTemp, 2)).Value = arrTemp
        objWB.SaveAs Filename:=strFilePass, FileFormat:=xlCSV, CreateBackup:=False
        objWB.Close
        MsgBox "以下のCSVファイルを出力しました！" & Chr(13) & strFilePass
    End If

    ' 追加済みの情報を削除
    ws.Range(ws.Cells(2, 1), ws.Cells(lastrow, 4)).ClearContents
    ws.Cells(3, 9).ClearContents
End Sub

今後の課題

・バックアップがこれでセキュアにできているか調査
・一日に複数バックアップを作成する際にどう作成すればわかりやすいか

最後に

ここまで見ていただきありがとうございます。
備忘録として書いたつもりですが、どういった改善ができるか
ぜひご意見を頂戴できればと存じます。
これからはExcel VBAのみでなくCCNAの勉強やJavaに関するコードも載せていければと思います。

参考サイト
https://blog.djuggernaut.com/excel-vba-calendar-control/#index_id6

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?