{"id":2419,"date":"2022-06-14T20:57:16","date_gmt":"2022-06-14T11:57:16","guid":{"rendered":"http:\/\/oreoreki.gotdns.ch\/?p=2419"},"modified":"2022-06-14T21:16:59","modified_gmt":"2022-06-14T12:16:59","slug":"post-2419","status":"publish","type":"post","link":"http:\/\/oreoreki.gotdns.ch\/?p=2419","title":{"rendered":"ExcelVBA\u306eMid\u95a2\u6570\u306f\u5207\u308a\u629c\u304f\u3060\u3051\u3058\u3083\u306a\u3044"},"content":{"rendered":"<p><span class=\"embed-youtube\" style=\"text-align:center; display: block;\"><iframe loading=\"lazy\" class=\"youtube-player\" width=\"750\" height=\"422\" src=\"https:\/\/www.youtube.com\/embed\/1Smm6orvj1Q?version=3&#038;rel=1&#038;showsearch=0&#038;showinfo=1&#038;iv_load_policy=1&#038;fs=1&#038;hl=ja&#038;autohide=2&#038;wmode=transparent\" allowfullscreen=\"true\" style=\"border:0;\" sandbox=\"allow-scripts allow-same-origin allow-popups allow-presentation allow-popups-to-escape-sandbox\"><\/iframe><\/span><\/p>\n<div class=\"hcb_wrap\">\n<p>ExcelVBA\u306eMid\u95a2\u6570\u306f\u5207\u308a\u629c\u304f\u3060\u3051\u3058\u3083\u306a\u3044<br \/>\n<span class=\"bold-red\">\u3053\u306e\u52d5\u753b\u306e\u30de\u30af\u30ed\u306fWindows11\/Office2013\u3067\u4f5c\u6210\u3057\u3066\u307e\u3059\u3002<br \/>\n<\/span><\/p>\n<h2><\/h2>\n<pre class=\"prism line-numbers lang-ts\" data-lang=\"TypeScript\"><code>Sub sample_1()\r\n\r\n    a = \"A12345ABC\": Debug.Print \"\u5143\u30c7\u30fc\u30bf :\" & a\r\n    \r\n    ' a\u306e2\u6587\u5b57\u76ee\u304b\u30895\u6587\u5b57\u5206\u3092\u5207\u308a\u629c\u304f\r\n    Debug.Print \"\u30d1\u30bf\u30fc\u30f31:\" & Mid(a, 2, 5)\r\n\r\n    ' a\u306e2\u6587\u5b57\u76ee\u304b\u3089\u4ee5\u4e0b\u5168\u90e8\u3092\u5207\u308a\u629c\u304f\r\n    Debug.Print \"\u30d1\u30bf\u30fc\u30f32:\" & Mid(a, 2)\r\n\r\n    ' a\u306e2\u6587\u5b57\u76ee\u304b\u3089100\u6587\u5b57\u5206\u3092\u5207\u308a\u629c\u304f\r\n    Debug.Print \"\u30d1\u30bf\u30fc\u30f33:\" & Mid(a, 2, 100)\r\n\r\nEnd Sub\r\n\r\nSub sample_2()\r\n\r\n    a = \"A00000ABC\": Debug.Print \"\u5143\u30c7\u30fc\u30bf :\" & a\r\n    \r\n    ' a\u306e2\u6587\u5b57\u76ee\u304b\u30895\u6587\u5b57\u5206\u306e\u30a8\u30ea\u30a2\u306b12345\u3092\u5165\u308c\u308b\r\n    Mid(a, 2, 5) = 12345\r\n    Debug.Print \"\u30d1\u30bf\u30fc\u30f31:\" & a\r\n\r\n    ' a\u306e2\u6587\u5b57\u76ee\u304b\u30895\u6587\u5b57\u5206\u306e\u30a8\u30ea\u30a2\u306b123\u3092\u5165\u308c\u308b\r\n    a = \"A00000ABC\"\r\n    Mid(a, 2, 5) = 123\r\n    Debug.Print \"\u30d1\u30bf\u30fc\u30f32:\" & a\r\n\r\n    ' a\u306e2\u6587\u5b57\u76ee\u304b\u3089123\u3092\u5165\u308c\u308b\r\n    a = \"A00000ABC\"\r\n    Mid(a, 2) = 123\r\n    Debug.Print \"\u30d1\u30bf\u30fc\u30f33:\" & a\r\n\r\n    ' a\u306e2\u6587\u5b57\u76ee\u304b\u30895\u6587\u5b57\u5206\u306e\u30a8\u30ea\u30a2\u306b123456789\u3092\u5165\u308c\u308b\r\n    a = \"A00000ABC\"\r\n    Mid(a, 2, 5) = 123456789\r\n    Debug.Print \"\u30d1\u30bf\u30fc\u30f34:\" & a\r\n\r\nEnd Sub\r\nPublic Sub VBA100_65_1()\r\n    Dim dat As String\r\n    Dim lLen As Long, c, r, fso, file\r\n    Set fso = CreateObject(\"Scripting.FileSystemObject\")\r\n    CurrentDirectory = ActiveWorkbook.Path\r\n    Set file = fso.OpenTextFile(fso.BuildPath(CurrentDirectory, \"text.txt\"), 2, True)\r\n    For r = 2 To Range(\"A1\").CurrentRegion.Rows.Count\r\n\r\n        dat = \"\"\r\n        For c = 1 To Range(\"A1\").CurrentRegion.Columns.Count\r\n            'Debug.Print lLen, Sheets(2).Cells(c + 1, 1), Sheets(2).Cells(c + 1, 2), Sheets(2).Cells(c + 1, 3), Sheets(1).Cells(r, c)\r\n            Select Case Sheets(2).Cells(c + 1, 2)\r\n                Case \"N\"   '0\u3065\u3081\u6570\u5b57\r\n                    dat = dat & Format(Sheets(1).Cells(r, c), String(Sheets(2).Cells(c + 1, 3), \"0\"))\r\n                Case \"C\"    '\u5de6\u8a70\u3081\u6587\u5b57\r\n                    dat = dat & Left(Sheets(1).Cells(r, c) + String(Sheets(2).Cells(c + 1, 3), \" \"), Sheets(2).Cells(c + 1, 3))\r\n            End Select\r\n\r\n        Next\r\n        Debug.Print dat & \"*\"  '\u52d5\u4f5c\u78ba\u8a8d\u7528\r\n        file.writeline dat\r\n    Next\r\n    file.Close\r\n    Set file = Nothing\r\nEnd Sub\r\nPublic Sub VBA100_65_2()\r\n    Dim dat As String * 100   '\u5c11\u3057\u591a\u3081\u306b\u53d6\u3063\u3066\u304a\u304f\r\n    Dim lLen As Long, c, r, fso, file\r\n    Set fso = CreateObject(\"Scripting.FileSystemObject\")\r\n    CurrentDirectory = ActiveWorkbook.Path\r\n    Set file = fso.OpenTextFile(fso.BuildPath(CurrentDirectory, \"text.txt\"), 2, True)\r\n    For r = 2 To Range(\"A1\").CurrentRegion.Rows.Count\r\n        lLen = 1\r\n        dat = \"\"\r\n        For c = 1 To Range(\"A1\").CurrentRegion.Columns.Count\r\n            'Debug.Print lLen, Sheets(2).Cells(c + 1, 1), Sheets(2).Cells(c + 1, 2), Sheets(2).Cells(c + 1, 3), Sheets(1).Cells(r, c)\r\n            Select Case Sheets(2).Cells(c + 1, 2)\r\n                Case \"N\"   '0\u3065\u3081\u6570\u5b57\r\n                    Mid(dat, lLen, Sheets(2).Cells(c + 1, 3)) = Format(Sheets(1).Cells(r, c), String(Sheets(2).Cells(c + 1, 3), \"0\"))\r\n                Case \"C\"    '\u5de6\u8a70\u3081\u6587\u5b57\r\n                    Mid(dat, lLen, Sheets(2).Cells(c + 1, 3)) = Sheets(1).Cells(r, c)\r\n            End Select\r\n            lLen = lLen + Sheets(2).Cells(c + 1, 3)\r\n        Next\r\n            Debug.Print Mid(dat, 1, lLen - 1) & \"*\"    '\u52d5\u4f5c\u78ba\u8a8d\u7528\r\n            file.writeline Mid(dat, 1, lLen - 1)\r\n    Next\r\n    file.Close\r\n    Set file = Nothing\r\nEnd Sub\r\n\r\n<\/code><\/pre>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>ExcelVBA\u306eMid\u95a2\u6570\u306f\u5207\u308a\u629c\u304f\u3060\u3051\u3058\u3083\u306a\u3044 \u3053\u306e\u52d5\u753b\u306e\u30de\u30af\u30ed\u306fWindows11\/Offic [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":2421,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[34],"tags":[],"class_list":["post-2419","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-vba"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"http:\/\/oreoreki.gotdns.ch\/wp-content\/uploads\/sites\/2\/2022\/06\/excelvbamid.jpg","jetpack_shortlink":"https:\/\/wp.me\/p9WqRX-D1","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"http:\/\/oreoreki.gotdns.ch\/index.php?rest_route=\/wp\/v2\/posts\/2419","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/oreoreki.gotdns.ch\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/oreoreki.gotdns.ch\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/oreoreki.gotdns.ch\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/oreoreki.gotdns.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2419"}],"version-history":[{"count":1,"href":"http:\/\/oreoreki.gotdns.ch\/index.php?rest_route=\/wp\/v2\/posts\/2419\/revisions"}],"predecessor-version":[{"id":2420,"href":"http:\/\/oreoreki.gotdns.ch\/index.php?rest_route=\/wp\/v2\/posts\/2419\/revisions\/2420"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/oreoreki.gotdns.ch\/index.php?rest_route=\/wp\/v2\/media\/2421"}],"wp:attachment":[{"href":"http:\/\/oreoreki.gotdns.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2419"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/oreoreki.gotdns.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2419"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/oreoreki.gotdns.ch\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2419"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}