|
|
|
Technology
>>
Programming
>>
Introduction to Visual Basic for Applications
|
Introduction to Visual Basic for Applications
Writing a First VBA Program
Aug 1, 2009 © Harry P. Schlanger
Visual Basic for Applications is a high level visual programming language available in all Microsoft
Office products. To use it, one just needs to look "under the hood".
Users wishing to increase the power of their applications in MS Office products including Excel, Word, and
Access may turn developer when discovering and applying the power of
Visual Basic for Applications, or simply VBA.
The MS Access Development Environment
The VBA language can easily be demonstrated using MS Access. This is a suitable integrated development environment
(IDE), since a user interface can be built up using Access forms. Objects are available in the IDE, such as:
- Labels
- Text Boxes (input boxes)
- Buttons
- Dropdowns (called "combos")
- List boxes
- Etc.
When the user clicks on any of these controls, it is called an "event"; for example, clicking on a button or
hitting "Enter" after typing in data.
It is possible to run code as a result of events. The code is originally typed into
a code window and this block of code that is associated with the event is then called an "event procedure".
VBA can provide the necessary logic between the users' input data and the database, making data
storage and editing possible.
However, this article is limited to explaining the first steps: how to
access the code window and write code. When a control such as a button is activated by a clicking event,
this allows the user to run the program "behind" the button. A working application written in Access
2007 that is described here, is available for
download.
Creating the MS Access Form
On opening MS Access, a
security feature with a button marked “Options…” located under the top menu, should be
clicked and the “Enable Contents” checkbox checked.
To create a form, click on the "Create" menu and select "Form Design". Then save the form as "frmCalculate".
Next, create the button control:
- Click on the Button design icon and drag a rectangular button shape on the form, select Cancel.
- Click on the Properties sheet and click on the ellipsis of the On Click event procedure
- Select Code Builder, OK
Typing in the VBA Program
With the opened code window, one may type in the following simple Visual Basic for Applications program.
The user is asked to enter a student test score and maximum score. The program
then determines the percentage score and displays it to the screen.
' Declare the variables
Dim Score, Answer As Single
Dim Maximum As Integer
' Initialise the variables
Score = 0
Maximum = 0
Answer = 0
' Set the variables
Score = InputBox("Please input score earned by student", "Enter Score", Default)
Maximum = InputBox("Please input MAXIMUM score possible", "Enter Score", Default)
Answer = (Score / Maximum) * 100
' Output the result to the screen
MsgBox ("Answer is: " & Answer & "%")
Running and Debugging the Program
To run the program, one needs to click on the "View Form" icon located at the top left of the Access IDE.
This will display the button on the form interface. On clicking the button, an input dialog box will ask
for the student score. A value of 15 could be entered. When the program asks the user to enter the maximum score, a value of 20
could be entered. A message should then appears "Answer is: 75%" as shown in the figure below.
Should the program have errors of logic or syntax, execution will stop. Fixing the errors is called "debugging".
For this purpose, the MS Access development interface has excellent debugging tools.
For more information about VBA programming language, the reader may search the Internet for tutorials, such
as at FunctionX.
The copyright of the article Introduction to Visual Basic for Applications: Writing a First VBA Program is owned by Harry P. Schlanger.
Permission to republish in print or online must be granted by the author in writing.
smart-koala.com © 2009 All Rights Reserved
|
|
Custom Search
Links:
Website Construction:
Gum Leaf Designs © 2009
|
