Personal Macro Workbook

Do you use any macros repeatedly?

If your answer is YES, then Excel provides a wonderful feature to save your macros somewhere and makes them available whenever you open any workbook, instead of copying the macros to every workbook, manually. This feature is called "Personal Macro Workbook".

How to create a Personal Macro Workbook?

If you are using xl2007, select View > Macros > Record Macro...

In the window select “Personal Macro Workbook” under Store macro in:

Excel saves the steps whatever you do, in to a VBA procedure. Afterwards, you can open up the Personal Macro Workbook via the Visual Basic button on the Developer tab later and update/delete whatever you record.

That created workbook is shown as VBAProject (PERSONAL.XLSB) in the VBA Project Explorer (Left side menu) and the same is shown whenever you open any workbook, so that you can use that macro directly.

You will see a message box alerting you about your Personal Macro Workbook, like this.

Personal Macro Workbook Path
Something to be noted is, this Personal Macro Workbook is saved in a specific path (in xl2007)
C:\Users\[Username]\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB

Have an EXCELlent day!

VBA Basics - 1

MYTH!!!

VBA is a very critical software language, which, nobody except intellectual software professionals can understand and work on.

Simply… VBA in Excel does the same things which we do in the excel worksheets … and a few more extra.

You select a cell, format it with different colors, borders, styles, do CUT COPY and PASTE, insert a chart or pivot table in the sheet. Everything we do manually like these can be done automatically by telling Excel in its very simple language called VBA.

So the disclaimer is you don’t have to be panic, while just looking at a 10 page VBA code having colorful text. J It’s quite simple…believe me!

Let’s go through few sessions, to see some basics of VBA.

Visual Basic for Applications

VBA is Visual Basic for Applications.

If you are familiar with Excel before, you may know that, Macro is something we do for repetitive actions. We can record our steps and then use the macro next time to do the same things we did while recording. Don’t worry if you don’t know about this…look at VBA Recording (link)

When you record some actions, Excel remembers those actions, step by step and saves it somewhere. You can view them by typing Alt+F11. It opens Visual Basic Editor.

Excel Visual Basic Editor is the place where we tell Excel (instructions) to do something.

The Window looks something like this.