{"id":9217,"date":"2017-10-13T17:56:07","date_gmt":"2017-10-13T08:56:07","guid":{"rendered":"https:\/\/www.ka-net.org\/blog\/?p=9217"},"modified":"2017-10-13T17:56:07","modified_gmt":"2017-10-13T08:56:07","slug":"%e5%90%8d%e5%89%8d%e4%bb%98%e3%81%8d%e3%82%bb%e3%83%ab%e7%af%84%e5%9b%b2%e3%81%ab%e3%82%b3%e3%83%a1%e3%83%b3%e3%83%88%e3%82%92%e4%bb%98%e3%81%91%e3%82%8bvba%e3%83%9e%e3%82%af%e3%83%ad","status":"publish","type":"post","link":"https:\/\/www.ka-net.org\/blog\/?p=9217","title":{"rendered":"\u540d\u524d\u4ed8\u304d\u30bb\u30eb(\u7bc4\u56f2)\u306b\u30b3\u30e1\u30f3\u30c8\u3092\u4ed8\u3051\u308bVBA\u30de\u30af\u30ed"},"content":{"rendered":"<p>Excel\u3067\u30de\u30cb\u30e5\u30a2\u30eb\u3092\u4f5c\u6210\u3059\u308b\u969b\u3001\u540d\u524d\u4ed8\u304d\u30bb\u30eb\u7bc4\u56f2\u3092\u660e\u8a18\u3059\u308b\u5fc5\u8981\u304c\u3042\u3063\u305f\u306e\u3067\u3001\u30b3\u30e1\u30f3\u30c8\u3092\u4ed8\u52a0\u3059\u308b\u30de\u30af\u30ed\u3092\u66f8\u3044\u3066\u307f\u307e\u3057\u305f\u3002<\/p>\n<pre class=\"brush: vb; title: ; notranslate\" title=\"\">Option Explicit\r\n\r\nPublic Sub AddCommentNamedRanges()\r\n'\u540d\u524d\u4ed8\u304d\u30bb\u30eb(\u7bc4\u56f2)\u306b\u30b3\u30e1\u30f3\u30c8\u3092\u4ed8\u3051\u308b\r\n  Dim n As Excel.Name\r\n  \r\n  For Each n In ActiveWorkbook.Names\r\n    With n.RefersToRange\r\n      '\u30a2\u30af\u30c6\u30a3\u30d6\u30b7\u30fc\u30c8\u306e\u307f\u51e6\u7406\r\n      If .Worksheet.Name = ActiveSheet.Name Then\r\n        If .Cells.Count &gt; 1 Then\r\n          On Error Resume Next\r\n          .Item(1, 1).Comment.Delete\r\n          On Error GoTo 0\r\n          .Item(1, 1).AddComment(n.NameLocal &amp; vbNewLine &amp; .Address).Visible = True\r\n        Else\r\n          On Error Resume Next\r\n          .Comment.Delete\r\n          On Error GoTo 0\r\n          .AddComment(n.NameLocal &amp; vbNewLine &amp; .Address).Visible = True\r\n        End If\r\n      End If\r\n    End With\r\n  Next\r\n  'ActiveSheet.PageSetup.PrintComments = xlPrintInPlace '\u30da\u30fc\u30b8\u8a2d\u5b9a(\u30b3\u30e1\u30f3\u30c8\uff1a\u753b\u9762\u8868\u793a\u30a4\u30e1\u30fc\u30b8)\r\nEnd Sub\r\n<\/pre>\n<p>\u4e0a\u8a18\u30de\u30af\u30ed\u3092\u5b9f\u884c\u3059\u308b\u3068\u3001\u30a2\u30af\u30c6\u30a3\u30d6\u30b7\u30fc\u30c8\u4e0a\u306e\u540d\u524d\u4ed8\u304d\u30bb\u30eb\u7bc4\u56f2\u306b\u3001\u540d\u524d\u3068\u30a2\u30c9\u30ec\u30b9\u3092\u30b3\u30e1\u30f3\u30c8\u3068\u3057\u3066\u8ffd\u52a0\u3057\u307e\u3059\u3002<\/p>\n<p><a href=\"https:\/\/www.ka-net.org\/blog\/wp-content\/uploads\/AddCommentNamedRanges_01.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.ka-net.org\/blog\/wp-content\/uploads\/AddCommentNamedRanges_01.jpg\" alt=\"\" width=\"693\" height=\"501\" class=\"alignnone size-full wp-image-9218\" srcset=\"https:\/\/www.ka-net.org\/blog\/wp-content\/uploads\/AddCommentNamedRanges_01.jpg 693w, https:\/\/www.ka-net.org\/blog\/wp-content\/uploads\/AddCommentNamedRanges_01-300x217.jpg 300w\" sizes=\"auto, (max-width: 693px) 100vw, 693px\" \/><\/a><\/p>\n<p>\u3053\u3046\u3044\u3063\u305f\u624b\u52d5\u3067\u884c\u3046\u3068\u9762\u5012\u306a\u4f5c\u696d\u306f\u3001\u30de\u30af\u30ed\u3092\u4f7f\u3063\u305f\u65b9\u304c\u52b9\u7387\u7684\u3067\u3059\u306d\uff01<\/p>\n<div id=\"single_banner_area2\" class=\"clearfix one_banner\">\n<div class=\"single_banner single_banner_left\">\n<script async src=\"https:\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\r\n<!-- \u8a18\u4e8b\u4e2d(\u30c7\u30a3\u30b9\u30d7\u30ec\u30a4\u5e83\u544a\u30e6\u30cb\u30c3\u30c8) -->\r\n<ins class=\"adsbygoogle\"\r\n     style=\"display:block\"\r\n     data-ad-client=\"ca-pub-7306936664602087\"\r\n     data-ad-slot=\"5429724765\"\r\n     data-ad-format=\"auto\"\r\n     data-full-width-responsive=\"true\"><\/ins>\r\n<script>\r\n     (adsbygoogle = window.adsbygoogle || []).push({});\r\n<\/script>\n<\/div>\n<div class=\"single_banner single_banner_right\">\n<a href=\"\" target=\"_blank\"><img decoding=\"async\" src=\"\" alt=\"\" title=\"\" \/><\/a>\n<\/div>\n<\/div>\n\n","protected":false},"excerpt":{"rendered":"Excel\u3067\u30de\u30cb\u30e5\u30a2\u30eb\u3092\u4f5c\u6210\u3059\u308b\u969b\u3001\u540d\u524d\u4ed8\u304d\u30bb\u30eb\u7bc4\u56f2\u3092\u660e\u8a18\u3059\u308b\u5fc5\u8981\u304c\u3042\u3063\u305f\u306e\u3067\u3001\u30b3\u30e1\u30f3\u30c8\u3092\u4ed8\u52a0\u3059\u308b\u30de\u30af\u30ed\u3092\u66f8\u3044\u3066\u307f\u307e\u3057\u305f\u3002 Option Explicit Public Sub AddCommentNamedRanges [&hellip;]","protected":false},"author":1,"featured_media":7112,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"[\u540d\u524d\u4ed8\u304d\u30bb\u30eb(\u7bc4\u56f2)\u306b\u30b3\u30e1\u30f3\u30c8\u3092\u4ed8\u3051\u308bVBA\u30de\u30af\u30ed]","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":[4,48],"tags":[],"class_list":["post-9217","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-office","category-excel-office"],"aioseo_notices":[],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.ka-net.org\/blog\/wp-content\/uploads\/eyecatch-ExcelVBA.png","jetpack_shortlink":"https:\/\/wp.me\/p4UZZr-2oF","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.ka-net.org\/blog\/index.php?rest_route=\/wp\/v2\/posts\/9217","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ka-net.org\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ka-net.org\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ka-net.org\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ka-net.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=9217"}],"version-history":[{"count":1,"href":"https:\/\/www.ka-net.org\/blog\/index.php?rest_route=\/wp\/v2\/posts\/9217\/revisions"}],"predecessor-version":[{"id":9219,"href":"https:\/\/www.ka-net.org\/blog\/index.php?rest_route=\/wp\/v2\/posts\/9217\/revisions\/9219"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.ka-net.org\/blog\/index.php?rest_route=\/wp\/v2\/media\/7112"}],"wp:attachment":[{"href":"https:\/\/www.ka-net.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=9217"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ka-net.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=9217"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ka-net.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=9217"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}