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.

Google Online Preview   Download