【エクセルカレンダー】VBAで予定の転記、年数の切替、ハイパーリンクの機能を設定する方法

Left Caption

豆父ちゃん

エクセルを使って予定の管理をしたい。使いやすくて便利なエクセルカレンダーを作りたい。

こんな要望にお応えします。

結論からいうと、VBAを使って便利な機能をエクセルカレンダーに組み込んでしまう！ということです。

私が、「こんな機能があると便利だなぁ」と考え付いた機能をVBAを使って表現しています。

エクセルカレンダーに導入した機能①年数の切替で日付と曜日が自動的に変更

②年間カレンダーから月間カレンダーへのハイパーリンク

③年間カレンダーと月間カレンダーの簡単な予定を転記

④内容を区別してセルを塗りつぶす

この記事では4つの便利な機能について詳しく紹介していきます。

 

広告

エクセルカレンダーに導入した便利な機能

下記の動画では、エクセルカレンダーに設定された4つの便利な機能について紹介されています。どのようにマクロが実行され、どのような処理が行われているのかを詳しく説明しています。エクセルを使ってカレンダーを作成しようと考えている方は必見の内容です。

 

この動画で紹介しているエクセルカレンダーは下記のサイトでダウンロードすることが可能です。

便利な機能が設定されたエクセルカレンダーのダウンロードはこちら

使ってみたい！という方は是非参考にしてみてください。

 

便利な機能① 年数の切替で日付と曜日が自動的に変更

仕事をする上で今まで使っていたエクセルカレンダーが使えなくなるのはとても不便ですよね。今までの予定やこれからの予定を新しいカレンダーにコピーしなければならない！といった手間が生じてしまいます。

こんな不満を解決するために導入したのが、年数を指定すると日付と曜日が自動的に変更されるVBAです。年度を切り替える機能を使えば、このエクセルカレンダーをずっと使い続けることができます。さらに曜日やうるう年の設定を自動で変更してくれるので、カレンダーを修正する手間がほとんど発生しません。

 

閏年を判別するVBA

1 ～抜粋～
2 If (Day(DateSerial(i, 2, 28) + 1)) = 29 Then
3 >Ash.Cells(34, 6) = “29"
4 Else
5 Ash.Cells(34, 6).ClearContents
6 Ash.Cells(34, 7).ClearContents
7 End If

上記VBAは閏年を表現するVBAの一部です。関数IFを使って、閏年の2月29日は29を入力、それ以外の年は削除する！というもので、DateSerial関数が使われています。

 

日付から曜日を判別するVBA

1 ～抜粋～
2 For j = 1 To 56 Step 5
3 tuki = Mo(Ash.Range(Ash.Cells(4, j), Ash.Cells(4, j)))
4 For k = 6 To 36
5 hizuke = Ash.Cells(k, j)
6 On Error Resume Next
7 If Ash.Cells(k, j) <> “" Then
8 Ash.Cells(k, j + 1) = WeekdayName(Weekday(i & “/" & tuki & “/" & hizuke), True)
9 Else
10 End If
11 Next k
12 Next j

上記VBAは適切な曜日を入力するVBAの一部です。8行目のVBAは、年数と月数と日付から曜日を判別する！というもので、WeekdayName関数が使われています。

いろんな変数やFor～Next(繰り返し処理)を使っているので分かりにくくなっているかと思いますがご了承ください。

年数切替機能がある便利カレンダーは年が変わっても使えるので、長期間の予定を組む方にとてもおすすめです。

 

 

便利な機能② 年間カレンダーから月間カレンダーへのハイパーリンク

 

Right Caption

豆父ちゃん

月間カレンダーは予定を詳しく入力するスペースが大きい分、行数が多くなってしまうので、上から下に移動するのがとても不便だなぁ

こんな不満を解決するために導入したのが、年間カレンダーから月間カレンダーへハイパーリンクするVBAです。

サンプルVBA

