Timerクラス
最近の働き方改革で夜遅くまでオフィスで仕事をしない人は関係ない話なので、この記事は読まなくても大丈夫です。
経過時間をざっくり計算するために一番簡単な方法はVBAのTimer関数です。たいていの人がこんな感じで済ませていると思いますが、これだと対応できないケースがあります。
Double dStart As Double Double dEnd As Double dStart = Timer ..... ..... dEnd = Timer Debug.Print "Elapsed: " & dEnd - dStart
ローカル変数で宣言してるけど、複数計測したい場合に変数を個数分宣言しなきゃいけないのは煩雑。そもそもタイトルがTimerクラスってなってるしｗ
残念でした。不正解です。日付をまたいだケースに対応できないが正解です。Timer関数は午前０時からの経過秒数を返してきます。そのため夜０時をまたぐと上記のdEnd - dStartがマイナスになってしまいます。
例： 23:55PMにプログラムを走らせて、0:05に処理が終わった。
dStart = Timer '(23*60+55) *60 = 86100
dEnd = Timer '5 * 60 = 300
Elapsed: -85800 '300 - 86100
日付が変わるまでオフィスで仕事をしない人は関係ないと言った理由がお分かりいただけただろうか。自宅のPCで何か走らせたい人は下のコードをコピペしてください。
グローバルモジュールで関数を定義してもいいですが、それだと１箇所でしか計測できません。そのためにクラスにしておくとよいでしょう。
Option Explicit Private m_dStartTime As Double Private m_dEndTime As Double ' --------------------------------------------- ' タイマー ' --------------------------------------------- Public Sub StartTimer() m_dStartTime = Timer m_dEndTime = Timer End Sub Public Function ElapsedTime() As Double m_dEndTime = Timer If m_dEndTime - m_dStartTime < 0 Then ElapsedTime = m_dEndTime + (86400 - m_dStartTime) Else ElapsedTime = m_dEndTime - m_dStartTime End If End Function
メモ：Windows10でDLNAメディアサーバーを立ててみる
DLNAメディアサーバー
PS4にメディアプレーヤーをインストールすると、Nasneにあるファイルを再生できるのはいいんですが、Windows10のファイルは再生できません。単純にメディアサーバーを動かせばいいわけで、今回はそのやり方を解説します。
DLNA (Digital Living Network Alliance)
家電、モバイル、およびパーソナルコンピュータ産業における異メーカー間の機器の相互接続を容易にするために2003年6月に結成された非営利業界団体。ソニー、東芝、パナソニック、日立制作、バッファロー、シャープ、アイ・オーデータなどの企業が参加しています。こんなものがあるとは知りませんでした。マイクロソフト独自の規格ではないというのはいいことです。
メディアサーバーの起動
▶エクスプローラの左側にあるネットワークを右クリック
▶プロパティをクリック
▶コントロールパネルが出てくる
▶左の『メディアストリーミングオプション』をクリック
▶『メディアストリーミングを有効にする』をクリック
▶メディアライブラリに名前を付けておく
ファイル保存場所の追加
▶エクスプローラの左側にあるネットワークをクリック
▶先ほどのメディアライブラリをクリック
▶Windows Media Playerが起動する
▶音楽を右クリックして、『音楽ライブラリの管理』
▶追加ボタンをクリックして、MP3が置いてあるフォルダーを選択
▶ビデオを右クリックして『ビデオライブラリの管理』、
▶追加ボタンをクリックして、動画が置いてあるフォルダーを選択
デバイスキャスト
Windows Media Playerやエクスプローラの右クリックメニューにデバイスキャストというメニューがありますが、これはローカルエリアネットワークに接続されているスピーカーに音声出力できるという機能です。ソニーのMusic Center for PCが一番エレガントにこの機能を実装していますね。ハードディスクやNASに置いてあるMP3ファイルをサウンドバーに出力できるのはいいです。
Boolean表記とIIF関数
IF-ELSE文で次のようなコードを書くときに５行も使うの嫌ですよね。
Dim fFlag As Boolean
If buff = "EP9 sucks" Then
fFlag = True
Else
fFlag = False
End If
IIF関数なら１行で書けます。
fFlag = IIf(buff = "EP9 sucks", True, False)
でも、ステートメントでBoolean評価してくれるって知ってましたか？。しかも、IIF関数よりも４倍早いです。
fFlag = (buff = "EP9 sucks")
IIF関数はTrueパートとFalseパート両方とも実行されてしまうので、初期化されていないオブジェクトを参照するようなコードを書くとエラーになってしまいます。そういう場合は、おとなしくIF-ELSE構造にするか、実行時エラーにならないようにオブジェクトを初期化するとか、注意してください。
メモ：条件付き書式ダイアログのバグ
条件付き書式ダイアログで書式ルールを6個以上でクリックできなくなる、適用先が消えてしまうバグが発生します。
１．６個の書式ルールが定義されたダイアログ
２．６個目を表示しようとしてスクロールすると・・・・
一番下の適用先が表示されていません。
３．さらに上にスクロールすると・・・
もう何がなんだか。しかも適用先はクリックしても編集すらできない状態です。
条件をダブルクリックして編集画面を出しておいてOKボタンを押すと、適用先が編集できるようになるようです。わたしが使っているのはエクセル2016ですが、最新バージョンも同じバグがあるんでしょうか。
日本国民として知っておくべきこと：国民祝日
まずは技術的な話
Windows10のカレンダーアプリには、OutlookとGmailの国民祝日があります。普通に考えてOutlookのAPIで簡単に取れるだろうと思って、いろいろ調べてみたんですが、サンプル通りにやってもうまくいきません。
休日一覧をウェブから取ってくればいっか
年に一度のイベントと思って手入力してもいいんですが、やっぱりそれではダサいのでもうちょっと調べてみたらGoogleカレンダーAPIで可能だということが分かりました。そしてこのAPIを使って自前でGithubを自動更新しているページを見つけたので、ここから休日を取ってくるコードを書いてみました。商用・非商用OKということなので問題ないと思います。
すべてはこれが始まりでした
もうひとつCalendar Service (http://calendar-service.net/api.php) というページも見つけましたが、この2つのページで日付の差異があることに気が付きました。普段は国民祝日なんて気にも留めていないし、日本は祝日が多すぎてよくないと思っているくらいなので、ましてやそれぞれの休みの意味とか背景などどうでもよく、休みなら仕方なく休むかという感じでした。
違いが発生した理由その１ 天皇誕生日
2019年12月23日は天皇誕生日のはずでした。しかし、皇位継承で元号も変わり、この日は平日に変更されました。そして、ヤフーファイナンスのトップページもこの有様です。ヤフーは12/23 13:20くらいまで株価を更新していませんでした。おそらく2階建てのネタになるでしょう。
しかしながら、わたしが勘違いした理由にはもう一つあって、それはOutlookの休日カレンダーです。左ペインにあるとおり、OutlookとGmailの両方に日本の休日というイベントがあり、12/23を休日としているのはOutlookです。Gmailはこの日を平日扱いにしています。
さきほど紹介した２つのウェブページを見てみましょう。holidays-jp.github.ioの方は12/23は平日扱い、calendar-service.netは祝日となっています。そして、Outlookとcarendar-service.netは2020年12月23日も天皇誕生日らしいです。
違いが発生した理由その２ 即位礼正殿の儀の行われる日
これも皇位継承に伴う例外的な祝日ですね。
| Outlook | 平日扱い |
| Gmail | 祝日扱い |
| calendar-service.net | 平日扱い |
| holidays-jp.github.io | 祝日扱い |
違いが発生した理由その３ 海の日
そもそも海の日ってなに？っていう話から。
国民の祝日に関する法律（祝日法、昭和23年7月20日法律第178号）第2条は、「海の恩恵に感謝するとともに、海洋国日本の繁栄を願う」ことを趣旨としている。国土交通省は「世界の国々の中で『海の日』を国民の祝日としている国は唯一日本だけ」[1]としている。
制定当初は7月20日であったが、2003年（平成15年）に改正された祝日法のハッピーマンデー制度により、7月の第3月曜日となった。
(Wikipediaより抜粋)
これでいくと2020年の海の日は7月第3月曜日にぶつけて7/20になるはずでした。ところが、2020年は東京オリンピック開催で3連休にすると都内が渋滞するので例外的に7/23に変更することになったらしいです。
2020年（令和2年）は東京五輪・パラリンピック特措法により、東京オリンピックの開会式の前日に当たる7月23日（木曜日）に変更される[6]。
(Wikipediaより抜粋)
一番の問題はこの例外処理に対応しているのはどのカレンダーということです。今回でもOutlookとcalendar-service.netはNG。
| Outlook | 7月20日 |
| Gmail | 7月23日 |
| calendar-service.net | 7月20日 |
| holidays-jp.github.io | 7月23日 |
2019年の祝日を総チェック
そろそろ分かってきましたが、Outlookとcalendar-serviceはデータソースとしてはダメです。普段は気にも留めていなかった祝日にここまで時間をかけたのは生まれて初めてのことですが、この際なので徹底的に調べてみることにしましょう。
| Outlook | Gmail | calendar-service | holidays-jp | ||
|---|---|---|---|---|---|
| 04/30 | 祝日 | X | O | X | O |
| 05/01 | 天皇の即位の日 | X | O | X | O |
| 05/02 | 祝日 | X | O | X | O |
| 10/22 | 即位礼正殿の儀 | X | O | X | O |
| 12/23 | 平日 | X | O | X | O |
もはやVBAの技術論ではなく日本国民として知っておくべきチコちゃんネタになってしまいましたが、Outlookの国民祝日カレンダーは使うべきではありません。いますぐ外しましょう。そして、Gmailとholidays-jpに賛辞を贈ることにします。
https://holidays-jp.github.io/api/v1/2020/date.json
出力結果：
{
"2020-01-01": "元日",
"2020-01-13": "成人の日",
"2020-02-11": "建国記念の日",
"2020-02-23": "天皇誕生日",
"2020-02-24": "天皇誕生日 振替休日",
"2020-03-20": "春分の日",
"2020-04-29": "昭和の日",
"2020-05-03": "憲法記念日",
"2020-05-04": "みどりの日",
"2020-05-05": "こどもの日",
"2020-05-06": "憲法記念日 振替休日",
"2020-07-23": "海の日",
"2020-07-24": "体育の日",
"2020-08-10": "山の日",
"2020-09-21": "敬老の日",
"2020-09-22": "秋分の日",
"2020-11-03": "文化の日",
"2020-11-23": "勤労感謝の日"
}
Private Sub TestHarness() Debug.Print IsHoliday("2019/5/2") UpdateHolidays 2019 End Sub Public Function IsHoliday(pDate As String) As Boolean Dim iDay As Long IsHoliday = True iDay = Weekday(pDate) If iDay = vbSunday Or iDay = vbSaturday Then Exit Function Dim vMatch As Variant On Error GoTo Unmatch vMatch = Application.WorksheetFunction.Match(_
Clng(CDate(pDate)), wksCalendar.Range("A:A"), 0)
IsHoliday = (vMatch > 0) Exit Function Unmatch: IsHoliday = False End Function Public Sub UpdateHolidays(pYear As Long) Dim sURL As String Dim buff As String Dim oStream As Stream Dim sDate As String Dim xCell As Excel.Range ' ---- Download Holidays sURL = "https://holidays-jp.github.io/api/v1/" & pYear & "/date.json" buff = modUtil.GetHTMLText(sURL) Set oStream = New Stream oStream.Buffer = buff Set xCell = wksCalendar.Range("A1") ' ---- Update calendar worksheet Do Until oStream.EOF buff = oStream.ReadLine If InStr(buff, ":") > 0 Then sDate = parseString(buff, """", """") xCell.Value = "'" & Format(sDate, "YYYY/MM/DD") Set xCell = xCell.Offset(1, 0) End If Loop End Sub Private Function parseString(pBuff As String, pKey1 As String, pKey2 As String) As String Dim iPos1 As Long Dim iPos2 As Long iPos1 = InStr(pBuff, pKey1) iPos2 = InStr(iPos1 + 1, pBuff, pKey2) If iPos1 + iPos2 = 0 Then parseString = "" Else iPos1 = iPos1 + Len(pKey1) parseString = Mid(pBuff, iPos1, iPos2 - iPos1) End If End Function
いまさらRangeオブジェクト： Value、Text、メンバー指定なし
RangeオブジェクトのディフォルトのプロパティはValueです。Textではありません。この２つのプロパティの違いはちゃんと理解しておいた方がよいです。
Dim xCell As Excel.Range Set xCell = Sheet1.Range("A1") xCell = Now xCell.NumberFormatLocal = "[$-ja-JP-x-gannen]ggge""年""m""月""d""日"";@" Debug.Print xCell Debug.Print xCell.Value Debug.Print xCell.Tex
実行結果：
2019/12/11 13:42:18
2019/12/11 13:42:18
令和元年12月11日
xCellには今日の日付をセットして、表示形式を和暦にしてあります。xCellとxCell.Valueは西暦を返していますが、xCell.Textは令和になっています。つまり、Textは書式付きの値を返します。あと、Textは読み取り専用です。
普通に考えて.Valueよりも.Textの方が時間がかかるだろうと予想できますが、どのくらい違うのか計測してみました。
Value指定 Private Sub test1() Dim xCell As Excel.Range Dim i As Long Dim buff As String Set xCell = Sheet1.Range("A10") StartTimer For i = 1 To 10000 buff = xCell.Offset(0, 1).Value Next Debug.Print "Test1: " & ElapsedTime End Sub
Textプロパティ Private Sub test2() Dim xCell As Excel.Range Dim i As Long Dim buff As String Set xCell = Sheet1.Range("A10") StartTimer For i = 1 To 10000 buff = xCell.Offset(0, 1).Text Next Debug.Print "Test2: " & ElapsedTime End Sub
プロパティ指定なしと.Value指定の違いはほとんどありませんが、.Value指定の方が誤差の範囲程度で早いです。気になるtest1とtest2の結果ですが、test2はtest1の４倍時間がかかるという結果が出ました。
結論
.Textはセルに表示されている形式で返してきます。ここで問題になるのはDate/TimeとNumberです。この２つのデータタイプはセル幅が狭くなると、"#"と表示されてしまいます。
| Time | Number | String |
| 11:30 | 9,999,999,999 | This is a test |
| 11:35 | ||
| 11:40 | ||
| 11:50 | ||
| 11:55 |
セル幅を狭くすると、このようになります。
| Time | Number | String |
| ## | ##### | This is a test |
| ## | ||
| ## | ||
| ## | ||
| ## |
? Sheet1.Range("A5").Text
#
? Sheet1.Range("B5").Text
#
? Sheet1.Range("C5").Text
This is a test
.Textプロパティにこんな実行時エラーがあるとはプロ泣かせです。
