Excel2010

When you select an item from the list, its order appears in the cell link (G1). Vice versa, if you write a number into the linked cell that item is selected in the combo box.

Similar to buttons and other Form Control items, you can assign macro to drop down combo boxes. This macro will be automatically called every time you change the value in the combo box. You can use the same methods, which we described for buttons, to assign a macro.

9.5.3 Check Box

Check box is usually used in true/false questions. You can find many samples of check box in many programs. Except the initial state, check box has two possible states; selected or unselected. For the initial state, there is the third option: Mixed which means “State is not available” (not true but also not false).

Same as the combo boxes, from the Format control Control tab, you can assign a cell link and define other status options. Linked cell will contain TRUE value, if the check box is checked. Otherwise it’ll have FALSE.

9.5.4 Option Button

Option button is also a common button in many programs. It’s used when you have different options where it’s possible to select only one. When you have two possible options, it’s better to use a check box. But, if you have three or more options and only one of them can be selected at a time, then, it’s better to use combo box or option buttons group. For example, ‘For cells with comments’ option in Excel Options, we have 3 possible choices. And, you can select only one of the choices. Or, in most of the multiple choice exams, only one answer can be selected: A, B, C, D or none.

Option button also has the Assign macro option and Format Controls. In the Control tab, it has cell link, value and 3-D effect options.

Example 9.6:

The accountant in your father’s company is using Excel worksheets in his balances. He has difficulties in his balances. So, he wants to automate his balance operations. As it’s shown on the next page, he designed two main panes. The first part is for entering data starting from the third row. The second part is beneath it and is the database that keeps all records. At the top, he wants to see the total amount.

Figure 9.19: Combo Box

Figure 9.20a: Sample combo boxes

Figure 9.20b: Sample combo boxes

Macros

171

Figure 9.21a

If you don’t change the Format Control Properties, when copying or resizing cells, the controls over these cells are also copied or resized.

Figure 9.22: Combo box properties

	A	B	C	D	E
1			TOTAL	EXPENSES
2				6295
3		Expense Type	Date	Explanation	Amount
4	10
5
6		Expense Type	Date	Explanation	Amount
7	9	5	16-May	Seminar in Boston	2500
8	8	6	16-May	Seminar in California	380
9	7	3	16-May	A computer for accountancy	650
10	6	3	16-May	A computer for Secretary	650
11	5	1	16-May	John Clayton	1500
12	4	4	16-May	100 kg White paint	115
13	3	3	16-May	A new table for the big hall	500

Figure 9.21b: Accountancy program

For his expenses, he prepared a list of categories and numbered them. Instead of memorizing many categories with their numeric values, he wants to select the category from a combo box. After then, he’ll write the date, explanation and the amount. Finally, after pressing a button, he wants to put this record into the database.

Analysis and Solution

First, we design the worksheet as in the figure above. We write our new expenses in row four. We have just one combo box and its cell link is at its back: B4. Combo box input range can be in another sheet showing the expenses (the table on the left).

When entering data, we select the expense category from the combo box. Because, the cell link of the combo box is B4, the result will appear at the back of it. We’ll then write the date, explanation and the amount of payment. After we press the macro button, The macro will

copy and insert the range A4:E4 to the top of the list, A7:E7

then, it will clear the range C4:E4

finally increment the number in A4 for the next operation.

The sum of the numbers will appear in A2. But for this sum formula, our start cell must be less than 7 (6 is OK), and somehow bigger than our initial size of list (20 or 30).

=SUM(E6:E20)

Because, if we take 7 as the start point, every time we insert new record to the top of the list, start address of Sum formula will also move down and the newly inserted record will not be included in the formula.

172	Microsoft Excel

Now, we know what to do clearly, and all this can be recorded from the Macro recorder. But for incrementation, you need to change the formula,

Range("A4") = Range("A4") + 1

Otherwise, the number you’ve written will be shown as a direct value and will

be repeated every time.

Sub Save()

'

'Save Macro

'Keyboard Shortcut: Ctrl+Shift+S

Range("A4:E4").Select	'Select
Selection.Copy	'Copy
Range("A7:E7").Select	'Select destination
Selection.Insert Shift:=xlDown	'insert
Range("A4") = Range("A4") + 1	'Increment A4
Range("C4:E4") = ""	'Clear for the next operation
End Sub

