Copyright: Copyright (c) 1994 Macro Systems.


Contents

About Bob's Macros

Excel 4 version vs Excel 5 version

Installation

Evaluating Bob's Macros

ACCESS TO THE MACRO CODE

How to Register

The Toolbars

The Commands

The Commands Added to the Excel Menus

The QuickAccess Menu

Customizing

Questions and Answers

Conditions of Use

What's New

Feedback / Technical Support

About ReportRunner - Another Macro Systems Product

Custom Macros/About Macro Systems


About Bob's Macros

Bob's Macros are designed to save you time in your use of Microsoft Excel and eliminate repetitious tasks.  The macros will increase your productivity every time you are in Excel.  The commands will save you anywhere from a few seconds to a few minutes each time you use them.  Added up over the course of a year that's a lot of time savings.  You do not have to be an expert to use the macros.  They are designed for use by everyone.

There are over 100 macros that comprise Bob's Macros.  The macros range from the simple to the very very powerful.  All are designed for ease of use.  These are macros that I use personally in my job and I thought it would be useful to you.  There is more to Bob's Macros than the list of macros that appears when you click on Bob's Icon.  Bob's Macros add commands to the Excel menus found at the top of the screen, and to the pop up menus when you press the right mouse button.  These pop up additions you will find very useful in developing spreadsheets.

By clicking on Bob's Icon [image] or by selecting "Bob's Macros" from the Tool menu (Excel 5) or from the Options menu (Excel 4), you can see a list of almost all the commands.  You can access many of the macros directly from the Excel Menus.  Also, additional commands have been added to the Excel pop-up menus that are not listed on the macro list.

Another nice feature is the repeat icon.  If you run a macro from the macro list, this icon is automatically customized to repeat the command!  And, if you highlight the icon, it reminds you what the icon is set to do.  Just another way to save you time!   Furthermore, you can assign any of the macros to icons or let Bob's Macros assign them to an icon for you!

Please let me know what you think of the macros and any improvements that are needed.  I use the macros every day and look forward to hearing your comments.  And, If you think of a macro you would like to have added, let me know when you register.

Sincerely, Bob Flanagan, Macro Systems


Evaluating Bob's Macros

As you probably have noticed, Bob's Macros can only be evaluated during certain times of the day.  Those times are 9-10 in the morning and evening, and 2-3 in the afternoon.  During these time periods, you have full and complete use of all the macros.  The help information however is available 24 hours a day.  Test drive them as much as you like during these time periods.  If you need to, exit Excel and change the time on your computer and test again.  There is no restriction from doing this.  However,  if you chose to change the clock all the time, you'll find our soon enough that you are consuming a lot of time changing the clock to save a few dollars.  And your time is far more valuable, so let us hope you pay Bob money to register your copy!


The Commands

Relocate Commands

Copy and Paste (Fill) Commands

Select Commands

Special Edit Commands

Time Period Commands

Multiplying, Dividing, Adding, and Subtracting

Data Commands

View and Window Commands

File Commands

Miscellaneous Commands

Print Commands

Unlisted Commands


How to Register

When you register your copy of Bob's Macros, Macro Systems will send you registration instructions that will allow you full use of the macros. If you reguest the latest version ($5 shipping and handling extra), you will be sent a disk containing the latest versions for both Excel 4 and 5.

In addition to using the form below to buy Bob's Macros, you can also use it to order ReportRunner.  ReportRunner is a spreadsheet and macro system that prints reports automatically based on stored report setup information.  Multiple reports can be printed just by highlighting the report descriptions and selecting print from ReportRunner's menu. See the section in the Bob's Macros Help for more details.

If registering by mail, send the form below with your payment to:

                   Macro Systems

                   1008 Lawndale Road

                   Wilmington, DE, USA 19810

You may pay by check or cash.  If you are paying from outside the U.S., you may pay only by cash, by a check drawn on a U.S. bank or by an international money order.

You may also charge  your order to your Compuserve account by typing GO SWREG.  Use the following registration numbers depending on your order:

                         Compuserve

       Cost         Number       Use for:

       $19           3553   Registration only of Bob's Macros

       $24           3554   Registration and disk containing the latest version of Bob's Macros

       $19           3990   Registration only of ReportRunner

       $24           3991   Registration and disk containing the latest version of ReportRunner

       $36           3995   Registration and disk containing the latest of ReportRunner and Bob's Macros

CREDIT CARD ORDERS ONLY:

-----------------------------------------------

You can also order with MC, Visa, Amex, or Discover from Public (software) Library by calling 800-2424-PsL or 713-524-6394 or by FAX to 713-524-6398 or by CIS Email to 71355,470. You can also mail credit card orders to PsL at P.O.Box 35705, Houston, TX 77235-5705.

The reference numbers beside the above prices are for CompuServe and not for  800 number phone orders.  The reference numbers for phone orders are 11703 for Bob's Macros, and 11704 for ReportRunner.

THE ABOVE PHONE NUMBERS ARE FOR CREDIT CARD ORDERS ONLY.  THE OPERATORS CAN NOT PROVIDE ANY SUPPORT OR INFORMATION ON THESE PRODUCTS, NOR RELAY ANY SUCH REQUEST.

Any questions about the status of the shipment of the order, refunds, registration options, product details, technical support, volume discounts, dealer pricing, site licenses, etc, must be directed to Macro Systems,  CIS Email 72774,416 or the address at the top.

Both PsL and CompuServe will notify us on the day of your order and we will fill it immediately.

                 ORDER FORM FOR MACRO SYSTEMS PRODUCTS:

Registration fee:

Bob's Macros      _______    X       $22     = _______

ReportRunner      _______    X       $22     = _______

If registering both, fill in the following instead of the above

ReportRunner &

Bob's Macros      ________    X       $34     = ________

Shipping and handling* if ordering disks:

Disks Needed     ________    X        $5     = ________

                                     TOTAL AMOUNT:   ________

Note:  Purchase of disks is optional, but recommended.  This insures that you have the latest release.

* If you want the lastest version of both programs, you need only pay for 1 disk.

Mailing address (please print)

       Name    ____________________________

      Address  ____________________________

                      ____________________________

City,State/Province  ______________________

Country & Postal Code _____________________

Payment Method (Please Circle)

   Check      Cash       MasterCard       Visa       Amex       Discover

If by Credit Card:

Card Number _______________________

Expire Date _____________

Name as it appears on the card:

______________________________

NOTE:  CREDIT CARD ORDERS MUST BE SENT DIRECT TO PsL IN TEXAS.  CREDIT CARD ORDERS CAN NOT BE ACCEPTED IF SENT TO MACRO SYSTEMS IN DELAWARE.  THIS IS A PsL REQUIREMENT.

 Suggestions for new macros:

_______________________________________________

_______________________________________________

_______________________________________________

_______________________________________________


Relocate Commands

The relocate commands are very useful.  They help you position the screen to display the cells that you want to see with a minimum of keystrokes and mouse clicks.  And they allow you to flash back to any location you mark.

Relocate Current Cell to Upper Left Corner of the Screen

Relocate Using Last Relocate Location

Mark and Return


The Copy and Paste (Fill) Commands
Copy and Paste (Fill) Commands

The following are the primary copy and paste commands:

Fill Highlighted Cells to the Right

Fill Highlighted Cells ALL the Way to the Right

Fill Highlighted Cells Down

 	Fill Highlighted Cells ALL the Way Down

Set the Target Column/Row

Fill to the Target Row

Fill to the Target Column

These commands first copy the selected cells and then pastes them either down or to the right.  And they return the screen right back to where it was before you issued the command.  No more having to scroll left and right or up and down to do your copy and paste!

If you use the command, Fill Highlighted Cells ALL the Way Down, then you may want to use the command Blank Rows Based On Reference Column to remove unneeded entries that may result.


Select Commands

These commands help you select text so that you can copy, paste, cut, format, etc.  These  commands are:

Select Cells to the Right of the Selected Range

Select All Cells to the Right of the Selected Range

Select Cells Below the Selected Range

Select All Cells Below the Selected Range

Set the Target Column/Row

Select to the Target Column

Select to the Target Row

You must use the first macro (set target column/row) before using the other two macros.


Special Edit Commands

Format Commands

Colorization Commands

Blank Cells Based On Reference Column

Blank Cells Based On Reference Row

Insert a Big Dot

Insert a Thick Dash

Insert a Thick Underline

Insert a Long Thick Underline

Insert Blank Rows Every X Rows

Insert Blank Rows If Cells Are Different

Remove Blank Rows

Outlines and Boxes

Capitalization

Indenting and Truncating

Removing Un-needed Spaces

Fix (text flow) A Paragraph

Shift Macro Equations 5 Spaces to the Right

Storing and Writing Stored Text

Blank Entries

Transpose Rows and Columns

Copy Column Widths

Color Cells Based on IF Test


Time Period Commands

There are four macros related to time in Bob's Macros.  They are:

Months Down or Across

Insert Date, Time, File Name and Sheet Name

Alarms

Activity Timers


Multiplying, Dividing, Adding, and Subtracting

To use the following macros, just highlight the selected range and select the macro.  The macro will add the necessary code to the contents of the cell and not modify any equations that are in the cell.  For example if the cell equation is "=A7*D8" and you select divide by 1000, the resulting cell equation is "=(A7*D8)/1000".

