Examine the following entities and consider what data types you would use to represent them as variables in a VBA program:

♦ the cost of a colour television

♦ the temperature in a freezer cabinet

♦ someone's presence or absence at a meeting

♦ the number of people at a rugby match

♦ a deadline for returning a machine on hire

♦ the population of the UK

♦ The percentage swings at an election.. Write VBA statements for the following:

a) Declare a Long integer variable called NoOfStars. Assign the value of 65,000 to this variable.

b) Declare a Single variable called HeightOfStudent, and then assign the value of the variable to 1.655.

c) Declare a Currency variable called PriceOfBook, and then assign the value of the variable to 18.95.

Write a VBA program that will input two numbers using the InputBox function and output the smallest number input using a MsgBox function. Test the program with a suitable range of values.

Write a VBA program that will input three values using the InputBox function, and output in a blank sheet using the cell range C2:C4, the three numbers in reverse order.

Consider the following real-world entities and represent them with user defined types as in the examples EmployeeRecord and Course above: a member/customer of a video shop, a bank account, a book in the library, a CD in a music shop stock system. You will have to decide on which fields are needed for each of the entities. Write an example macro to use one of your user-defined types.

Study the following code and see if you can work out what it does. Then open the weeklySales worksheet of the SALESMAN workbook, go to the VBE and enter this sub procedure. Run and test using suitable values.

Sub listBonusValues()

Dim bonus(12) As Currency 'declare 3 variables as integers

Dim bonus_number As Currency

Dim index As Integer

Dim cell As Object

Dim strMsg As String bonus_number = InputBox(prompt:="enter bonus value")

'ask the user for some values index = 0

For Each cell In Range("month_bonus") bonus(index) = cell.Value index = index + 1 Next strMsg = "Current bonuses of salesmen: &vbCr &vbCr" For index = 0 To 8

strMsg = strMsg & Str(bonus(index)) & vbCr Next

MsgBox strMsg, vbOKOnly + vblnformation, "Array of bonuses" End Sub

Create a new workbook called my_array_book. Write a VBA macro that declares an array called MyArray of size 8. Input the array items using the InputBox function. Under the headings 'Array elements' and 'Array reverse elements' the macro should transfer the array to column A in the default worksheet. The program should also write the contents of the array in reverse order to column B of the worksheet. (Hint: to write the contents in reverse use For num =8 to 1 step -1).

6 Decisions in VBA

Comparison operators 114

Comparing different data types . . 116

Logical operators 122

Select case 123

The operators in VBA 127

Exercises 128

0 0

Post a comment