Introductory Notes: Variables, Equations & Functions ...

Brigham Young University - Idaho College of Physical Sciences and Engineering Department of Mechanical Engineering

Introductory Notes: Variables, Equations & Functions Introduction to VBA Programming Language

In this section, we will learn how to write and execute a simple VBA program. In order to do such, we need to understand a bit about basic programming constructs including variables, expressions, and math operations. Much of what we've learned in Excel is directly applicable to VBA.

Variables A variable is a named element that stores information or data. The concept is the same as using a named call in Excel. Variables can hold a single value or an array of values.

The data values contained in a variable may come from a variety of places including: ? Being read from a cell in the worksheet ? Being entered interactively ? Being passed into a function through an argument list ? Being read from an external file ? Being computed from calculations within the program

Data is stored in a variable by means of an assignment statement. Note the name of the variable being assigned is to the left of the equals sign. Several examples of assignment statements are given below.

Variable Example 1 Height = 2.45

The real number 2.45 is stored in variable called Height. This overwrites any value currently stored in the variable Height. Spaces are ignored when assigning variables, except in string variables.

Variable Example 2 X=Y+Z

The math operation of addition is performed on the previously defined values of variables Y and Z. The results are stored in variable X. The values of variables Y and Z remain unchanged.

Variable Example 3 I=I+1

A common task while programming is to increment a variable, as shown in this example. The right side of the equation is first evaluated. The results are then stored in the variable on the left side of the equation, overwriting the value of the previous variable.

Variable Example 4

B="Hello Class" VBA supports a number of different data types including integers, real numbers, and text strings. Text strings must be enclosed in quotes, as in this example.

Basic Math Operators

VBA uses the same basic math operators as Excel to form equations or expressions.

Symbol

Meaning

+

Add

-

Subtract

*

Multiply

/

Divide

()

Parenthesis

^

Exponent

Comparison of Algebra Equations and VBA Statements.

Algebra

VBA

y = 2x -5

y = 2*x ? 5

c= a+b 2

y = 5x3

c = (a + b) / 2 y = 5 * x^3

p

=

(1+ i)n (1+ i)n -1

p = (1+i)^n / ((1+i)^n ? 1)

Getting Started with VBA VBA is an acronym for Microsoft's Visual Basic for Applications. VBA is a programming language that is included with the Microsoft Office suite, including Excel, Word, and Powerpoint. It is the language used when macros are developed in these applications.

VBA is not the same as VB or Visual Basic. VB is a compiled language and contains many more functions than VBA. VBA may be thought of as a subset of VB with added functionality for working directly with Microsoft Office applications.

VBA files are stored within the application in which they are used. For Excel, this means that to write and view programs, you must work in a program development environment, commonly referred to as an IDE (interactive development environment). VBA is located in the Developer's ribbon, which is hidden by default.

Enabling the Developers Ribbon To enable the Developers ribbon, follow the steps in the diagrams below:

? Check Show Developer's tab in the Ribbon under Popular

1. Click on the File tab, and then click on Options

2. Click Customize Ribbon, and then in the right column, check Developer. Then click OK.

Once the Developer's ribbon is enabled, it should remain enabled. Launching VBA With the Developer's ribbon enabled, click on the Developer's ribbon to reveal the following:

To Launch VBA, select the Visual Basic icon located on the left side of the Developer's ribbon. The VBA development environment appears, as is shown below:

In order to begin writing a program, a module must be inserted (unless this has been done previously). This can be done in several ways. In the example below, this is done from the insert menu.

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download