The following are the macros:

Divide By 2.2046 (Lbs to Kg)

Divide All By 10

Divide All By 100

Divide All By 1000

Divide By Any  Value

Add or Subtract Any Value

Multiply By 2.2046 (Kg to Lbs)

Multiply All By 10

Multiply All By 100

Multiply All By 1000

Multiply By Any Value


Data Commands

 	The following macros will help you to manipulate your data and rearrange your cells.

Compare Two Columns - Stop When Not Alike

Re-arrange Range Based on Reference Column W/O Changing References

Re-arrange Range Based on Reference Row W/O Changing References

Load Data From One File to Another


View and Window Commands

The following commands are pretty obvious.  You will find them also on the Excel menus.

Zoom to 85%

Set Zoom Back to Normal (100%)

Cascade Screens (Excel 5, but easier to get to)

Click on the following commands to see detail on how they work:

Windowize All Selected Sheets

Set All of the Worksheet's Windows to the Same Upper Left Cell and Zoom

Set All Selected Sheets to the Same Upper Left Cell and Zoom

Set all Selected Sheets to the Same Split and Freeze Panes

Zoom In

Zoom Out

Synchronize Commands


File Commands

Click on the following for detail on these commands:

Create an ASCII File

Delete the Active File

Save All Files

Close All Files


Format Commands

The following commands don't really require much explanation.  Basically, I got tired of clicking the right mouse button, picking a tab, selecting a format from a big list, etc.  So I add these to the list and also put a number of them on the Format pull down menu.

Format to 0

Format to 0.0

Format to 0.00

Format to 0.000

Format to 0%

Format to 0.0%

 	The above formats include commas in the format, which improves the readability of the numbers.


Miscellaneous Commands

Determine the Width of the Selected Range

Determine the Location and Length of the Longest Text Entry

Setting and Using Alarms

Calendar  and Calculator

Activity Timers

Initialize a Sheet

Sticky Notes

Insert a Circle

Find A Macro Command

Display Macro Command Name

Hide and Save Bob's Macro File

Remove or Show Macro Commands on the Excel Menus

Print the Macro List


Print Commands

Hopefully, the following macros are self explanatory.  If not, let me know and I'll add more help in the next release.  I have added pop-up help to several of them to give you some additional clues on their use.

Display Bottom of Print Area (F8 then allows extension )

Add or Subtract Rows or Columns to the Print Area

Shift Print Area "x" Rows or Columns

Print Selection and Then Restore Print Area

Store the Print Range and/or Titles For Use On Another Sheet

Apply the Stored Print Range to the Current Sheet

Set the Print Range On All Selected Sheets to the Current Selected Range

Insert Horizontal Page Breaks Every X Rows

Remove the Print Area, Titles, Page Breaks...

Set Print Titles

The following panel appears when the Remove the Print Area,... command is selected:

Please note the option on this panel to remove page breaks removes ALL page breaks, and is not limited by the range you may have selected on the sheet.  Also, the option to remove automatic page breaks is a display option.  If you select it, and then decide you want to see the automatic page breaks, you will need to manually reset this option.


Unlisted Commands

There are several commands found on the pop-up menus that are not listed when the Macro list is displayed.   For example, I've added clear options to the cell pop-up menus, and to the row and column pop-up menus.  I've also added under the help pull down menu a selection that gets you to the help on Bob's Macros.

And, most importantly, I've added under the Tool pull down menu an option to display Bob's Macros.


Customizing

There are several commands that allow you to customize Bob's Macros to your specifications.  They are discussed below.  In addition, if you prefer certain commands to have different defaults, run the commands to set the defaults the way you want them, and then save the macro file using the save command on the macro selection menu.

If you are using Excel 5, you can add macro commands that add the commands you prefer to the Excel menus vs the default set that gets added.  For help on this, click on:

Customizing the Excel Menus

The following are the customization options built into Bob's Macros.

Show Bob's Tool Bars

 	This command gives you the option to either show Bob's tool bar or prevent Bob's tool bar from appearing when you fire in the worksheet.  You can still get to the macros by using the command "Bob's Macros" located in the Tool pull down menu (or the options menu from Excel 4's menu's).

Adding Commands to Icons

With Microsoft Excel, you can add icons to your toolbars or create new toolbars.  And, you can assign any of Bob's Macros to the icons.  This makes it much more convenient to use the commands that help you the most.  The easiest way to assign a command to a button is to select the option on the select a macro list titled "assign to a button".  Then select the macro.  A selection of four buttons will appear.  Pick the one of your choice.  Bob's Macros then creates a single button toolbar with the name of the macro assign to the button and to the toolbar.

In Excel 5, you can easily customize the image.  In fact, the instructions on how to do so appear whenever you assign a command to a button.  In Excel 4, it is much more difficult.  First, you have to go into Paint Brush and create a new file.  Make sure the image size is small - 30 by 30 pels (this is found under the option menu).  Then, zoom in on the area being edited.  Design your icon image in the bottom right 15 by 16 squares.  Then zoom out and select the cut button and highlight the section containing just the icon design.  Now go back to Excel.  Right click on the button, select customize, and right click on the button again.  Select paste image, and the icon you designed appears.  (Avoiding this hassle is just one of many reasons to upgrade to Excel 5).

You can also assign any of the macros to a button manually.  First use the command on the list that displays the macros names to find out the exact spelling of the macro you are interested in.  The next step is to right click on any of the toolbars, and select customize.  Select the icon you want to use from the selections that Excels presents and drop it on a toolbar or out in the open.  If the icon is one that is in the custom group, you will be prompted for the macro name.  If it is one from the standard groups, Excel will assume you want the predefined function to be used.  However, you can change it by right clicking on the button and assigning a macro to it.

Adding Commands to the Excel Menus

The default setting of Bob's Macros is to add a number of commands to the Excel Menus.  If you decide you don't want the commands added, you can selectively remove groups of commands by using the macro "Add or Remove Commands from Excel's Menus".  To make the change permanent (so that its the default setting), use the save Bob's Macros command on the macro list.


The Toolbars

Bob's macros comes with six toolbars.  Two are predefined, and four may be customized by the user.  The two predefined toolbars are called Bob's Toolbar and Bob's Zoom.

The following are the icons on Bob's Toolbar:

  This icon displays a list of the macros.  You have the option to either execute the macro or display the help information on the macro.

  This icon repeats the last command that was selected from the list of commands.

The following are the icons on the Zoom Toolbar:

  This icon increases the zoom percentage 5%.  Using it and its companion icon to quickly adjust the zoom setting to what you want.

  This icon decreases the zoom percentage 5%.

If you don't want to display Bob's toolbar or the Zoom toolbar, then you can prevent them from appearing by using the command called "Show Bob's Tool Bars" on the macro list.

The other four toolbars appear only if you assign a macro to a button.  The toolbars that appear are red, blue, yellow, and green boxes.

If you want, you can assign any of the macro commands to a button of your choice.  You do this using the Excel commands to customize the toolbars.  Please note that you should use the command "Display Macro Command Name" found at the bottom of Bob's Macros to get the name of the macro.  Some are not intuitively obvious!


Conditions of Use

LICENSE AGREEMENT

Bob's Macros is being distributed by Macro Systems as shareware.  This means you can copy the unregistered version of Bob's Macros to evaluate it for a 60 day period.  Use beyond this period without registering your copy is strictly prohibited.

The program can be stored on multiple machines and payment made for only a single copy only as long as the copies on the different machines are not used simultaneously.  Under the software license from Macro Systems, registering a copy of Bob's Macros gives you only the right to use the macros and modify them for personal use.  It does not give you the right to resale or distribute the macros or prepare or distribute a derivative work or modified version of the macros.

The shareware version of Bob's Macros is the complete program.  Please note that the money you pay to a shareware distributor is to cover his distribution costs, and is not for the purchase or registration of a copy of the program.

Macro Systems encourages you to make copies of the UNREGISTERED version of Bob's Macros and give them to others to evaluate.  Please feel free to upload the UNREGISTERED version of Bob's Macros to your favorite bulletin boards.  The files your are allowed to distribute are the original UNREGISTERED files you received.  Do not distribute your copy after you have used it, as it contains information about your files, and you may have customized it to your preferences.

If you are a shareware distributor you may distribute the UNREGISTERED version of Bob's Macros software as long as you distribute all the files intact and in an unmodified form.  Also, you may not charge more than a reasonable distribution fee.

REGISTERED VERSIONS OF BOB'S MACROS MAY NOT BE DISTRIBUTED TO OTHERS, MAY NOT BE DISTRIBUTED AS SHAREWARE, AND MAY NOT BE PUT ON BULLETIN BOARDS. To determine which version you have, install the software, and run the command "About Bob's Macros" from the help menu when you load the spreadsheet.

COPYRIGHT AND TRADEMARKS

The software files that comprise Bob's Macros are copyrighted (c) software of Macro Systems.  All rights reserved.  It is not in the public domain nor is it freeware.  It is the property of Macro Systems.  Modifying the program in any way is strictly prohibited.

Bob's Macros is a trademark of Macro Systems.

Excel is a trademark of Microsoft Corporation.

