Visual Basic Programming in Excel for Institutional Researchers


This site is primarily for the purpose of providing resource files relating to VBA workshops and demonstrations given by Chris Maxwell as well as other recent presentation materials.  Provided are files that relate directly to these events, but stay tuned for updates to the "Useful bits of VBA code" section.  To ensure your work on these files is not lost, save them to your computer before exploring the code. Also, make sure your Excel setting for macro virus protection is not set on high.

AIR 2011 Presentation:  "Analysis and Communication of US News Rankings using Monte Carlo Simulations: An Update"

      AIR 2011 PowerPoint  

AIR 2011 Workshop:  "Visual Basic Programming in Excel"

   Instructions:

      AIR 2011 Instructions  

   Completed Exercise Files:

     Data Conversion.xls
     NRC Faculty Lists.xls
     CS Evaluations.xls
     Relative Range.xls
     Delete Unneeded Rows.xls
     Data Matching.xls
     Headers and Footers.xls
     NSSE Reports.xls
     Many Sheets.xls

Useful bits of VBA code: 

  Here are examples of other applications that you might find useful to adapt to your own needs.

  Review the commented VBA code (Alt-F11) and explore how it functions:

      Database Matching   Performs database-style matching of records

      Working with Data Blocks   Query Excel datasets; this example is a survey report generator

      Copy Cell Formulas   Copy and Paste formulas while preserving absolute references - Also
           includes directions on building a VBA library and creating custom menu items


      Specify Pie Chart Size   See an example of altering chart objects with VBA - Also
           includes some code on handling run time errors in custom ways


      Send data to PowerPoint   Access and manipulate other Office programs from Excel VBA

      Peer Comparison Stats   Compute peer comparison stats on any data table anywhere

      Add Headers and Footers   Also uses custom user forms- run it and see

      Color-code data elements   Highlights the min and max in each column of data


Questions: contact Chris Maxwell