Microsoft Excel personal macro workbook.

          We have already explored different Excel VBA macros here, here, and here, . While the power of Excel VBA is limited only by our creative imagination, the real limitation of a typical VBA code comes from the fact that it resides in the workbook where it was saved, and as such, can only be revoked while this file is open. This is where the personal macro workbook comes into play: if we save our code in this centralized place, we could use it in any Excel workbook on our local drive. This productivity hack would help us perform repetitive tasks (formatting and data presentation pet peeves, anyone) by automating them. The easiest way to save your code into PERSONAL.XLSB workbook is through recording a macro.

          To record a macro, we simply need to navigate to the Excel ribbon and select the View tab, click on Macros and then Record Macro ; alternatively, you have your Developer tab enabled, you could invoke a Record Macro wizard from there. The key is to ensure that you store your macro in Personal Macro Workbook location. They type of action you actually record is not overly important, as you can easily edit your VBA code once your stop recording your macro and save changes to your personal workbook. To create another macro and save it in your personal macro workbook, follow the same steps and re-save your workbook. To run your macro, you can either navigate to the View tab, or Developer tab and then click on Macros , all of the macros in the personal macro workbook would show under the Personal! location. Should you want to use a shortcut to save you a step, ALT + F8 would do the trick.

          To edit existing macro in your personal workbook, you would need to first unhide your personal workbook. Navigate to the View tab, click on Unhide dialog box, and then select your PERSONAL.XLSB file and click OK to view it. Alternatively, you can always easily edit VBA code of your personal workbook macros using the Visual Basic Editor (VBE) environment.

          Should you need to copy your personal macro workbook from one PC to another (maybe from your work desktop to your home laptop,) you could do this by locating the XLSTART folder containing your personal workbook and copying your workbook file between both computers. Most likely this workbook will be found in the directory below:

C:\Users\UserName\AppData\Roaming\Microsoft\Excel\XLSTART

However, depending on the version of your Windows OS and/or MS software, this location might vary:

Windows XP :
C:\Documents and Settings\UserName\Application Data\Microsoft\Excel\XLSTART

Excel 2007 :
C:\Program Files\Microsoft Office\Office12\XLSTART\

Excel 2010 :
C:\Program Files\Microsoft Office\Office14\XLSTART\

Excel 2013:
C:\Program Files\Microsoft Office\Office15\XLSTART\

Excel 2016:
C:\Program Files\Microsoft Office\Office16\XLSTART\

          If all else fails, we can retrieve exact location of your personal workbook directly from the VBE: Developer tab and Visual Basic menu, or ALT + F11 keyboard shortcut. If immediate window is not available, use CTRL + G shortcut to bring up and type the following command:

  ?Application.StartupPath 

Hit Enter key to display this location.

NOTE:
          This file does not exist until you record a macro and save it in it (as described above.) Personal workbook directory must be a trusted location – check your Trust Center setting within Excel’s OPTIONS.

          While only you can decide on the best use of this functionality – which routine steps would you like to automate, below are 3 example macros that might help make your life easier, more interesting:

          Example 1 Clean formatting and presentation of your work is strongly underrated, how about removing all gridlines from a new worksheet?

 
        Sheets.Add After:=ActiveSheet
        ActiveWindow.DisplayGridlines = False
        Range("A1").Select

          Example 2 Did you ever wish you could unhide more than one worksheet at a time? How about all hidden worksheets?

 
        Dim ws As Worksheet
        For Each ws In ActiveWorkbook.Worksheets
            ws.Visible = xlSheetVisible
        Next ws

          Example 3 Would it be great if Excel could talk to you and give you a motivational boost along with some practical information, such as workbook location and name?

 
        Application.Speech.Speak Application.UserName & ", you are making great progress today!" & _
             "This file is called " & ActiveWorkbook.Name & ", it is stored in the " & _
         Application.ActiveWorkbook.Path & " folder. " & " Current worksheet selected is called " & ActiveSheet.Name

          Now that you have saved your top macros, you can customize your ribbon and assign these macros to custom buttons for ease of use.

          Are there any macros saved in your personal workbook that you cannot live without, let us know in the comments section.