Visual Basic for Applications – MS Access
Technical Paper: MICROSOFT EXCEL
Author: Samuel Lee Deering
Target Audience: Beginner to Intermediate
Introduction: This document details visually how to use Microsoft Excel 2003/2007 and contains tips and tricks that I have picked up over years of using the software package everyday.
Data Manipulation 2
Raw Data 2
Data Types 2
Data Cleansing 3
Sorting Data 3
AutoFilter 4
Total, Average, Count, Min, Max 5
Advanced Filters 6
Pivot Tables 8
Introduction to Macros and VBA 22
Macros 22
Visual Basic for Applications (VBA) 25
Tables and Charts 26
Tables
Borders 26
Merging Cells 27
Cell Shading 28
Auto Format 28
Charts (graphs) 29
Creating a Chart 29
Saving a Chart as a Template 32
Customising Charts 34
Changing the Data in a Chart 37
Excel Formulas 38
Basic Formulas 39
Advanced Formulas 41
VLOOKUP 41
Copying Formulas between Cells 43
Referencing Relative and Absolute Cells 44
Printing Tips 45
Other Tips and Tricks 46
Colour Coded Worksheets 46
Freezing Columns/Rows 47
Password Protecting 47
Creating Automated Text 48
Text to Columns 49
Conditional Formatting 50
Cut, Copy & Paste 53
Format Painter Tool 53
Paste Special 54
Creating a Drop-Down List 55
How to Avoid Percentage Rounding Errors 56
Add a Command Button to a Macro 56
Copying To and From Microsoft Word 57
Show All Data by using Auto Fit 57
Adding Comments to Cells 58
Appendix 58
Advanced VBA Example: Converting Position Titles to SAP Abbreviations 58
Data Manipulation
Raw Data
It is a fact that information is only as accurate as the data it promotes, therefore it is best to keep the raw data almost 100% of the time. This provides you with means of checking the validity of the information provided. Raw data is data at its purest and hasn’t been processed (ie - remains untouched!) From this raw data you can produce anything you wish, without it you are left wondering if someone has changed it (ie - by applying a filter or removing org units).
Data Types
To change the data type of a cell (or region):
1. Right click > Format Cells…
2. Select the category and type for the data type (ie – catergory Date and type dd/mm/yyyy)
[pic]
Cells with Numbers
There are shortcuts on the toolbar that may help you save time:
• For decimal numbers use the rounding buttons
• For percentages use the percent button
[pic]
Cells with Dates
Sometimes when copying dates they appear as numbers (ie - 40179). The reason for this is at copy time the cell has been set to display data as a number and not a date. To correct simply change the data type to Date (explained above).
Cells with Text
Cells that contain text have different behaviour to those that contain numbers:
They automatically overlap empty adjacent cells to the right (the text belongs to cell in the J column below).
[pic]
If the adjacent cells to the right contain text it doesn’t overlap and shrinks out of view.
[pic]
There are two ways to correct this:
1. Double click the column divider to “Fit column to size”.
[pic]
OR
2. Format the cell to “Wrap Text”. Rick click > Format Cell… > Alignment (tab) > Wrap Text
It should now appear in the J column as should below:
[pic]
Data Cleansing
A useful method when performing data cleansing is simply “Find and Replace”.
Working example:
1. From the toolbar select Edit > Replace… (or press CTRL + H)
2. Enter the find and replace characters (the default is not case sensitive, if you require this click options)
[pic]
3. Click “Replace All” (if some are not to be changed click just find next and replace one by one).
Sorting Data
1. Highlight the data
[pic]
2. From the toolbar select Data > Sort…
3. Select your sort and order (up to 3 sorts can be applied simultaneously).
[pic]
4. Click OK. The data should now be sorted alphabetically by Service Centre.
[pic]
AutoFilter
The AutoFilter tool is very useful for manipulating the data to find data subsets at the click of a button.
To turn the AutoFilter on:
1. Highlight the data including the header column (if applicable).
Note: the filter can be turned on without highlighting the data and it predicts the data range (usually all the data on the worksheet).
2. From the toolbar select Data > Filter > AutoFilter…(the downward arrows will appear for each heading)
[pic]
Working example: You want to find out what Service Centres have a boiling point of higher than 80.
1. Select the Boiling Point downward arrow
2. Select Custom
[pic]
3. Select is greater than and enter 80 into the value box
Click OK
[pic]
4. The data now only displays those rows that match the criteria. How this works is that the rows that do not match the criteria are hidden not removed so be careful when editing the data and dragging formulas as this is prone to overwrite data that is not in view without you knowing! It is advisable to copy the data from here to another worksheet and then edit it from there especially when you’re dealing with large number of rows.
[pic]
5. You can now sort in order of Boiling Point as follows:
[pic]
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- university of hawaiʻi
- vb script lets you print the file path in excel 2000
- functions in excel
- writing visual basic programs in excel
- programming microsoft windows with visual basic
- basic excel skills depauw university
- so you want to learn some visual basic
- visual basic for applications vba in microsoft excel 97
- visual basic for applications ms access
Related searches
- visual basic codes for excel
- visual basic for excel examples
- visual basic for beginners excel
- excel visual basic programming examples
- visual basic examples for beginners
- microsoft visual basic for excel
- excel visual basic tutorial pdf
- visual basic for beginners pdf
- ms access many to many relationship
- microsoft visual basic tutorial pdf
- visual basic programs with codes
- visual basic book pdf download