Excel2010

9.3.6.1 Selection statements

The most common selection statement is the If Then structure. It is used to decide. It has two essential and one optional part.

If (Boolean Expression) Then

Statement

Else

This part is optional

Statement

End if

Example 9.3:

You want to decide, according to his average, if a student has passed or failed. The students pass if their average is greater than or equal to 5. Student average is in D1 and you want to write the result to D2.

Sub Passes()

If Range("D1") < 5 Then

Result = "Failed"

Else

Result = "Passed"

End If

Range("D2") = Result

End Sub

Example 9.4:

In example 9.1, we had a sub procedure to delete selected cells. Now, add a message, whether the user is sure to delete. If he presses yes, the selected cells will be deleted.

Sub Macro1()

Name = InputBox("Enter your name please..:")

Res=MsgBox("Hello Mr." & Name & ". Are you sure?", _ vbYesNo,"Delete Record?")

If Res = vbYes Then

Selection.Delete Shift:=xlToLeft

End If

End Sub

Figure 9.10: Using InputBox and

MsgBox in Macros

Macros

161

D1 is the parameter and its value is assigned to Num.

Figure 9.11:

Using Functions in Excel

9.3.6.2 Repeating some statements

Using For Next loops, you can repeat some operations:

For x = 1 To 10

' The code here will be repeated 10 times

Next x

Example 9.5:

Here is a function to calculate N!

Function NFactorial(Num As Integer)

Result = 1

For x = 1 To Num		The loop is repeated Num
		times (7 in this example).
Result = Result * x
Next x
		x represents all numbers
NFactorial = Result		from 1 to num(7). Then,
End Function		Result is multiplied by all
		the numbers from 1 to 7.

9.3.7 Object Browser

Object Browser gives short description and usage information about commands and objects. It shows class names and Members of the selected class. It also gives the properties of the member; either the member is property or a function of the class.

Write your command here to search

Figure 9.12: Object browser

162	Microsoft Excel

9.4 Ready for a bigger project?

Your physics teacher is preparing a multiple choice exam and he wants to check this exam using Excel. He designed a worksheet similar to Figure 9.13. He is going to write the answers of a student in this sheet. At the end, he wants to store the result using a macro. Macro will take the result (D14) and name (C1) to the end of the list in the columns F, G and H.

Analysis and Solution: According to his design:

The current student’s name will be written into C1

The current Student order is written in E4

Student answers will be written in to B4:B13

The cells C4:C13 already contains the correct answers,

The cells D4:D13 have the formulas to check if the student answer is the same as the correct answer.

F, G and H columns contain the result list.

	A	B	C	D	E	F	G	H
1		Student's	Todd Williams		Active	1	David O’Conner	7
2		Name			Student	2	Simon White	2
					Order
3	QN	Answers	Correct Answers	Result		3	Shear Gambol	9
4	1	A	A	TRUE		4	Alan Norman	6
5	2	C	A	FALSE	7	5	Abraham Adair	5
6	3	C	C	TRUE		6	Dave Ones	10
7	4	D	D	TRUE
8	5	C	C	TRUE
9	6	B	B	TRUE
10	7	B	B	TRUE
11	8	A	A	TRUE
12	9	B	B	TRUE
13	10	B	B	TRUE
14				9

Figure 9.13: Physics exam results

Macros

163

Arranging formulas first

Result is a formula that checks if the answer of current student is the same as the correct answer. For the first question, it’ll check if C4 is equal to B4. If both are the same, it’ll give TRUE, otherwise FALSE.

=IF(C4=B4,TRUE, FALSE)

Total correct formula in D14 is another formula. It uses the COUNTIF function to count the number of TRUEs

=COUNTIF(D4:D13,TRUE)

When recording, you can show both the Excel window and VB Editor at the same time to watch your recording.

The cell E4 will contain the active student order. So, the name and the result will be copied into that row in the columns G and H. In the example above, Todd Williams will be saved in the 7th row in columns F, G and H. These are the formulas that will remain throughout all macro runs.

Before starting to record macro

After you prepare the sheet, for each student, the teacher will write names into C1 and answers into B4 through B13. Then, the formulas will produce his total correct answers. Macro is now ready to run and store this student info.

Record your macro

After entering all data, we’ll call the macro for this student. We’ll use the number in E4 for the row number of the destination. After starting macro, you will

Give a name and a shortcut for your macro

Copy the student name from C1 into destination row in column G

Copy result from D14 into column H.

After that increment the number in E4

And clear old data (The cells B4:B13 and C1)

Select C1

Stop recording

Figure 9.14: Viewing Excel and Visual Basic Editor

