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.

varAnswer = Application. WorksheetFunction.Sum(Range("E1 :E32"))

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

Post a comment