## Functions

Any Excel user will b e aware of functions such as Sum, Average, Count, and so on. These functions can be used from Excel, just like defining a formula in a cell, i.e., begin with the '=' sign, then select the function required from the Function list box. These are the built-in functions. The idea behind a function is that you send data into it and the function sends back, or returns, an answer. These examples show how built-in functions are used in Excel:

The trigonometric Tan function returns the value of Tan(0.7) = 0.842288 =Count(C1:C4)

The Count function returns the number of cells in the range C1:C4 = 4.

This statement will call WorksheetFunction.Sum to sum the range from E1 to E32 and assign the result to varAnswer. In this function, you send in to it the cell range, and return the sum of that range.

varAnswer = Application. WorksheetFunction.Sum(Range("A1", Range("A1 ").End(xlDown)))

This statement will use WorksheetFunction.Sum to sum the range from E1 to the last active cell in the column and assign the result to varAnswer.

If LCase(Selection.value)= "" then MsgBox "Good"

This statement uses the LCase function within the test and translates the string into lower case letters. This is a very useful function when there is a possibility of the user selecting a non-tested text case. You send it a text string and it returns the same string in lower-case.

In all of the above examples we can see that one or more values, or arguments, are send into the function and a value, the answer, is returned. These built-in functions can be used with the VBA language and we have already used some of them in previous chapters. We have come across functions that convert data from one type to another - such as Val (string) for converting string format to numeric, or Str (number) for converting a numeric into a string representation (see Chapter 5). We have also used functions to manipulate strings. The following example uses more string conversion functions.

The example in Listing 9.3 allows the user to select a number of cells then checks each of them to see if there is a formula. If there is, a message box is displayed giving the address of the cell and its formula. If not, no message box will be displayed. The test to find out if each cell uses a formula is the line that reads:

This line uses the b uilt-in string function Mid(). This takes three arguments: the first looks at the cell formula, the second refers to the position in the string (in this case 1) and the third refers to the number of characters to b e extracted (in this case 1). Clearly, if this value is the '=' character, then there must be a formula in the cell, since every cell containing a formula begins with '='.

Listing 9.3 Example of using the built-in String function Mid()

Sub addressFormulasMsgBox() 'Displays the address and formula For Each Cell In Selection

MsgBox "The formula in " & Cell.Address(rowAbsolute:=False, _ columnAbsolute:=False) & " is: " & Cell.Formula, vblnformation End If Next End Sub

zrv EdR Insert Format lools Data Window Help -j TypB-a question for help

H! U. " Times.Wew.Rimar. - 10 - |jj] I U K ff a _ - <3> - A

zrv EdR Insert Format lools Data Window Help -j TypB-a question for help

H! U. " Times.Wew.Rimar. - 10 - |jj] I U K ff a _ - <3> - A

 B t: D E F & | i - 20 Formulae Desriieiiutts: _— 21 Each cell yalue.in this is calculate dlsy addingt •he-Sfales To Date to the Monthly Total ing M onthly T otal by the E! onus Rate Fl 22 Each cell value in this is -calculated by multiply 23. 24 Each cell inthis range carned-över fro] This c ell yalue is incremente d at the star tnthe1 each new mo: nf jj "litrosoft Excel M .'/V ij The formula in i32 is;- <=SUMfC32;F32) 1 Sales Daia: m 2S QK SK '1 r ! 30 Weekly Sales □ 31 Sales To Date 2 3 4 | Montldy Total MonthlyBoiius EM "f Vornii S l1--> M 135 5 B Ui 77. £ IKS Jill 212 S3: 459 43 1 33 136 £ 680-00 595 34 3 it 23 -is 150 166 372 £ L&60J10 729 35- J40 77 ~S2\ 19 180 £ 900X10 720 36 489 44 54 0 59 157 £ 785 J0U 646 37- 333 '2Q 56 39 78 193 £ 965 J)0 526 38;: 336 2? 100 9 123 £ 615JI0 459 33 453 33 54 4D2 _6S 557 £ 2.785J10 1010 40 330 27 33 18 122 £ 610JI0 452 41 3432 286 466 | 719 446 1917 9565 5349 ■HT.

Figure 9.2 Screenshot of output from Listing 9.3

Figure 9.2 Screenshot of output from Listing 9.3

0 0