Simultaneously

When recording, you can show both Excel window and VB Editor at the same time to watch your recording.

If you record this macro, it will be something similar to the following code,

164	Microsoft Excel

Sub Macro1()

'

'Macro1 Macro

'Macro recorded 7/20/2009

'Keyboard Shortcut: Ctrl+Shift+P

Range("E4").Select	'Copy order
Selection.Copy
Range("F7").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("C1").Select	'Copy name
Selection.Copy
Range("G7").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("D14").Select	'Copy result
Selection.Copy

Range("H7").Select

Selection.PasteSpecial Paste:=xlPasteValues

Range("E4").Select	'Assigns the new value for order
ActiveCell.FormulaR1C1 = "8"
Range("C1").Select	'Clear old name
Selection.ClearContents
Range("B4:B13").Select	'Clear old answers
Selection.ClearContents
Range("C1 ").Select	'Select name cell

End Sub

Record finished but we need some modifications in this code. First of all, this macro will always write the results to the 7th row. So, we need to modify the resulting row order. For this purpose we can use a variable for the RowOrder. We’ll take the RowOrder from E4 with,

RowOrder = Range("E4")

' RowOrder is currently 7

Then, instead of writing the result always to G7, we can concatenate and form the new address as,

Range("G" & RowOrder)

' This is like Range("G7")

Macros

165

Secondly, we don’t have to copy paste every time. Instead, we can directly assign the source value to the destination.

Range("G" & RowOrder) = Range("C1") 'Copies the name into column G

Then, our final program becomes,

Sub Macro1()
'	Keyboard Shortcut: Ctrl+Shift+P
'	RowOrder = Range("E4")	'Takes the row order from E4

Range("G" & RowOrder) = Range("C1") 'Copy						the	name into column G
Range("H"	&	RowOrder)	=	Range("D14")'Copy		the	result
Range("F"	&	RowOrder)	=	RowOrder	'Copy	Student order

Range("E4")	=	RowOrder + 1		'Increment row order
Range("B4:B13") = ""			'Clear	the	old	answers
Range("C1")	=	""	'Clear	the	Name cell for next student
Range("C1").Select			'select C1		for	the next student

End Sub

9.4.1 Want More? (Optional)

If you find it confusing, don’t worry, you are not alone. Programming subjects come difficult for many people in the world. But, be patient and ask more and more from Excel, it’ll show how to solve your problems. Don’t forget that practice (with patience) makes perfect. Here is another project for you.

In your school, Science teachers decided to have a general exam every month. Because it’s getting more and more difficult to process data every month, they decided to use Macros. They’ll have a list similar to the figure on the left.

The sheet has two parts: Starting from the 6th row, they will place general exam data; they want to see the results

of query at the top.

Figure 9.14: General exam sheet

The class and student name information will be provided into the cells A3 and B3. And then, with a shortcut key, macro will run. Macro will search all the data for the specified class and student. In the third row, they want specified student’s marks. And in the fourth row, they want to see lesson averages of the selected class.

166	Microsoft Excel

Solution:

The project is based on a simple idea: Paste Special. But using loops and selection structures (If Then) makes it a little bit difficult. Here is the algorithm:

First, clear old data: the lesson and class averages, Range(C3:F4)

Get the Class and Student names from A3 and B3

Get the Number of students (LastSt) from A6

Start a loop from the 7th row until the LastSt number

Check if the current student (in the first case, Range(“A7”)) is from the same class. If YES:

Copy the range CRowNumber:FRowNumber (e.g. C7:F7)

And use paste special with Operation:=xlAdd over the Range(C4:F4)

Check again to see if this is the student you are searching for Paste also the results over the Range(C3:F3)

Repeat this LastSt times

It’s quite normal that nobody can know everything. So, for the parts we don’t know, we can ask from ‘Record Macro’ to find out. Let’s say that we don’t know how to add a range over another.

We run Record Macro from Developer Tab. It will ask for a macro name and a shortcut. After you press OK, it will start recording Macro.

In this record macro, we want Excel to show us how to add the value of a cell range onto another. So,

We select the source range then, we copy it.

After that, we right click on destination, and select ‘Paste Special’ from the menu.

Finally, from the ‘Paste special’ window, we select Paste: Value, Operation: Add.

Figure 9.15: Record GDS GetData

macro

When giving macro names:

1.Use Letters, digits, and underscore sign only. No special characters allowed like: %, $, # or space. GetDollar is OK; Get$ is not.

2.Start with a letter: FirstPlace is OK; 1stPlace is not.

