{"id":5434,"date":"2022-12-15T17:25:22","date_gmt":"2022-12-15T08:25:22","guid":{"rendered":"https:\/\/okamurax.com\/?p=5434"},"modified":"2022-12-21T08:28:28","modified_gmt":"2022-12-20T23:28:28","slug":"vba-%e5%8a%b4%e5%83%8d%e6%99%82%e9%96%93%e8%a8%88%e7%ae%97","status":"publish","type":"post","link":"https:\/\/appbay.org\/?p=5434","title":{"rendered":"VBA \u52b4\u50cd\u6642\u9593\u8a08\u7b97"},"content":{"rendered":"<p>9:00\u59cb\u696d\u306a\u30898:59:59\u307e\u3067OK\u3067\u30019:00\u306e\u6253\u523b\u306f\u30c0\u30e1\u3068\u3044\u3046\u8a71\u3092\u6642\u3005\u805e\u304f\u304c\u3001\u79d2\u307e\u3067\u8a18\u9332\u3057\u306a\u3044\u5834\u5408\u306ffrom-to\u3069\u3061\u3089\u3082\u79d2\u3092\u5207\u308a\u6368\u3066\u308b\u306e\u3067\u30019:00\u306e\u6253\u523b\u306f9:00:00\u3068\u8003\u3048\u3066\u3001\u554f\u984c\u306a\u3044\u3068\u3057\u305f\u65b9\u304c\u5206\u304b\u308a\u3084\u3059\u3044\u3002<\/p>\n<p>\uff0a\uff0a\uff0a<\/p>\n<p>If\u306a\u3069\u3067\u6642\u523b\u306e\u6761\u4ef6\u3092\u66f8\u304f\u3068\u304d\u3001\u6d6e\u52d5\u5c0f\u6570\u306e\u95a2\u4fc2\u3067\u8aa4\u5dee\u304c\u751f\u3058\u308b\u3053\u3068\u304c\u3042\u308b\u3002\u30bb\u30eb\u306b\u5165\u529b\u3055\u308c\u305f\u5024\u3084TimeValue()\u306e\u5024\u305d\u306e\u307e\u307e\u306a\u3089\u554f\u984c\u306a\u3044\u3051\u3069\u3001\u4f55\u304b\u3057\u3089\u8a08\u7b97\u3057\u305f\u5834\u5408\u306fDateDiff()\u3001\u3042\u308b\u3044\u306fTimeValue()\u3092\u7d4c\u7531\u3055\u305b\u308b\u306a\u3069\u3057\u305f\u307b\u3046\u304c\u5b89\u5168\u3002<\/p>\n<pre class=\"lang:vb decode:true \">Sub test1()\r\n\r\nDim tmp As Date\r\n\r\ntmp = TimeSerial(\"1\", \"0\", \"0\")\r\n\r\nDo While tmp &lt; TimeSerial(\"22\", \"00\", \"0\")\r\n  tmp = DateAdd(\"n\", 1, tmp)\r\nLoop\r\n\r\nDebug.Print Format(tmp, \"hh:mm\") ' \u672c\u6765\u306a\u308922:00\u306b\u306a\u308b\u306f\u305a\u306a\u306e\u306b22:01\u306b\u306a\u308b\r\n\r\nEnd Sub\r\n\r\nSub test2()\r\n\r\ntmp = TimeSerial(\"1\", \"0\", \"0\")\r\n\r\nDo While tmp &lt; TimeSerial(\"22\", \"00\", \"0\")\r\n  \r\n  tmp = DateAdd(\"n\", 1, tmp)\r\n  \r\n  If tmp = TimeSerial(\"22\", \"00\", \"0\") Then\r\n    Debug.Print \"Match\" ' test1\u3068\u540c\u3058\u304f\u30de\u30c3\u30c1\u3057\u306a\u3044(\u8868\u793a\u3055\u308c\u306a\u3044)\r\n  End If\r\n  \r\nLoop\r\n\r\n' \u30bb\u30eb\u306b\u5165\u529b\u3055\u308c\u305f\"22:00\"\u3084TimeValue()\u3067\u4f5c\u3089\u308c\u305f22:00\u306a\u3089\u30de\u30c3\u30c1\u3059\u308b\r\n\r\nEnd Sub\r\n\r\nFunction test3(tmp As Date)\r\n\r\n' tmp\u306f\u30bb\u30eb\u306b\u5165\u529b\u3055\u308c\u305f\"22:00\"\r\n\r\n  If tmp = TimeSerial(\"22\", \"00\", \"0\") Then\r\n    Debug.Print \"Match\" ' \u30de\u30c3\u30c1\u3059\u308b\r\n  End If\r\n  \r\n  If TimeValue(\"22:00\") = TimeSerial(\"22\", \"00\", \"0\") Then Debug.Print \"Match\" ' \u30de\u30c3\u30c1\u3059\u308b\r\n\r\nEnd Function\r\n\r\nSub test4()\r\n \r\n' \u4eca\u56de\u306e\u3088\u3046\u306b\u30eb\u30fc\u30d7\u3067\u5206\u3092\u52a0\u7b97\u3057\u305f\u5834\u5408\u306e\u8aa4\u5dee\u306f\u30de\u30a4\u30ca\u30b9\u306e\u3088\u3046\u3067\u3001\r\n' to\u306f\u30de\u30c3\u30c1\u3001from\u304c\u30de\u30c3\u30c1\u3057\u306a\u3044\u3002\r\n\r\nDim tmp As Date\r\n \r\ntmp = TimeSerial(\"1\", \"0\", \"0\")\r\n \r\nDo While tmp &lt; TimeSerial(\"23\", \"00\", \"0\")\r\n\r\n  If tmp &gt;= TimeSerial(\"22\", \"00\", \"0\") And tmp &lt;= TimeSerial(\"22\", \"15\", \"0\") Then\r\n    Debug.Print Format(tmp, \"hh:mm\") ' 22:01 - 22:15\r\n  End If\r\n  \r\n  tmp = DateAdd(\"n\", 1, tmp)\r\nLoop\r\n \r\nEnd Sub\r\n\r\nFunction Adjust(tmp)\r\n\r\n' \u3053\u308c\u3067\u8a08\u7b97\u8aa4\u5dee\u3092\u306a\u304f\u3059\r\n\r\nAdjust = TimeValue(Format(tmp, \"hh:mm\"))\r\n\r\nEnd Function<\/pre>\n<p>\uff0a\uff0a\uff0a<\/p>\n<p>\u52b4\u50cd\u6642\u9593\u306e\u8a08\u7b97\u306715\u5206\u5358\u4f4d\u5207\u308a\u6368\u3066\u306e\u51e6\u7406\u3092\u3057\u3088\u3046\u3068\u3057\u305f\u304c(\u3061\u306a\u307f\u306b\u3001\u3053\u308c\u306f\u7ba1\u7406\u4f1a\u8a08\u306e\u8a71\u3067\u7d66\u4e0e\u3067\u306f\u5207\u308a\u6368\u3066\u3061\u3083\u30c0\u30e1)\u4f11\u61a9\u304c15\u5206\u5358\u4f4d\u3067\u306f\u306a\u304b\u3063\u305f\u306e\u3067\u3001\u4f11\u61a9\u3092\u631f\u3093\u306715\u5206\u3092\u30ab\u30a6\u30f3\u30c8\u3057\u3066\u96c6\u8a08\u3059\u308b\u3053\u3068\u306b\u3057\u305f\u3002<\/p>\n<p>\u3053\u3046\u3044\u3046\u8a08\u7b97\u3067\u30eb\u30fc\u30d7\u3092\u56de\u3059\u306e\u306f\u60aa\u624b\u3060\u3068\u601d\u3046\u3051\u3069\u3001\u96e3\u3057\u3044\u3053\u3068\u306f\u3067\u304d\u306a\u3044\u306e\u3067\u3001\u3068\u308a\u3042\u3048\u305a\u30eb\u30fc\u30d7\u3067\u8a08\u7b97\u3057\u3066\u3057\u307e\u3063\u305f\u3002\u3002<\/p>\n<pre class=\"lang:vb decode:true \">Sub test()\r\n\r\nCall \u52b4\u50cd\u6642\u9593(\"8:00\", \"11:00\", \"9:00\", \"9:02\")\r\n\r\nEnd Sub\r\nFunction Adjust(tmp)\r\n\r\nAdjust = TimeValue(Format(tmp, \"hh:mm\"))\r\n\r\nEnd Function\r\n\r\nFunction \u52b4\u50cd\u6642\u9593(\u51fa\u52e4 As Date, \u9000\u52e4 As Date, \u5916\u51fa As Date, \u518d\u5165 As Date)\r\n\r\n' \u51fa\u52e4\u8abf\u6574\r\n\r\nIf DateDiff(\"n\", \u51fa\u52e4, TimeSerial(\"8\", \"0\", \"0\")) &gt; 0 Then \u51fa\u52e4 = TimeSerial(\"8\", \"0\", \"0\")\r\n\r\nworkingHour = 0 ' 15\u5206\u3054\u3068\u306b0.25\u30c1\u30e3\u30fc\u30b8\u3059\u308b\r\ncounter = 0\r\nDo While DateDiff(\"n\", \u51fa\u52e4, \u9000\u52e4) &gt; 0 ' \u51fa\u52e4\u3092\u76f4\u63a5\u52a0\u7b97\u3057\u3066\u3044\u308b\r\n\r\n  \u51fa\u52e4 = Adjust(\u51fa\u52e4)\r\n  If \u51fa\u52e4 &gt; TimeSerial(\"10\", \"0\", \"0\") And \u51fa\u52e4 &lt;= TimeSerial(\"10\", \"10\", \"0\") Then\r\n  ElseIf \u51fa\u52e4 &gt; TimeSerial(\"12\", \"0\", \"0\") And \u51fa\u52e4 &lt;= TimeSerial(\"12\", \"40\", \"0\") Then\r\n  ElseIf \u51fa\u52e4 &gt; TimeSerial(\"15\", \"0\", \"0\") And \u51fa\u52e4 &lt;= TimeSerial(\"15\", \"10\", \"0\") Then\r\n  ElseIf \u51fa\u52e4 &gt; \u5916\u51fa And \u51fa\u52e4 &lt;= \u518d\u5165 Then\r\n  Else\r\n    'Debug.Print Format(\u51fa\u52e4, \"hh:mm\") &amp; \"_\" &amp; counter\r\n    counter = counter + 1\r\n  End If\r\n \r\n  If counter = 15 Then\r\n    workingHour = workingHour + 0.25\r\n    counter = 0\r\n  End If\r\n\r\ncontinue:\r\n\u51fa\u52e4 = DateAdd(\"n\", 1, \u51fa\u52e4)\r\nLoop\r\n\r\n\u52b4\u50cd\u6642\u9593 = workingHour\r\n\r\nEnd Function\r\n\r\n'8:00\r\n'10:00 - 10:10\r\n'12:00 - 12:40\r\n'15:00 - 15:10\r\n'17:00\r\n\r\n'11:55\uff5e12:50\u30671\u30c1\u30e3\u30fc\u30b8\u3068\u3057\u3066\u4e2d\u306b40\u5206\u306e\u4f11\u61a9\r\n'14:50\uff5e15:15\u30671\u30c1\u30e3\u30fc\u30b8\u3068\u3057\u3066\u4e2d\u306b10\u5206\u306e\u4f11\u61a9\r\n<\/pre>\n<p>\uff0a\uff0a\uff0a<\/p>\n<p>\u6df1\u591c\u3092\u5165\u529b\u3059\u308b\u5834\u5408\u3001VBA\u3067\u306f[h]:mm\u3068\u3044\u3046\u8868\u8a18\u3092\u6271\u3048\u306a\u3044\u3057\u3001\u30bb\u30eb\u3078\u306e\u5165\u529b\u3067\u308225:00\u306a\u3069\u5165\u529b\u3059\u308b\u3068\u81ea\u52d5\u3067\u65e5\u4ed8\u304c\u5165\u3063\u3066\u3057\u307e\u3046\u306e\u3067\u3001\u30bb\u30eb\u306e\u66f8\u5f0f\u3092\u6587\u5b57\u5217\u306b\u5909\u3048\u3066VBA\u3067\u65e5\u4ed8\u3092\u4ed8\u52a0\u3057\u3066\u307f\u305f\u3002(\u4eca\u56de\u3001\u30bb\u30eb\u306f\u6642\u523b\u3060\u3051\u5165\u529b\u3057\u3066\u3044\u305f\u304c\u3001\u30bb\u30eb\u306b\u65e5\u4ed8\u307e\u3067\u5165\u308c\u3066\u3057\u307e\u3063\u305f\u307b\u3046\u304c\u7c21\u5358\u304b\u3082\u77e5\u308c\u306a\u3044)<\/p>\n<pre class=\"lang:vb decode:true \">Function FormatTime(arg)\r\n\r\nDim h As Integer\r\nDim m As Integer\r\n\r\nDim baseDate As Date: baseDate = \"1999\/1\/1\"\r\nDim tmp As Date\r\n\r\nh = CInt(Left(arg, InStr(arg, \":\") - 1))\r\nm = CInt(Mid(arg, InStr(arg, \":\") + 1))\r\n\r\nIf h &gt;= 24 Then\r\n    tmp = DateAdd(\"d\", 1, baseDate)\r\n    h = h - 24\r\n  Else\r\n    tmp = baseDate\r\nEnd If\r\n\r\nFormatTime = CDate(tmp &amp; \" \" &amp; h &amp; \":\" &amp; m)\r\n\r\nEnd Function\r\n\r\nFunction ArgCheck(arg)\r\n\r\n' :\u304c\u542b\u307e\u308c\u3066\u3044\u308b\u304b\u3069\u3046\u304b\r\n\r\nIf InStr(arg, \":\") = 0 Then\r\n  ArgCheck = False\r\n  Exit Function\r\nEnd If\r\n\r\n' :\u306e\u5de6\u304c\u6570\u5024\u304b\u3069\u3046\u304b\r\n\r\nhh = Left(arg, InStr(arg, \":\") - 1)\r\nIf IsNumeric(hh) = False Then\r\n  ArgCheck = False\r\n  Exit Function\r\nEnd If\r\n\r\n' :\u306e\u53f3\u304c\u6570\u5024\u304b\u3069\u3046\u304b\r\n\r\nmm = Mid(arg, InStr(arg, \":\") + 1)\r\nIf IsNumeric(mm) = False Then\r\n  ArgCheck = False\r\n  Exit Function\r\nEnd If\r\n\r\nArgCheck = True\r\n\r\nEnd Function\r\n\r\nFunction GetWorkTime(arg1 As String, arg2 As String, arg3 As String, arg4 As String, arg5 As String, arg6 As String, arg7 As String)\r\n\r\nIf arg1 &lt;&gt; \"\" And arg2 &lt;&gt; \"\" Then\r\n  If ArgCheck(arg1) = False Then Err.Raise (1)\r\n  If ArgCheck(arg2) = False Then Err.Raise (1)\r\nElse\r\n  GetWorkTime = \"\"\r\n  Exit Function\r\nEnd If\r\n\r\nIf arg3 &lt;&gt; \"\" And arg4 &lt;&gt; \"\" Then\r\n  If ArgCheck(arg3) = False Then Err.Raise (1)\r\n  If ArgCheck(arg4) = False Then Err.Raise (1)\r\nElse\r\n  arg3 = \"9:00\" ' dummy\r\n  arg4 = \"9:00\"\r\nEnd If\r\n\r\nDim \u51fa\u52e4 As Date\r\nDim \u9000\u52e4 As Date\r\nDim \u5916\u51fa As Date\r\nDim \u518d\u5165 As Date\r\n\r\n\u51fa\u52e4 = FormatTime(arg1)\r\n\u9000\u52e4 = FormatTime(arg2)\r\n\u5916\u51fa = FormatTime(arg3)\r\n\u518d\u5165 = FormatTime(arg4)\r\n\r\n' \u51fa\u52e4\u8abf\u6574\r\n\r\nIf DateDiff(\"n\", \u51fa\u52e4, TimeSerial(\"8\", \"0\", \"0\")) &gt; 0 Then \u51fa\u52e4 = TimeSerial(\"8\", \"0\", \"0\")\r\n\r\nworkingHour = 0 ' 15\u5206\u3054\u3068\u306b0.25\u30c1\u30e3\u30fc\u30b8\u3059\u308b\r\ncounter = 0\r\nDo While DateDiff(\"n\", \u51fa\u52e4, \u9000\u52e4) &gt; 0 ' \u51fa\u52e4\u3092\u76f4\u63a5\u52a0\u7b97\u3057\u3066\u3044\u308b\r\n\r\n  If \u51fa\u52e4 &gt; CDate(\"1999\/1\/1 10:00:00\") And \u51fa\u52e4 &lt;= CDate(\"1999\/1\/1 10:10:00\") Then\r\n  \r\n    If arg5 = \"\u672a\" Then counter = counter + 1\r\n    \r\n  ElseIf \u51fa\u52e4 &gt; CDate(\"1999\/1\/1 12:00:00\") And \u51fa\u52e4 &lt;= CDate(\"1999\/1\/1 12:40:00\") Then\r\n  \r\n    If arg6 = \"\u672a\" Then counter = counter + 1\r\n    \r\n  ElseIf \u51fa\u52e4 &gt; CDate(\"1999\/1\/1 15:00:00\") And \u51fa\u52e4 &lt;= CDate(\"1999\/1\/1 15:10:00\") Then\r\n  \r\n    If arg7 = \"\u672a\" Then counter = counter + 1\r\n    \r\n  ElseIf \u51fa\u52e4 &gt; \u5916\u51fa And \u51fa\u52e4 &lt;= \u518d\u5165 Then\r\n  \r\n  Else\r\n  \r\n    counter = counter + 1\r\n    \r\n  End If\r\n \r\n  If counter = 15 Then\r\n    workingHour = workingHour + 0.25\r\n    counter = 0\r\n  End If\r\n\r\ncontinue:\r\n\u51fa\u52e4 = DateAdd(\"n\", 1, \u51fa\u52e4)\r\nLoop\r\n\r\nGetWorkTime = workingHour\r\n\r\nEnd Function\r\n\r\n'8:00\r\n'10:00 - 10:10\r\n'12:00 - 12:40\r\n'15:00 - 15:10\r\n'17:00\r\n\r\n'11:55\uff5e12:50\u30671\u30c1\u30e3\u30fc\u30b8\u3068\u3057\u3066\u4e2d\u306b40\u5206\u306e\u4f11\u61a9\r\n'14:50\uff5e15:15\u30671\u30c1\u30e3\u30fc\u30b8\u3068\u3057\u3066\u4e2d\u306b10\u5206\u306e\u4f11\u61a9\r\n<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>9:00\u59cb\u696d\u306a\u30898:59:59\u307e\u3067OK\u3067\u30019:00\u306e\u6253\u523b\u306f\u30c0\u30e1\u3068\u3044\u3046\u8a71\u3092\u6642\u3005\u805e\u304f\u304c\u3001\u79d2\u307e\u3067\u8a18\u9332\u3057\u306a\u3044\u5834\u5408\u306ffrom-to\u3069\u3061\u3089\u3082\u79d2\u3092\u5207\u308a\u6368\u3066\u308b\u306e\u3067\u30019:00\u306e\u6253\u523b\u306f9:00:00\u3068\u8003\u3048\u3066\u3001\u554f\u984c\u306a\u3044\u3068\u3057\u305f\u65b9\u304c\u5206\u304b\u308a\u3084\u3059\u3044\u3002 &hellip; <\/p>\n<p class=\"link-more\"><a href=\"https:\/\/appbay.org\/?p=5434\" class=\"more-link\"><span class=\"screen-reader-text\">&#8220;VBA \u52b4\u50cd\u6642\u9593\u8a08\u7b97&#8221; \u306e<\/span>\u7d9a\u304d\u3092\u8aad\u3080<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[5],"class_list":["post-5434","post","type-post","status-publish","format-standard","hentry","category-1","tag-vba"],"_links":{"self":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/5434","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5434"}],"version-history":[{"count":13,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/5434\/revisions"}],"predecessor-version":[{"id":5456,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/5434\/revisions\/5456"}],"wp:attachment":[{"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5434"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5434"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5434"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}