Brigham Young University - Idaho



Brigham Young University - Idaho

College of Physical Sciences and Engineering

Department of Mechanical Engineering

Class Prep Notes #V3

Input, Output, and Documentation

VBA provides several means of getting input from and outputting results back to the user. We will explore several means in class period. We will also discuss the importance of using comment statements and carefully chosen variable names to document your programs.

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

o Chapter 9 (all) – Computations

o Chapter 10 (all) – Strings and Dialogue Boxes

Documentation

Comments are used in a program to make the programs easier to understand, modify, and use. Imagine how difficult it would be to read a book without paragraphs, chapters, or punctuation. A program without comments can be just as difficult to interpret.

Comments begin with an apostrophe (‘). VBA ignores any text that follows an apostrophe in a line of code. Comments may also be placed at the end of a line of code. If the apostrophe is contained between quotations (“…”) of a text string, the apostrophe is not interpreted as a comment. Note the use of comments in the following example:

Sub CommentDemo()

‘This program demonstrates the use of comments

‘ Define initial variables

A = 3 ‘A is the leg of a triangle

B = 4 ‘B is the leg of a triangle

Msg = “Enter triangle’s dimensions:”

…

End Sub

Good internal documentation typically includes a header at the beginning of the program. A header commonly includes the following information:

Purpose or description – briefly describe the purpose of the program

Input – list the name of each input variable and briefly describe

Output – list the name of each output variable and briefly describe output produced

Other Key Variables – list other important variables by name and describe their usage

Author – person who wrote the program

Date created or revised – keep track of changes made to the program

Limitations or special conditions – describe unusual conditions or limitations

Other suggestions for improving program readability:

• Make liberal use of blank lines

• Use indentation to clarify structure

• Use descriptive variable names

o Makes programs self-documenting

o Use same names in other programs for ease of use

o Variable names may contain letters, numbers and some special characters, but the first character must be a letter

o No spaces or periods may be used in a variable name

o VBA does NOT distinguish between uppercase and lowercase letters

o The following characters are NOT allowed in variable names: # $ % & !

o Variables names are limited to 255 characters.

o To make variable names more readable, try using mixed case: InterestRate

o VBA reserved words cannot be used for variable names

Getting Input from the User into the Program

Three ways are commonly used to get input into the program:

• Passing Information to a Function via an Argument List

• Reading Data from a Spreadsheet Cell

• InputBox Function

InputBox Function will be covered next class.

Passing Information to a Function via an Argument List

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. Finally variable c is assigned to the name of the function, AddMe, which writes the information back to the spreadsheet cell.

Reading Data from a Spreadsheet Cell

One of the most useful things about VBA interaction with Excel is the ability to read and write information directly to a cell in the spreadsheet. We will cover a couple of basic methods for doing this in this class period.

Data may be read from a spreadsheet cell using the cells command as shown below:

A=Cells(3,2)

The arguments represent the row,column numbers of the cell address. The contents of the cell will then be stored in the variable named A.

Data may also be read from a spreadsheet cell as shown in the example below:

Sheets(“Sheet1”).Select

Range(“A2”).Select

A=ActiveCell.Value

The first line makes sheet1 the active sheet, in other words you are interested in extracting information from sheet1, as opposed to some other sheet in the workbook. The second line sets the active cell, or the cell in which the data is to be read from. In this example cell A2 is selected. The third line reads the contents of the active cell (A2) and places it in a variable named A.

With the information in a VBA variable, it may now be used within your program as needed. Other options exist for selecting an entire range of variables or using relative as opposed to absolute addressing. These will be discussed at a later date.

Returning Output to the User from the Program

Three ways are commonly used to get output from the program:

• Returning Results from a Function to a Cell

• Writing Data to a Spreadsheet Cell

• MsgBox Function

MsgBox Function will be covered next class.

Returning Results from a Function to a Cell

The results of a function computation are typically written directly to a cell, as shown in the AddMe function example above. The key is to equate the value to be written to the name of the function. This is usually done as the last line of the function, prior to the end statement.

Writing Data to a Spreadsheet Cell

Data can be written to a spreadsheet cell by the reversing the read data from a cell process discussed above.

Assuming the cell is to be written to the same sheet from which data is read, the sheet need not be reselected. Simply make the cell to be written to the active cell and equate the desired variable to the active cell:

'output results to cell

Range("c2").Select

ActiveCell.Value = c

Data may also be written to a spreadsheet cell using the cells command as shown below:

Cells(3,2) = A

The arguments represent the row,column numbers of the cell address. In this example, the contents of the variable named A, will be stored in the cell.

Other Useful Tips

Working with the VB Editor

The VB editor (VBE) has a lot of nice features to enhance the readability of programs and also to assist in the debugging process. The following default colors are used by the editor:

• Green – used for comments

• Blue – used for keywords

• Red – used for statements that contain syntax errors

• Black – used for all other text in the program

We will discuss some of the debugging features at a later date.

Executing an Excel Function

Excel functions may be executed within VB by utilizing worksheetfunction. An example of to using Excel’s Pi function in VB is shown below:

Perimeter = worksheetfunction.pi * Dia

Linking a Program to a Button

Once a program has been written, it may be linked to a button on a spreadsheet to simplify execution. This is done by selecting Insert/Button under the Forms Control heading from the Developer Tab, and selecting the name of the Program to be linked to.

Saving Workbooks that Contain Macros

Because VBA is such a powerful language, security precautions have been taken by Microsoft with any spreadsheets that contain programs or macros to mitigate risks. One change in Excel 2007 is that Excel files that contain programs (or macros) must be saved with “macros enabled”. The file must be saved with the .XLSM extension rather than the normal .XLSX extension. To do this choose Save As and select Excel Macro-Enabled Workbook, the first time the file is saved. When the spreadsheet is saved, the program is automatically saved with the spreadsheet.

Accessing Help on VB Functions

The Help file in Visual Basic typically defaults to the Online help of Excel, rather than VB. By changing the search to use the content on the local computer version, as shown below, extensive help on Visual Basic is available.

[pic]

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

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

Google Online Preview   Download