Looking for:
– Microsoft office excel 2010 manual pdf free
Click here to know how to put your classifieds as VIP. Sea on foot. Terrace with Kitchenette microwave, toaster, autodesk mudbox 2016 product free ,minibar,t. Can accommodate four peoples 2 double beds. Situated in full town center close to restaurants, Each piece is handmade and unique, and cannot be exactly replicated.
Slight variation may occur compared to the pictures. Follow me finding. Earrings purchased are strictly non-exchangeable and non-refundable. Artists Premium is an artistic and event agency specializing in artistic production and organization of shows. Our agency has a catalog of music bands and professional artists microsoft office excel 2010 manual pdf free authentic gospel in the African American style, reggae, jazz, soul, Pop, dance Gospel choir for как сообщается здесь, weddings, and other events June 09, You are organizing an event and you want to listen to the real gospel?
Afro-American gospel: authentic gospel? You are at the right place! Your Gospel Team is a gospel choir, the first one in Switzerland, specialized in the animation of the weddings, concerts, The machine is in good working order. Detailed photos available on request. Perhaps you’d like to talk Very beautiful house “le Clos du chat tambour”, of m2 with basement, for sale on the Alabaster coast in Seine Maritime This house with a garden of m2, benefits from an exceptional location, quiet, 3km from the sea and 7 km from the city center Sell a living room coffee table made of exotic solid wood.
This semi-precious wooden coffee table “Courbaril” was brought back from French Guiana in It is in very good condition and very rare, not to say not to be found in metropolitan France and even We also do tutoring from CP primary to baccalaureat’s grade. We remain at your disposal. Guadeloupe Scribe Business Administration is a leading young business in marketing, communication and press relation at your services. With a past experience in graphic design proficiency, the team is waiting 3000 windows 10 free boost your business on the market undoubtedly.
Our services Business bilingual secretary available to all types of businesses – Special business package November 16, Whatever your microsoft office excel 2010 manual pdf free in getting your projet done, or documents, we are experienced enough to provide you with the business communication level suitable to your need.
French mother tong and proficient in english for business we are the one skilled solution at This event is unique in our department. On this occasion, microsoft office excel 2010 manual pdf free and Reunion Island Ideal for sporty, adventurous bon vivants. Wake up with the glow of the first rays of the sun over the mangrove forest.
First a hearty breakfast with a view of the islands Nosy Carry out your projects in complete safety June 17, For all your credit or financing needs, we offer our services. Reliable and very secure with a good interest rate. The property is about 12 minutes drive from Узнать больше 15th October at Conforma, guaranteed for 2 years. Selling because we are moving.
No delivery available. To be collected in Lamentin. To be collected in Lamentin Table
Microsoft office excel 2010 manual pdf free.Microsoft Access
Artists Premium is an artistic and event agency specializing in artistic production and organization of shows. Our agency has a catalog of music bands and professional artists from authentic gospel in the African American style, reggae, jazz, soul, Pop, dance Gospel choir for concerts, weddings, and other events June 09, You are organizing an event and you want to listen to the real gospel?
Afro-American gospel: authentic gospel? You are at the right place! Your Gospel Team is a gospel choir, the first one in Switzerland, specialized in the animation of the weddings, concerts, The machine is in good working order. Detailed photos available on request. Perhaps you’d like to talk Very beautiful house “le Clos du chat tambour”, of m2 with basement, for sale on the Alabaster coast in Seine Maritime This house with a garden of m2, benefits from an exceptional location, quiet, 3km from the sea and 7 km from the city center Sell a living room coffee table made of exotic solid wood.
This semi-precious wooden coffee table “Courbaril” was brought back from French Guiana in It is in very good condition and very rare, not to say not to be found in metropolitan France and even We also do tutoring from CP primary to baccalaureat’s grade.
We remain at your disposal. Guadeloupe Scribe Business Administration is a leading young business in marketing, communication and press relation at your services.
With a past experience in graphic design proficiency, the team is waiting to boost your business on the market undoubtedly. Our services Business bilingual secretary available to all types of businesses – Special business package November 16, Whatever your need in getting your projet done, or documents, we are experienced enough to provide you with the business communication level suitable to your need.
French mother tong and proficient in english for business we are the one skilled solution at Windows Mac microsoft office for windows microsoft office for windows 10 microsoft office for windows 11 microsoft office for windows 7.
Apache OpenOffice 4. Free open-source office suite The Apache Software Foundation received the free open-source office suite from Oracle Corporation in and has managed the platform since then. Windows apache apache for windows 7 business software for windows 10 database for windows 7 database free. Windows Mac doc to pdf converter document converter easy to use file converter file converter for windows.
Microsoft Excel Viewer Freeware for accessing Excel spreadsheets Microsoft Excel Viewer is a free program that enables you to view and print spreadsheet documents created in Microsoft Excel without having the program Windows excel viewer microsoft microsoft excel microsoft excel for windows microsoft excel for windows 7. Office Service Pack 1 1. Windows microsoft office microsoft office for windows microsoft office for windows 10 microsoft office for windows 7.
Grammarly for Microsoft Office 6. Write right Grammarly for Microsoft Word is a helpful productivity tool that users can use to check if there are any grammatical errors in their text while they are Windows microsoft office for windows microsoft office for windows 10 microsoft office for windows 11 microsoft office free for windows.
LibreOffice 7. Windows Mac database for windows 7 database free for windows 10 drawing drawing free for windows 10 drawing free for windows 7. Adobe PDF Converter 5. Windows adobe adobe for windows adobe for windows free adobe free excel converter for windows 7. Windows business management business management free email management email management for windows email management free. Microsoft Whiteboard varies-with-device 3. Collaboration tools for Microsoft users Microsoft Whiteboard is a collaboration tool that allows Microsoft users to quickly share ideas with other people.
Windows Android iPhone business business for windows business for windows free business free cloud app free. Improve security, performance and stability of Microsoft Office Office Service Pack 1 for Microsoft Office bit Edition contains updates that improve the safety, performance, and stability of the Microsoft Windows microsoft office microsoft office microsoft office for windows microsoft office for windows 10 microsoft office for windows 7.
Free Download for Android. All you need, now in your pocket Microsoft Office has launched Microsoft Office for Mobile, which can now be purchased for your use and enjoyment. Android iPhone access access for android microsoft microsoft access microsoft access free.
Word Online 2 4. Windows Chrome microsoft office suite microsoft office suite for windows microsoft office suite for windows 10 microsoft office suite for windows 7.
Microsoft Office Compatibility Pack Windows bridge bridge free microsoft microsoft for windows microsoft for windows free. Microsoft OneNote Create and share a digital notebook Microsoft OneNote is a digital notebook designed for multiple users. Windows Mac microsoft microsoft for windows microsoft for windows free microsoft free microsoft office.
Microsoft Office Word Viewer Free Microsoft Word document viewer Microsoft Office Word Viewer is a handy tool that lets you view or print any Microsoft Word document quickly, without having to install Microsoft Office in Windows document converter document viewer document viewer for windows document viewer for windows 7 docx for windows 7.
Microsoft Office IconPack 3. All of the Microsoft Office icons Microsoft Office IconPack is a handy, free program only available for Windows, belonging to the category Desktop customization software with subcategory Windows Mac desktop icons desktop icons for windows 7 microsoft microsoft for windows microsoft for windows free.
Microsoft Excel – Training Manual (Beginners) | PDF | Spreadsheet | Microsoft Excel
Worksheets “sales” wks. The property of the tab is changed by referencing the object variable and setting the property to the appropriate colour. Example 2 define the sheet to change Sub changetabsales ThisWorkbook. Worksheets “sales”. Select ActiveWorkbook. Sheets ActiveSheet. The accessibility or scope is determined where the variable has been declared. Declarations Section The top of the code window includes a declaration section; variables placed here can be used by all procedures within the module remember option explicit is also set in this area.
Types of Scope Three types of scope are available in VBA as already stated the scope of a variable is determined by the way you declare it. Procedure-level, when you declare a variable within a procedure it is not accessible outside of the procedure. A procedure level variable is only available within the procedure it is written. Private module-Level, when declaring a variable in the declaration section of a module using Dim or Private keyword the variable is known as a private module-level variable, the variable can be used by any procedure within the module but cannot be accessed by a procedure outside the module.
Public Module Level, when a variable is declared in the declaration section with the prefix keyword public, the variable is called a public module-level variable these variables can be called from any procedure or module. You can also specify a type for the procedure it can be one of the following, Sub, function or property function procedure is similar to a sub function but whereas the sub procedure executes code the function procedure returns a value. Private indicates the procedure can only be used in the current Module.
A procedure can have one or more statements that are executed sequentially. To call the procedure you use the Call keyword followed by the name of the procedure you wish to execute. A procedure that calls another procedure is called a calling procedure, the procedure it refers to is known as the called procedure. When you call a procedure you can pass information to it through arguments. A called procedure runs its code and then returns control to the next line in the calling procedure.
The value that the function produces is stored in a predefined variable. The scope of the function will be either private or public. Though optional if not declared the variables are set to variant remember we said earlier code is more efficient when specified.
Value Cells 8, 5. The result is now shown as see below. Else Construction If Else constructions allow you to test for one or more conditions and run one or more statements depending on each condition. Else Statement in VBA. You can use a single-line version if you have just one test and one statement to run.
If you have a more complex set of conditions and actions, you can use the multiple- line version. Must evaluate to True or False, or to a data type that is implicitly convertible to Boolean.
Then Required in the single-line form, optional in the multiple-line form. Statements Optional. One or more statements following If Then that are executed if condition evaluates to True. Elseifcondition Required if ElseIf is present.
Elseifstatements Optional. One or more statements following ElseIf Then that are executed if elseifcondition evaluates to True. Elsestatements Optional. One or more statements that are executed if no previous condition or elseifcondition expression evaluates to True. End If Terminates the If Else block. Sub test4 If Cells 4, 5. You can execute various statements for each condition each based on the variable.
Use one of two loop structures depending on the number of iterations that are required. Fixed iterationThis runs a set of statements for a predetermined number of times. Example For… next loop Indefinite Iterationthis runs a set of statement until a defined condition is met. Next statement allows you to repeat a set of actions on the individual elements of a collection or array.
For example, if you select a range of cells, then this command allows you to repeat a set of commands on each cell in the range. Another examplewould be to repeat a set of actions on the worksheets in a workbook. The following is the typical syntax for the For Each..
Value Next End Sub Example 2 In this exercise if a cell has a number in the range A1:A50 the procedure will Double the cell value, if it contains a number,otherwise clear the cell. Now we introduce you to Conditional LoopsRepetition while a certain condition is satisfied or until a certain condition is satisfied.
Check for the condition before running the loop: Do Whilecondition Statements Loop Execute the commands once before checking the condition: Do Statements Loop Whilecondition Use the keywords Until or While to define the condition, placing them either at the top orat the end of the Do…Loop. Loop using Exit Do. Save your file before testing the code. It is very easy to get stuck in a conditional loop.
You must try to terminate the procedure if you are stuck. The programming of User Forms can be time-consuming as every action that the User Form performs has to be coded, the OK button does not do anything until you write the code contained in its click event. You need to be familiar with User Form objects, there is no macro recorder.
Naming objects It is awkward having to use the default object names when you are completing the event procedures for each control; is the OK button CommandButton1 or is it CommandButton2? Follow the published standard conventions for Control names, add the three-character lower case prefix to your names and you will never have any problems identifying your control objects in code. Switch from the excel view to the visual basic editor, from the insert menu select user form. The screen will now look as shown below.
If the toolbox is not displayed from the menu bar select view toolbox. Now we are going to add a List Box click on the icon add the list box below employee name input box.
To show as below. Next change the name property to lstempdepartment, locate RowSourceenter the following details E4:E10, the list Box will be populated from this data Range. Next add one more label rename the caption to Earnings, add a text box change the name property to txtempearnings. Now we shall finish it off navigate to the properties of the form in the caption box type the following.
Employee Information now for the commandButton1 in the properties change the caption to Enter Data change the name property to CmdAdd now for the commandButton2 in the properties change the caption to Close change the name property to CmdClose your form should now look like the one below. We have now created our user form click the save button to update and save your work In the next section we will look at events and how to transfer data from the user form to the excel data sheet.
Procedures are executed when an event is triggered. User forms and their controls respond to the events that are associated with them. Below is a list of events and their triggers. Value Cells 12, 2. Value Cells 12, 3. Offset nextrow, 0. Value Range “a1”. Offset nextrow, 1. Value If optMale.
Offset nextrow, 2. Offset nextrow, 3. If so, you may want to use a series of dialogs like a Wizard , or use a Multipage control. The user should be able to tab through the dialog box and access the controls sequentially. It’s important to remember that an add-in will never be the active workbook. Excel’s spelling checker doesn’t work with UserForms. And does it explain all the controls? This can be because a mistake in the code, called a bug or if a statement in the code tries to call an invalid operation.
Examples of this can be a misspelt variable that will cause an error. When an error occurs VBA will either display an error message or refer you to the help file. VBA provides you with a set of tools for debugging your code these include, toggle or break points, and the watch window. Error Types There are three types of programming error, compile time, run time and logical. The following table describes the type of error.
Error Description Compile-error A compile-time error happens when the program is being compiled. Generally, compile-time errors are syntax errors; and they are caught by the compiler. Run-time error Run-time errors occur at at run-time; generally, the program compiles but does not run correctly.
An example of a compile-time error might be leaving out a semi-colon in C. An attempt to read past dereference a null pointer in C would be an example of a run-time error. Logical a logic error is a bug in a program that causes it to operate incorrectly, but not to terminate abnormally or crash. A logic error produces unintended or undesired output or other behaviour, although it may not immediately be recognized as such.
Logic errors occur in both compiled and interpreted languages. Unlike a program with a syntax error, a program with a logic error is a valid program in the language, though it does not behave as intended. The only clue to the existence of logic errors is the production of wrong solutions.
VBA provides the following tools to help you debug your code these are located on the debug toolbar. Debugging tools The following table describes the tools available to you in VBA to view values of variables and expressions and trace the execution of a program. You can insert a breakpoint in the first line of code segment that you suspect to be the cause of the error. At this point in time the line of code causing the problem is displayed and highlighted in yellow, this helps you trace and debug the error.
You can set your own breakpoint in the code to pause at a specific place, when the executed code reaches the breakpoint VBA switches into break mode. Breakpoints are temporary markers and are not saved along with the code. To insert or remove a breakpoint place the insertion point in the code you want to insert the break point and use one of the following methods.
You will see that the calculation has now returned the correct value, so using a break point we can identify the problem area, in the next section we will look at the watch window.
In cases such as this you need to monitor the behaviour the expressions and variables of the entire procedure. Each expression or variable you monitor is known as a watch, watch expressions can be defined in either break mode or design time.
VBA automatically monitors and displays the expressions in the watch window. The watch window is displayed automatically when you enter break mode or you choose to open the watch window manually by clicking the watch window button on the debug toolbar. The watch window can be used to change values of variables and expressions this allows you to observe how these changes to affect the code.
Running code line by line examining the code line by line is known as steeping through, you can step through the code to determine which statement causes the error. There are three ways to step through the code. This allows you to observe the effect of each statement on variables. To step into the code either choose debug step into or press F8 or click step into button on the debug toolbar.
If the current procedure is a called procedure the remaining code in the procedure is executed and the debugging stops at the next statement in the calling procedure.
To step out of the code, choose debug, step out. The watch window before running the procedure commission The watch window after running the procedure commision Now we understand what stepping through procedure and how it works we shall now put that into practice. You can use the print method or use a? To view , immediate window click the immediate window on the debug window. Immediate window with example of print method and?
Working with the intermediate window Launch a new work book and type in the information below save as employee information, name the worksheet Employeeinfo.
Start to examine these objects by writing a short routine to change the value of a variable. You will see the entries in the Locals Window change as each line is executed with the contents and type of each variable displayed in turn. Errors in general come in three types: compiler errors such as undeclared variables that prevent your code from compiling; user data entry error such as a user entering a negative value where only a positive number is acceptable; Run time errors, that occur when VBA cannot correctly execute a program statement.
We will concern ourselves here only with run time errors. Typical run time errors include attempting to access a non-existent worksheet or workbook, or attempting to divide by zero.
The example code in this article will use the division by zero error Error 11 when we want to deliberately raise an error. If you have no error handling code and a run time error occurs, VBA will display its standard run time error dialog box. While this may be acceptable, even desirable, in a development environment, it is not acceptable in a production environment.
The goal of well-designed error handling code is to anticipate potential errors, and correct them at run time or to terminate code execution in a controlled, graceful method. Your goal should be to prevent unhandled errors from arising. This statement instructs VBA what to do when an run time error is encountered.
The On Error statement takes three forms. This indicates that when a run time error occurs VBA should display its standard run time error message box, allowing you to enter the code in debug mode or to terminate the VBA program. When On Error Goto 0 is in effect, it is the same as having no enabled error handler. It instructs to VBA to essentially ignore the error and resume execution on the next line of code. It is very important to remember that On Error Resume Next does not in any way “fix” the error.
It simply instructs VBA to continue as if no error occurred. However, the error may have side effects, such as uninitialized variables or objects set to Nothing. You do this by testing the value of Err. Number and if it is not zero execute appropriate code. This statement tests the value of Err. Number and assigns some other number to N.
Whenever an error occurs, code execution immediately goes to the line following the line label. None of the code between the error and the label is executed, including any loop control statements. Only one error handler is enabled at any given time, and VBA will behave according to the enabled error handler. This code should be designed either to fix the problem and resume execution in the main code block or to terminate execution of the procedure.
The error hander is still active when the second error occurs, and therefore the second error is not trapped by the On Error statement. You can use Resume only in an error handling block; any other use will cause an error. Moreover, Resume is the only way, aside from exiting the procedure, to get out of an error handling block. Do not use the Goto statement to direct code execution out of an error handling block. Doing so will cause strange problems with the error handlers.
In this case you must ensure that your error handling block fixed the problem that caused the initial error. Otherwise, your code will enter an endless loop, jumping between the line of code that caused the error and the error handling block. The following code attempts to activate a worksheet that does not exist. This causes an error 9 – Subscript Out Of Range , and the code jumps to the error handling block which creates the sheet, correcting the problem, and resumes execution at the line of code that caused the error.
This causes code execution to resume at the line immediately following the line which caused the error. The following code causes an error 11 – Division By Zero when attempting to set the value of N. The error handling block assigns 1 to the variable N, and then causes execution to resume at the statement after the statement that caused the error.
This causes code execution to resume at a line label. This allows you to skip a section of code if an error occurs. If the code causing the error is in a procedure with an On Error statement, error handling is as described in the above section.
However, if the procedure in which the error occurs does not have an error handler, VBA looks backwards through the procedure calls which lead to the erroneous code. For example if procedure A calls B and B calls C, and A is the only procedure with an error handler, if an error occurs in procedure C, code execution is immediately transferred to the error handler in procedure A, skipping the remaining code in B. This is very bad coding practice. Remember that using On Error Resume Next does not fix errors.
It merely ignores them. However you tend to only use certain objects on a regular basis and the following diagram shows the relationship between the most commonly used objects. Object references: Cells, Sheets and Workbooks The macro recorder will show you what your object references are but it will not show you the variety of different expressions that can be used to access common Excel objects.
Press Enter when done. This tool defines where and if horizontal, vertical, and depth axis titles will be displayed. Depth axis titles are only available within 3D charts.
Use this tool to define whether a legend will be displayed and, if so, where in relation to the chart. A legend simply defines what is being plotted. This tool is used to display or hide data labels. These labels represent the actual values that are being plotted for your chart. Use this tool to add or remove a data table from the chart. The table is placed below the chart and provides a complete list of what is being plotted. These two tools are located within the axes section:. This tool defines whether the horizontal and vertical axes data will be included within the chart.
Use this tool to display or hide the horizontal and vertical gridlines within the chart. The Format Ribbon also appears when the chart is selected. This Ribbon contains tools for formatting the current selection, resetting the current selection to match the originally selected style for the chart, changing the shape and style of the current selection, selecting WordArt styles if text is selected , and adjusting the arrangement and size of the current selection.
This is referred to as relative addressing since the cell addresses of a formula adjust “relative” to their new position. There may be instances, however, when you may not want the formulas to be adjusted according to the cell s they are copied to.
All salaries would need to be multiplied by that one cell. When creating a formula to calculate new salaries, that cell would, then, be referred to as absolute, meaning that no matter how many times you copied the formula down a column or across a row that particular cell address would not change within the formula. By placing dollar signs before the column and again before the row, you are indicating that both the column and row are to be absolute never adjusted when copied.
Below are examples of how the dollar signs can be used depending on which portion of the cell should be absolute and which portion should remain relative.
After creating the workbook and inserting all of the necessary information for the template, follow the steps outlined below to save it as a template for future workbooks to be based on: 1.
Select Save As from the pull-down list of options within the File tab on the Ribbon; The following window will be displayed:. Be sure you have entered a descriptive name for the template before clicking on the Save button. Using a template To use the template that you created, you will follow the usual steps to create a new workbook with one exception.
You will choose your template as the one to base the new workbook on. To create a new workbook based on an existing template, follow the steps outlined below. Select New from the pull-down list of options within the File tab on the Ribbon; The following window will be displayed:. From within this dialog box, select the template to base your new workbook on; 4. Select Workbook as the type of new file to create; 5. Choose OK to actually create the new file based on the currently selected template.
Opening a template If you realize that a template needs to be modified, you can open it as you would any other workbook. Once opened, you will be able to edit the template and then save it again. You can also edit the Blank Workbook template to set any defaults you would like applied to all workbooks created using the default template.
To open a template, follow these steps: 1. Select Open from the pull-down list of options within the File tab on the Ribbon; The following window will be displayed:. Select the template to modify and choose Open; Make the changes youd like to the template and then save it again as you would any other workbook. Appendices 1. Appendix A: Function Keys 2. Appendix B: Movement Keys 3. Appendix C: Shortcut Keys. Format cells so the content type will be currency with two decimals; Format the Net column so its content will be rounded Ex.
Shade the header and the averages section; Create a 3-D column chart based on the sales data from the worksheet using the data for 1st Qtr,2nd Qtr,3rd Qtr,4th Qtr. Open navigation menu. Close suggestions Search Search. User Settings. Skip carousel. Carousel Previous. Carousel Next. What is Scribd? Explore Ebooks. Bestsellers Editors’ Picks All Ebooks.
Explore Audiobooks. Bestsellers Editors’ Picks All audiobooks. Explore Magazines. Editors’ Picks All magazines. Explore Podcasts All podcasts. Difficulty Beginner Intermediate Advanced. Explore Documents. Microsoft Excel – Training Manual Beginners. Uploaded by ripjojon. Did you find this document useful? Is this content inappropriate? Report this Document. Flag for inappropriate content. Download now. Jump to Page. Search inside document. Module 1 1.
Spreadsheet Basics 2. The Excel Screen 3. Movement Keys 4. Accessing Help 1. Select Microsoft Excel from the sub-menu. Closing a File 4. From within the section labeled Charts, select a chart category column, line, pie, bar, area or scatter ; 4. These two tools are located within the axes section: This tool defines whether the horizontal and vertical axes data will be included within the chart.
Module 7 1. Absolute cell addressing 2. Working with templates 1. Select Save As from the pull-down list of options within the File tab on the Ribbon; The following window will be displayed: 2. Select New from the pull-down list of options within the File tab on the Ribbon; The following window will be displayed: 2.
Appendix C: Shortcut Keys 1. You might also like ms excel Microsoft Access Excel Macros Handout – Prospects. Excel Tutorial Notes. Advanced Microsoft Excel Excel Tutorial. Microsoft Excel – a jargon free guide. Spreadsheets Crash Course. SharePoint Administrator. Excel Macros VBA Quantitative Analysis With Excel. Shirley Priyanka Lee CV. Excel Introduction and Referencing. Excel Foundation Skills Lesson01 Autosum. Excel Training. Microsoft Excel Advance Formulas.
Learn Excel. ExcelWays Excel Course. Record Excel Macros That Work. Excel Formula Tips and Tricks. Excel Intermediate. JCM Brochure. Direct Reprogramming of Lymphocites B. Jose Silva. Survivor Guide to Bone Marrow Transplat. StemCell – NCI. Astronomybasics Con Celestron. Visible Body Controls. Openerp Book. Openerp 61 Accounting. Real TimeOS Updated.
Services Marketing-Mix. Jobsheet P2 – Antarmuka Port Serial. Order Form.
Manual Microsoft Office Excel – PDF Drive – VIP classifieds
End If Terminates the If Else block. Sub test4 If Cells 4, 5. You can execute various statements for each condition each based on the variable. Use one of two loop structures depending on the number of iterations that are required. Fixed iterationThis runs a set of statements for a predetermined number of times.
Example For… next loop Indefinite Iterationthis runs a set of statement until a defined condition is met. Next statement allows you to repeat a set of actions on the individual elements of a collection or array. For example, if you select a range of cells, then this command allows you to repeat a set of commands on each cell in the range.
Another examplewould be to repeat a set of actions on the worksheets in a workbook. The following is the typical syntax for the For Each.. Value Next End Sub Example 2 In this exercise if a cell has a number in the range A1:A50 the procedure will Double the cell value, if it contains a number,otherwise clear the cell.
Now we introduce you to Conditional LoopsRepetition while a certain condition is satisfied or until a certain condition is satisfied. Check for the condition before running the loop: Do Whilecondition Statements Loop Execute the commands once before checking the condition: Do Statements Loop Whilecondition Use the keywords Until or While to define the condition, placing them either at the top orat the end of the Do…Loop.
Loop using Exit Do. Save your file before testing the code. It is very easy to get stuck in a conditional loop. You must try to terminate the procedure if you are stuck. The programming of User Forms can be time-consuming as every action that the User Form performs has to be coded, the OK button does not do anything until you write the code contained in its click event.
You need to be familiar with User Form objects, there is no macro recorder. Naming objects It is awkward having to use the default object names when you are completing the event procedures for each control; is the OK button CommandButton1 or is it CommandButton2? Follow the published standard conventions for Control names, add the three-character lower case prefix to your names and you will never have any problems identifying your control objects in code.
Switch from the excel view to the visual basic editor, from the insert menu select user form. The screen will now look as shown below. If the toolbox is not displayed from the menu bar select view toolbox. Now we are going to add a List Box click on the icon add the list box below employee name input box. To show as below. Next change the name property to lstempdepartment, locate RowSourceenter the following details E4:E10, the list Box will be populated from this data Range.
Next add one more label rename the caption to Earnings, add a text box change the name property to txtempearnings. Now we shall finish it off navigate to the properties of the form in the caption box type the following. Employee Information now for the commandButton1 in the properties change the caption to Enter Data change the name property to CmdAdd now for the commandButton2 in the properties change the caption to Close change the name property to CmdClose your form should now look like the one below.
We have now created our user form click the save button to update and save your work In the next section we will look at events and how to transfer data from the user form to the excel data sheet. Procedures are executed when an event is triggered.
User forms and their controls respond to the events that are associated with them. Below is a list of events and their triggers. Value Cells 12, 2. Value Cells 12, 3. Offset nextrow, 0. Value Range “a1”. Offset nextrow, 1. Value If optMale. Offset nextrow, 2. Offset nextrow, 3. If so, you may want to use a series of dialogs like a Wizard , or use a Multipage control.
The user should be able to tab through the dialog box and access the controls sequentially. It’s important to remember that an add-in will never be the active workbook. Excel’s spelling checker doesn’t work with UserForms.
And does it explain all the controls? This can be because a mistake in the code, called a bug or if a statement in the code tries to call an invalid operation. Examples of this can be a misspelt variable that will cause an error. When an error occurs VBA will either display an error message or refer you to the help file. VBA provides you with a set of tools for debugging your code these include, toggle or break points, and the watch window.
Error Types There are three types of programming error, compile time, run time and logical. The following table describes the type of error. Error Description Compile-error A compile-time error happens when the program is being compiled. Generally, compile-time errors are syntax errors; and they are caught by the compiler. Run-time error Run-time errors occur at at run-time; generally, the program compiles but does not run correctly. An example of a compile-time error might be leaving out a semi-colon in C.
An attempt to read past dereference a null pointer in C would be an example of a run-time error. Logical a logic error is a bug in a program that causes it to operate incorrectly, but not to terminate abnormally or crash. A logic error produces unintended or undesired output or other behaviour, although it may not immediately be recognized as such.
Logic errors occur in both compiled and interpreted languages. Unlike a program with a syntax error, a program with a logic error is a valid program in the language, though it does not behave as intended. The only clue to the existence of logic errors is the production of wrong solutions.
VBA provides the following tools to help you debug your code these are located on the debug toolbar. Debugging tools The following table describes the tools available to you in VBA to view values of variables and expressions and trace the execution of a program. You can insert a breakpoint in the first line of code segment that you suspect to be the cause of the error.
At this point in time the line of code causing the problem is displayed and highlighted in yellow, this helps you trace and debug the error. You can set your own breakpoint in the code to pause at a specific place, when the executed code reaches the breakpoint VBA switches into break mode.
Breakpoints are temporary markers and are not saved along with the code. To insert or remove a breakpoint place the insertion point in the code you want to insert the break point and use one of the following methods.
You will see that the calculation has now returned the correct value, so using a break point we can identify the problem area, in the next section we will look at the watch window. In cases such as this you need to monitor the behaviour the expressions and variables of the entire procedure.
Each expression or variable you monitor is known as a watch, watch expressions can be defined in either break mode or design time. VBA automatically monitors and displays the expressions in the watch window. The watch window is displayed automatically when you enter break mode or you choose to open the watch window manually by clicking the watch window button on the debug toolbar.
The watch window can be used to change values of variables and expressions this allows you to observe how these changes to affect the code. Running code line by line examining the code line by line is known as steeping through, you can step through the code to determine which statement causes the error. There are three ways to step through the code. This allows you to observe the effect of each statement on variables.
To step into the code either choose debug step into or press F8 or click step into button on the debug toolbar. If the current procedure is a called procedure the remaining code in the procedure is executed and the debugging stops at the next statement in the calling procedure. To step out of the code, choose debug, step out. The watch window before running the procedure commission The watch window after running the procedure commision Now we understand what stepping through procedure and how it works we shall now put that into practice.
You can use the print method or use a? To view , immediate window click the immediate window on the debug window. Immediate window with example of print method and? Working with the intermediate window Launch a new work book and type in the information below save as employee information, name the worksheet Employeeinfo. Start to examine these objects by writing a short routine to change the value of a variable.
You will see the entries in the Locals Window change as each line is executed with the contents and type of each variable displayed in turn. Errors in general come in three types: compiler errors such as undeclared variables that prevent your code from compiling; user data entry error such as a user entering a negative value where only a positive number is acceptable; Run time errors, that occur when VBA cannot correctly execute a program statement.
We will concern ourselves here only with run time errors. Typical run time errors include attempting to access a non-existent worksheet or workbook, or attempting to divide by zero. The example code in this article will use the division by zero error Error 11 when we want to deliberately raise an error. If you have no error handling code and a run time error occurs, VBA will display its standard run time error dialog box. While this may be acceptable, even desirable, in a development environment, it is not acceptable in a production environment.
The goal of well-designed error handling code is to anticipate potential errors, and correct them at run time or to terminate code execution in a controlled, graceful method. Your goal should be to prevent unhandled errors from arising. This statement instructs VBA what to do when an run time error is encountered. The On Error statement takes three forms.
This indicates that when a run time error occurs VBA should display its standard run time error message box, allowing you to enter the code in debug mode or to terminate the VBA program. When On Error Goto 0 is in effect, it is the same as having no enabled error handler. It instructs to VBA to essentially ignore the error and resume execution on the next line of code.
It is very important to remember that On Error Resume Next does not in any way “fix” the error. It simply instructs VBA to continue as if no error occurred. However, the error may have side effects, such as uninitialized variables or objects set to Nothing. You do this by testing the value of Err. Number and if it is not zero execute appropriate code. This statement tests the value of Err. Number and assigns some other number to N. Whenever an error occurs, code execution immediately goes to the line following the line label.
None of the code between the error and the label is executed, including any loop control statements. Only one error handler is enabled at any given time, and VBA will behave according to the enabled error handler.
This code should be designed either to fix the problem and resume execution in the main code block or to terminate execution of the procedure. The error hander is still active when the second error occurs, and therefore the second error is not trapped by the On Error statement. You can use Resume only in an error handling block; any other use will cause an error. Moreover, Resume is the only way, aside from exiting the procedure, to get out of an error handling block.
Do not use the Goto statement to direct code execution out of an error handling block. Doing so will cause strange problems with the error handlers. In this case you must ensure that your error handling block fixed the problem that caused the initial error. Otherwise, your code will enter an endless loop, jumping between the line of code that caused the error and the error handling block.
The following code attempts to activate a worksheet that does not exist. This causes an error 9 – Subscript Out Of Range , and the code jumps to the error handling block which creates the sheet, correcting the problem, and resumes execution at the line of code that caused the error. This causes code execution to resume at the line immediately following the line which caused the error. The following code causes an error 11 – Division By Zero when attempting to set the value of N. The error handling block assigns 1 to the variable N, and then causes execution to resume at the statement after the statement that caused the error.
This causes code execution to resume at a line label. This allows you to skip a section of code if an error occurs. If the code causing the error is in a procedure with an On Error statement, error handling is as described in the above section.
However, if the procedure in which the error occurs does not have an error handler, VBA looks backwards through the procedure calls which lead to the erroneous code. For example if procedure A calls B and B calls C, and A is the only procedure with an error handler, if an error occurs in procedure C, code execution is immediately transferred to the error handler in procedure A, skipping the remaining code in B.
This is very bad coding practice. Remember that using On Error Resume Next does not fix errors. It merely ignores them. However you tend to only use certain objects on a regular basis and the following diagram shows the relationship between the most commonly used objects. Object references: Cells, Sheets and Workbooks The macro recorder will show you what your object references are but it will not show you the variety of different expressions that can be used to access common Excel objects.
If you are typing-in cell references rather than recording, it is easier to use the shortcut notation using square brackets, [A1]. You can use the same style of referencing on other objects as well, such as worksheets but there are a number of rules and restrictions.
It is usually best to restrict the square bracket notation to cell references only, where it is entirely definitive and reliable. With…End With The With statement is used so the object reference can be made and then retained so that multiple actions may be carried out without having to repeat the same object reference in each statement. You can keep the With reference open for as long as you like in the same procedure, just pointing to it using the dot operator. Every With requires an End With.
When you are reading code that uses multiple With pointers, the rule is simple; the dot points to the nearest With. With Object. Property With. Child Object. Deleting rows is one of many things that can become painfully slow. This can be overcome very easily by switching Excel into manual calculation before your code runs.
Just be aware that if your code bugs out, and you have no error trap, the Workbook will be left in manual calculation and NO properly designed spread sheet should ever be used with calculation in manual. If you ever get advice to switch Excel into manual calculation to prevent slow saving, closing and data entry This is a band aid approach rather than addressing the underlying cause.
If you run any Excel Workbook in manual calculation it’s only a matter of time before non-calculated data is read off! The code below shown how we can switch calculation into manual, run some code, then put it back how it was.
Calculation Application. Range “A1:A”. Copy Sheet2. Range “B1”. Range “B1:B”. Value ‘Or, if only formulae are needed: Sheet2. Copy Sheet1. PasteSpecial xlPasteValues Application. Only copies visible data. You can always use A Cell to ‘point to this cell, no matter where you are in the workbook. Note that by using ACell. ListObject, you ‘don’t need to know the name of the table to work with it.
With Application. Excel only supports ‘a maximum of 8, non-contiguous cells through VBA macros and manual. ListColumns 1. SpecialCells xlCellTypeVisible. Areas 1. Range “A1”. PasteSpecial xlPasteColumnWidths.
Select Application. PasteSpecial xlPasteFormats Application. Range “A1” With Application. You can always use ‘ACell now to point to this cell, no matter where you are in the workbook. GoTo Sheets “Yoursheetname”. The rows are numbered 1 through The intersection between a column and a row is referred to as a cell. A cell is similar to a box that can be used to store pieces of information. Each piece of information could be a word or group of words, a number or a mathematical formula.
Each cell has its own address. This address is used in formulas for referencing different parts of the worksheet. The address of a cell is defined by the letter of the column it is located in and the number of the row. For example, the address of a cell in column B, row 5 would be referred to as B5.
The column is always listed first followed by the row without any spaces between the two. These cell addresses are useful when entering formulas. Instead of typing actual values in your equations, you simply type the cell address that the value is stored in.
Then, if you need to go back and change one of the values the spreadsheet automatically updates the answer based on the new number s. The number 67 is stored in cell C5 and the number 5.
If these numbers change next month or next year, the formula remains correct as it references the cells – not the actual values. With the second formula, you can change the numbers stored in cells C5 and D5 as often as required and see the result recalculate immediately. Click on Windows Start button located along the far left side of the taskbar to access the Windows Start menu; 2.
Select All Programs from the pop-up menu; 3. Select Microsoft Office folder from the sub menu see picture below :. The Excel Screen The Excel user experience After you start the program, you are taken into a blank untitled workbook where you may begin entering your data.
You will notice that the program window includes many of the standard elements common to most Office applications as well as a few items that are unique to Excel. The screen can be quite intimidating the first time you see it as there are so many items displayed.
However, if you take a few minutes to familiarize yourself with the various screen elements, the program will become easier to work with. The Excel ribbon The very first time you launch Excel , its Ribbon contains the following seven tabs, proceeding from left to right: Home: Use this tab when creating, formatting, and editing a spreadsheet.
Insert: Use this when adding particular elements including graphics, PivotTables, charts, hyperlinks, and headers and footers to a spreadsheet. Page Layout: Use this tab when preparing a spreadsheet for printing or reordering graphics on the sheet. Formulas: Use this tab when adding formulas and functions to a spreadsheet or checking a worksheet for formula errors.
Data: Use this tab when importing, querying, outlining, and subtotaling the data placed into a worksheets data list. Note that this tab also contains an Analysis group if you activate add-ins, such as the Analysis Toolpak and Solver Add-In.
Review: Use this tab when proofing, protecting, and marking up a spreadsheet for review by others. This tab is arranged into the Proofing, Language, Comments, and Changes groups. Note that this tab also contains an Ink group with a sole Start Inking button if youre running Office on a Tablet PC or on a computer equipped with some sort of electronic input tablet.
View: Use this tab when changing the display of the Worksheet area and the data it contains. Module 2 1. Deleting Information 3. Adjusting Column Widths 4.
Creating Formulas 5. Saving a Workbook 7. Changing Page Setup 9. Closing a File. When creating formulas, you may use actual values, cell addresses or a combination of the two. The steps for creating formulas are: 1. This also ensures that formulas beginning with a cell address are not mistaken for text; 2.
Enter the actual formula for Excel to calculate. When a formula is complete and the ENTER key has been pressed, the result will be displayed in the current cell. The formula itself is displayed in the formula bar located in the upper-left of the screen next to the cell address.
Module 3 1. Opening an existing Workbook 2. Moving Data copy, paste, cut 3. Alignment 4. Formatting Values 5. Select the cell s to be moved; 2. Place the mouse pointer on one of the outer borders of the selected block. The pointer should change to a four-way arrow; 3. Begin dragging the block. An outline of the cells moves with you. Notice as you drag that Excel displays the current cell address so you are sure where you are dragging; 4. When you reach the correct location, release the mouse button.
The selection should have now been moved to the new location. Select the cell s to be copied; 2. Place the mouse pointer on one of the outer borders of the selected block; 3. Hold the ctrl key down as you drag the block. When you reach the desired location, release the mouse button. The selected block should now have been copied to the new location. Formats the current selection for currency with a dollar sign, a comma as a thousand separator and 2 decimal places.
If you click on the down arrow beside this tool, you can select which countrys currency symbol to use. Example: 4, Increases the number of decimal places displayed. Each time this button is selected another decimal place is added to the selection. Decreases the number of decimal places displayed. Each time this button is selected another decimal place is removed from the selection. Click on the down arrow beside this tool located within the Number section of the Home Ribbon to choose from a list of formats.
Module 4 1. Working with Function Arguments General mathematical functions are provided with Excel to carry out calculations on data within the spreadsheet and can take the place of certain types of formulas. However, Excel provides a mathematical function which is used primarily to add blocks of numbers. Module 5 1. Applying Attributes 2. Working with Fonts 3. Borders and Colors 4. Adding Cell Shading 5. Using Cell Styles.
Module 6 1. Creating and editing charts Creating a chart To quickly create a chart, follow these steps: 1. Select the data to be included within the chart; 2.
Switch to the Insert Ribbon; 3. From within the section labeled Charts, select a chart category column, line, pie, bar, area or scatter ;. A pull-down list of available chart types will be displayed. This list will display only those charts within the selected category. Select the exact type from this list. Excel will create a chart based on the data in the cells you selected and the type of chart you have chosen.
Editing the chart Once the chart has been added to your worksheet, you can edit it using the new tabs across the top of your screen. The Design Ribbon is used to change the type of chart, save it as a template to base other charts on, modify the data being plotted, change the chart layout or style, and move the chart to another worksheet.
The following tools are all located within the Labels section on the Layout Ribbon:. This tool defines where and if a chart title will be included. To enter the text for the chart tile, select the title and begin typing. Press Enter when done. This tool defines where and if horizontal, vertical, and depth axis titles will be displayed.
Depth axis titles are only available within 3D charts. Use this tool to define whether a legend will be displayed and, if so, where in relation to the chart. A legend simply defines what is being plotted.
This tool is used to display or hide data labels. These labels represent the actual values that are being plotted for your chart. Use this tool to add or remove a data table from the chart.
The table is placed below the chart and provides a complete list of what is being plotted. These two tools are located within the axes section:. This tool defines whether the horizontal and vertical axes data will be included within the chart. Use this tool to display or hide the horizontal and vertical gridlines within the chart.
The Format Ribbon also appears when the chart is selected. This Ribbon contains tools for formatting the current selection, resetting the current selection to match the originally selected style for the chart, changing the shape and style of the current selection, selecting WordArt styles if text is selected , and adjusting the arrangement and size of the current selection. This is referred to as relative addressing since the cell addresses of a formula adjust “relative” to their new position.
There may be instances, however, when you may not want the formulas to be adjusted according to the cell s they are copied to. All salaries would need to be multiplied by that one cell. When creating a formula to calculate new salaries, that cell would, then, be referred to as absolute, meaning that no matter how many times you copied the formula down a column or across a row that particular cell address would not change within the formula.
By placing dollar signs before the column and again before the row, you are indicating that both the column and row are to be absolute never adjusted when copied.
Below are examples of how the dollar signs can be used depending on which portion of the cell should be absolute and which portion should remain relative. After creating the workbook and inserting all of the necessary information for the template, follow the steps outlined below to save it as a template for future workbooks to be based on: 1. Select Save As from the pull-down list of options within the File tab on the Ribbon; The following window will be displayed:.
