【エクセルカレンダー】VBAで予定の転記、年数の切替、ハイパーリンクの機能を設定する方法
豆父ちゃん
こんな要望にお応えします。
結論からいうと、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(繰り返し処理)を使っているので分かりにくくなっているかと思いますがご了承ください。
年数切替機能がある便利カレンダーは年が変わっても使えるので、長期間の予定を組む方にとてもおすすめです。
便利な機能② 年間カレンダーから月間カレンダーへのハイパーリンク
豆父ちゃん
こんな不満を解決するために導入したのが、年間カレンダーから月間カレンダーへハイパーリンクする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関数が使われています。
ハイパーリンク関数の詳しい使い方は下記の記事で詳しく紹介してます。
Hyperkinks関数はいろんなエクセル業務に使われる機能なので使い方をしっかり覚えておきましょう。
便利機能③ 年間カレンダーと月間カレンダーの簡単な予定を転記
豆父ちゃん
こんな不満を解決するために導入したのが、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の使い方について下記の記事で詳しく紹介しています。
よく使われる機能なので使い方をマスターしておきましょう。
便利な機能④ 内容を区別してセルを塗り潰す
豆父ちゃん
こんな不満を解決するために導入したのが、土曜日、日曜日、休日は自動的にセルの背景色が｢青色｣、｢赤色｣、｢灰色｣に塗り潰される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つの便利機能が設定されたエクセルカレンダーを使いやすいようにアレンジして提供することが可能です。
気になる方は気軽に連絡してください。
エクセルカレンダーに使われている重要な関数
エクセルカレンダーVBAは簡単な関数を組み合わせて作られています。
使った関数一覧・Day関数
・WeekDay関数
・Hyperlink関数
・For〜next
・Functionプロシージャ
簡単な関数を組み合わせるだけでいろんなことができるように！
エクセルカレンダーVBAは簡単な関数を使い無理やり表現しているものが多いので動作が少し遅くなってしまいましたが、業務上とても使いやすいものになっています。
豆父ちゃん
仕事またはプライベートでカレンダーが必要という方は是非参考にしてみてください。
VBA初心者が効率化マクロを導入する方法
VBAなんて扱ったことないけどエクセル作業を自動化するマクロを導入したい！
このような問題を抱えながら日々のエクセル業務をこなしている方がおおくいらっしゃるのではないでしょうか。
こんな問題を解決する方法がこちら
- マクロの開発を外注に依頼する
- マクロが設定されたエクセルファイルをダウンロードする
- 参考書やネットでVBAスキルを身に付ける
VBAの勉強を始める前は上記の方法で効率化マクロを導入していました。
完成されたマクロを使ってエクセル業務を処理する
↓
作業効率が２倍以上アップ！！
↓
便利すぎて自分でもマクロを設定したいと考え始める
↓
参考書やネットの情報でVBAスキルを身に付ける
↓
マクロ開発を代行できる程のスキルを習得
VBAは
他のプログラミングに比べて参考書やネットで紹介されている情報量が多いので初心者でも導入しやすい
という特徴があります。
毎日のルーティーン作業にうんざりしている、だれでもできる単純作業に時間を費やしている、という方はあなたに合った方法でVBAを導入しちゃいましょう。
マクロの開発を外注に依頼する
マクロの開発を外注に依頼する導入方法は
VBAを全く扱えない方でもエクセル作業を自動化できる最も簡単な方法
です。
「こんなエクセル作業を自動化したい」「作った資料を自動的にPDFで保存したい」「決まった様式に写真を自動で貼り付けたい」
こんな感じで実現したい内容を依頼するだけで業務内容に合ったマクロを開発してくれます。
ガッツポーズの人
デメリットは、費用がかかることだけ。
開発内容、依頼先によってかかる費用は大きく異なります。
私が会社員の頃利用していた業者さんは１マクロあたり2万円～3万円の費用がかかりました。
疑問がある人
ちなみに、マメBlogでもエクセルマクロ開発代行サービスを承っています。
開発内容の確認、VBAコードの設定、動作確認後の調整、など全ての工程を私（マメ父ちゃん）が行っているので、
費用が安い！スピード納期！！
でやらせてもらっています。
ほとんど独学で身に付けたVBAスキルなので、内容によっては開発できないこともあるのでご了承ください。
ご相談、見積もり依頼は完全無料ですので気になる方は下記のリンクよりお問合せ下さい。
マクロが設定されたエクセルファイルをダウンロードする
VBA初心者でも『マクロが設定されたエクセルファイルをダウンロード』ですぐにエクセル作業を自動化することができます。
たとえば、
A４用紙に写真を貼り付けてコメントを入力する作業を自動化したい！という場合、
『写真を貼り付けるマクロ』が設定されたエクセルファイルをダウンロードすれば問題解決です。
実際に、『写真を貼り付けるマクロ』をダウンロードして使っている様子がこちら
ガッツポーズの人
『マクロが設定されたエクセルファイルをダウンロード』する導入方法は、
VBA初心者でもマクロを導入できる、費用が安い、
というメリットがあります。
しかし
VBAに関する情報が多く公開されているとはいえ、ネット上で想定するマクロは見つけることが難しい
というデメリットもあります。
ガッツポーズの人
個人ブログやファイルを販売できるサイト「note」や「Tips」ではいろんなマクロの情報が掲載されているので是非参考にしてみてください。
今までマメBlogで受注した効率化マクロを下記のnoteで掲載していますので併せてチェックしてみてください。
参考書やネットでVBAスキルを身に付ける
先程も紹介しましたが、
VBAは他のプログラミングに比べて参考書やネットで紹介されている情報量が多いので初心者でも導入しやすい
プログラミングです。
言い換えると、
情報量が豊富なので自分で勉強できちゃう
ということです。
私のVBAスキルはほとんど独学で身に付けたもので、
考えた処理のほとんどをVBAで表現できるレベルにまで到達した！
と思っています。
私の作ったマクロをプロのプログラマーが見れば、
お前の書くコードはインチキだ！「変数の宣言」とか「引数」を正しく使えよ！！
と感じるはずです。
でも、
私からすればどうでもいい！！！想定した内容を正しく処理できればよくないですか！？
独学であっても『写真を貼り付けるマクロ』『全てのシートをPDF出力するマクロ』『連番を振るマクロ』など、
業務内容に合ったマクロを開発できるようになれます。
独学でVBAスキルを身に付けるコツは、
エラーが発生してもいいからひたすらコードを書く、なんでもいいからエクセル作業をマクロ化してみる
です。
とは言っても、全くの知識0の方は何をすればいいかわからないですよね。
VBA知識0の初心者の方は、参考書『たった1秒で仕事が片づくExcel自動化の教科書【増強完全版】』を読んでみてください。
VBAの知識0の方でもすいすい読み進められる内容になっています。
やさしい内容にも関わらず「え！！VBAを使えばこんなことできるの！？」と感動すると思います。
実際に私はこの参考書を読んでからVBAの勉強を始めました。
ひらめく人
VBAの魅力を発見した後は、
ひたすら自動化したい処理を実現するコードを書くだけ
です。
【VBA 写真を貼る】、【VBA PDF出力】、のようにネット検索すれば知りたいコードをすぐにゲットできます。
実現したい処理に向かって、「調べる→書いてみる→エラーを改善する→調べる」を繰り返していると
自然といろんなマクロが作れるようになっているはずです。
VBAに興味が湧いてきたという方は今からVBA学習をスタートさせちゃいましょう。