LIABILITY AND WARRANTY

 This software is distributed "as is" and without warranties.  No statement made about this software by distributors, in advertisements, in software reviews, or in the documentation constitutes a warranty.

The user must test the program thoroughly.  In no event shall Macro Systems, owner of the copyright, or the author of the program be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of this software.  Evaluate and use this program at your own risk. There is no guarantee that this software will operate uninterrupted or error free.  Any liability will be limited exclusively to product replacement or refund of purchase price.  By using Bob's Macros, you agree that the only remedy available to you will be a refund of the purchase price of the Bob's Macros program.

EXCEPT AS EXPRESSLY PROVIDED HEREIN, THERE ARE NO OTHER WARRANTIES, EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE WITH RESPECT TO THE SOFTWARE AND ANY ACCOMPANYING WRITTEN MATERIALS.  IN NO EVENT WILL MACRO SYSTEMS BE LIABLE TO YOU FOR DAMAGES, INCLUDING ANY LOSS OF PROFITS, LOST SAVINGS, OR OTHER INCIDENTAL OR CONSEQUENTIAL DAMAGES ARISING OUT OF YOUR USE OR INABILITY TO USE THE SOFTWARE, EVEN IF MACRO SYSTEMS OR AN AUTHORIZED REPRESENTATIVE HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

This limited warranty gives you specific legal rights.  Some states provide other rights, and some states do not allow excluding or limiting implied warranties or limiting liability for incidental or consequential damages.  As a result, the above limitations and/or exclusions may not apply to you.  Some jurisdictions have statutory consumer protection provisions that may supersede this section of this Agreement.

Evaluation, use, distribution, or purchase of this software indicates your agreement with the terms and conditions in this document.  If you do not agree with the terms and conditions, do not use Bob's Macros, and, if applicable, return the copy you purchased.


What's New