1 ～抜粋～
2 Dim Ash As Worksheet
3 Dim Bsh As Worksheet
4 Set Ash = ThisWorkbook.Worksheets(“年間カレンダー")
5 Set Bsh = ThisWorkbook.Worksheets(“月間カレンダー")
6 Dim HY As Hyperlink
7 Set HY = Ash.Range(“A4").Hyperlinks.Add(Anchor:=Ash.Range(“A4″), Address:="", SubAddress:="月間カレンダー!A" & 1)
8 Ash.Range(“A4").Font.Color = RGB(0, 0, 0)
9 Ash.Range(“A4").Font.Underline = False
10 Set HY = Ash.Range(“F4").Hyperlinks.Add(Anchor:=Ash.Range(“F4″), Address:="", SubAddress:="月間カレンダー!A" & 43)
11 Ash.Range(“F4").Font.Color = RGB(0, 0, 0)
12 Ash.Range(“F4").Font.Underline = False
13 ～リンク先の月を変更し繰り返し入力～

上記サンプルVBAは年間カレンダーから月間カレンダーにハイパーリンクするVBAの一部です。年間カレンダーシートの｢1月｣をクリックすると、月間カレンダーシートの｢クリックした月｣に移動する！というもので、Hyperlinks関数が使われています。

ハイパーリンク関数の詳しい使い方は下記の記事で詳しく紹介してます。

mamemametochan.com
仕事で使えるExcel VBA【 ハイパーリンクを使った業務効率化 】
https://mamemametochan.com/excel-10
ハイパーリンクとはクリックで設定したリンク先へ一瞬でジャンプする機能のことです。特定のセルに設定したり、挿入した画像に設定したりといろんな使い方ができます。Excelでハイパーリンクを設定している人は主にハイパーリンクを設定したセルをクリックすると特定のフォルダが表示される！というような使い方をしている人がほとんどだと思います。設定も簡単でフォルダを探す時間の削減に繋がるとても便利な使い方です。このような使い方以外に私の職場ではハイパーリンクを設定したセルをクリックすると別Sheetの特定のセルにジャ...

Hyperkinks関数はいろんなエクセル業務に使われる機能なので使い方をしっかり覚えておきましょう。

 

便利機能③ 年間カレンダーと月間カレンダーの簡単な予定を転記

 

Right Caption

豆父ちゃん

予定を入力するとき、月間カレンダーと年間カレンダーに同じことを入力するのはとても不便だなぁ

こんな不満を解決するために導入したのが、Sheetを切り替えると月間カレンダーシートと年間カレンダーシートの内容を一致させるVBAです。

サンプルVBA(Module1)

1 ～一部抜粋～
2 Sub 月転記()
3 Application.ScreenUpdating = False
4 Dim Ash As Worksheet
5 Dim Bsh As Worksheet
6 Set Ash = ThisWorkbook.Worksheets(“年間カレンダー")
7 Set Bsh = ThisWorkbook.Worksheets(“月間カレンダー")
8 Call 月転記1月
9 Call 月転記2月
10 ～Call 月転記〇月の繰り返し～
11 End Sub

サンプルVBA(Module2)

1 Sub 月転記1月()
2 Application.ScreenUpdating = False
3 Dim Ash As Worksheet
4 Dim Bsh As Worksheet
5 Set Ash = ThisWorkbook.Worksheets(“年間カレンダー")
6 Set Bsh = ThisWorkbook.Worksheets(“月間カレンダー")
7 Ash.Range(Ash.Cells(6, 3), Ash.Cells(36, 5)).ClearContents
8 Ash.Range(Ash.Cells(6, 1), Ash.Cells(36, 5)).ClearFormats
9 For j = 6 To 36 Step 6
10 For i = 7 To 61 Step 9
11 If (Bsh.Cells(j, i) <> “") Or (Bsh.Cells(j, i + 1) <> “") Or (Bsh.Cells(j, i + 2) <> “") Then
12 Ash.Cells(Bsh.Cells(j, i – 6) + 5, 3) = Bsh.Cells(j, i)
13 Ash.Range(Ash.Cells(Bsh.Cells(j, i – 6) + 5, 1), Ash.Cells(Bsh.Cells(j, i – 6) + 5, 2)).Interior.Color = RGB(234, 234, 234)
14 Ash.Cells(Bsh.Cells(j, i – 6) + 5, 4) = Bsh.Cells(j, i + 1)
15 Ash.Range(Ash.Cells(Bsh.Cells(j, i – 6) + 5, 1), Ash.Cells(Bsh.Cells(j, i – 6) + 5, 2)).Interior.Color = RGB(234, 234, 234)
16 Ash.Cells(Bsh.Cells(j, i – 6) + 5, 5) = Bsh.Cells(j, i + 2)
17 Ash.Range(Ash.Cells(Bsh.Cells(j, i – 6) + 5, 1), Ash.Cells(Bsh.Cells(j, i – 6) + 5, 2)).Interior.Color = RGB(234, 234, 234)
18 Else
19 End If
20 Next
21 Next
22 For j = 6 To 36
23 If Ash.Cells(j, 2) = “日" Then
24 Ash.Range(Ash.Cells(j, 1), Ash.Cells(j, 2)).Interior.Color = RGB(255, 180, 180)
25 ElseIf Ash.Cells(j, 2) = “土" Then
26 Ash.Range(Ash.Cells(j, 1), Ash.Cells(j, 2)).Interior.Color = RGB(205, 255, 255)
27 Else
28 End If
29 Next
30 End Sub

※上記サンプルVBAは1月分の転記のみ

サンプルVBA(Sheet年間カレンダー)

1 Private Sub Worksheet_Activate()
2 Call 月転記
3 Call HY
4 End Sub

サンプルVBA(Sheet月間カレンダー)

1 Private Sub Worksheet_Activate()
2 Call 年転記
3 End Sub

上記4つのサンプルVBAは、Sheetを切り替えると月間カレンダーシートと年間カレンダーシートの内容を一致させるVBAの一部です。シートを切り替えると転記VBAをCAllで呼び出す！というもの。複雑で難しそうと感じるかもしれませんが、難しい関数や引数などは一切使っていません。

Callの使い方について下記の記事で詳しく紹介しています。

mamemametochan.com
関数を自分で作る？【 Functionプロシージャの使い方とは 】
https://mamemametochan.com/excel-16
豆父ちゃん自分で関数が作れるFunctionプロシージャってなに？どうやって使うの？こんな疑問にお答えします。この記事ではFunctionプロシージャ処理動作が似ているCallと比較して使い方や特徴を紹介しています。FunctionやCallはとても便利な機能なので、使いこなせるようになればVBAでさらにいろんな処理が出来るようになるので、しっかり使いこなせるようになりましょう。 FunctionプロシージャとはFunctionは自分で作ったプログラムをあたかも登録されている関数のように使えるようになるというものどのようにしてFunctionを使...

よく使われる機能なので使い方をマスターしておきましょう。

 

便利な機能④ 内容を区別してセルを塗り潰す

Right Caption

豆父ちゃん

土曜日と日曜日の背景色が自動的に変わらないと不便だなぁ

こんな不満を解決するために導入したのが、土曜日、日曜日、休日は自動的にセルの背景色が｢青色｣、｢赤色｣、｢灰色｣に塗り潰されるVBAです。

サンプルVBA

1 Sub 月転記1月()
2 Application.ScreenUpdating = False
3 Dim Ash As Worksheet
4 Dim Bsh As Worksheet
5 Set Ash = ThisWorkbook.Worksheets(“年間カレンダー")
6 Set Bsh = ThisWorkbook.Worksheets(“月間カレンダー")
7 ～一部省略～
8 For j = 6 To 36
9 If Ash.Cells(j, 2) = “日" Then
10 Ash.Range(Ash.Cells(j, 1), Ash.Cells(j, 2)).Interior.Color = RGB(255, 180, 180)
11 ElseIf Ash.Cells(j, 2) = “土" Then
12 Ash.Range(Ash.Cells(j, 1), Ash.Cells(j, 2)).Interior.Color = RGB(205, 255, 255)
13 Else
14 End If
15 Next
16 End Sub

 

上記サンプルVBAは、土曜日と日曜日の背景色を塗りつぶすVBAの一部です。セルに｢土｣という文字が入力されたとき、青色に塗り潰す。セルに｢日｣という文字が入力されたとき、赤色に塗り潰す！というもので、簡単な関数だけで作ることができます。

 

  • 関数IF
  • For～Next(繰り返し処理)
  • .Interior.Color

今回は仕事用のカレンダーなので年休、特休、公休というコメントを記入すると灰色に塗りつぶされるというVBAにしました。休日や特別な予定は背景色を変えて目立つようにするとカレンダーが見やすくなりますよね。

背景色で休日を区別するVBAはエクセルカレンダーの基本です。使い方をマスターしておきましょう。

 

エクセルカレンダーと手帳カレンダーの比較

エクセルカレンダーは仕事をするうえで必須アイテムです。以前手帳タイプのカレンダーを使っていたのですが、今は手帳タイプのカレンダーを一切使っていません。

手帳タイプのカレンダーを使わない理由

 

  • 毎年買い替えるのに費用がかかる。
  • エクセル入力した方が早い(デスクワークメインなので・・）
  • 素早く色分けができない
  • 職場で予定を共有できない

手帳タイプのカレンダーで予定を調整していると、予定の調整ミスが発生することがありますよね。私の職場では自分自身の予定は手帳タイプのカレンダーで管理していたけど、職場のみんなに予定の周知を忘れてしまった！という事象が発生していました。

VBAを使ったエクセルカレンダーの最大のメリットとして、いろんな種類のカレンダーの予定を一瞬んで集約することが可能ということ。

予定の調整に使うカレンダーはとても重要なものなので、便利で使いやすいものにしなければなりません。今回作成したエクセルカレンダーVBAは毎年使えて、条件で色分けを行う便利な機能を導入しています。

 

便利機能の設定は自由自在

今回は仕事で使えるをテーマにしたエクセルカレンダーVBAを作成しましたが、設定を変えればいろんなことに応用可能

家族の予定表

家計簿の情報を反映させる

旅行の予定表

家族の誕生日や結婚記念日を設定する

 

職場では、さまざまな様式のエクセルカレンダーの予定を一瞬で1つのエクセルカレンダーに集約するVBAを導入していて、とても好評です。

下記のサイト(ココナラ)では当ブログで紹介しているエクセルVBAの提供サービスを行っています。この記事で紹介している4つの便利機能が設定されたエクセルカレンダーを使いやすいようにアレンジして提供することが可能です。

coconala.com
エクセルでの作業の効率化お手伝いします 単純作業を自動化して今までの半分の時間...
https://coconala.com/services/847462
日頃エクセル作業を行っている依頼者様へデータの転記、同じ作業の繰り返し、膨大な量のデータ処理を自動化するvbaを作成します。下記に記入しているものに近いvbaの...

気になる方は気軽に連絡してください。

 

エクセルカレンダーに使われている重要な関数

エクセルカレンダーVBAは簡単な関数を組み合わせて作られています。

使った関数一覧・Day関数

・WeekDay関数

・Hyperlink関数

・For〜next

・Functionプロシージャ

 

簡単な関数を組み合わせるだけでいろんなことができるように！

エクセルカレンダーVBAは簡単な関数を使い無理やり表現しているものが多いので動作が少し遅くなってしまいましたが、業務上とても使いやすいものになっています。

 

Right Caption

豆父ちゃん

特に年数の切替は、曜日の変更する手間がなく毎年使えるのでかなり便利な機能になっています

仕事またはプライベートでカレンダーが必要という方は是非参考にしてみてください。

エクセル

Posted by t.a.k.ubonse926@gmail.com