Document Sample
intro_vba Powered By Docstoc

                 Introduction to VBA (Visual Basic for Applications)

VBA code is a written language capable of being understood and used by a computer with Microsoft
Office installed. In VBA you use code (instructions) to manipulate objects in an application (Excel, Word,
Access, PowerPoint, Visio, MS Project, etc).


An object is a “thing” that has:
    properties, or settings that you can change
    methods, or actions that the object can perform
    events, or actions that the object can detect and respond to

Examples of objects:
    Office in general: toolbar, menus, commands, application
    Word: documents, selected text
    Excel: Worksheet, cell, range,
    Access: table, form, report, query, text box, label


VBA code can be stored in several places:
   Modules: code that can be accessed by different events in an application is stored in modules.
   Access Forms: code that is only used for an Access form is stored in the module for that form.
   Access Reports: code that is only used for an Access report is stored in the module for that report.


A module can contain many sub procedures and functions. Code is often organized in different modules
based in its purpose.

Individual chunks of code are organized in procedures called subs or functions.

       Sub procedures perform actions but do not return values.
       Function procedures perform actions and generally return a values

Sub procedure names begin with the word sub followed by a space and the name of the sub and ending
with parentheses. Parentheses can contain the name(s) of variables that are passed to them. Subs must be
ended with the End Sub statement.

  Sub ConvertTemp()
     Dim itemp As Integer
     itemp = InputBox("Please enter the temperature in degrees F.", 1)
     MsgBox "The temperature is " & Celsius(itemp) & " degrees C."
  End Sub

74b382c6-8f79-4291-aca3-c0bfaf97fd59.doc                                                       Page 1 of 2

Function procedure names begin with the word Function followed by a space and the name of the
function and ending with parentheses which enclose a variable name. Functions must be ended with the
End Function statement.

  Function Celsius(itemp)
     Celsius = (itemp - 32) * 5 / 9
  End Function


Comments are used to comment or annotate code. Comments help explain what the code segment is
intended to do, the purpose of variables used, or just as notes to the programmer. You can insert
comments into your code by typing an apostrophe („) in front of your code. The code turns green as you
type to indicate that it is a comment and not executable code. See the example below:

Sub CopyToNewDoc()
' for a button on the TK Tools toolbar copy selection to the Clipboard
' create a new document
' paste in selection
' close the new document
End Sub

Pseudo code

Pseudo code is a method of writing code as a set of instructions in plain English. Pseudo code is used by
programmers to create an organized step-by-step outline of a procedure before actually writing the code.
You can look at pseudo code as a way of thinking through your code before you actually write it in VBA.

For example, here is a block of pseudo code that might be used to think through the preceding sub

Sub CopyToNewDoc()
   Copy the current selection
   Create a new document
   Paste the selection
   Close the document
End sub


Macros are actually VBA procedures. They are often referred to as macros because they can be created
from the Macro command on the tool s menu. This command allows you to generate the VBA code by
example. After your turn on the macro recorder you can perform actions in Word, Excel or PowerPoint
and each action is converted to VBA code which you can later execute from the Macro menu. Recording
macros is a great way to get acquainted with the basics of VBA code.

Note: The macro recording feature is not available in Access.

74b382c6-8f79-4291-aca3-c0bfaf97fd59.doc                                                       Page 2 of 2

Shared By: