VBA Development Configuration

Working with VBE

  • VBA development is done in Visual Basic Editor (VBE)
  • VBE is a separate application that works seamlessly with Excel
  • We can’t run VBE separately; Excel must be running for VBE to run
  • VBA modules are stored in workbook files
  • VBA modules aren’t visible unless you activate VBE

Displaying Excel’s Developer tab

  • Right-click anywhere on the Ribbon and choose Customize the Ribbon
  • In the list box on the right, place a check mark next to Developer and click OK

Activating VBE

  • Press Alt + F11
  • Choose Developer >> Code >> Visual Basic 

Customize VBE

When VBE is active, choose Tools >> Options.

  • Auto Syntax Check
    • This setting determines whether VBE pops up a dialog box if it discovers a syntax error while you’re entering your VBA code.
    • If you don’t choose this setting, VBE flags syntax errors by displaying them in a different color from the rest of the code, and you don’t have to deal with any dialog boxes popping up on your screen.

VBE Windows

  • No ribbon interface
  • Hierarchy of menu bar and toolbar
  • Many menu options have short-cut key
  • Right click for short-cut menu

VBE Menu and Hierarchy

Toolbar

VBE Project Environment

  • Project Explorer
    • Tree diagram that consists of every workbook
    • Each workbook and Add-In is known as a project
      • Think of project as a collection of objects arranged as an expandable tree
      • If you try to expand a project that’s protected with a password, you’re prompted to enter the password
    • If the Project Explorer window isn’t visible, press Ctrl+R
  • Code Window
    • Contains VBA code
    • Every item in a project’s tree has an associated code
    • Unless you’ve added some VBA code, the code window is empty
      • The code module that’s displayed may not correspond to the highlighted object in the Project Explorer window
    • Every project expands to show at least one node called Microsoft Excel Objects
      • This node expands to show an item for each worksheet and chart sheet in the workbook
    • Another object called ThisWorkbook (which represents the Workbook object)
    • If the project has any VBA modules, the project listing also shows a Modules node
    • A project can also contain a node called Forms that contains UserForm objects (also known as custom dialog boxes)
    • If your project has any class modules, it displays another node called Class Modules
  • Immediate Window
    • Execute VBA statements directly
    • Useful for testing statements and debugging code
    • If the Immediate window isn’t visible, press Ctrl + G

Leave your comment; I love them!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s