Engineering Optimization

Appendix FBrief on VBA Programming: Excel in Office 2013

F.1 Introduction

This is intended as a basic how‐to program in Excel VBA. It presumes that you can program, in some computer language, and just need to know how to do it in VBA. I’m currently running Office 2013, but the operations are very similar in other versions.

F.2 To Start

• Open an Excel worksheet (and save it as macro‐enabled, as .xlsm).
• Set the Macro Security Setting to Medium. Click on the Office Button in the upper left of the menu bar, then the Excel Options button in bottom of window, then Trust Center in the left hand menu, then Trust Center Settings in middle right of window, and then choose your security preference (“disable with notification”, probably). Finally click OK.
• Press ALT‐F11 to open the VBA editor. Alternately, use the “Launch VBA” icon on the Developer Toolbar. [To install the Developer Toolbar, click on the “Office Button” (upper left), click on “Excel Options” (lower border of new window), and in the “Popular” set, check the “Show Developer Tab in the Ribbon” box. Close the “Office Button” window, and you should see the “Developer” tab on the upper ribbon.]
• In the new open window “VBA Editor”, you should see three windows (Project, Properties, and Immediate) and a grayed space for another window. If the “Project” window does not appear, use the “View” menu item to add “Project Explorer”.
• Right‐click in the Project window, and then click on “Insert”, then “Module”. This will open a window ...