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.
Read more posts by Paul Gimbel