We've tried to make Bob's Macros as bug free as possible.  If you come across any, please let us know.  We have noticed that some of the macros get upset if run on a protected worksheet, and rightly so.  (However, we don't consider this a bug!)

The following is a history of the updates and changes to Bob's Macros.  Please note that many of the changes are upgrades and additions vs bug fixes and reflect my commitment to continually improve the macros.  If you do register, I recommend that you also ask for the latest release.

3/7/95

Fixed a problem with the Indent Text macro.

3/4/95

Added a repeat find macro to the Excel 5 release.  This allows this feature to be assigned to a button for ease of use.

Removed the "All Done" messages that appeared in several macros.  This was done to allow the macros to be called by a user's macro and not required the user to acknowledge the message in order for the user macros to continue.

2/24/95

Modify the macros so that all the additions to the Excel menus are added if the file is opened by clicking on the Bob's Macros icon.  This allows the file to be stored other than in XLSTART directory.  However, additions to the menus only occur after clicking on the icon if the file is not stored in the XLSTART directory.

Added a spacer to the menu that pops up when you left click on a cell.  This makes it easier to pick the fill vs select macros.

Add the macro "Add or Subtract Any Value" to the data menu in Excel 5.

2/6/95

Modified the macros that prompt for an input area to suggest the currently  selected range as the desired area.  The user still has the option to change the range.  Macros changed where the Create an Ascii file macro, Compare Two Columns macro, and the Load Data macro,

Modified the Load Data macro to allow multiple columns to be loaded at one time.

2/5/95

Modified the macro that sets selected sheets to the same upper left cell and zoom so that if no sheets are selected, all sheets are automatically selected.  Also, added this macro to the Window pull down menu (replacing a little used macro that was previously on the pull down menu).  This change affected only the version 5 model.

1/7/95

Fixed a problem with the create an ascii file macro that occured when header columns are specified, but header rows are not.

1/4/95

Added a new macro called Print Current Selection and then Restore the Print Area.

12/26/94

Modified the load data from one file to another macro to make the colorization of data that is not loaded optional.

Modified the compare two columns macro so that the screen does not end up showing column IV when the compare is done on the contents of column A when they are title columns.

12/10/95

Added 800 number for orders, and provided a discount for orders for both the macros and ReportRunner.

11/29/95

Add a macro to set print titles to the Excel 5 release.  By assigning to a button, it makes setting print titles far easier.

11/15/95

Change the pattern of the dotted lines in the box and outline macros to give a finer dotted pattern.

Added an explanation in the Question and Answer section as to why users get a macro not found message and how to cure (Its caused by moving the file, and all you do is manually load the macros to cure).

Modified the macros so that they can be called by user written programs, and then allow the user macros to continue executing.  Prior to this, the macros halted at completion of their commands and did not return control to the user macro.

11/14/94

Fix bugs in the "Load data from one file to another macro" and in the "Relocate cell to upper left.

Modified the macro that assigns any of the macros to a button/toolbar so that an almost unlimited number of buttons/toolbars can be created.

Add in the Excel 5 version an option to create a button/toolbar with a user defined pop-up label.  The button can then be assigned to a user written macro.

Modified the find a macro command macro so that it sets the find command to the correct setup prior to doing a search for a macro.

11/7/94

Renamed the QuickAccess menu to "Quik" to provide room on the Excel menu for users to add their own menu.

Fixed a problem in the Create an Ascii macro.  The macro was not properly copying column widths.

Add a Question and Answer section to the help

10/31/94

Minor efficiency improvements to several macros.

Re-wrote major sections of the help files, and add additional help.

10/29/94

Modified the macros so that users can customize the Excel menus upon startup.

Combined the two transpose macros into one and removed it from the Excel Menus.  It is still available from the macro list from Bob's Icon.

Made the QuickAccess menu optional

Added an automatic save if the user changes which sets of macros are shown on the Excel menus.

Modified the Transpose Macros so that the cell to appear in the upper left corner after the macros are done is requested.  This better positions the screen after the macros are done.

Modified the Add Blank Rows Every X macro so that the user can specify how many blank rows are inserted.

Add a new macro, Add Blank Rows if Cells Are Different.

10/20/94

Modified the startup instructions so that the appearance of Bob's toolbar is determined by how it was last set when you exited Excel vs always being displayed.

Added a new macro that allows you to delete the active file.

Add an option to the date and time macro to also enter the file name and sheet name into the worksheet.

10/9/94

Modified the macro that blanks cells based on a reference row so that the user specifies the reference row vs the macro selecting the closest row in the direction indicated.

Added a macro to Blank Cells based on Reference Row .

Fixed a bug in the add commands to the menus so that the data commands in Excel 5 are removed if the user chooses to not display them.

Modified format 0.0, 0.00, and 0.000 commands so that commas are

entered if the value is greater than zero.

9/29/94

Added Control Panel to the Quick Access menu

Changed the descriptor on the Initialize Sheet command to say "Initialize Selected Sheets" in the Excel 5 version

Fixed a bug in the Find command in the Excel 5 version.

9/25/94

First release!


Installation

Installation of Bob's Macros is very easy.

STEP 1

If you are using Excel 5, copy the file BOBSMACS.XL5 to your Excel startup directory.  If you are using Excel 4, copy the file BOBSMACS.XL4 to your startup directory.  DO NOT COPY BOTH FILES.  The startup directory is almost always named "XLSTART" and is almost always a subdirectory of your Excel directory.  The following are examples of Excel directories:

C:\EXCEL\XLSTART

C:\MSOFFICE\EXCEL\XLSTART

 By putting this in your startup directory, it is loaded each time you load Excel.  This is extremely important, as Bob Macros are designed to help you all the time while you are in Excell And if they are not loaded when you need them, you won't get as much benefit.

In case you are wondering, yes you can put the files in a directory other than your startup directory.  But, we don't recommend that since the Excel menus will not reflect the menu additions until the macro file is loaded.  If you do put the file in a directory other than your startup directory, you will need to manually load the macro file only once.  There after, the file will be loaded automatically by Excel when you click on Bob's Icon or select one of the macro commands from one of the menus.

STEP 2

Copy the file BOBSMACS.HLP to your Windows directory.

STEP 3

Try them and you'll like them.  Then register!


Relocate Current Cell

This command relocates the active cell to the upper left corner of the window.  This is a quick way to reposition the cells on the screen.  If, after you've used this command you've scrolled around in the spreadsheet, you can return to where you were by using the command

Relocate Using Last Relocate Location


Relocate using Last Relocate

This command becomes active after you have used the command "Relocate Current Cell to Upper left Corner of the screen".  It allows you to repeat the results of the this command.  That is to say, it relocates you back to that cell and sets the screen accordingly.

Please note that if you have added or deleted rows or columns above or to the left of the stored location, then you will be returned to a location off by the number of rows or columns added or deleted.  Although we could have defined a range name on your sheet to prevent that, we felt that was a liberty we should not take with your spreadsheet.  Thus we store the row and column numbers and return to that position.


Blank Cells Based On Reference Column

If you used the command

Fill Highlighted Cells ALL the Way Down,

then you will probably end up with cells that shouldn't have entries.  For example if you have skip rows to improve readability, then those skipped rows will have had cells copied into them.  That where this command helps.

First,  select the range of cells (multiple columns allowed) that need cells blanked or erased.  Then run this command (Blank Cells Based on Reference Column).  You are first asked to specify a cell in the reference column.  You are then asked if you want to copy text found in the reference column.  Reply yes if you want to copy dashes that have been put in as underlines.  Once this is done, the macro takes over.  It compares the selected cells to the cells in the reference column.  If the cell entry in the reference column is blank, then the cell in the selected range is erased.


Insert a Thick Underline

If you like to uses dashes (------) in a cell to create an underline affect, you'll really like this macro.  It uses the Wide Latin font which comes with Windows to create a very thick underline to create a much better looking underline.   The difference between a Thick Underline and a Long Thick Underline is the length of the underline!

The following illustrates the difference between ordinary underlines and thick underlines:

Please note that if you use this command and then insert a row right below the cells containing the thick underline, the new cells below the underline will have the Wide Latin font.


Insert a Thick Dash

If you use Excel to create presentations or documentation, then you may like to put a dash in the cells to the left of your text.  As opposed to using the wimpy dash (-), this macro gives you a nice thick dash by using the Wide Latin font.

The following illustrates this:

 	Please note that if you use this command and then insert a row right below the cells containing the thick underline, the new cells below the dash will have the Wide Latin font.  Also, the text will be centered vertically vs the default bottom centering.


Insert Blank Rows Every X Rows

Ever have a bunch of data and decide that you need to insert a blank row every couple of rows?  And then decide after your half way through that you've spend a lot of time inserting rows?  Well, this macro saves you that time.  Just highlight the area that needs the blank rows and let this macro do the work.  What's more you have the option to shift the cells in the selected range down vs inserting an entire row.  But, if inserting an entire row is OK, then your selected range needs to be only one column wide!  And, you can specify how many blank rows are inserted.

Now, lets say you've run the command and done some work in your spreadsheet and deleted and added rows.  Your data is no longer nicely separated every X rows.  No problem.  Use the command Remove Blank Rows to remove the blank rows.  Then re-run the command to insert blank rows.


Remove Blank Rows

Use this command to remove unwanted blank rows in your data.  You have the option of deleting the entire row or shifting text below the blank cells upward.  If you want to delete the entire row, then all you need to do is to select a range that is a single column wide.  Otherwise, select all columns and let the macro loose.


Outlines and Boxes

Although Excel's outline button (available in release 5) has a lot of outlining and boxing options, it doesn't have them all.  Nor does Excel 4 have the outline button.  So I added the ones that were missing and duplicated others so that Excel 4 users would have them.

To use, select the range you want outlined or boxed and let the macro do the work.

A special word about the Remove Just the Outline command.  It does just that.  Cells which are boxed are not affected (unless they are on the edge of the outline).  So, if you outline your work and realize that you need to remove the outline, just highlight the selection inside the outline and let the macro do the work.  (Excel's 5 option to remove lines removes all lines, not just the outline) .

The following is a list of the outline and box functions:

Outline using a dotted line

Outline using a thin line

Outline using a medium line

Outline using a thick line

Box using a dotted line

Box using a thin line

Box using a medium line

Box using a thick line

Remove just the outline


Capitalization

There are four macros that control capitalization:

 Convert All Letters to Lower Case

Capitalize the First Letter of the First Word Only

Capitalize the First Letter of All Words (also called proper case)

Capitalize All Letters

 They do just what they say they do.  Just select the range and run the macro of your choice.  They are available from both the macro list and also Excel's menus.


Indent or Truncate Text

This macro will add as many spaces as you want in front of text or will truncate them the requested number of characters.  Please note that the truncate command does not truncate just spaces, but also letters, periods, etc.

The following is the box that appears that allows you to specify how much indentation or truncation is to occur:

These command acts only on cells containing text.  Cells with formulas are not affected.


Fix (text flow) A Paragraph

Using Excel as a mini word processor to write document is not too easy.  There is a command buried on one of the menus that helps flow paragraphs together.  But it removes the spaces at the beginning of a paragraph.  This command solves that - it retains the beginning indentation.  And, it is easy to find as it is on the Excel 5 edit menu.

The only restriction is that you must do one paragraph at a time.  Which is the safe way to do the command.

To use the command highlight the range that you want to fit the paragraph into before running the command.  Include extra blank lines to insure that there is enough space for the paragraph once it is fixed.  The command will flow the text so that it fits in the column range specified.  Be careful you don't select a range one column wide, or else you will end up with a very narrow paragraph! (In this situation, we do confirm that you wanted to highlight a single column.)


Shift Macro Equations

The macro Shift Macro Equations 5 Spaces to the Right does just what it says.  This is a very useful since it allows you to indent groups of related equations.  Such as the equations between an IF() and an END.IF().  This command will not work on regular worksheets.  It works only on Excel 4 macro sheets, and is available on the cell popup menu.


Storing and Writing Stored Text

Every now and then I found that I had to type the same text over and over again.  So I wrote a macro!  Actually, two macros.  One to store the text that I had to type, and one to write it out.  These macros used in conjunction with the Repeat Icon makes writing repetitious phrases very very easy.

The names of the two macros are:

Store Text to be Written Out by Write Out Stored Text Command

Write out Stored Text

Use the first macro to specify the text to be written out and the options.  Once you have specified the text and options, you are asked if you want to run the macro.  If you want to run it additional times, use the second macro to repeat the text writing without having to revisit the option panel.

The Write Out Stored Text has many as illustrated by the following panel that appears when it is selected:


Blank Entries

There are two macros written to either blank duplicate entries and or to fill blanks with the entries above a cell.  The macros are:

Blank Entries If the Same As the Cell Above

Fill Blanks With the Contents of the Cell Above

The first creates blanks and the other fills blanks.

How are these macros useful?  If you import data into Excel from external files then you may want to fill all the cells so that you can then sort the data.  Or you may want to remove all the duplicate entries to make the output reports look less cluttered.


Mark and Return

There are two macros that help you remember where you were and return you to that location when you're ready:

Store Current Cell Location For Easy Return

Return to Stored Location - From Any Spreadsheet

The first command stores the current cell and spreadsheet location.  The second returns you to that location from where ever you may be.

There are two related commands that also may prove useful:

Relocate Current Cell to Upper Left Corner of the Screen

Relocate Using Last Relocate


Compare Two Columns

How often do you have two columns of data and would like to find out where they are different?  That's what this macro is designed to do for you.  The columns can be on the same sheet or on different sheets in different worksheets.

The macro asks you to select the cells in the first column and the first cell in the second column.  When a difference is detected, a menu is displayed that shows the different cells and asks you if you want to highlight the cells.  You have the option to outline or turn the background red.  Next, you are asked if you wish to continue the comparison.  This continues until the data has been checked or you decide to stop.


Transpose Rows or Columns
Transpose Rows and Columns

This macro takes a block of cells and transposes it (rows to columns, columns to rows) without changing references to the cells.  Also, the formulas inside the cells being transformed are not affected.  Excel has a transpose feature under the paste special menu command, but it works only if you are copying cells.  This command is the equivalent to that, but is a cut and transpose, which is not available via Excel.  Formulas referring to the cells being transposed continue to refer to the transposed cells in their new locations.

For example, this command would convert

into

To run the macro, first select the block of cells to be transposed then select the macro.  The macro asks you where you want the transposed cells located.  It must be on the same sheet as the highlighted range.  You can not cross sheets with this command.  Finally, the macro asks you what cell you want to appear in the top left once the work is done.


Re-arrange based on Reference Col

I'm certain you are familiar with the Excel sort command.  It works great but it has one drawback:  Formulas that refer to cells inside the sorted range don't change the cells they refer to when the cells are sorted even though the contents move.  That's great most of the time, but sometimes you want to re-arrange the data in a sorted order and maintain the references.  Up until now, the only solution was manually cutting and pasting, cutting and pasting,....

To use this command, first select the range of data you want re-arranged.  Then execute the macro.  It then asks you to identify which column is used to sort the data.  It also asks you if you want the data re-arranged in ascending or descending order.  Then it goes to work.  Please note that this macro does a lot of work, so have patience.

If you want to re-arrange a range of data based on several columns, run the macro several times, but select the columns in reverse order.  For example if you want the data re-arranged by columns A, B, and C.  Run column C first, then B, then A.


Set All Windows the Same

This command appears in the list of macro commands as:

Set All of the Worksheet's Windows to the Same Upper Left Cell and Zoom

It also appears on the Windows pull down menu as:

Set all Windows the Same

Basically, if you have two or more windows on the same worksheet, this command will set them up so that the same cell is in the upper left and the zoom setting is the same.  This makes it very easy to compare sheets, since you're looking at the same range of cells.


Same Upper Left Cell and Zoom

This command is located in the Macro List and is called:

Set All Selected Sheets to the Same Upper Left Cell and Zoom

It is also located under the View pull down menu as:

Set All Selected Sheets the Same

The command does just what its title describes.  When executed, it asks you to specify the cell to be located in the upper left, then it sets all selected sheets to have this cell in the upper left.  It also sets the zoom the same on all selected sheets.  This command will automatically skip non-worksheet sheets if you do a select all sheets.


Same Split and Freeze Panes

This command (Set All Selected Sheets to the Same Split and Freeze Panes) is a quick and easy way to get the same view on a group of selected sheets.  Once you've used this command, you should use the following related command to set selected sheets to the same view when you need to vs re-using this command (it avoids a little work on your part)

Set All Selected Sheets to the Same Upper Left Cell and Zoom


Zoom In and Out

The Zoom In and Out commands increase or decrease the zoom 5% each time they are run.  They are also associated with  the following Icons on Bob's Zoom Tool bar:

 and

I found that increasing or decreasing the zoom in 5% increments was much more effective getting the zoom I want vs using the default Zoom settings from Excel's magnifying glass icons.


Synchronize Commands

There are four synchronize commands in Bob's Macro's.  Excel 4 used to have these commands, but they disappeared in Excel 5.  Well, they're back!  Please note that synchronization applies only to windows on the same sheet.  It does not work on two different sheets.  When the windows on the same sheet are synchronized, the word "sync" appears in the windows title bar.

The commands which hopefully are self explanatory are:

Horizontally Synchronizes the Current Sheet's Windows

Vertically Synchronizes the Current Sheet's Windows

Horizontally and Vertically Synchronize the Current Sheet's Windows

Removes Synchronization From Current Sheet


Reference Row Re-arrange

I'm certain you are familiar with the Excel sort command.  It works great but it has one drawback:  Formulas that refer to cells inside the sorted range don't change the cells they refer to when the cells are sorted even though the contents move.  That's great most of the time, but sometimes you want to re-arrange the data in a sorted row order and maintain the formula references to the data as it is re-arranged.  Up until now, the only solution was manually cutting and pasting, cutting and pasting,....

To use this command, first select the range of data you want re-arranged.  Then execute the macro.  It then asks you to identify which row is used to sort the data.  It also asks you if you want the data re-arranged in ascending or descending order.  Then it goes to work.  Please note that this macro does a lot of work, so have patience.

If you want to rearrange a range of data based on several rows, run the macro several times, but select the rows in reverse order.  For example if you want the data re-arranged by row 1, 2 and 3, run the macro first on row 3, then row 2 and last on row 1.


Create an ASCII File

One of the hardest things to do in Excel is to create an ASCII file.  You've go to remember which of the file save as commands to use, and then figure out how to get header rows and columns into the output.  Kind of difficult if the header rows and columns are not adjacent to the main body of text you want to write out as an ASCII file.  Never fear, Bob's Macros solves this one!

One word of warning.  If you want almost WYSIWYG results in your ASCII file, then you should use a courier 10 point font.  If you use a proportional font like Ariel or Times Roman, weird results and truncation can happen.  Although courier 10 isn't perfect, its the closest I've come up with.  If the text strings are long, you may want to use courier 8 point font.

When you execute the macro, it prompts you for the information it needs:  the main body, the header rows and columns, and the file name and location for the ASCII file.  If you don't supply a filetype with the ASCII file, then it uses a type of PRN as the default filetype.

One restriction on filenames - you can not replace an existing file.  You must create a new file vs replacing an old one.  This appears to be an Excel restriction.  If we find a solution, we'll add it into the macro ASAP!

Please note that when you create an ASCII file, if you have text that extends past the border of the last column, all of that text will appear in the ASCII file.  So your output may be a little wider than you expect.

Related Commands:

Determine the Width of the Selected Range

Determine the Location and Length of the Longest Text Entry


Determine the Width

This is one of those specialized commands.  Its primary purpose is to determine the width of a group of columns so that when you are creating an ASCII file, you know how wide the results will be.  To use, select a range of cells (it doesn't have to be the full columns) and run the command.  It tells you both the "true width", which is what the Excel widths add up to, and the "truncated width" which is what you see in an ASCII file.

Please note that when you create an ASCII file, if you have text that extends past the border of the last column, all of that text will appear in the ASCII file.  So your output may be a little wider than you expect.


Longest Text Entry

This is another macro designed to help you with using Excel for writing documentation or creating ASCII files.  The full name of the macro is:

Determine the Location and Length of the Longest Text Entry

To use, first highlight the range you want searched and let the macro run.  When it completes running, it tells you which cell has the longest entry and how long the entry is.


Setting and Using Alarms

Bob's Macros allows you to set up to ten alarms.  Each alarm can be set to go off at a specify time or to go off in X minutes.  Whichever you prefer.

This command is located in the macro list and also under the Help menu.  The following box appears when this command is selected:

Then, after selecting New, the following box appears so that you can set an alarm.

Please note that if you set an alarm and then close down Excel, the alarms are canceled and no warning of that is given.  I considered adding the capability of setting the alarms for any date, and then realized that there is a lot of shareware products which are designed to do just that.  And, I use one!  So, I do provide some alarm capability, but if you really need to set alarms and reminders I recommend:

Alarm

by

Stefan Tucker

Perpetual Motion Software

310 West Noyes Street

Arlington, Heights, IL 60005-3638

USA

The registration fee is only $15 and includes all shipping, handing, and packaging fees.  Please indicate disk size when ordering (check or money order only)

It uses very little of your system resources and has a lot of features.   And, you can leave it active all day, which you probably won't do with Excel.

.


Initialize a Sheet

 	When I create a new sheet or a new workbook, I've found that there are certain characteristics that I like to set on the sheets.  For example, I like to turn the grid off, use the Lotus transition formula entry and evaluation option, and get rid of the print header and footers.  Thus this macro was born.  You have the option to set any or all of these options when you use this command.

This command works for worksheets or graph sheets only.

As info, this command is located not only on the macro list, but also on the File pull down menu.


Find A Macro Command

With all the macros on the list, it became inevitable that I needed a macro to find a macro!  Thus this command was created.  Its available not only on the macro list, but also on the tools pull down menu.  To use, you need to remember a key word, or better yet, a key phrase in the macro command description.  The Excel find box will pop up and allow you to enter in the key word and then cycle through any matches that it finds.  Once you find the macro you are after, close or exit the find box.  The macro command that is last found will automatically be displayed for execution when you exit the find command.


Display Macro Command Name

The macro list for Bob's Macros gives you a description of the command, but doesn't tell you the exact macro name.  You really only need to know the exact macro name if you want to assign one of the commands to a button on a toolbar.  This command allows you to select the command you're interested in, and then it displays the macro name.

You will notice that all the command names begin with "Z_".  The reason for this is simple.  I didn't want to mix up Bob's Macros with ones you have already written when you execute the macros manually from Excel's menus.  You'll still see Bob's Macros, but they will be on the bottom of the list, not mixed in with your own macros.


Feedback / Technical Support

 	All feedback, both positive and negative is welcome.  I do like my programs to be as error free as possible, and if possible will try to solve the problem you have in a future release.  However, because of the low cost of Bob's Macros, free technical support will be very limited.  I may be contacted by mail at:

Bob Flanagan

Macro Systems

1008 Lawndale Road

Wilmington, DE 19810

or via CompuServe (ID:  72774,416).


Custom Macros/About Macro Systems

Macro Systems is a software business that I run dedicated to developing applications in Excel and other Microsoft products.  If you would like to have a specialized set of macros built let us know.  For example, we've built macro systems that automatically loads data that would take hours to do manually.  So, if you're doing a repetitive task each day, week, or month, we can automate it and save you time and eliminate errors associated with such manual work.

 And, we've built very special models.  For example, we've built models that give the production cost and earnings by item for entire factories.  So if you need specialized Excel models or systems developed we're interested in working with you.

We can't think of a better way to advertise our services than with our products, and we consider Bob's Macros a fine example.  If you're interested in contacting us, we can be reached by mail at:

Macro Systems

1008 Lawndale Road

Wilmington, DE 19810

or via CompuServe (ID:  72774,416).


Fill Highlighted Cells to the Right

This command copies the highlighted cells and then pastes them from the active cell to the right until the first blank is reached if there is text to the right of the active cell.  If there is no text to the right of the active cell, but there is text above the cell, then the first cell above with text is  used to determine how far right to copy and paste the selected cells.


Fill Highlighted Cells All the Way Right

The command copies the highlighted cells and then pastes them all the way to the right to the last cell on the active row that has an entry.  If there are no cells to the right, then the first row above with text is used as the basis to determine how far right to do the copy and paste.

One of the problems with this command is that it fills cells that you want left blanked.  To remove these unwanted blanks, use the command Blank Rows based on Reference Row.


Fill to the Target Row

Quite frequently, the range you want to paste to is not nicely defined by having a blank cell right where you want to end you copy and paste.  For example, you may want to copy formulas down 10 rows but not affect the 11th row which has formulas that sum the first 10 rows.  That's where this macro fits in.

First, you define a reference row, which is the last row to which you want to copy and paste.  You do this be running the macro Set the Target Row/Column.  This command requests you to highlight a cell in the last column or row to be pasted to.  If all you are doing is filling to a target row, then the target column is un-important.  Please note that you can set the target row or column over and over.

Next you run this macro.  It copies the cells you have selected to and including the reference row.  Its that easy!


Fill to the Target Column

Quite frequently, the range you want to paste to is not nicely defined by having a blank cell right where you want to end you copy and paste.  For example, you may want to copy formulas to the first 12 columns but not affect the 13th column which has formulas that sum the first 12 columns.  That's where this macro fits in.

First, you define a reference column, which is the last column to which you want to copy and paste.  You do this be running the command Set the Target Column/Row.   This command requests you to highlight a cell in the last column or row to be pasted to.  If all you are doing is filling to a target column, then the target row is un-important.  Please note that you can set the target row or column over and over.

Next you run this macro.  It copies the cells you have selected over to and including the reference column.  Its that easy!


Hide and Save Bob's Macro File

If you customize part of Bob's Macros, you will probably want to save the file.  Do that using this command.  That way, the file is saved as a hidden file, and the macro list will always display the top of the list when you select it the first time after the file is loaded.

For example, you may want to save the macro file if you prefer a different configuration on the macro "Initialize selected sheets", or if you prefer to different default on the insert blank rows (ex: every 3 rows vs every 5).


Fill Highlighted Cells Down

This command copies the highlighted cells and then pastes them from the active cell down until the first blank is reached if there is text below of the active cell.  If there is no text below the cell, but there is text to the left of the cell, then the first cell to the left with text is  used to determine how far down to copy and paste the selected cells.


Fill Highlighed Cells ALL the Way Down
Fill Highlighted Cells ALL the Way Down

This command copies the highlighted cells and then pastes them from the active cell all the way to the last cell in the active column that has an entry.  If there are no cells below the active cell, then the first column to the left with text is used as the basis to determine how far down to do the copy and paste.

One of the problems with this command is that it fills cells that you want left blanked.  To remove these unwanted blanks, use the command Blank Rows based on Reference Column.


Windowize All Selected Sheets

This commands opens a new window for each sheet that is selected.  It then cascades all the windows.


Insert a Big Dot

This command is great for improving a presentation!.  The following illustrates the big dot and its effect on a presentation:

So no more remembering what font and key strokes are needed to put a dot into your spreadsheet.  Use the dots and thick dashes to make presentations written in Excel stand out.


Load Data From One File to Another

The best way to illustrate what this command does is by way of an example.  Say that you routinely obtain a file containing a list of sales dollars by customer and need to store in your spreadsheet.  Most users would go to the spreadsheet and search and type, search and type,....  No more!  Assuming that you can import the data into Excel (by loading a text file), this command will do that searching and loading for you. (You may need to trim blanks after importing text.  Use the Trim Blanks command to do that for you)

This macros asks you to do the following before it starts work:

1) Identify the input data range,

2) Specify a column which contains data identifiers for the data (such as customer names),

3) Specify the column in the destination spreadsheet where the data is to be loaded, and

