Microsoft Excel VBA Examples

The intent of this page is to show some useful Excel VBA (Visual Basic for Applications) examples that I have picked up in the process of creating my own applications. It is not intended that this page be "state of the art" VBA programming (for that I recommend microsoft.public.excel.programming) but just fairly simple subroutines that illustrate formats and how to use the syntax. Most of these routines I wrote myself, but some were "cloned" from other sources.

Note: I have tried to make these subroutines as "vanilla " as possible, however some of the subs were copied directly from my applications. In those cases, you will need to substitute your own sheet names, range names, cell addresses, etc.

Links to Other VBA Sites

• Send Outlook Mail Message: This sub sends an Outlook mail message from Excel.
• Show Index No. & Name of Shapes:  To show the index number (ZOrderPosition) and name of all shapes on a worksheet.
• Create a Word Document:   To create, open and put some text on a MS Word document from Excel.
• Find:  This is a sub that uses the Find method to find a series of dates and copy them to another worksheet.
• Arrays:  An example of building an array.  You will need to substitute meaningful information for the elements.
• Replace Information:   This sub will find and replace information in all of the worksheets of the workbook.
• Move Minus Sign:  If you download mainframe files that have the nasty habit of putting the negative sign (-) on the right-hand side, this sub will put it where it belongs.  I have seen much more elaborate routines to do this, but this has worked for me every time.
• Counting: Several subs that count various things and show the results in a Message Box.
• Selecting: Some handy subs for doing different types of selecting.
• Listing: Various listing subs.
• Delete Range Names:   This sub deletes all of the range names in the current workbook. This is especially handy for converted Lotus 123 files.
• Type of Sheet: Sub returns in a Message Box the type of the active sheet.
• Add New Sheet: This sub adds a new worksheet, names it based on a string in cell A1 of Sheet 1, checks to see if sheet name already exists (if so it quits) and places it as the last worksheet in the workbook.   A couple of variations of this follow.  The first one creates a new sheet and then copies "some"  information from Sheet1 to the new sheet.  The next one creates a new sheet which is a clone of Sheet1 with a new name.
• Check Values: Various different approaches that reset values. All of the sheet names, range names and cell addresses are for illustration purposes. You will have to substitute your own.
• Input Boxes and Message Boxes: A few simple examples of using input boxes to collect information and messages boxes to report the results.
• Printing: Various examples of different print situations.
• OnEntry: A simple example of using the OnEntry property.
• Enter the Value of a Formula: To place the value (result) of a formula into a cell rather than the formula itself.
• Adding Range Names: Various ways of adding a range name.
• For-Next For-Each Loops: Some basic (no pun intended) examples of for-next loops.
• Hide/UnHide: Some examples of how to hide and unhide sheets.
• Just for Fun:  A sub that inserts random stars into a worksheet and then removes them.
• Unlock Cells:  This sub unlocks all cells that do NOT contain a formula, a date or text and makes the font blue.  It then protects the worksheet.
• Tests the values in each cell of a range and the values that are greater than a given amount are placed in another column.
• Determine the "real" UsedRange on a worksheet.  (The UsedRange property works only if you have kept the worksheet "pure".
• Events:  Illustrates some simple event procedures.
• Dates:   This sub selects a series of dates (using InputBoxes to set the start/stop dates) from a table of consecutive dates, but only lists/copies the workday dates (Monday-Friday).
• Passing Arguments:  An example of passing an argument to another sub.

LE FastCounter