Example 9.7:

Your math teacher is organizing a contest throughout the school. Because there are many students participating, he doesn’t want to read answer sheets one by one. He wants to use a macro to enter the answers and check. To simplify the process for you, he doesn’t want to keep the individual result details in the computer. After you design this level, he can ask once more to upgrade it :)

In the table, he has the correct answers in the C column. And a formula in column D; giving +8 points, if the answer is correct; zero points, if there is no answer( left blank); otherwise -1. For now, he wants option buttons. Every time he clicks on an answer (option) button, that answer will be put into student’s answers table in Column B.

Analysis and solution

After we design our worksheet and insert 5 option buttons, we can select a cell as a cell link (for example C2). This selected cell link is automatically applied for all the option buttons. (Note: The selected option buttons’ number is shown in the cell link. So, you should pay attention that the caption (visible text) over the first option button is A, and on the second one is B, etc.) Student’s answers will appear in column B; column C will have correct answers and column D will have

the result for every question.

Figure 9.23: Multiple choice exam

Macros

173

In D19 we have a sum formula to calculate the current student’s total points. When checking the student result for the first question, we’ll check if B4=C4 or not. If it is, then, the result is 8. Otherwise, we’ll have another ‘If’ to check whether the cell B4 is empty or not. If B4 is empty then the result is 0, otherwise the result is -1. So, the formula is clear then;

=IF(C4=B4,8,IF(B4="",0, -1))

The main program is actually with option buttons. Every time, we click on an option button, we want the answer we click to appear in the cell that Question Number -in the cell D1gives.

For example, when ‘Question number’ in D1 is 11 and we click C, we want the Macro to

write C in Question 11’s place (cell B14)

and increment the Question Number to 12.

Writing your macro

Now right click on an option button and select Assign Macro… from the menu. For Macro Name box, write Answers. And click New from the Assign Macro window. This will open the VB editor with a new sub procedure. Change it as follows:

Sub Answers()
'Option button has been clicked
answer = Range("C2")	'Get the answer. The one has been clicked.
If answer = 1 Then Result = "A"	'Convert option button to a letter
If answer = 2 Then Result = "B"
If answer = 3 Then Result = "C"
If answer = 4 Then Result = "D"
If answer = 5 Then Result = ""
QN = Range("D1")	'Get current Question Number
Range("B" & QN+3) = Result	'Write the result in column B
QN = QN + 1	'Increment QN for next operation and
Range("D1") = QN	'store QN
If QN > 15 Then	'After the last question show a MsgBox
res = Range("D19")	'Get his result

C=MsgBox("The result is" & res & _

", Clear the answers?", vbYesNo, "Information") If C = vbYes Then

Range("D1") = 1

Range("B4:B19") = ""

End If ' end of C=vbYes

End If ' end of QN>15

End Sub

174	Microsoft Excel

Explanation of the macro

When we click on an option button, it’ll

read the button number from the cell link (C2) and convert it to a letter (Result).

After that, it will read the question number from D1 (initially it’s 1).

Because, we have 3 more lines at the top, and our first answer is in the 4th row, write the result to the cell B4 (“B” & QN+3).

Then it will increment the QN for next question (QN= QN + 1) and assign it to D1 (Range("D1") = QN)

for every click, it will repeat the same macro process, until, QN exceeds the last question (15). When it exceeds the last question, it’s going to show the result

and ask whether to clear the old data or not. If you say OK then the old answers will be cleared.

Macro for the selected option button is ready. Now, you need to assign this macro also to the other option buttons. Pay attention that you don’t need to write the same macro for each, just assign this macro for all the others.

9.6 Using User Forms

Insert UserForm

button

Using the Insert UserForm button, you can insert Forms that have the same properties as the Visual Basic environment.

User Form contains nearly similar toolbox. These tools have Visual Basic environment properties, and have some more functions comparing with Form Controls. After you design your Form using controls and arrange their properties, you can show or hide it any time from any macro in the workbook using the commands

Userform1.Show

UserForm1.Hide

