Excel2010
.pdf
Practice
1.The City police department wants a computerized system to accept visas. In order to avoid mistakes, they want to keep the lists for countries and cities in separate ranges. So, any input that is in this list will be accepted as valid input. The police department wants Microsoft Excel to warn for any other inputs.
2.Create the following table and apply grouping as shown in the figure.
3.Use the split command on the table in the first practice.
4.Suppose that your workbook contains 4 sheets. Show how you can write the same data to all sheets simultaneously.
5.Can you hide
•sheet tabs,
•vertical & horizontal scroll bar,
•column & row headers,
•formula bar,
•status bar.
6.Change the color scheme silver and the gridlines color to dark blue.
7.Change the cell calculation style to manual so that it won’t calculate results automatically.
8.Add a list showing the days of the week of your country.
9.How can you secure your Excel document?
Extra Options |
151 |
Put the Words in Groups
Write the following commands into their own Tab on the Options menu.
1. |
Font size |
|
|
8. |
Include this many sheets |
|
|||
2. |
Flag repeated words |
|
9. |
AutoCorrect options |
|
|
|||
3. |
Show all windows in the taskbar |
10. |
Use all processors on this computer |
||||||
4. |
Save in this file format |
|
11. |
Use 1904 date system |
|
||||
5. |
R1C1 reference style |
|
12. |
Use table names in formulas |
|
||||
6. |
Enable iterative calculation |
|
13. |
Dictionary language |
|
||||
7. |
Color scheme |
|
14. |
Show insert options buttons |
|
||||
|
|
|
|
|
|
|
|
||
|
|
Tab |
General |
Formulas |
Proofing |
Save |
Advanced |
||
|
|
|
Font Size |
|
|
|
|
|
|
Commands
Project 
1.Find and change the default font properties in your Excel environment.
2.Prepare a questionnaire for your friends and put it in an Excel workbook. Start Tracking changes on the document and give this diskette to 5 friends. After you take the diskette back, prepare charts for every question in it and present the results of your survey to the class.
3.Prepare a workbook for your class that includes a column for Registration date. Use Excel’s Data Validation feature to restrict entering any date later than 7/7/2009
4.Use the Freeze command to make the title row and names columns always visible.
5.Find and add your country’s language dictionary to the spelling options.
6.Put a password in your Excel workbook to open and modify.
Figure 9.1: Developer tab Code group commands
MACROS
Macro is a series of instructions to perform an operation automatically. If you perform a task repeatedly, it’s better to use a macro for this process. You can save your processes in Excel and give a name and a shortcut for this process (macro), then, any time you need to perform it; you can just call it to repeat the same process for you.
9.1 Before Starting Macros
Here are some essential things that you should know before starting macros.
9.1.1 Displaying Developer Tab
If you plan to work with VBA macros, you’ll use Developer tab often. To display this tab:
1.Choose Office Excel Options.
2.In the Excel Options dialog box, select Customize Ribbon.
3.Place a check mark next to Developer tab.
4.Click OK to return to Excel.
9.1.2 Some Definitions
VBA newcomers are often overwhelmed by the terminology that is used in Macro programming. Here are some key definitions for a better understanding.
Macro: A series of commands and functions that are stored in a Microsoft Visual Basic module and can be run whenever you need to perform the task.
VBA-Visual Basic for Applications: Special programming language for Macros that you can use in Excel and other Office applications
Module: A container for VBA code where you write your macros.
Code: VBA commands that are produced in a module sheet when you record a macro. These commands -keywords- have special syntax. You can make Excel to produce the code for you or you can enter it manually.
Function: One of two types of VBA macros that you can create. (The other is a Sub procedure.) A function returns a single value. You can use VBA functions in other VBA macros and also in Excel sheets.
Procedure: The other name for macro sub programs. A VBA macro can be a Sub procedure or a Function procedure. Sub procedures do your operations described there.
154 |
Microsoft Excel |
Object: An element that you manipulate with VBA. Examples include: Ranges, Charts, Sheets, and so on.
Controls: Objects on a UserForm (or in a worksheet) that you manipulate. Examples include: buttons, list boxes, etc.
Method: An action taken on an object (or control). For example, applying the Clear method to a Range object erases the contents and formatting of the cells.
Property: A particular aspect of an object. For example, a Range object has properties like: Height, Style, and Name.
UserForm: A container that holds controls for a custom dialog box and holds VBA code to manipulate the controls.
VB Editor: The window (separate from Excel) that you use to create/edit VBA macros and UserForms.
9.1.3 Security first
Because macros can be very dangerous, in the earlier Excel versions, you needed to be very careful when opening Excel documents. But now, developers of Excel made a lot of improvements. The most important change is the Macro containing files’ extensions. If an Excel document contains macros, it is saved with the .xlsm extension. This means that before opening the document you know that it contains macros. The good side is that, in a way, if the extension is changed to .xlsx Excel will not open it and warn that this is not a recognized format.
Trust Center is the essential window for security in general. By default, developers setup it quite secure. You just need to be careful when you see a warning message over the Formula bar:
If you click Options button, it’ll show MS. Security Options dialog box. If you trust the sender of the file you can enable the macros in it. If you are not sure what to do, you should protect yourself. If you don’t enable the macros, you can still edit the document as a standard Excel document. In order to arrange security tightly, select Trust Center from the Office button Excel Options.
Figure 9.2: Options in Trust center
Macros |
155 |
Figure 9.3: Developer tab Code group commands
Write the macro name here
Figure 9.4: Record Macro dialog box
9.2 Writing Your Macros
In general, you can write macros using two common methods:
By recording
Writing macros manually
9.2.1 Recording Macros
For beginners, recording macro is easier. You can record macro from the Code in Developer tab. When you select “Record Macro…”, It’ll show the Record Macro dialog box.
In this window, you can give your macro a name in the Macro name box and then give a shortcut key for your macro. Because many programmers forget the details about their programs after some time, it’s a good idea to give brief information about the macro for later use. After you click OK, ‘Record Macro’ button will change to ‘Stop recording’.
And, Excel’ll start recording your actions. All your mouse clicks, cell operations and other Excel commands will be stored in the macro until you press ‘Stop recording’ button.
Example 9.1:
Your brother prepares a document for his school homework. In his document, he uses Delete cells command frequently. He wants you to do something to simplify this process. He wants, every time he selects a range then presses a shortcut key, the selected range to be deleted and the other cells to be moved to left.
Solution:
Select the range that you want to delete.
Select “Record Macro…” from Developer Tab
Write your macro name in the dialog box and
give a shortcut for your macro (Ctrl+Shift+D)
Press OK.
Now, it starts recording all your commands. So,
Right click on the selected area
Select ‘Delete…’ from popup menu.
It will ask you to move the cells up or left.
Select left and press OK
Press ‘Stop Recording’ button to stop the process.
156 |
Microsoft Excel |
You can see your macro using the “Macros…” command in the Developer tab. This will open the main window for Macros. You can do all operations related to Macros from here, Figure 9.5. You can Run, Delete, Edit or Create other macros from this window. When you press Edit button here, it’ll open Microsoft Visual Basic Editor, Figure 9.6.
ALT+F8 displays the Macro dialog box to create, run, edit, or delete a macro.
Insert User |
|
Run, Pause or |
|
|
Opens Properties Window |
||||||
Form |
|
Stop Macros |
|
|
Project Explorer Object Browser |
||||||
|
|
|
|
|
|
|
|
|
|
|
|
Project
Explorer
Figure 9.5: Macros dialog box
Here is your Macro.
Properties
window
Figure 9.6: VB Editor environment
In this window, the main pane includes your Macro.
The header of subprogram
Explanation /
User Comments
Actual
code
Sub Macro1()
'Macro1 Macro
'Macro recorded 08.04.2009
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Selection.Delete Shift:=xlToLeft
End Sub
Press Alt+F11 to open VB Editor.
Macros |
157 |
9.2.2 Writing Macros Manually
From this Visual Basic Editor window, you can edit your code (Macros) or you can write your entire code from the beginning. Most of Basic Programming Language, Excel and Visual Basic instructions can be used here. Here is a brief explanation about the code above:
The text after the apostrophe is an explanation, not a programming code.
Sub is a command that says to VB (Visual Basic) that a subprogram with the name Macro1 is starting.
And “End Sub” shows that the subprogram has ended.
So, we have just one line of code here. And, instead of recording, you could manually write your macro like this one:
Sub Macro1()
Selection.Delete Shift:=xlToLeft
End Sub
Now, your macro is ready to use. Any time you press Ctrl+Shift+D keys, it will delete selected range of cells and move the right cells to the left.
9.2.3 Types of Macro Procedures
There are two types of macro procedures:
Sub procedures
Function procedures
In the example above, we had a sub procedure. You can also have function procedures in your macros. Function procedures are like Excel functions, and they return a result to the name of function somewhere in your code (usually at the end). They start with the keyword Function. When you create a function procedure, you can use it in your macros and in your Excel sheets.
Example 9.2:
We don’t have a cube function in Excel, but, you can create it.
Open an empty Excel sheet.
Go to Developer Tab and click Macros. This will open the Macros window which is initially empty.
Write ‘Cube’ in the Macro Name box
Then click the Create button. This will open the VB Editor and create your sub procedure.
Now, change it manually as follows,
158 |
Microsoft Excel |
Parameter(s)
Function header 
Function Cube(num As Integer)
After your |
Cube = num * num * num |
|
calculations, you |
|
|
return a result to |
End Function |
|
the name of the |
||
|
||
function. |
|
|
|
|
Then in your Excel sheet, you can use it as your other predefined functions.
9.3 Macro Language
In your Macros, you can use Excel object language, or Basic programming language.
9.3.1 Excel Object Hierarchy
VBA is an object-oriented language, which means that it manipulates objects, such as Ranges, Charts, AutoShapes, and so on. These objects are arranged in a hierarchy. The Application object (which is Excel) is the topmost object and contains other objects. Most of objects can contain other objects. For example, a Workbook object can contain the following objects:
Charts (a collection of Chart sheet objects)
Names (a collection of Name objects)
Styles (a collection of Style objects)
Windows (a collection of Window objects in the workbook)
Worksheets (a collection of Worksheet objects)
When you write the object name followed by a period, after a short time, Excel will display possible operations, sub objects and properties that you can use in this context.
9.3.2 Object Collections
A collection consists of all like objects. For example, the collection of all Workbook objects is known as the Workbooks collection. You can refer to an individual object in a collection by using an index number or a name. For example, if a workbook has three worksheets named: Sheet1, Sheet2, and Sheet3, you can refer to the first object in the Worksheets collection in either of these ways:
Figure 9.7:
Using VB functions in Excel
Figure 9.8: Object model code compilation
Macros |
159 |
Figure 9.9: Using inputbox and message box
Worksheets(1)
Worksheets("Sheet1")
But using index number can be sometimes problematic, if you add and remove worksheet, so, it is not suggested. Using a period as a separator, you refer to an object in your VBA code by specifying its position in the object hierarchy.
Application.Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1")
You can omit default references. So, if you simply use Range("D3") when you’re in Sheet2, Excel uses the D3 cell of that worksheet from the active workbook.
9.3.3 Assigning value to a cell
Using the ‘Range’ command in Excel, we can assign a value to a range.
Range("B2") = |
7 |
'Puts 7 to the cell B2 |
|
Range("C1:D3") |
= 5 |
'Puts 5 to all cells in the range C1:D3 |
|
Range("A:A") = |
3 |
'Puts 3 |
to all cells in column A |
Range("5:5") = |
4 |
'Puts 4 |
to all cells in Row 5 |
Range("B1") = |
Range("D7") |
'Copies the value of D7 to B1 |
|
Range("B2") = |
"" |
'Clear |
the contents of B2 |
9.3.4 Using the Message Box and Input Box
InputBox can be used to get info from the user.
Name = InputBox("Enter your name please..:")
The result is assigned to the variable: Name. And, you can use it in your further processes.
You can show your messages using a windows dialog box.
MsgBox ("Hello World!")
9.3.5 Concatenating Two Strings
Using “&”signampersand sign-, you can concatenate two strings (add one string to the end of another). When you execute the following command
Result =Range("C1") & "A"
second string (‘A’) will be added to the end of first one. So, if Range ("C1") contains "5", the Result becomes "5A".
9.3.6Using Basic Programming Language in your macros
You can use Basic Programming Language in your Macros. Here are some essential structures from it.
160 |
Microsoft Excel |