4) Specify the column in the destination sheet that identifies the data in that sheet.

Once all this is done, you have one last option.  That is to indicate a "flag column" that can be used to identify data that is not loaded.  This option is optional.  In all cases data that is not loaded is colored yellow, and data without an identifier is colored red.  The advantage of the flag column is that it allows you to sort your data to group all the not loaded data together.

One warning about data identifiers.  They should be unique.  If not, then the wrong row may be selected.


Trim Blanks

If you import data into Excel from ASCII files or copy data from one program via screen copies into Excel, you will quite often end up with spaces in front of or following the text that has been copied.  The trim blank command eliminates the pain of having to manually remove those spaces to make the text fit in the cell.  Just highlight the range for it to act on and let it loose.

Please note that if there are extra spaces between words, these extra spaces are also removed.


Activity Timers

If you need to keep track of how long you work on a given project, then there are 5 activity timers available for you.  They can all run at once, allowing you to time different parts of an overall task.  And, they can be suspended or reset as needed.  The following screen illustrates the timer panel.

You can get to this timer panel very quickly by selecting it from the QuickAccess Menu!  O

To activate a timer, just click on the check box so that an X appears in it.  To suspend timing, just click to remove the X.


Print the Macro List

This command does just that.  This way you can make notes on the list, decide what you want to run and show the list to all your friends so that they will use Bob's macros also (after registering of course).


