Looking for:
[PDF] Microsoft Office free tutorial for Beginners |
- (PDF) MS Office MS Excel VBA Manual | Hendro Hermanto -
But also many other tutorials are accessible just as easily! You should come see our Office documents. You will find your happiness without trouble!
The latest news and especially the best tutorials on your favorite topics, that is why Computer PDF is number 1 for courses and tutorials for download in pdf files - Microsoft Office Tutorial. Download other tutorials for advice on Microsoft Office Tutorial. We will do everything to help you! And you dear surfers what you need? The best course and tutorial, and how to learn and use Microsoft Office Tutorial. Spreadsheet programs were first developed for accountants but have now been adopted by anyone wanting to prepare a budget, forecast sales data, create profit and loss statements, compare financial alternatives, and any other mathematical applications requiring calculations.
The electronic spreadsheet is laid out similar to the paper ledger sheet in that it is divided into columns and rows. Any task that can be done on paper can be performed on an electronic spreadsheet faster and more accurately. The problem with manual sheets is that if any error is found within the data, all answers must be erased and recalculated manually.
With the computer, formulas can be written that are automatically updated whenever the data is changed. By having formulas that automatically recalculate, you can play with the numbers to see how the final result is affected.
The horizontal bar across the top of the worksheet is filled with letters, beginning with A and ending with the letter XFD. 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. The purpose of this course is to give you the fundamental tools to start down the path of VBA programming in Microsoft Excel and to encourage you to further your knowledge beyond basic functionality; maybe you will use what you learn as the basis to explore VBA in other applications.
Whatever path you decide to take the basic VBA for excel will give you the tools at your fingertips to explore further.
VBA controls Microsoft Excel by means of macros which are also called procedures. VBA is a subset of VB which runs inside one of the office applications. As a result VBA inherits the current Office object library and application instance by default and any references that are included. However in VB you have to create the application instances if you need to manipulate one or more of the Office application objects VBA Terminology Before you start coding in VBA you need to be familiar with some key terms associated with it.
The following table describes some of those terms. Object VBA object is something like a tool or a thing that has certain functions and properties, and can contain data. For example, an Excel Worksheet is an object, cell in a worksheet is an object, range of cells is an object, font of a cell is an object, a command button is an object, and a text box is an object and more.
Property Each VBA object has its own properties that control its appearance. When we talk about range as an object typical properties are:- Column Width, Row Height, Font, Text, Value, Formula, Borders Method While most objects only provide characteristics to describe them, other objects can perform actions. For example, a house can be used to protect people when it is raining outside.
In computer programming, an action that an object can perform is referred to as method. Procedure A procedure is a section of code created to carry an assignment, separate from a spread sheet, whose action can be used to complement a spread sheet. You cre6ate the procedure by writing code. Comment A line of text within a procedure, that you can use to describe each line of code or the entire procedure.
To comment a line out place an apostrophe at the beginning of the line. The comment will turn green. Module Is a file that you can write and edit blocks of code and other VBA code. Collections and container Objects. Collections are a set of related objects having the same properties. Container objects are used to store and manipulate collections of data objects, allowing access to individual members of the collection in a simple and consistent manner container objects may or may not be related to each other.
To launch the VBE editor from Excel you can do one of the following Click on the visual basic icon on the developer tab. The developer tab is not shown by default and you have to select it from: - File — options — Customise Ribbon. As an alternative use the following keystroke. A project is a collection of modules the name of the project is the same that of the corresponding work book. Each work sheet can also contain Modules. The Properties Window This lists the properties of the selected object, you can change the properties for an object in this window.
The Code Window As it suggests this is the place when you open a module you can create or edit the code view and compile the VBA code. Object Programming What is object programming? In excel objects have certain properties, methods, and events.
Properties control the appearance and other attributes of an object. Methods are built in procedures that you use to perform specific actions on an object. Events are actions such as mouse click double click or open and close a workbook. Most objects in VBA have events associated with them. For example a worksheet object has an event called activate. The Properties Window From the properties control panel you can change the behaviour and appearance of an object by modifying the properties in that panel.
The name of the selected object appears in the object list pane, there are two tabs alphabetic and categorized. The alphabetic tab displays the property names alphabetically and categorized tab displays the properties grouped on the basis of the tasks they perform.
To open a code window you double click the object or module in the project explorer. The code window contains two lists, the object list and the procedure list. The object list displays all objects associated with the current module. The procedures list displays all the procedures in the current module or all the events of the object selected in the object list, the definition of the objects default procedure appears in the code window, for example if you select the object worksheet from the object list the following code appears in the window.
This event occurs when the user selects a cell in the worksheet. Code written within this procedure will execute every time this event occurs. For example if you write code within this event procedure to display a specific message that message will appear every time you select a different cell. Type in the following to give it a try: MsgBox "you selected another cell" The general syntax for changing object properties through code is Object. Some methods need a value as input to complete their actions.
For example, the open method of the workbook object takes a file name as input so it knows specifically what workbook to open. The input value is called an argument. An argument is a variable, constant or expression that provides additional information to a method, so that it can execute properly. To use a method in VBA code, you would use the following syntax. You can do this by associating the code with an event object. This association is created using an event procedure. An event procedure is a code that is executed when an event occurs.
For example, you can write code for the activate event of a worksheet to display a message indicating that you cannot change the data in the worksheet this procedure will activate when the user activates the worksheet. To program an event, double click the object to display a code window.
Select an event from the procedure list and enter the code, the code will run automatically when a user triggers the event for that procedure. You cannot edit the data in the list. From the ribbon bar navigate to the developers tab, from the controls group select insert then click on the button icon. Draw the button to the size required. Next the macros dialog box opens, in the macro name box type in calculate, next click on new. Insert the code below in the code window.
Sub Calculate ActiveCell. Later on we will look at the looping stamen to automatically add up all values in the columns. The object Browser As already discussed the object browser is a window that displays the classes, properties, methods events, and constants in the various object library, below shows the result of the excel library being chosen, searching for the activate method that shows the classes objects available.
Below shows the activate event for the worksheet object. Unit summary In this unit you have learnt that VBA is a programming language been introduced to the Visual basic Editor, Terminology use in VBA and gained an understanding of how objects, properties, methods and events play a role in Object-orientated programming. You have learnt how to modify properties and use methods by using the code window, you also learned how to associate code to an event, and finally in this section you added a button to execute some VBA code.
In the last part of this section we looked at how the object browser worked, familiarised ourselves with the interface and icon sets and performed a search to find out information about properties and methods. When inputting data the program uses a temporary storage space called a variable, the variable consists of a name and data type.
The name is used to identify itself in the program and the data type indicates the type of data to be stored. Currency 8 bytes ,,,, For example in a procedure that calculates a commission value, you would create a variable to contain that value. The process of defining a variable and its data type is called declaration; there are two types of declaration in VBA either implicitly or explicitly.
Implicit Declaration You can use a variable without declaring it. This is called implicit declaration, consider the following code. As you can see the display box is empty, this is because of a spelling mistake, and the spelling mistake for the variable is not recognised because we have chosen to run implicitly. So from this small demonstration we can identify two basic flaws in using implicit declarations.
Prone to errors and spelling mistakes causing the code to fail when executed. By default the variant data type is assigned whilst declaring it as an integer would be far more efficient.
To make sure this action takes place type Option Explicit at the top of the code window as shown below. With your cursor inside the code press F5 to run the code as you can see from the dialog box we get a compile error complaining the variable is not found. To make this code run we have to declare the variables add the following code line to the sub routine.
Dim answer As Integer the code will now run to display the correct result. You can declare more than one variable at a time, for example Employee surname and salary amount as. When you use an expression in code it returns a value in one of the data types provided by VBA. Use the syntax as follows cells row, column operates similar to the index function the main difference between the cell object function is unlike the excel functions that call column first then row, the cell object calls the row first then the column.
The code below pastes into cell D2 Sub Push Cells 2, 4. Value MsgBox myvar End Sub The two examples above show how the push code places code into a cell and that the pull function extracts data from a cell. User interaction functions These functions help to accept user input or display output the two most common user interface functions are the inputbox and the msgbox You can use the input box function to get data from the user, use the following syntax.
Message boxes display exactly that messages text or numbers these can be code written or from variables as shown below. Try typing in the code below comment out each line to describe what it does.
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.
No comments:
Post a Comment