Here Userform1 is the name of the form and it can be changed from the Properties window. Similarly, in order to hide the user form, you can assign the command Userform1.hide to a button.

Figure 9.24: Inserting UserForms

Example 9.8:

A nation-wide company wants to prepare a questionnaire for their future product. In the questionnaire there are 10 questions with 5 multiple choices each. Because of the high expenses the administration decided not to buy an optical reader for just one questionnaire, and they want you to write a macro that will take all the data into an Excel workbook for analyses.

Macros

175

Your task is to read and concatenate all 10 answers of each interviewer into a cell and all questionnaires into a column. In order to simplify the process, you decided to use option buttons. First you will be asked to enter some specific data; pollster id and number of children in the family. Then, for the 10 multiple choice questions, every time you click on an option button A, B, C, D, or E, it will concatenate the current choice to the end of the answers of the current examinee. After that, it’ll increment the answer number by one. After the 10th answer, we want Macro to ask you whether to save the data or not. If you accept to save, the data will be stored in a special column otherwise the data will be cleared.

Analysis and Solution: Before starting macros, first, you design the worksheet. You’ll have 5 option buttons for answers, and a combo box for pollsters. You can have two macros. The first macro is Answers() which is assigned to the option buttons. The second macro, Save, will be called from the Answers() macro after processing question number 10. You can select the cell link for the Option buttons: E4 and for Combo box: E5. E3 and E6 are Questionnaire and Question numbers and will be changed from the macros.

The process; first, you are going to select the Pollster and write the number of children into the B5 cell. Now, you are ready to enter the answers. Every time you click on an option button its number will appear in the linked cell E4. So, using E4, you can see which option button has been clicked.

Figure 9.25: Questionnaire

If E4 is 1 then the converted result “A” will be added to the end of the Result,

If E4 is 2 then the converted result “B” will be added to the end of the Result, or

If E4 is 5 then the converted result “E” will be added to the end of the Result,. Then the code can be like,

If Range("E3") = 1 then Result = Result & "A"

176	Microsoft Excel

Concatenation operator (&) is used to add the second string to the end of the first one. So, if the Result is “DDBC” and we concatenate “A”, the new value for the result will be “DDBCA”.

After we assign the proper letter to the end of the Resulting string, we increment the Question number and save it to the cell E5. You check, every time, whether the question number’s greater than 10 or not. If it’s greater, then, you call the ‘Save’ sub program; otherwise, we put the new value of Question back to E5.

Sub Answers()	'Option button was clicked
Answer = Range("E4")	'Get the Answer for the current question
QN = Range("E3") + 3	'Get Questionnaire number
	'QN is for Questionnaire Number
Question = Range("E6")	'Get Question Number
Result = Range("J" & QN)	'Get the answers of current examinee
'Convert the Answer into letter

If Answer = 1 Then Result = Result & "A"
If Answer =			2	Then Result = Result & "B"
If Answer =			3	Then Result = Result & "C"
If	Answer	=	4	Then	Result	=	Result	&	"D"
If	Answer	=	5	Then	Result	=	Result	&	"E"

Question = Question + 1		'Increment the		Question number and
Range("E6") = Question		'Store	back into E6
Range("J" &	QN) = Result	'Store	current	answers in column J
If Question	> 10 Then Call Save	'If we finish all questions ask for saving

End Sub

In the Subprogram Save, first we ask whether the user wants to save or not. If he clicks the “OK” button, C will get the result 1, otherwise 2. If C is 1 then, we save our data and increment QN by one. Otherwise, we clear our data resetting the Question number to 1 and QN will remain the same.

Sub Save()

C = MsgBox("Do you want to Save?", vbOKCancel, "Warning") 'Ask for saving

Question = Range("E6")	'Get	Question Number
QN = Range("E3") + 3	'Get	Questionnaire Number
Result = Range("J" & QN)	'Get	Answers

If C =	vbOK Then	'You clicked OK and you want to save
Range("G" & QN) = QN - 3			'Put Questionnaire number into column G
Range("H" & QN) = Range("E5")			'Put PollsterID into column H
Range("I" & QN) = Range("B5")			'Put Number of children into column I
Range("J" & QN) = Result			'Put Answers into column J
Range("E3") = Range("E3") + 1			'Increment the Questionnaire number
Else	'You cancel