Remove or Show Macro Commands on the Excel Menus
Remove or Show Macro Commands

One thing that Bob's macros do is that they put a number of commands under Excel's menus.  Like an option to go to the File Manager is now found under the File menu.  And there are more.  But if you don't want them added, you can remove them (except for one or two that give you access to Bob's Macros and its help) from the menu's using this command.  To make the change permanent, use the save command on the macro list.  Otherwise, the original setup will be in effect on startup.


Color Cells Based on IF Test

This macro allows you to change the color and appearance of the cell and its contents based on an IF test.  The test may be a numeric test, or it may be a text test.  The following panel shows the various options available:

If the Colorize box is checked, then there are further options:


Set the Target Column/Row

This macro works with the following four macros:

Fill To the Target Column

Fill to the Target Row

Select to Target Column

Select to Target Row

 	This macro allows you to select the target row and/or column.  The target row has meaning only when you use the fill or select to the target row macros.  The target column only has meaning if you use the fill or select to the target column macros.

The select or fill that occur is to the target whether it is above or below the active cell (for the row macros), or to the left or right (for the column macros).


Select to the Target Column

This command selects from the active cell to the target column.  The number of rows selected is based on the highlighted rows.  If the target column is to the left, then cells to the left are selected to and including the target column.  If the target column is to the right, then cells to the right are selected to and including the target column


Select to the Target Row

This command selects from the active cell to the target row.  The number of columns selected is based on the highlighted columns.  If the target row is to below the active cell, then cells down to the target row are selected to and including the target row.  If the target row is above the active cell, then cells up to the target row are selected to and including the target row


Sticky Notes

Sticky notes are an easy way to put a note in a spreadsheet that does not go into a cell and is at the same time clearly visible.  Furthermore, you have the option to make the note printable or non-printable.  The latter is very useful for a quick documentation note or reminder.

When you select the stick note option, a box pops up giving you a number of options.  The following illustrates this box:


Select Cells to the Right
Select Cells to the Right of the Selected Range

 	The select to the right command selects all cells from the highlighted cells to the first blank to the right.  If there are no cells to the right of the top cell in the selected range, then the first row with data above the selected range is used as the basis of selecting cells.


Select All Cells to the Right

The select to the right command selects all cells from the highlighted cells to the last entry to the right.  If there are no cells to the right of the top cell in the selected range, then the first row with data above the selected range is used as the basis of selecting cells.


Select Cells Below the Selected Range

This command selects all cells below the highlighted cells to the first blank.  The first blank is based on the first column in the selected range.  If there is no data below the first cell in the selected range, then the first entry to the left is used as the basis of the selection.


Select All Cells Below the Selected Range

This command selects all cells below the highlighted cells to the last entry in the first column.  If the first column is blank,  then the first column to the left is used as the basis of the selection.


Months Down or Across

How often do you have to type in the names of the months?  Microsoft Excel has a nice feature, drag and fill, to do this - if you have a slow computer.  But if you have a fast one, how often have you zoomed to far left or down and then over-corrected, etc.?  Well, I got tired of this, thus the macro.

This macro is both on the list and under the Insert menu in Excel 5.  The following selection box appears when this macro is run:

Pick the option you want and presto, the entries are made!  No more dragging and guessing or overdragging!


Insert Date, Time, File Name and Sheet Name

I also got tired of typing in the date and time on worksheets and having to format the cells and range value the result (when I used the NOW() command).  Thus this macro was born.

This macro is both on the list and under the Insert menu in Excel 5.  The following selection box appears when this macro is run:

This macro allows you to enter the date and time, just the date or just the time into a worksheet.  And, you don't have to browse a massive list to find your favorite format.  It stores your format for you so that you don't have to search a format list for the a desired format.  If you load in your desired formats, be sure to save the macro file using the save command on the macro list.  Otherwise, they will not be there the next time you load Bob's Macros.

To create a custom format, use m's for the month, d's for the days, y's for the years, h's for hours and m's for minutes, and s's for seconds.  You may also use the characters "/", ":", "-" and spaces to further define the format.  The following table shows you what the various combination of entries do:

Entry	Samples

DAYS:

d	1, 2, 10

dd	01, 02, 10

ddd	Mon, Tues,...

dddd	Monday, Tuesday,...

MONTHS or MINUTES

m	1, 2, 10

mm	01, 02,10

mmm	Jan, Feb,...

mmmm	January, February, ...

YEARS

y	93, 94,95,...

yy	93, 94

yyy	1993, 1994,...

SECONDS

s	1, 2, ...., 10, 11,..

ss	01, 02,...., 10, 11, ...

HOURS

h	1, 2, ... ,10,11,...

hh	01, 02, ...10, 11,...

The following are some sample formats and their result:

Sample	Result

m/d/y	1/1/94

m/d/yy	1/1/94

mmm d, yyyy	Jan 1, 1994

mmmm d, yyyy	January 1, 1994

h:m:s	1:22:33

h:m am/fm	1:22 AM


Insert a Circle

This command inserts a circle around the selected cells.  This command is located not only on the Macro selection list, but also under the insert pull down menu.  This is a quick way to draw a circle without having to display the draw toolbar.  And, once the circle appears, you can resize it as needed.


Excel 4 version vs Excel 5 version

The Excel 5 version of Bob's Macros has several more macros than the Excel 4 version.  If you are running Excel 5 or upgrade to Excel 5, you should use the Excel 5 version of Bob's Macros.  If you run the Excel 4 version of Bob's Macros in Excel 5, the Excel menu's will not be customized for the new commands available from Bob's macros.

If you are using Excel 5 but have chosen the option to use the Excel 4 menus, the menus will not be customized with the additional commands available from Bob's Macros.  Only the Excel 5 menus are customized by the Excel 5 version of the macros.

