Auto Macros in Word and Excel

Visual Basic for Applications can come in quite handy for creating powerful spreadsheets, tracking the progress of a project or creating master documents for a design automation system. One very powerful feature is the ability to automatically execute macro code within the document.

When you enter the Visual Basic editor, you will notice the Project Explorer on the upper left side. If you double-click the “ThisDocument” object for Microsoft Word, or “ThisWorkbook” object for Microsoft Excel, you will be able to add code to the document or workbook object. Simply select Document or Workbook from the left-hand, pull-down menu at the top of the main window, and look at the options in the list on the right.

In Microsoft Excel, you have a ton of options including “BeforeSave” and “BeforeClose.” Microsoft Word provides you with Close and a bunch of less than useful choices. Whichever document type you are creating, you can now enter macro code that will be run before exiting or saving the document, which is great for last-minute formatting tasks or even exporting the document as another file format.

Some examples of functionality that Razorleaf has instituted include:

  • AutoSizing columns in a spreadsheet where values of unknown length have been automatically populated;
  • Deleting unwanted text by searching for keywords (like “Qty. 0”) and deleting the paragraphs containing them;
  • Removing blank or unnecessary lines from an Excel BOM then sorting the results;
  • Exporting a document as a text file since some design automation tools cannot save directly to a text file.

Tags: , , , , , ,

Read more posts by

This entry was posted on Thursday, April 16th, 2009 at 4:23 am and is filed under Office Applications, Technical Tips. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.