Unit 4 Excel

Excel, Formula, Formatting, VBA ME 325 Spring, 2007

Overview • Formulas and Functions • Formatting • Printing • Macros/VBA

Excel Formula • A formula consist of one or many expressions, such as cells arithmetic and built in or user defined functions • In Excel, formulas always begin with an equal sign (=). • Ex. =A1+2*B6+Sum(C2:C10)

Excel Functions • Excel supplies more than 350 functions organized into 10 categories: – Database, Date and Time, Engineering, Financial, Information, Logical, Lookup, Math, Text and Data, and Statistical functions

• You can use the Insert Function button on the Formula bar to select from a list of functions. • A series of dialog boxes will assist you in filling in the arguments of the function and this process also enforces the use of proper syntax.

Relative Reference • A relative reference is a cell reference that shifts when you copy it to a new location on a worksheet. • A relative reference changes in relation to the change of location. • If you copy a formula to a cell three rows down and five columns to the right, a relative reference to cell B5 in the source cell would become G8 in the destination cell.

Absolute Reference • An absolute reference is a cell reference that does not change when you copy the formula to a new location. • To create an absolute reference, you preface the column and/or row designations with a dollar sign ($). • For example, the absolute reference for B5 would be $B$5. • This cell reference would stay the same no matter where you copied the

Insert Function Wizard • To get help from Excel to insert a function, first click the cell in which you wish to insert the function. • Click the Insert then Function menu item. This action will open the Insert Function dialog box.

Insert Function Dialog

Insert Function tips • In the Insert Function dialog box you can type in a description of what you would like to do in the Search for a function text box and then click the Go button, and Excel will come up with some suggestions for you. • You may also select a category from the drop-down box, or choose All, which will list every function in Excel alphabetically. • Once you make your selection and click the OK button, another dialog box shows you all the arguments for the function.

– The arguments shown in boldfaced type are required – While the cursor is in an argument's text box, you can either enter a value or click a cell and that cell will appear in the text box, saving you the

Ex. Average Function • The average function is necessary to calculate the average of a range of cells. • Like any other formula, the average function may be copied across cells.

Logical functions • A function that determines whether a condition is true or false is called a logical function. • Excel supports several logical functions such as AND, FALSE, IF, NOT, OR and TRUE. • A very common function is the IF function, which uses a logical test to determine whether an expression is true or false, and then returns one value if true or another value if false. • The logical test is constructed using a comparison operator that compares two expressions to determine if they are equal, not equal, if one is greater than the other, and so forth. – The comparison operators are =, >, >=, <, <=, and <>

• You can also make comparisons with text strings. You must enclose text strings within quotation marks.

IF Function • The arguments for the IF function are: – IF(logical_test,value_if_true,value_if_fals e) – For example, the function =IF(A1=10,20,30) tests whether the value in cell A1 is equal to 10 – If it is, the function returns the value 20, otherwise the function returns the value 30 – Cell A1 could be empty or contain anything else besides the value 10 and the logical test would be false;

Formatting

Formatting Cells • Formatting is the process of changing the appearance of your workbook. • The formatting toolbar is the fastest way to format your worksheet. • If you select a cell or range, click Format on the menu bar or right click and select format cells, the

The Format Cells dialog box

Alignment Tab

Patterns Tab

Conditional Formatting • There are times when you will want data to have a different appearance if it meets some criteria. – For example, you might want data to appear in red, if the data is more than six months old – Or, you might want a value to be black if it is positive and red if it is negative

• This kind of formatting is called conditional formatting. • You specify the condition under which you want the formatting to take place and what the formatting should be.

Conditional Formatting Dialog

Print Area • By default, Excel prints all parts of the active worksheet that contain text, formulas, or values. • You can define a print area that contains only the content that you want to print. • To define a print area, select the range you want to print, click File on the menu bar, point to Print Area, and then click Set Print Area. • You can also specify different sections of your worksheet to print on separate pages. – Insert a page break by clicking on a cell, clicking

Page Setup dialog box

The Header dialog box

VBA Visual Basic for Application

Visual Basic for Applications • VBA is a version of Visual Basic (VB) available in most Microsoft (MS) Office applications • VBA is used to develop procedures, known as Macros, that can be run within the application itself. • VBA have the necessary elements to programmatically automate your

Macros • Excel has a build-in macro recorder that translates your actions into VBA macro commands.  • After you recorded the macro, you will be able to see the layout and syntax.  • Before you record or write a macro, plan the steps and commands you want the macro to perform.  • Every actions that you take during the recording of the macro will be recorded including the correction that you made. • You can use the generated VBA code in your custom functions.

VBA Loops • A loop is an expression used to repeat an action. Microsoft Visual Basic presents many variations of the loops and they combine the Do and the Loop keywords. • Loops can be constructed many different ways to suit different circumstances. Often the same result can be obtained in different ways to suit your personal preferences. These exercises demonstrate a selection of

Do While … Loop Do While Condition     Statement(s)

Loop This expression examines the Condition. If the condition is true, then it executes the Statement or statements. After executing the statement(s), it goes back to examine the Condition. AS LONG AS the Condition is true, the Statement will be executed and the Condition will be tested again.

Do...Loop While Do Statement(s) Loop While Condition In this case, the Statement or Statements will be executed first. Then the Condition will be tested.

The Do Until ... Loop Do Until Condition     Statement(s) Loop • This loop will first examine the Condition, instead of examining whether the Condition is true, it will test whether the Condition is false.

For...To...Next Loop For Counter = Start To End   Statement(s) Next • The expression begins counting at the Start point. Then it examines whether the current value is smaller than End. If so, it then executes the Statement(s). Next, it increments the value of Counter by 1 and examines the condition again. This process goes on until the value of Counter becomes equal to the End value. Once this condition is reached, the looping stops. 

Stepping the Counting Loop For Counter = Start To End Step Increment   Statement(s) Next Counter Same as the For loop, the next counter is incremented/decremented by the Increment value

Do … Loop Until, Example Sub FillData(startRow,nRows) Dim x as Double Dim y as Double Dim row as Integer row=startRow Do x=Cells(row,”A”).Value y=Exp(x)*Sin(2*x)-3.25 Cells(row,”B”).Value=y row=row+1  Loop Until row >(startRow+nRows)

For … Next Loop Sub FillData(startRow,nRows) Dim x as Double Dim y as Double For row=startRow to (startRow+nRows) x=Cells(row,”A”).Value y=Exp(x)*Sin(2*x)-3.25 Cells(row,”B”).Value=y

The If...Then...ElseIf Statement If Condition1 is True Then Statement1 ElseIf Condition2 is True Then Statement2 ElseIf Conditionk is True Then Statementk End If

If Then … Function MyFunction(x) retVal=“N/A” If x <10 Then retVal=“Less Than 10” ElseIf x<20 retVal=“10 to 20” Else retVal=“More Than 20” End If MyFunction=retVal End Function

The Select Case Statement Select Case Expression Case Expression1 Statement1 Case Expression2 Statement2 Case Expressionk Statementk Case Else Default statement End Select The Expression will be examined and evaluated once. Then Microsoft Visual Basic will compare the result of this examination with the Expression of each case. Once it finds one that matches, it would execute the corresponding Statement.