Create Number Sequence with VBA Custom Function

An Excel custom function that allows sequental number sequences to be created.

Read More

Excel VBA Number of Printable Pages

When I was younger the hashtag symbol was universally recognised as the symbol for a number.  Now it appears as the opening character in a tweet or other social media post.  I was recently asked to generate a procedure which would add the page numbers which were to be printed to the bottom of an Excel sheet.  The idea was when the file printed the first page had a description which said there will be X number of pages in the current report.  Excel does not currently have a generic report pages generator algorithm so here is a starting point.

Read More

Excel Maximum Cell Length in a Range

Finding the maximum length string in a range of cells can be handy especially if you are using the max length for another purpose.  The following custom function will find the maximum cell length in a range.


Function LongString(rng)

    LongString = Evaluate("Max(len(" & rng.Address & "))")

End Function

The custom function is called as follows:

= LongString(A1:A10)

Read More

Red, Green, Blue Interior Cell Colour

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.

Read More