One place where the Excel 5 version is slightly faster is in the very first macro, the Find a macro command.  Consider that another reason for upgrading to Excel 5!

Also, do not run both the Excel 4 and the Excel 5 version of Bob's Macro's at the same time.  There is no telling what will happen!


Blank Cells Based On Reference Row

If you used the command

Fill Highlighted Cells ALL the Way to the Right,

then you will probably end up with cells that shouldn't have entries in it.  For example if you have skip columns to improve readability, then those skip columns will have had cells copied into them.  That where this command helps.

First,  select the range of cells (multiple rows allowed) that need cells blanked or erased.  Then run this command (Blank Cells Based on Reference Row).  You are first asked to specify the reference row.  Once this is done, the macro takes over.  It compares the selected cells to the reference row.  If the cell entry in the reference row is blank, then the cell in the selected range is erased.


The Commands Added to the Excel Menus

Bob's Macros not only gives you a list box that allows you to select a macro, it also adds quite a few of the most likely used macros to the Excel Menus.  The following lists the commands added to the Excel 5 menus.  The commands have also been added to the Excel 4 menus if you are running Excel 4.  And, if you are using Excel 5, we've added a way to customize the Excel Menus yourself.  Please note that if you are running Excel 5 but are using the Excel 4 menus, no commands are added to the Excel 4 menus.

NEW EDIT POPUP COMMANDS

Clear Contents

Clear Formats

Clear All

Fill to the Right

Fill Down

Fill ALL the way to the Right

Fill ALL the way Down

Select to the Right

Select Down

Select ALL the way to the Right

Select ALL the way Down

NEW FILE COMMANDS

Initialize Selected Sheets...

Save All Files

Close All Files

Create an ASCII File...

Go to the  File Manager

NEW EDIT COMMANDS

Fill Right

Fill Down

Text Flow a Paragraph

Indent Text..

NEW VIEW COMMANDS

Show Bob's Tool Bars...

Zoom to Selection

85% Zoom

100% Zoom

Set All Selected Sheets to the Same View...

Relocate Current Cell to Upper Left

Relocate Using Last Relocate

Store Location

Return to Stored Location

NEW INSERT COMMANDS

Sticky Note...

Sticky Note...

Months...

Circle

Date and Time...

Blank Row Every X Rows...

Remove Blank Rows

NEW FORMAT MENU COMMANDS

0             (with commas)

0.0

0.00

0.000

0%

0.0%

Blue and Bold

Shade Light Gray

Convert to Lower Case

Capitalize 1st Letter Only

Convert to Proper Case

Capitalize All Letters

NEW TOOL MENU COMMANDS

Find a Macro Cmd...

Multiply by Any Value...

Divide by Any Value...

NEW DATA MENU COMMANDS

Re-arrange Range Based on Reference Column...

Re-arrange Range Based on Reference Row...

Blank Duplicate Entries

Fill Blanks with Cell Entry Above

Compare 2 Columns...

NEW WINDOW MENU COMMANDS

Cascade Screens

Windowize Selected Sheets

Set All Windows of a Worksheet the Same...

Synchronize Windows...


Delete the Active File

This command displays a menu which gives you three options:

- Delete the active file

- Close but do not delete the active file

- Do Nothing!

The name of the active file and its directory is displayed above the three options.  If you choose delete, the file is first closed and then deleted from the disk.

One way to use this command is to first open a group of files by holding the control key down and highlighting the files to be opened.  Then, after the files are opened to repeated run this macro using the repeat key.


Customizing the Excel Menus

The Excel 5 version of Bob's Macros contains a sheet named "User Macros".  This sheet contains a macro named "User_Startup_Commands" that can be customized to add commands to the Excel menus.  This macro is run automatically when the macro file is loaded so that the commands are immediately executed and the menus modified.  That way, the commands on the menus are the ones you prefer vs the default set.  This sheet also includes a macro named "User_Close_Commands" that allows you to remove commands you've added to the menus if you close the macro file but remain in Excel.

If you would like to learn more about writing macros before you attempt this work, you will find a fair amount of documentation on adding commands in the Excel 4 function Reference book.  Much of this is also available online in the Excel 5 online help.  Please note that Bob's Macros are in the Excel 4 macro language.  If you prefer, we at Macro Systems would be glad to customize the menus for you.  Our charge for doing this is $30.

One caution before you read too far.  The macro file has an auto_close macro that automatically closes the macro file and does not prompt you to ask if the file has been modified.  So, if you add commands in this section, you must save the file when you change it otherwise you will not get a chance to save the file when you exit Excel.

The first step in customizing is to identify the macro names of the macros you want to add.  Do this by selecting the command "Display Macro Command Names..." from the macro selection list.  (It is near the bottom of the list)  Repeat this process until you have identified all the macros you want to add to the menus.  Please note that all the names begin with Z_, and have no spaces between words (underscores are used).  The reason for beginning the macros with Z_ is so that they appear at the bottom of the macro selection list.  This way, macros you add appear above them.

The next step is to unhide BOBSMACS.XL5 and go to the sheet named "User Macros".  Copy the template command into the user startup macro between the macro name and the =RETURN().  Add blank rows if necessary.  DO NOT delete the rows with the words "User_Startup_Commands" or "=Return()".  The template looks like this:

ADD.COMMAND(BarNumber,"Menu",{"Description","Macro"},Position)

The next step is to edit the commands you have added to the startup macro.  When you do this, it is very important that you do not delete the {}'s.  They are needed.  If you accidentlly delete one of the {}'s, start over - recopy the template.  The {}'s are more than characters.  They represent an array entry, and deleting one may remove the array command.

The first edit step is to decide which menu bars you want to add commands to.  For example, the Excel 5 menu bar which appears at the top when a file is displayed and has the menus File, Edit, View,.etc is menu bar 10.  The menus that pop up when you right click on a cell are on menu bar 7.  Replace the phrase "BarNumber" (and its double quotes) with the appropriate number.  The following is a list of the major menu bars and their numbers:

Bar number	Built-in menu bar

1	Worksheet and macro sheet (Excel 4.0)

3	Null (the menu displayed when no workbooks are open)

7	Cell, toolbar, and workbook (shortcut menus)

10	Worksheet and Macro sheet (Excel 5)

The next step is to specify which menu is to be modified by replacing "Menu" with either a number or a name enclosed in double quotes.  If a number, do not enclose the number in quotes.  For example, If adding a command to the File menu that appears while you are editing a file, the entry would be "FILE".  If adding a command to the cell popup menu, the entry would be a 4.  The following table is a list of the menu numbers you would use if you are adding a command to a popup menu (bar number 7)

Menu	Menu Number

Cell Popup	4

Column Popup	5

Row Popup	6

Workbook Popup	7

Macro Cell Popup	8

The next edit step is to replace the word "Description" with the description of what you want to appear on the menu.  It can have spaces and can be quite wordy, although the few words probably the better.  It must be enclosed in double quotes.

Then, replace the word "Macro" with the name of the macro.  It must be enclosed in double quotes.  Please note that all the macros in Bob's Macros begin with a Z_ so that they appear below a list of user macros if one is running macros from the Excel macro list.  Also, if the macro name includes underscores (_'s), they must be included.  And, no spaces are allowed.

The last edit step is to specify the position on the menu where the description is to appear.  If you want the command to appear last, just remove the word "Position". (Commands will be added in the order that the add.command macros are run).  If you want to specify the position, then replace the word "Position" with a number.  You must either remove the word "Position" or change it to a number. Please note that figuring out the correct number to position a command is a trial and exercise headache.  You would run your macros, examine the results, remove the commands, edit the macros, etc.

For instructions on how to  remove menu commands you have added, click on the following:

Removing Commands

For example, you would want to have menu commands removed if you close the macro file and stay in Excel, or if you are trying to determine the correct position number for a macro.

To test run your macros, do the following:

1) Position the cell pointer on the first macro to be tested.

2) Click on the RIGHT mouse button.  A pop up menu will appear.

3) Select Run

4) Select Step

5) Select Evaluate over and over to step through the macro.  If it runs correctly, the final evaluation will be the word "TRUE" or with a number.

If an error occurs, you obviously will need to correct the macro, and repeat the above steps.

The last step in checking out your work is to go to another worksheet and test the commands you have added.  If you have typed the macro name correctly, it will execute.  Otherwise, you need to fix your entry.

The following are some sample commands:

=ADD.COMMAND(10,"File",{"Initialize Selected Sheets...","Z_Initialize_Spreadsheet"},2)

=ADD.COMMAND(10,"File",{"Save All Files","Z_Save_All"},9)

=ADD.COMMAND(10,"File",{"Close All Files","Z_Close_All"},5)

=ADD.COMMAND(10,"File",{"Create an ASCII File...","Z_Create_Ascii_File"},11)

=ADD.COMMAND(10,"Insert",{"Months...","Z_Months"})

=ADD.COMMAND(10,"Insert",{"Circle","Z_circle"},14)

=ADD.COMMAND(10,"Insert",{"Insert Blank Rows...","Z_Insert_Blank_Rows"})


Removing Commands

To remove commands you have added to a menu requires you to construct a macro that removes the command.  If you want these commands to execute each time you close the macro file, include them in the macro titled "User_Close_Commands" located on the "User Macro" sheet.

