Brigham Young University - Idaho



Brigham Young University - Idaho

College of Physical Sciences and Engineering

Department of Mechanical Engineering

Class Prep Notes #V2

Variables, Equations & Functions

Introduction to VBA Programming Language

In today’s class, 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.

To prepare for class, please read the following sections/chapters from your text, Introduction to VBA for Excel

o Chapter 2 (all) – Overview of VBA for Excel

o Chapter 4 (all) – Customized Worksheet Functions

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 value.

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 = 2*x – 5 |

| |c = (a + b) / 2 |

| |y = 5 * x^3 |

| |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.

Note that VBA is NOT available in the Excel 2008 version on the Macintosh computer, but is available in the most recent release, Excel 2011.

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

[pic]

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

[pic]

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:

[pic]

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.

[pic]

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

[pic]

-----------------------

[pic]

[pic]

[pic]

[pic]

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

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

Google Online Preview   Download