VBA program using variables

VBA Listing 5.1 includes line numbers for ease of explanation. Do not include these in any VBA for Excel code.

Listing 5.1 VBA program using variables

1 Option Explicit 'forces the programmer to declare all variables

2 Sub sumValues()

1 Option Explicit 'forces the programmer to declare all variables

2 Sub sumValues()

3

Dim first_number As Integer 'declare 3 variables as integers

4

Dim second_number As Integer

5

Dim sum As Integer

6

Dim Result As Range

7

first_number = InputBox(prompt:="enter first number")

8

second_number = InputBox(prompt:="enter second number")

9

sum = first_number + second_number

10

Worksheets(1).Select

11

Columns("B:B").ColumnWidth = 18

12

Range("B1").Font.Bold = True

13

Range("B1").Value = "Number Adding Program"

14

Range("B3:B5").Clear

15

Range("B3").Value = "First Number ="

16

Range("B4").Value = "Second Number ="

17

Range("B5").Value = "Sum ="

18

Range("C3:C5").Clear

19

Range ("C3").Value = first_number

20

Range ("C4").Value = second number

21

Set Result = Range ("C5")

22

Result.Value = sum

23

Result.Font.Bold = True

24

Result.Borders (xlBottom).Weight = xlMedium

25

Result.Borders (lop).Weight = xlMedium

26 End Sub

26 End Sub

Listing 5.1 is a VBA program that enables a user to input two numbers, and output the result along with the numbers on a blank spreadsheet (see Figure 5.3). The program assigns two integer variables called first_number and second_number using input boxes. The sum of these variables after input is then assigned to another integer variable called sum. Each of these variables is then assigned to worksheet cells in the range C3 to C5, along with appropriate labels in the previous column cell range (i.e. B3 to B5) and a heading which begins in cell B1.

Lines 3 to 5 declares the integer variables first_number, second_number and sum.

Line 6 declares the range object variable result which will store the output (i.e. transfer the sum) to a cell range.

Lines 7 and 8 use input boxes to input values into the variables firstjiumber and second_number.

Line 9 assigns firstjiumber + second_number to the variable sum.

Line 10 selects a new blank worksheet to present the results.

Line 11 adjusts the column width to make it big enough for the new text.

Line 12 makes the heading in cell B1 bold.

Line 13 inserts the text heading.

Line 14 clears the cell range B3 to B5 ready for the text labels which are inserted with lines 15 to 17.

Line 18 clears the cell range C3 to C5.

M fife Edit <5

ew Insert Format Tools Data Window Help

JsJ*]

DSQ| âft? 1|S| il

M tl Arid

Ê

□ 16

-J =l

A

B

■:ffi

! D

E !

F I

G

H

g

Number Adding Pro;

jram

2

3"

Firîl Wurflbar =

6

4

Second Number -

1

S

Sum =

12

Ë

?

8

9

Microsoft Excel

à

1D 11

on the BSc BIT course, I am ting the Artificial Intelligence ünitj there are 35 5

12

I am

'¿UiMlii l.dNliy Llliyjil.

13

1

14

fff'"1'!

15

16

17

IB

I_!

]HH:HHl\SheeMjf3ieet2 j Sheet3 /

t*

! f I

Ready

I—I

^■■ni

Figure 5.3 Addition program output in Excel

Line 19 puts the value offirst_number into cell C3 and line 20 puts the value of second_number into cell C4.

Line 21 then Line 22 sets C5 with the variable called result.

Line 23 puts the value of sum into result - i.e. into C5.

Lines 24 and 25 set the top and bottom border of the cell C5 to a medium weight by assigning the xlMedium constant to the Weight property of the object Borders (which is a property of a Range object).

You should study the VBA program carefully to ensure that you clearly understand the output in Figure 5.3.

0 0

Post a comment