Excel resources
This is a collection of Excel and VBA resources that hopefully
you will find useful. This list of resources will grow over time;
please come back to check for new material.
You are welcome to use the samples from this
site, as long as you cite this site as the source. If you want help
with implementing any of the concepts found here, please
contact DataWright to discuss
your requirements.
General tips
-
Setting a reference to a code library
in the VBE -
Three ways to merge
lists in Excel -
Part
1: Use a unique key to identify duplicates -
Part 2:
Create a query to extract the data into another worksheet -
Part 3: Use Advanced Filter to display a unique list of
items -
Selecting cells with the Special Cells dialog -
Speed up your code
by avoiding loops where possible -
Duplicate several sheets at once
Working with the Excel Ribbon in Office 2007
Working with Cells and Ranges in VBA
Reporting techniques
Pivot tables
Using ADO to Integrate Excel and Access
- This tutorial was updated on 6 Oct 2010 to include code
for creating a primary key (see Part 1). - Introduction
- Part 1: Create an Access database and table
- Part 2: Create multiple records in the new Access table
- Part 3: Add a field to the table, and update the existing
records with new data - Part 4: Download the entire table into Excel
- Part 5: Use an Excel validation list to filter records from
Access - Part 6: From Excel, update a single record in the Access
table - Part 7: Delete a field in the Access table
- Note: The ADO recordset statement changes when you refer
to tables and queries, and when you use Excel to write to
Access.
Here
is a collection of recordset statements that illustrate
the differences. - Importing a large
dataset into Excel using ADO
Filtering data using VBA
Using range names in worksheets
- Introduction to
range names - Creating dynamic
ranges - Using relative range
names to reduce calculation errors
Creating a chart that expands to handle new data
Choosing the start point, and the amount of data to plot- Consolidating data
with sheet-level names - Using relative names
- Managing range names
in VBA
