Ravindra BagaleCourses & study guides

12. Macros and VBA

12.16 The Personal Macro Workbook

By Last updated:

PERSONAL.XLSB is a hidden workbook that opens every time Excel starts. Macros stored there are available in all workbooks – ideal for your everyday utilities (TrimCityColumn, FormatHeader, DeliveryFee).

Steps in Excel

  1. Developer › Code › Record Macro › Store macro in: Personal Macro Workbook › record any small action › Stop Recording. Excel creates PERSONAL.XLSB (in the XLSTART folder).
  2. Close Excel – when asked save changes to the Personal Macro Workbook? click Save.
  3. Open the VBE (Alt + F11) – VBAProject (PERSONAL.XLSB) appears in Project Explorer. Copy your general macros into its module.
  4. To edit it as a sheet: View › Window › Unhide › PERSONAL.XLSB (hide it again afterwards with View › Hide).
  5. Run from any workbook with Alt + F8 (macros show as PERSONAL.XLSB!FormatHeader), or add them to the Quick Access Toolbar.

Ravindra Bagale's Tip

Personal Macro Workbook madhla macro fakt tumchya computer var asto – khup students file colleague la pathavtat aani macro button chalat nahi. Sagalyanna lagnare macros tya file madhech (.xlsm) kiwa add-in madhe theva. Ani Excel band kartana PERSONAL.XLSB save karayla "Don't Save" dabu naka, nahitar navin macro jaato.

Practice task

Move TrimCityColumn and FormatHeader into PERSONAL.XLSB, add them to your Quick Access Toolbar, and run them on a brand-new workbook.

Thodkyaat sangaycha tar (quick recap)

Developer tab on, security Disable with notification, file .xlsm; recorder ne shika (absolute vs relative aadhi tharva); code module madhe, Option Explicit, Long aani Set; Select talava, ws.Range vapra; If/Select Case, For/For Each/Do loops, last row khalun varti; macro la Undo nahi – backup theva; UDF value return karto; On Error sathi cleanup; F8 ne debug; roj che macros PERSONAL.XLSB madhe. Aata pudhe jaauya – dashboards!