Range("H" & QN & ":J" & QN) = "" 'Clear written data

End If

Range("E6") = 1 'Reset Question Number

End Sub

Macros

177

Questions

1.If Range("A1") has 5 in it, what does the following statement do?

ClassName =Range("A1") & "A"

a.A1 will have the value in ClassName - "A"

b.It’s an illegal operation

c.A1 will have "5A"

d.It assigns "5A" to ClassName

2.How can you run a macro?

a.Select Macros from the Developer Tab. Then, select your Macro and click the Run button

b.Select the Developer tab Play Macro and select your macro

c.Press Alt+F11 and click somewhere in your macro

d.Click the Run Dialog button from the Developer tab Controls then select your macro and press OK

3.If you perform a task repeatedly in Microsoft Excel, you can automate the task by using a ...

a. Filtering	b. Formula
c. Macro	d. User Form

4.Which of the followings are not valid macro names? Explain.

a. CalcTax%	b. Calculate Salary
c. FirstofAprilJokes	d. 2ndExpense

5.What is the shortcut to open the VB Editor?

a. Ctrl+F5	b. Alt+F7
c. Alt+F11	d. Ctrl+F12

6.Which of the following is not a Form Control?

a. Option Button	b. Check box
c. Button	d. Popup menu

7.What is the shortcut to show the Macro dialog box?

a. Alt+F8	b. Alt+F11
c. Ctrl+M	d. Ctrl+F5

8. What does the following statement do?

Range("A1") = Range("A1") * 2

a.Multiplies the value in A1 by 2 and puts the result back in A1

b.A1 will contain half of the old value

c.The square of the value in A1 is written

d.It’s an illegal operation

9.What does the following statement do?

Range("A1:F1") = ""

a.The Range("A1:F1") will have a double quotation

b.The Range("A1:F1") will have zero

c.Clear the contents in the Range("A1:F1")

d.It’s an illegal operation

10.Which of the following is not a property of Combo box in Excel?

a. Cell link	b. Input range
c. Selected Item	d. Drop down lines

11.For what an input box is used for?

a.to get a value from the user using a box

b.To store some values there

c.To show a message to the users

d.To hide some details from the users

178	Microsoft Excel

Project

1.Mr. John Lions, Accountant of the MiCows Company, is having difficulty with lots of calculations. He asked for some help from the boss. And the boss selected you for the job. Mr. John, to help you, numbered the expenses. So, in a data board, he keeps the list starting from the 11th row. From time to time, he wants, when he runs the macro, to calculate the sum of each expense type to be written to the summary list at the top.

2.Champions League Matches: UEFA has fired their computer programmer, because of some disagreements. and now, they need someone who can solve their programming problems.

Basically, they have difficulty with their current pointing system and they want to calculate points of each team directly from the score board. In a data board, in an Excel sheet, they store the scores of each match. And they want you to write a macro program that will take the data from the board and calculate the points and sort the teams.

3.Now, you are ready to write the previous project that you prepared for your science teachers. This time they ask for some combo boxes from you to simplify the selections.

Your teachers will keep the class names in a separate range of cells. There will be two separate combo boxes; one for class names and another for students in the selected class. Any time they select a class from the combo box, the second combo box items will be

updated and will have the student list in the new class. When they select any student from the student combo box, it will show the information on the selected student and averages for the selected class.

Macros

179

4.Prepare a calculator in Excel.

Prepare buttons as in the figure. As a second project, you can also upgrade this calculator to make complex calculations using parenthesis.

Note: Use Macros, don’t use the easier method in which you calculate the results without programming.

Practice

1.In Chapter 5 you prepared a formula for your teacher which takes 25% of two exams and 50% of the final. Now, you are ready to prepare a function for him

2.Write a function to convert USD into Turkish Liras. Your function will take the amount of dollars and exchange rate as parameters, and then calculate and return the converted value.

3.Write a function to calculate the area of a circle with the given radius.

4.Write a function to calculate the cube root of a number. Note: You can use power operator (^) num ^(1/3)

5.How many Macro procedure types do you know?

6.What are the properties of Functions?