Laboratory work №15

VBA. User functions of a work sheet and procedure

Objective: To examine a technique of creation of the user functions, to gain skills of creation and application of the user functions.

Functions of the user are created on a work sheet with the name the VBA module then with them it is possible to work with the help of the master of functions. By default created function at a choice by means of the master of functions belongs to category Defined by the user. The general view of function of the user has the following appearance:

Function Name of function (list of named)

Functions body

End Function

Functions body consists of descriptive part and the block of the operators who are carrying out one by one. The carrier of returned value of function of the user is the function name. Therefore in a body of function of the user calculating some value, there has to be, at least, one operator, attributing a function name value of any expression.

JOB 1. Сreating the function of user by VBA

1. Open MS EXCEL. Create function which returns goods cost at its cost without the NDS and to value of a rate of the NDS. For this purpose it is necessary:

• to load the VBA editor team of Service-Makros-Redaktor of Visual and to insert a module leaf the Insert (Insert) team – the Module

• in the standard module to gather a code of the user function:

Function Cost(CostWithout, NDS)
Cost= CostWithout *(1+NDS/100)
End Function

3. To check the created function: enter the following data into the table since A1 cell (if your actions are correct, in library of the Master of functions the category Created by the user and in this category the Cost function is formed:

Table 44 – Determination of cost of goods

JOB 2.

The sales manager needs to develop the function, allowing to calculate commission charges. The percent of commission charges depends on the volume of the sold goods and is charged by the rule presented in table 45 (this table doesn't participate in calculations):

Table 45 – Calculation of commission charges

1. For calculation of commission charges it is necessary to create the user function in the standard module of the VBA editor:

Function Premium (Sale)
If Sale <= 9999 Then
Premium =Sale*0.08
ElseIf Sale <= 19999 Then
Premium = Sale *0.1
ElseIf Продажи <= 39999 Then
C = Sale *0.12
Else: Premium = Sale *0.14
End If
End Function

2. To calculate commission charges on a work sheet by means of the user function the Award (X) where argument of function X is the sales volume. Create the table on below given sample, you enter data, since A1 cell:

3. Make changes in a task (independently): commission charges of the seller depend on the rate occupied by the manager. If it is accepted to the constant staff of firm, commission charges are charged usually. If it is on a trial period, commission charges make 75% of face value.

JOB 3. Creation of the VBA procedures

1. Pass in the VBA editor, having pressed a combination of the Alt+F11 keys. Add in the project the standard module (Insert↔Module)

2. Correctly type the program text, observing syntax of teams

3. Execute the program, having pressed the F5 key:

Sub DateTime()
MsgBox “Today:” & CStr(Now)

MsgBox “Current time:” & Time

End Sub

4. Type the text of procedure and execute the program:

Sub FullName()
Dim Name, Surname, Patro As String
Name =InputBox(“Input your name”)
Surname =InputBox(“Input your surname”)
Patro =InputBox(“Input your patronymic”)
MsgBox(”Your full name: ” & Name & ” ” & Surname & ” ” & Patro)
End Sub

5. Add the created program with individual information: number IIN, place of study, No. of group, course No.

Date: 2015-12-24; view: 2040