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.

With the module inserted, you're ready to begin programming!

Writing a VBA Function VBA supports two general types of programs: functions and subroutines. Many of the differences of these types of programs will be explored in detail at a later date. User defined functions, however, provide an easy way to pass information from a spreadsheet to the program via an argument list. User defined functions work just like built-in excel functions. Note the following example:

Function AddMe(a,b) ` This functions adds two numbers ` a and b are numbers to be added `compute sum

c = a+b 'output results

AddMe = c End Function The function receives information directly from the spreadsheet cells that are selected when the AddMe function is invoked. The numbers from the cells are stored in variables a and b. Next the sum of the variables is computed and stored in variable c. The results of a function computation are typically written directly to the cell from which the function was called. The key is to equate the value to be written to the name of the function. In our example, the variable c is assigned to the name of the function, AddMe, which writes the information back to the spreadsheet cell. This is usually done as the last line of the function, prior to the End Function statement.

Executing a VBA Function

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

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

Google Online Preview   Download