A good naming convention goes a long way
Here is my naming convention, for VBA declarations, pivot tables, charts and (range) names.
read more...
|
A shortlist of tips on VBA debugging
The forums are littered with questions to do with debug errors. Here is a shortlist of tips on how to debug your code yourself.
read more...
|
A tutorial on ADO for Excel
I don't think there are enough online articles on ADO for Excel, so I decided to write my own!
read more...
|
A tutorial on how to use the XLDS Developer Addin
This tutorial provides screen shots and instructions on how to use the many features available in the XLDS Developer Addin.
read more...
|
A tutorial on Regular Expressions
This article provides and overview of Regular Expressions and a few handy examples.
read more...
|
A tutorial on SQL
There are already various tutorials available online but here is another just in case you are reading a tutorial on ADO or query tables and you don't feel like darting off to a different website.
read more...
|
A tutorial on the XLDS Data Consolidation Addin
This article provides some help on how to consolidate multiple workbooks and/or multiple worksheets using the XLDS Data Consolidation Addin.
read more...
|
A tutorial on the XLDS Waterfall Creator Addin
A step-by-step tutorial on how to build waterfall charts using the XLDS Waterfall Creator Addin.
read more...
|
A tutorial on treeview control
If you need to manage hierarchical data in a userform, consider using treeview control...
read more...
|
Add or count values across multiple sheets but with criteria.
This article describes how to add or count values across a range in multiple sheets based on a set of criteria.
read more...
|
Browse and loop through a directory, its' subfolders and all files within
Another frequent question on the forums. This article describes three different methods to loop through a directory, all or some of its' subfolders and retrieve a list of documents and their properties.
read more...
|
Build dynamic charts using dynamic named ranges
How to build dynamic charts with dynamic named ranges.
read more...
|
CalcFormat
A user defined function that can be used as a worksheet function to aggregate values in a range based on their formats.
read more...
|
CalcNth
A user defined function that can be used as a worksheet function to aggregate every nth value in a range.
read more...
|
Changing the font size in a validation drop down list
Ok, so you can't actually change the size of the font of a validation drop down list, but there is a work around!
read more...
|
Collecting an array of selected items from a multi-extend listbox
I love multi-extend listboxes simply because it allows users to collect a group of values from a larger list of values. But I am frequently asked how to collect the selected values and either store them in an array for later use or to write the selection to a range.
read more...
|
ConcatenateIf
A user defined function that can be used as a worksheet function to concatenate a range of cell values where the cell adjacent meets a given criteria.
read more...
|
Create a document index
VBA code to create a document index for your spreadsheets.
read more...
|
Create a table driven interface for your model
Using tables to draw an interface for your models is not only a quicker but also much easier to maintain!
read more...
|
Create and write data to a text file
This article describes three different methods to create and write to a text file (csv and txt).
read more...
|
Create query tables with MS Query - ideal for chart data
This is very much a general MS Query tutorial, but I especially like using query tables for my charts.
read more...
|
Creating a progress bar
Many users appreciate a progress bar so that they know if they have time to get a coffee while they wait for a macro to finish processing. Use a progress bar!
read more...
|
Creating dynamic named ranges
Various methods to create dynamic named ranges.
read more...
|
Delete rows in a worksheet based on criteria
Here are a few methods on how to delete rows in a worksheet based on criteria, using VBA.
read more...
|
Disable copy and paste
From time to time you may have the need to disable copy and paste. The intention may be to avoid overwriting existing formulas (in which case you should probably rely on worksheet protection), or you may want to prohibit a user from being able to paste over a validation cell (which may in fact overwrite the validation rule).
read more...
|
Entering dates in a userform
This article describes two different methods to capture dates in your userform, Date Picker and Calendar Control.
read more...
|
Filter a range according to date values
Filtering dates can be painful. This article describes how and why!
read more...
|
Filter multiple tables in the same worksheet
We know we can only apply auto filter to a single table in a sheet - here's how to build your own custom MultiFilter!
read more...
|
Handling dates in excel
In order to perform calculations with dates it is important to appreciate how Excel stores them.
read more...
|
How to build conventional charts from pivot tables
I'm an old dog, I don't like pivot charts. If you agree then read this tutorial to see how to build conventional charts from pivot table data.
read more...
|
Locating folders
Here are a few VBA statements to return the directory path to some of the common folders.
read more...
|
Lookup but with more than one lookup value
We know how to lookup a value from a range using a single lookup value, but this non-array formula method describes how to lookup a value from a table with more than one lookup value.
read more...
|
MatchNth
A user defined function that can be used as a worksheet function to find the nth index number of a lookup value in a range.
read more...
|
Network days between two dates with variable week days
A formula method that can be used to calculate the number of days between two dates but with variable week days.
read more...
|
Personalise your default workbook and worksheet
We spend so much time formatting our spreadsheets and we regularly use the same structures, names and styles. Do yourself a favour and personalise your default workbook and worksheet.
read more...
|
Programming form controls
Worksheet level activeX controls can be a big buggy. Seems like form controls is the way to go!
read more...
|
Programming pivot tables
Another subject I think is relatively poorly documented on the web, so I decided to give some pointers!
read more...
|
SetAppSettings and RestoreAppSettings
Use SetAppSettings at the start of you VBA procedure to change application settings to optimise the procedure. Use RestoreAppSettings to restore the application settings to what they were before the procedure was executed.
read more...
|
Sort a table using a custom sort list
In excel we can create custom lists. We can also use these lists to sort tables of data.
read more...
|
SplitText
A user defined function that can be used as a worksheet function to return the nth element of a delimited text string.
read more...
|
Storing distinct values from a table with duplicated data
Various methods on to collect unique values from a range of data. This article demonstrates how to collect unique values using advanced filter, SQL, formulae, arrays, collections and the scripting dictionary.
read more...
|
Storing your favourite macro's and user defined functions
Rather than rewriting your favourite macro's each time you need them, here are a few different options on how to store them ready for use whenever you want.
read more...
|
Sumproduct - add / count a range of values based on multiple criteria.
Similar to a SUMIF, but allows for more than one criteria.
read more...
|
Sumproduct - add or count results from a filtered range
We know we can use SUBTOTAL to add or count the visible results after a filter has been applied, but here is how you can add or count the same but with additional criteria.
read more...
|
Sumproduct - add/count every Nth row or column
Use sumproduct to add or count every nth value in a row or column.
read more...
|
Sumproduct - counting unique values in a range
You have a table of data with various values repeated. This article explains how you can get a count of unique values.
read more...
|
Sumproduct - explained again!
Ask any guru what their favourite function is and (s)he will tell you 'SUMPRODUCT'! This article describes Sumproduct and some of the good ways to put it to use!
read more...
|
Trapping application events
Use classes to trap events, such as workbook_open (any workbook)!
read more...
|
Use styles to improve the look and feel of your spreadsheets
Styles are a great way to make sure you are consistent with the use of formats. But there are many more benefits too!
read more...
|
Write macro's with macro's
A tutorial on how to build macro's with macro's...
read more...
|