3.Don’t use special words (Keywords) for Excel and VBA. GetData is OK; For, While are not.

When giving shortcuts be careful not to overwrite the predefined (standard) shortcuts. Ctrl+C means copy.

	167
Macros

Pay attention to the underscore sign at the end of line 9. If your statement exceeds the visible window size, using an underscore you can continue from the next line.

1.Sub GetData()

2.' Getdata Macro

3.' Macro recorded 7/20/2007

'

' Keyboard Shortcut: Ctrl+Shift+D

	Range("C11:F11").Select	'Select source
	Selection.Copy	'Copy
	Range("C4").Select	'Select destination

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, _

SkipBlanks :=False, Transpose:=False

Application.CutCopyMode = False

End Sub

Now, you learnt how to add a range onto another range. In this code, we can skip the transpose and skipblanks parts, because we are not using them. So, we only need to write:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd

Now, we are ready to write the entire code. To make it easy, your teachers placed the number of students in the A6 cell. First, we get the data: Name and Class information, from the cells A3 and B3

CName = Range("A3") ' Get Class Name from the cell A3

StName = Range("B3")' Get Student Name from the cell B3

LastSt = Range("A6")' Get The number of Students from the cell A6

StNum = 0 ' Because, we take the class average, we need to

' count the number of students in the class

Range("C4:F4") = "" ' Clear the range for the current operation

After we get initial data and prepare our result part for run, we can start searching from the first until the last student. To repeat commands or a code, we studied that we can use FOR NEXT loop structure from Basic Programming Language.

For x = 1 To N

‘ The code here will be repeated N times

Next x

168	Microsoft Excel

Here is the entire code

Sub Calculate()
CName = Range("A3")	' Get Class Name from the cell A3
StName = Range("B3")	' Get Student Name from the cell B3
LastSt = Range("A6")	' Get The number of Students from the cell A6
StNum = 0 ' Because,	we take the class average, we need to
' count the number of students in the class
Range("C4:F4") = ""	' Clear the range for the next operation

For x = 1 To LastSt

' y is the row order that contains current student name y = 6 + x

If Range("A" & y) = CName Then

' If Class Name is the same as the current line info then

' Increment the number of students in the class StNum = StNum + 1

'Copy this line to average part Range("C" & y & ":F" & y).Select Selection.Copy Range("C4").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd

'By saying Operation:=xlAdd we take

'the sum of each subject when pasting

'If current name is the same as the searching name (StName) then If Range("B" & y) = StName Then

Range("C3").Select

Selection.PasteSpecial Paste:=xlPasteValues

'Paste the results also for this student onto Range("C3:F3) End If

End If

Next x

'Calculate the averages If StNum<> 0 Then

Range("C4") = Range("C4") / StNum

Range("D4") = Range("D4") / StNum

Range("E4") = Range("E4") / StNum

Range("F4") = Range("F4") / StNum

End If

Application.CutCopyMode = False

Range("C3").Select

End Sub

Macros

169

Figure 9.17: Assign Macro dialog box

Figure 9.18:

Combo box Format Control

9.5. Form Controls

Until now, we studied how to record a macro; how to modify it; and some main Excel Macro elements. However, there are some other tools which facilitate many operations in Microsoft Excel Macro. Form Controls is one of these tools.

1.	Button	7.		Group box
2.	Combo box			Label
			8.
3.	Check Box		9.	Scroll bar
4.	Spinner		10.	Edit Box
5.	List Box		11.	Combo List Edit
6.	Radio Button		12.	Combo Drop Down

Figure 9.15: Developer tab Controls Insert Form Control

Writing code is very easy with these controls. Now, we’ll study Form Controls and using them with Macros.

9.5.1 Button

When you select the Button tool, it’ll let you draw the size and place of the button. After you define the place, it automatically opens the ‘Assign Macro’ window and asks you the name of macro to associate with this button. You can

write a new macro name and press the New button

or press the Record Button to record a new macro

or you can select one of the existing macros in the list then press Assign.

Any time you need, you can see this macro by using the “Macros…” command from the Developer Tab (or right click on the button and select ‘Assign Macro’) then click the Edit button to edit it as you wish.

9.5.2 Combo Box

Combo box is used to list some items and lets you select one. Selected item will appear in the main box. Different than other objects, Form Controls have another tab in the ‘Format Control’ window: Control Tab. Using this tab, you can change the controls like; input range, cell link and some other status options for the combo box.

Input range defines the items that’ll appear in the list box. Cell link defines the cell that will have the result of the selection. Using the ‘Drop down lines’ box; you can define the number of lines in the drop down list.

170	Microsoft Excel