| EXCEL VBA PRIMER | CCE 170 Brigham Young University Norm Jones |
| INTRO - | [Table of Contents] |
| EXCEL - | [Formulas] [VLOOKUP] [Validation] [Names] [Operators] [IF Function] [Goal Seek] [Charts] [Annotation] [Array Formulas] |
| VBA - | [VB Editor] [Macros] [Controls] [Ranges] [MsgBox] [Variables] [Objects] [If Then] [Debug] [Loops] [Arrays] [Strings] [User Forms] [Functions] [Excel Funs] [Subs] [Events] [Standards] |
| ADVANCED - | [Graphics] [E-mail] [Files] [Apps] [Google Sheets] |
The following spreadsheets, documents, and VB modules represent examples of how VBA can be used in advanced applications within Excel and by other programs in addition to Excel. Right click on each link to download. I typically discuss these samples at the end of the semester.
| Title | Link | Description |
| Weekly Appt. Message Generator | MailAppts.bas | This is a VBA module for Outlook that will search through appointments in the default calendar and find all appointments of a specified type within a given window (one week for example) of the current date. These appointments are then listed in a new html formatted e-mail message. |
| MS Word Calendar Generator | calendar.dot | This is a macro for MS Word that I found on the internet. It is attached to a document template. Simply open up the template in Word and it should prompt you with a user form. If that doesn't work, go to the Macros command in the Tools menu and manually start the macro. This macro will search through your default calendar in Outlook and generate a beautifully formatted calendar in Word that you can print or save. |
| MS Word Remove Line Breaks Macro | RemoveBreaks.doc | This is a little macro I recorded in Word that is useful for taking text from an e-mail message and removing the extra line breaks. To use it, open up this document and cut and paste the text from your e-mail message to the document. Then run the macro from the Tools menu. The macro searches through the text in the document and gets rid of the extra line breaks in the middle of paragraphs. It assumes that all paragraphs are delineated with double line returns. |
| Auto Mail Script | ReminderEmail.vbs | This is a VB script that can be run simply by clicking on the file or you can launch it on a repeating basis using Windows scheduling. It generates an e-mail message to a list of recipients. To use this, you will need to add it to your Outlook VB code and then modify a few lines of code (which are all clearly marked). |
| AutoCAD Polygon Area Plotter | polyarea.bas | This was sent to me by a former student. I have not tried it personally. It generates a text tag on a set of polygons indicating the area of each polygon. |
| Get Directory | get_dir.bas | This is a VB module with functions for prompting the user with the standard Windows dialog for selecting a directory. Can be used in any VBA application. |
| Get File | get_file.bas | This is a VB module with functions for prompting the user with the standard Windows dialog for selecting a file. Both the import and export version of the file selector dialog are supported. Can be used with any VBA application. |
| New Letter | NewLetter.bas | This is an Outlook macro. If you first select a contact and then run this macro, it will generate a formatted business letter in Word to the contact. |