Red, Green, Blue Interior Cell Colour

• January 24, 2016
• Marcus Small

Recently I was giving a half day course on heat maps and came up with the novel idea of creating a custom function which would identify the primary colour scheme for a cells interior colour.  It is in an effort to save a little time in the creation of a colour scheme for heat mapping.  Rather than laying down the colour and looking up the Red, Green and Blue numerical combination I simply lay the colour down and the custom function does the work for me.

Filter Source Data in Excel with Slicer

• September 10, 2015
• Marcus Small

I was recently answering a post on Ozgrid about filtering a list using a slicer.  The post was very similar to a blog post of mine with a slight twist.  The poster wanted the original list filtered based on the selection of the slicer and if no slicer item was selected then the filter was to be taken off the dataset.  I found the problem interesting because in order to solve the problem you had to know how many slicer items were in the list in the first place.

Excel VBA Colour Function

• September 8, 2015
• Marcus Small

This is an alternative to conditional formatting.  The following will detect the colours Red, Yellow and Green in a particular cell.  It will add the name of the colour – good for those colour blind individuals.  It is a custom function so needs to used in conjunction with a formula or in a VBA procedure.

VBA Open File Dialog

• August 22, 2015
• Marcus Small

During a procedure you may wish your user to choose a file to open.  This can slow the process of running code down but if you have a moving target this will be essential in getting the right data imported or manipulated via VBA.  I have not had to do this very often but a client asked if they could choose a file mid procedure and the following is what I came up with.

Copy Multiple Sheets with VBA

• July 5, 2015
• Marcus Small

I was asked during a webinar recently how you send multiple worksheets to a new workbook in a batch.  I was pretty sure this information would be on my site but a quick search of my site did not reveal any joy.

I put together a simple file which sends an output sheet and two source data sheets to a directory saves it then starts the process again after changing a unique identifier.  The process is a little more in-depth than sending just one sheet but there is not a great deal more code involved.

Incident Tracking Dashboard

• April 27, 2015
• Marcus Small

The dashboard tracks incidents by year and by cost. It is only a small dashboard but has stacks of functionality.

Evaluate and Filter Part 2

• April 26, 2015
• Marcus Small

This post is a follow up from the blog post earlier in the month on Filtering without a filter.  It is an alternative to the autofilter method I have outlined extensively.  The post focuses on filtering out more than one criteria and putting the results in a fresh sheet.  To do so we use the evaluate method.  This is relatively complex, however if we break down what needs to change we can significantly simplify the process.

Filter Data in VBA without an Excel Filter

• March 4, 2015
• Marcus Small

In Excel VBA it is possible to apply a filter directly to items contained within an array.  This method, little known, is a way of filtering without the more traditional auto filter.  I have not conducted any tests but as the filter is performed in memory I assume the process happens more quickly than a regular Excel auto filter.  This would make a nice Excel case study.

Split Text in Excel VBA

• January 19, 2015
• Marcus Small

Excel VBA offers a customised way to split out text within a cell.  Larger systems don't always talk to Excel as we would like and it is sometimes necessary to split the contents of a cell into many parts.  

Split (string, delimiter, limit, compare)

• String - the string you wish to split or separate.

• Delimiter - the character which divides the string into segments

• Limit - The default for limit is -1 which means every delimite...

Excel VBA Slicer Selection

• January 15, 2015
• Marcus Small

Slicers are one of the most attractive ways to show summary data. Introduced in Excel 2010, slicers are a way to show a list of data as buttons on a page. This can be used as criteria in formulas or to filter a pivot table or table.