This site will look better if you upgrade to a browser that supports web standards.

McGimpsey & Associates 

Home

Macintosh XL Pages

General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions

Excel VBA Macros

Newsgroups

What is an MVP?

This site will look better if you upgrade to a browser that supports web standards.

J.E. McGimpsey's XL Pages

These pages are sparse as yet, but hold some of my favorite or most useful tips for using Excel and VBA. I use a Mac and Office 2004 almost exclusively, but most of what you find here can be used cross-platform without modification.

General XL Tips

• Remove internal worksheet/workbook protection
• Why your sum is a penny off...
• Highlight row without losing color formatting
• Convert trailing minus signs
• Text files: how to output custom format
• Getting 6 conditional font colors without macros
• Why use -- in SUMPRODUCT formulae

XL Formulae

• Using SUMPRODUCT() to calculate variable rates and commissions
• Three-dimensional SUMIF()s
• Calculating using significant digits
• Bitwise Logical Functions
• Converting UNIX timestamps to XL date/times
• Number of Pages, using XL4M commands
• Sampling from a range
• Summing every Nth column or row
• Worksheet and Workbook names using the CELL() function

Using VBA with XL

• Where to put your code
• Event macros
• Insert sheets to the right
• Create an accumulator
• Hide cells when printing
• Speed up Page Setup
• Looking up and displaying pictures based on cell value
• Easier Time Entries
• Invoices and other Sequential Numbers
• Changing Sheet Name to match cell, automatically
• Cells() property gives a run-time error

XL VBA Functions

• Random integers without repetition
• Convert long decimal numbers to binary
• Return references to a previous sheet
• Converting UNIX timestamps to XL date/times
• Page Setup using XL4M command
• Sampling from a range without duplication
• Summing only bold cells
• Concatenating a range of cells - MultiCat()
• Displaying the page number of a cell

XL VBA Macros

• List workbook formulas to a separate sheet
• Convert trailing minus signs
• Protect grouped sheets
• Merging data in multiple columns into one
• Merging the selection into one cell
• Text files: how to output custom format
• Hide cells when printing
• Convert email addresses to mailto: links
• Scrub a mailing list
• Delete every Nth Row
• Path and Filename in headers and footers - for all workbooks
• Print different first page headers or footers

This page last updated on Saturday, 31 March 2005

© Copyright 2001 - 2007 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.

Quick Links

New to Newsgroups? See Chip Pearsons's Hints And Tips For New Posters In The Excel Newsgroups

New to Macros? See David McRitchie's Getting Started with Macros and User Defined Functions