The following is a template macro that you can follow to construct the remove commands you need.

=DELETE.COMMAND(BarNumber,"Menu","Description")

=RETURN()

The restrictions on these entries are discussed in the help topic called Customizing the Menus.  You can have as many DELETE.COMMANDs as you need.  You must end the commands with a cell containing the command "=RETURN()"

The following are sample commands:

=DELETE.COMMAND(10,"Insert","Months...")

=DELETE.COMMAND(10,"Insert","Circle")

=DELETE.COMMAND(10,"Insert","Insert Blank Rows...")


Insert Blank Rows If Cells Are Different

Lets say you have a set of data and wish to insert blank rows every time the contents of one of the selected column changes.  For example, the rows may contain data like the following

Representative	Customer

John	IBM

John	Microsoft

Bill	Dupont

Bill	Ford

Bill	GM

Linda	US Air

If you highlight the Representative data and select this macro, it will automatically insert the number of rows you specify whenever the representative changes.  The result is a much more readable set of data.  And, if you need to remove the blanks rows, there is a macro called "Remove Blank Rows" that will remove the blank rows for you.

The first column in the selected range is the one that is used to determine if row entries are different.  Also, you have the option of inserting blank rows or shifting the selected text down.


The QuickAccess Menu

In addition to adding over a 100 commands available from the macro list and enhancing the Excel menus, Bob's Macros also adds a new menu, called QuickAccess  (Actually "Quik" on the Excel menu).  It provides quick access to the following Windows features:

Calendar

Card File

Character Map

Clock

Control Panel

File Manager

Notepad

Print Manager

Recorder

Write

In addition to easy access to the above, it also has selections that give you quick access to:

Program Manager

DOS

Lastly, the QuickAccess menu also provides access to the alarm macro and timer macro that are supplied with Bob's Macros.


Save All Files

The Save All Files commands help you to save all open files without having to go to each file individually and do the commands.  As each file is saved , the Excel display at the bottom of the screen is activated to show you the save activity.  You have the option of saving or not saving hidden files.


Close All Files

The command Close All Files is a quick way to close all open files.  If the file has been modified you will be prompted to save the file before it is closed.  Please note that Excel is extremely sensitive to what it perceives as changes to a file.  Sometimes if you've saved all the files and then close them using the close command, Excel will prompt you to save the file again.  Your decision.  However, you know what you've done and if the file really needs to be saved again.


Colorization Commands

The purpose of these macros is pretty obvious from their titles.  Hopefully, they will save you some time.  The Blue and bold format is a useful way to highlight cells that contain data entries vs those containing formulas.

Color Selected Range Light Gray

Color Selected Range Dark Gray

Blue and Bold Text


Copy Column Widths

This command is useful when your copying information from one file to another and realize that you've also need to set the column widths the same.  Which you could do manually, but this macro does it for you automatically.  And, the columns do not have to be the same.  For example, you can copy the widths of columns K-Z into another file or sheet starting at column C


Questions and Answers

This is a new section to the macros.  Your questions would be appreciated so that we can expand this section!

Q - I get the error message "Macro Z_Select_Macro_To_Run" error message or the error message "BOBSMACS.XLS not found".

What has happened is that you loaded Excel and then loaded the macros.  The macros then created the toolbar with the lightning bolts.  Everything worked fine for a number of days.  At some point you moved the macro file from one directory to another using file manager.  Now, when you click on the macro icon, it says it can't find the macro Z_select_macro_to_run (Excel $ message) or else it says it can't find the file (Excel 5 message).

    What's really happened is that it can't find the macro file because it's been relocated.  If you first manually open the macro file, it will delete the old toolbar and create a new one, and things should work fine.  And, in the future as long as you don't relocate the file, clicking on the icon will also automatically load the file.  Putting the file in your startup directory is preferred as this allows the macro file to add commands to the various Excel menus upon startup of Excel.

Q - When I click on the icon that popped up the macro selection list, I get the message "Cannot find Bobsmacs.xl5" or some other nasty message"

There are several possible causes.  This can be cause if the file is not loaded and has been relocated to a different directory after the icons were created.  Regardless of the cause, the simplest solution is to delete the offending toolbar, exit out of Excel, go back into Excel, and reload the macro file.  This will allow the macro file to recreate the toolbars.  (You may be able to skip exiting Excel, but better save than sorry).

To delete a toolbar, right click on it, select toolbars, make certain that the one to be deleted is highlighted, and then select the delete button.  (Making a toolbar disappear by clicking on the upper left box of a toolbar does not delete it, but just hides it.)

Q - How do I control the calculation option?  No matter what I set it too, it changes the next time I load Excel.

When you save a file, Excel stores the calculation option with the file.  For example, if you turn calc off and save a file, the calc off setting is stored with the file.  The purpose of this is simple:  When you load the first file into Excel, Excel sets the calculation option based on what was stored in the file.  Normally this doesn't create a problem.  Make sure the calculation setting is the way you want it when you save Bob's Macros.   Why?  Because Bob's Macros is the first file you open since it is in the startup directory, and thus sets the calculation option to the way it was when the macro file was saved.

Q - Why can't I get at the macros to customize them to my needs?

We've set the macro file up so that the macro code is very well protected, for several reasons.  First, we did not want someone modifying it and then releasing it to others with code different than what we wrote.  Second, one can not get at the code of most other programs such as Excel, Word, etc.  Bob's Macros is the same way.

Q - Do I have to put the file in my startup directory?

No, but if you do, this allows the additions to the menus to be immediately available.


Set Print Titles

The Set Print Titles macro is found only in the Excel 5 release of Bob's Macros.  The reason is simple - It exists in Excel 4 right off of one of the pull down menus, thus there is no need for it in the Excel 4 version.

This macro is very useful if you first assign it to a tool on one of your toolbars.  The name of the macro to assign to the tool is Z_Set.Print.Titles.  To add a tool to one of your toolbars, right click on the toolbars and select customize.  Then select the button to use from the custom buttons.  Specify the macro and close the menus.

To use the macro, first select the rows and columns to be the print titles.  To select both at the same time, first select the rows and then hold the CTRL key down and select the columns.  Once you do this, run the macro (or if you've assigned it to a button, click on the button).


About ReportRunner - Another Macro Systems Product

ReportRunner is a shareware product by Macro Systems.  There is a version for Excel 4 and one for Excel 5.  It is spreadsheet and a set of menu driven macros that is designed to help you print multiple reports and graphs.  You do not need to know any macro code to use.

To use, you store report setup such as the print area, margin settings, the heading, etc.  in the ReportRunner spreadsheet.  This is done by an option on the ReportRunner menu.  You can store as many report definitions as you want in the spreadsheet.  And, you can selectively printout or just go to any report or group of reports.

For example, you may want to print out nine different reports , several of which are off of the same sheet.  Without ReportRunner, you would have to open the files, select or confirm the different print areas, and make any needed changes to the page setup from one report to another.  With ReportRunner, all you do is to select the report descriptions in the ReportRunner file and issue the print command from ReportRunner's menu.  Then sit back and enjoy!

ReportRunner opens files, prints reports, and close files automatically for you.  Thus, if you have many files and reports that need printing, ReportRunner will totally automate the task for you.


ACCESS TO THE MACRO CODE

Unregistered users do not have access to the macro code.  It is hidden from access.  If you register, you will be given full access to the code, subject to the restrictions in the conditions of use in this help file.  This way, you can tailor the macros to your specific needs.


Glossary

A

As each file is saved

C

Casade Screens

F

F8 then allows extension

Format to 0

G

Go to the File Manager

I

Insert Horizontal Page Breaks Every X Rows

L

Lots of Options

R

Remove the Print Area, Titles, Page Breaks...

Z

Zoom to 85%


Zoom to 85%

This command is most useful if you have a 17 inch or larger monitor.  The 85% zoom setting gives you a little bit more on the screen without impacting the readability of the screen


Casade Screens

Although this is one of Excel's selections, I've located it on the Windows pull down menu as opposed to where Excel has it located, which requires more mouse clicking.


Go to the File Manager

This command in addition to being on the macro list is also on the File pull down menu.  Use the Windows file manager to open and close files when you want to see  a bigger list with more information.

    And, you can use the file manager to rename, move, copy, etc.  Bob's Macros won't take credit for these features, just the convenience of getting to the  file manager.


As each file is saved

If saving the file will result in replacing a file by the same name then Excel's pop up menu to confirm this is displayed for your decision on what to do.


Format to 0

This command not only formats the cell to the "0" format, it also adds commas between thousands.


F8 then allows extension

The F8 key is actually a Microsoft Excel programed key.  It allows you to extend the select range to where ever you click until you either press the F8 key again, press the ESC key or do an Excel action.  SO, after you use it to select the range, press the ESC key.


Lots of Options

You can add text to cells containing text or only to cells without text.


Remove the Print Area, Titles, Page Breaks...

If you don't like to see the print area, have put in too many manual page breaks, etc, this command offers you a way to get a fresh start!


Insert Horizontal Page Breaks Every X Rows

One trick you can do here!  When the box pops up to ask for the number of rows, you can enter a formula.  For example 33-7.  Why, well if the next break is due on line 33 starting from line 7, it makes it easier