{"id":7956,"date":"2018-03-08T21:04:54","date_gmt":"2018-03-08T20:04:54","guid":{"rendered":"https:\/\/www.myexcelonline.com\/blog\/?p=7794"},"modified":"2024-02-06T16:19:35","modified_gmt":"2024-02-06T15:19:35","slug":"where-to-store-your-vba-macro-in-this-workbook-or-personal-macro-workbook","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/where-to-store-your-vba-macro-in-this-workbook-or-personal-macro-workbook\/","title":{"rendered":"Where To Store Your VBA Macro In? This Workbook or Personal Macro Workbook"},"content":{"rendered":"<p>A lot of Excel users get confused when they are recording an Excel <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/101-excel-macros-examples\/\" target=\"_blank\" rel=\"noopener\" title=\"Macro\" data-wpil-keyword-link=\"linked\">Macro<\/a> and they get prompted where they want to store their Excel Macro in?<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2018\/03\/2018-03-08_20-33-39.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"\" class=\"alignnone size-full wp-image-7795\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2018\/03\/2018-03-08_20-33-39.png\" alt=\"Where To Store Your VBA Macro In? This Workbook or Personal Macro Workbook\" width=\"344\" height=\"283\"><\/a><\/p>\n<p>&nbsp;<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_57_1 counter-flat ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title \" >Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/where-to-store-your-vba-macro-in-this-workbook-or-personal-macro-workbook\/#Store_Macro_In\" title=\"Store Macro In\">Store Macro In<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/where-to-store-your-vba-macro-in-this-workbook-or-personal-macro-workbook\/#This_Workbook\" title=\"This Workbook\">This Workbook<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/where-to-store-your-vba-macro-in-this-workbook-or-personal-macro-workbook\/#Personal_Macro_Workbook\" title=\"Personal Macro Workbook\">Personal Macro Workbook<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/where-to-store-your-vba-macro-in-this-workbook-or-personal-macro-workbook\/#New_Workbook\" title=\"New Workbook\">New Workbook<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"Store_Macro_In\"><\/span><span style=\"color: #ff0000;\"><strong>Store Macro In<\/strong><\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>You have 3 options:<\/p>\n<p>* <strong>Personal Macro Workbook<\/strong><\/p>\n<p>* <strong>New Workbook<\/strong><\/p>\n<p>* <strong>This Workbook<\/strong><\/p>\n<p>During the initial setup of recording a macro a prompt will present itself asking where the macro should be stored.\u00a0 This option ultimately determines not only where the macro will be saved, but also where you can access the macro and where it can be used.<\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"This_Workbook\"><\/span><span style=\"color: #ff0000;\"><strong>This Workbook<\/strong><\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The \u201cTHIS WORKBOOK\u201d option will create a module within the active workbook where Excel will save the macro (VBA Code). By storing the macro in the active workbook,<strong> the macro can be used within the that workbook or if that workbook is open<\/strong>.<\/p>\n<p>Since the macro is contained with the workbook it was created within, if the workbook is sent to another Excel user or placed on a network drive, the macro will be there as well and other users can use that macro when that workbook is open on their system.<\/p>\n<p>Files that contain a macro or VBA code must be saved as a .XLSM (MACRO ENABLED WORKBOOK) or .XLSB file.<\/p>\n<p><strong>Some Common Uses:<\/strong><\/p>\n<ul>\n<li>Macros that are specific to a workbooks data<\/li>\n<li>Macros that make data connections and refreshes of data contained with the workbook<\/li>\n<li>Macros that clean up data specific to the workbook<\/li>\n<\/ul>\n<h3><\/h3>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Personal_Macro_Workbook\"><\/span><span style=\"color: #ff0000;\"><strong>Personal Macro Workbook<\/strong><\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The PERSONAL MACRO WORKBOOK is a hidden workbook located on a machine that contains <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/free-microsoft-excel-online-course\/\" target=\"_blank\" rel=\"noopener\" title=\"Microsoft Excel\" data-wpil-keyword-link=\"linked\">Microsoft Excel<\/a>.<\/p>\n<p><strong>The workbook is always open, but, hidden from view by default. <\/strong><\/p>\n<p>If a macro is stored in the PERSONAL MACRO WORKBOOK, this macro <strong>can be used within any workbook on the computer that contains that personal macro workbook.<\/strong><\/p>\n<p><a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/101-excel-macros-examples\/\" target=\"_blank\" rel=\"noopener\" title=\"Macros\" data-wpil-keyword-link=\"linked\">Macros<\/a> stored in a personal macro workbook are not typically shared through normal exchange of Excel files. To share a macro contained within the personal workbook, one would need to explicitly share their personal macro workbook, export the module that contains the macro or copy the code to another file and send that new file.<\/p>\n<p><strong>Some Common Uses:<\/strong><\/p>\n<ul>\n<li>One-time setup macros that can be used on multiple workbooks<\/li>\n<li>Macros that are more specific to an individual job that isn\u2019t shared among others<\/li>\n<li>Repetitive tasks necessary across multiple Excel files<\/li>\n<\/ul>\n<h2><\/h2>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"New_Workbook\"><\/span><span style=\"color: #ff0000;\"><strong>New Workbook<\/strong><\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>A NEW WORKBOOK creates a brand new workbook at time of recording. This new workbook will contain the macro and all of the VBA code.<\/p>\n<p>In order to use these macros you must open the new workbook that Excel created.<\/p>\n<p>Not as common as the other two!<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>A lot of Excel users get confused when they are recording an Excel Macro and they get prompted where they want to store their Excel Macro in? &nbsp; Store Macro In You have 3 options: * Personal Macro Workbook * New Workbook * This Workbook During the initial setup of recording a macro a prompt [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":7991,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[56,648],"tags":[],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/7956"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=7956"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/7956\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/7991"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=7956"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=7956"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=7956"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}