My RSS Feed

A datedif function for excel that calculates correctly

Sat, 15 Apr 2017 08:00:00 +0100

This is one of those little quirks in excel that's annoyed me for a while, there must be some method in their madness but I've yet to come across anybody that thinks it is logically the way forward; so I've written one for you to use.

The way the in-built function works is that it reports back the answer as how many months apart are the two dates so regardless of the day in the month they fall, any date in May vs any date in June the answer will be one.

Can't actually think of any real world use of this as always want to know how many full months are bewteen the two dates. For example 23-June vs 1-July is less than one month away so 0; 23-June vs 27-July would give 1. Contrast this to the excel way and you'd get the answer of 1 for both.

So explanation over we'll give give you the code, head over to the VBA editor (Alt-F11), insert...module then paste the code in:

Function DateDifm(Date1 As Date, Date2 As Date) As Date

' =====================================================================================
' Author:      Martin Childs
' Create date: 2017-03-08
' Description: Returns correct date difference in months for given inputs
' =====================================================================================

    DateDifm = DateDiff("m", Date1 - Day(Date1) + 1, Date2 - Day(Date1) + 1)
    
End Function

When you completed that head back into excel and you can use the formula:

=DateDifm(A1,B1)

Without VBA option

If you don't want to use VBA, heres how you can do it straight in an excel formula, in this example the two dates we are comparing are in A1 and A2:

= DATEDIF( EOMONTH(A1,-1)+1, B1-DAY(A1)+1, "m")

I've slightly re-written it compared to the VBA function above to have less linking within the formula, but you can do it this way also:

= DATEDIF( A1-DAY(A1)+1, B1-DAY(A1)+1, "m")

SQL equivalent

The correct way of calculating it came about when I was performing a critical calculation in SQL and in the back of my mind it was nagging me whether, as they are both from Microsoft, the DATEDIFF() function in T-SQL had the same flaw. My suspisions were correct - both calculate in the same way, but from the same post on StackOverflow I had noticed a very efficient craft bit of code which was so much shorter than everything else and from here went on to adapt it into VBA.

Here the SQL function if you are also interested:

IF OBJECT_ID (N'dbo.DateDiffm', N'FN') IS NOT NULL  
    DROP FUNCTION dbo.DateDiffm;  
GO  

-- ==================================================================================
-- Author:	Martin Childs
-- Create date: 2017-01-23
-- Description:	Returns correct date difference in months for given inputs
-- Version:	v001
-- ==================================================================================

CREATE FUNCTION dbo.DateDiffm( @Date1 as datetime, @Date2 as datetime )  
RETURNS int
AS  
BEGIN

Return (
	datediff(month, dateadd(day,-day(@Date1)+1,@Date1),dateadd(day,-day(@Date1)+1,@Date2))
       )
END;

Create custom Excel function using an array/range input

Thu, 30 Mar 2017 08:00:00 +0100

My new adage of

"You only know what you know how to google"
- Martin Childs, 2016

stood true for this little puzzler, the answer wasn't apparent from simply googling it! All I wanted was to do something to a range input in a custom Excel function, easy you'd think - not in this case. I toiled for a bit of time before conceding that I'd just have to work it out from first principles.

First up it is properly referred to as an array rather than a range - range refers just to the cell reference rather than the contents of the range. To be able to use inputted range you need to determine a variable as an array of some type and then read all the possible cells in the range into it:

Dim MyArray() as Variant
MyArray = InputRange

Now we are rocking, next up is referencing it - LBound & UBound are your friends here, used quite universally in a lot of programming languages - they find the outer extremities of your array and allow you to loop through all the values. The next bit also foxed my for a short time - I could only read in one row with

for i = LBound(MyAr1, 2) To UBound(MyAr1, 2)

But why was it it two - well to get a two dimensional array working you have to loop both the 1 and the 2. Now we just have to hook it all together and loop through all the references and read the values, in this example I just complete a simple sum with the function:

Function mySum(input1 As Range) As Variant
'
' www.childs.be, 2017-02-04
'
    Dim MyAr1() As Variant
    MyAr1 = input1
   
    For j = LBound(MyAr1, 1) To UBound(MyAr1, 1)
    For i = LBound(MyAr1, 2) To UBound(MyAr1, 2)
        If IsNumeric(input1(j,i)) = True Then
                N = N + input1(j, i)
        End If
    Next i
    Next j
   
    mySum = N

End Function

Excel: How to calculate distances between longitude / latitude co-ordinates

Sun, 26 Feb 2017 20:00:00 +0000

Here's a blast from excel past, it was actually one of the more interesting projects I worked on. We were designing food waste recycling plants for the food manufacture and retail industries. The task was to calculate the most efficient solution ofhow many recycling plants to build and where to build them based on the waste arising of each shop and factory vs the cost to transport between the sites.

The finished algorythm wasn't that complicated, and I ran it for one, two, three four and five plant solutions. One of the bits of information that the work was predicated on was the sitings of each shop/factory and although I had the postcode my presumption was that converting them to x and y co-ordinates would be easy. Well, to cut a long story short it wasn't - I quite quickly found a lookup list of postcodes to latitude and longitude and after that it would just be a simple bit of triganometry and pythag right? Wrong!

Turns out converting lat/long to east/north is a complete PITA! Thankfully the guys at Ordnance Survey have the answer in their everything you need to know guide:

A guide to coordinate calculations (mirror)

Did that clear it up for you? Me neither,, the formula is a whopper:

Fortunately they make our lives a little easier by providing an Excel spreadsheet showing us how to calculate it and a whole lot of other stuff, yeah!

Coordinate calculation sheet(XLS) (mirror)

It's a little detailed so if you open it and head over to the [lat,long to E,N] tab you see the custom function helpfully spitting out the answer.

=Lat_Long_to_East(DecimalLat,DecimalLong,a,b,E0,F0,DecimalPHI0,DecimalLAM0)
=Lat_Long_to_North(DecimalLat,DecimalLong,a,b,E0,N0,F0,DecimalPHI0,DecimalLAM0)

Fortunately it is not all that complicated, most of the arguments are constant so we are just left with:

=Lat_Long_to_East(DecimalLat,DecimalLong,6377563.396,6356256.909,400000,0.9996012717,49,-2)
=Lat_Long_to_North(DecimalLat,DecimalLong,6377563.396,6356256.909,400000,-100000,0.9996012717,49,-2)

Simples, now all you need to do is copy the macro module over to your spreadsheet and you are good to go.

Using OR in SUMIF in Excel

Wed, 25 Jan 2017 08:00:00 +0000

Things have moved on since the days of DSUM, we now have the likes of SUMIFS from Excel 2007 onwards. Can't even remember life before SUMIFS having never being a fan on DSUM.

But with all things in life everything moves on, everything is never good enough and now SUMIFS's shortcomings are beginning to pop up.

The other week the need came up for a handy way to integrate OR logic into the SUMIFS, saves on some extra column calculations and makes us all look a bit cleverer. It is possible via an array formula however it does have its limitations. The biggy being that the OR variables have to be hard coded in - not so good.

Any how, it is simple enough to code up, just put the OR parameters in curly brackets and surround the SUMIFS with a SUM and you are away:

= SUM ( SUMIFS ( $C$2:$C$16 , $B$2:$B$16 , {1,2,3,4} ) )

works with COUNTIFS also:

= SUM( COUNTIFS( $B$2:$B$16 , {1,2,3,4} ) )

"You only know what you know how to google"

Fri, 30 Dec 2016 15:30:00 +0000

"You only know what you know how to google"
- Martin Childs, 2016

This post was supposed to be about how create a custom Excel VBA function, the origination of the idea was despite my lifelong career of tinkering with excel I'd never come across or had the requirement to have a function cycle through the cell you input as a range to perform a calculalation on them.

Nice, just the sort of Excel work I like, I firstly gave it a bash myself - I usually am able to wing the answer using induction. [Induction, if you didn't know is the actual method Sherlock Holmes uses rather than the often misquoted deductive reasoning.] No luck winging it so I turned to every programmers best friend Google.

There's pretty much nothing in the coding world that is invented or revolutionary these days, everything has been done before, anything new is just an incremental improvement and it is all documented on the internet. So it was off to google to what was what.

In hindsight I don't know whether I was having a bad day or I had lost my mind but for the life of me I couldn't find the simple answer I was looking for - everything solution was so convoluted I couldn't believe it. In fact I battled with it for a time much in excess of what it would usually take me.

I eventually found a solution which was acceptable - through trial and error more than anything. So a while later when I got round to writing the method up I thought I'd give it another google as you know how the saying goes:

You only know what you know

Well, would you believe it, this time around google was almost straight in there with the answer, damn it. Reflecting on the above adage it occurred to me that it is no longer true and have come up with a more up to date phrase:

"You only know what you know how to google"
- Martin Childs, 2016

Hopefully I'm the first one to coin it, but it probably like every app idea I come up with - one search on Google Play and it's already been done 20 times over!

Excel: convert Postcode in x,y grid co-ordinates

Tue, 05 Jul 2016 08:00:00 +0100

This is a cracking blast from the past, I was digging through my old personal macro excel backups when I came across this beauty.

It dates back to my recycling plant development days, I had to devise an algorithm to work out the most economical site placements for food waste recycling facilities for a given highly dispersion of waste arisings.

The data I had to work with was in the form of a list of postcode locations along with annual waste tonnages. I had originally intended to lookup the addresses against my longitude/latitude list however turns out the maths to convert long/lat into something usable in excel was rather complex.

I've since worked this long/lat conversion out and have been meaning to write it up into a blog post but it is frankly pretty boring even by my standards.

Screen scraper

So back then my next angle of attack was to use a screen scraper - a bit of code I could fire the postcode off into the interweb and get back the result I needed. I already knew the site I was going to leverage, Streetmap.co.uk, as I'd been using it for a while for all things MTB as it was it was the only service at the time where you could view Ordnance Survey maps.

The code

Paste this code into your VBA Project:

Then all you need to do is place you cursor in the cell next to the postcode you want to look up and run the macro.

If you have a list of postcodes the code will happily rattle down the list for you.

Uses

But what use is it to me I hear you ask? Plenty, I wasn't expecting you to need a recycling plant location algorythm, it has lots of uses:

with a bit of Pythagoras you can determine the distances between two locations
You can use it to plot locations (on a graph)
(well two, I'm sue there must be more!)

How to assign a shortcut key to the Explode Excel addin

Mon, 27 Jun 2016 08:00:00 +0100

Sorry to bore you with yet another Explode addin post, but I think this is worth it on a couple of fronts. Assingning a shortcut key to the explode plug-in makes more awesome - being able to devle into those dependants even quicker.

Also seeing as as my initial explode post is one of my most read posts, I know you'll all lap it up.

There are two ways of doing this, either to assign a shortcut in the ribbon macro window this is by far the easiest method, or to code it in VBA.

First Method

The trick here is that you need to know the name of the Sub which calls the main prog, however since the VBA project is locked for viewing you'll have to trust me in knowing what it is called.....

First of all you have to enable the developer ribbon - instructions here.

Then its just a matter of clicking the Developer tab, selecting the Macros button and type the following in the top box:

Explode.xla!ShowExplodeForm

If you spelled it correctly the options button should make itself available where you can then input your shortcut of choice. You'll need to try to avoid an already take key combo (so stick to Ctrl-Shift-something), my preference is Ctrl-Shift-X. To enter this just hold down shift and type X in the box then hit OK. You can then cancel the dialogue and merrily save a few seconds a day!

Second method

The next method is to insert some code into the ThisWorkbook object in the addin's VBA project, however since we've already established it is locked - I have a workaround - we'll put it in our Personal Macro workbook.

First off we'll have to enable the addins references to call from our code, from the menu bar go to Tools then References and check the VBA_Explode box.

Then all we need to do is paste the following code in your personal macro book's ThisWorkbook object:

Option Explicit

Private Sub Workbook_Open()
       
    Application.OnKey "^+X", "VBA_Explode.ShowExplodeForm"
End Sub

Hit save up at the top and reopen Excel and test it out - Ctrl-Shift-X should get you rockin'.

Create your own custom formatting shortcut in excel

Sun, 19 Jun 2016 08:00:00 +0100

Like with most things in life, the longer you do something for the more you get set in your ways. Excel is no exception and if I'm a sucker for anything it's number formatting.

Maybe it's the accountant in me, but I think negative numbers are easier to read and look far more stylish wiht a bracket around them. Then if you are going to use a bracket you must sort the alignment out.

Next is the zeros, it's gotta be just a dash, trust me. If you are looking at a model and you've go a sheet full of zeros you need to know if they are really a zero or just a small enough number to be rounded as zero.

Here's it in it's custom formating code along with the percentage style also:

#,##0_);(#,##0);-_)
0.00%_);(0.00%);-_)

All we need is the following bit of code hooked up to assigning a shortcut (in this case Ctrl-Shift-M), using the shortcut cycles through the formats, done!

Paste this code into a macro module in you personal macro workbook:

Sub mcFormattoggle()
'
' MC_Format Macro
'
  numformat = Cells(Selection.Row, Selection.Column).NumberFormat

  Select Case numformat
    Case "General"
      Selection.NumberFormat = "#,##0_);(#,##0);-_)"
    Case "#,##0_);(#,##0);-_)"
      Selection.NumberFormat = "0.00%_);(0.00%);-_)"
    Case Else
      Selection.NumberFormat = "General"
  End Select
End Sub

Then in the ThisWorkbook object paste this code which will assign you shortcut. In this instance it is Ctrl-Shift-M

Option Explicit

Private Sub Workbook_Open()
       
    Application.OnKey "^+M", "mcToggleFormat"
End Sub

Save then restart Excel and you are away!

Explode Excel Addin - download Latest 2008 version

Sat, 11 Jun 2016 20:00:00 +0100

After many months struggling with my favorite excel explode addin, I've finally discovered the solution to my issues.

The problems started after I arrived at a new job - the computers were super locked down and they had Office 2010. I had problems installing the addin and even when it was working there was no 'Explode..' option on the right-click context menu - my main way of accessing it. I also couldn't click outside the window when it was running, most inconvenient. I eventually got used to using the ribbon shortcut but then I ran into more trouble.

I got myself a new laptop, this one was a vast improvement on the brick I had before - everything ran super quick, however it had one major issue - it had Office 2015 64bit installed. I could get the addin installed but it wouldn't run. I spend an age tweaking the security and trusted location settings to no avail.

It was then I got thinking, I know that it had been discontinued some years ago but did I have the latest version installed, the quick answer was NO! My mistake - not following my own blog posting, Checking against the version I had on my home PC I had downloaded it from a different website and had the 2002 edition not the 'latest' 2008 version. Dumb-ass.

So to sum up:

Only download from the original XL-Logic.com website (the main website has been taken down but the download section still works.

Or you can download from my mirror.

Don't download from the Clubfinance Project Finance website - it's a really old version!

How to reset the default cell formatting in an Excel spreadsheet

Wed, 30 Sep 2015 20:00:00 +0100

One of the more irritating things you can come across in someones elses spreadsheet (on the basis that I've never managed to reproduce it myself) is when somehow the default style has been changed to spit out a daft format of 00:00:00 for seemingly any number.

This is especially apparant of you are a regular user of the clear formats function. It is a staple of mine in a few different circumstances, notably in clearing other peoples gash formatting & when you want to quickly see more significant figures regarless of the current formatting you have.

There are two ways to do this, you can put it in a macro or manually

Sub ResetNormalStyle()
    ActiveWorkbook.Styles("Normal").NumberFormat = "General"
End Sub

or my favorite personal style

Sub ResetNormalStyleMC()
    ActiveWorkbook.Styles("Normal").NumberFormat = "#,##0,_);(#,##0,);-_)"
End Sub

An enhanced name manager for Microsoft Excel

Fri, 26 Jun 2015 08:00:00 +0100

Named ranges are one of those marmite things in Excel, either you use them or you don't. I sit between the two camps and my utilisation depends on two things:

Firstly who the end user will be and whether they'll need to interact with them.

The other is a timesaving thing - their use will depend on if it will save me time over the long run.

My actual use of named ranges was pretty minimal as Excel's built in Name Manager is pretty basic being just a list.

However I after coming across this enhanced add-in has driven me to use them more and more.

I'll give you the blurb:

If you are in need of a utility to manage defined names in your Excel models, this one is a must-have. List all names in your active workbook. Filter them using 13 filters, e.g. "With external references", "With errors", Hidden, Visible. Show just names that contain a substring. Show just names unused in worksheet cells. Edit them in a simple dialog or make a list, edit the list and update all names in one go. Delete, hide, unhide selected names with a single mouse click.

I'm not sure if there is much I can add, that is a pretty comprehensive list of its functions. I'd encourage everybody to give it a go. It is definately an invaluable resource for me as there are a load of spreadheets I come across that have been infected with hundreds of broken and external named ranges that are of no use to the function of the spreadsheet that can be banished quickly.

Install instructions

Download the zip from here. Unpack the files in the zip file to a folder of your liking and open the file called "Setup Name Manager.xls". Press the button in that file to install. I did have trouble with the latest version though, had to go back to an older version I had lying about.

Extract a Surname from a string in Excel

Wed, 10 Jun 2015 08:00:00 +0100

There comes a point in every Excel users workflow that they've got to work with a list of names. Usually the data you get is in the wrong format, or you have to compare two dataset of different formats. This can be frustrating but here is a quick guide of how to manipulate them.

This tutorial is based around extracting the surname / family name from a variety of different formats, if you need to get to the title or firstname then it should be easily enough to adapt the workarounds.

Surname, Forename

Possibly the best format to get the data in, it is quick and easy to extract the surname, like everything in excel all you have to do is break the task into logical steps. Lets go with this example:

Fish, Phil the

Breaking it down, the portion of the name we want to take are all the letters before the comma. Using the FIND function and assuming the string is in cell A1 we'd use this formula:

=FIND(",",A1,1)-1

This results in the expected answer of 4. All we need to do next is use the LEFT formula to extract the letters:

=LEFT(A1,4)

We can then nest these formulas into one compact equation:

=LEFT(A1,FIND(",",A1,1)-1)

Et voila

Fish

Title Forename Initial Surname

It wouldn't be so bad to extract the Surname from a Forename Surname configuration, you just repeat the above steps but instead look for a space and use the RIGHT function along with LEN - which tells you how many characters are in the string so you can extract the space to last letter. E.g. for

Phil Fish

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

Again giving you

Fish

the trouble is that the data rarely comes through like this. There are several permitations which will come through including middle name/no middle name/multiple middle names and title:

Phil Fish
Phil the Fish
Lord Phil Fish
Phil Overloard of All Fish

Back to our human logic, all we really have to do here is take the letters after the last space, unfortunately this is trickier than it sounds!

To accomplish our task we have to break this down into futher steps, first off:

Count the number of spaces

Counting how many occurances a string has can be calculated by counting the total length of the string minus the length of the string without the text you are trying to find. Pretty cunning really, you'll have needed a bit of out of the box thinking for that one. Here's the formula to use:

=SUM(LEN(<range>)-LEN(SUBSTITUTE(<range>,"text","")))/LEN("text")

or in the context of our example:

Phil the Fish

this formula:

=SUM(LEN(A1)-LEN(SUBSTITUTE(A1," ","")))/LEN(" ")

will yield the answer of 2.

Find the last occurance

Now we have found how many times the space appears we have to locate the last occurance, we do this by replacing the last space with something unique which we know won't appear for this we'll use a special system charachter Null and call it using the CHAR function which retreives is using its ASCII code:

CHAR(1)

So as a formula we'll substitute the space in the text string and find it:

=FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),2))

This give us the position of 9,

Extract the surname

Next all we have to do is proceed to grab the last 9 letters of the string as described earlier and we are away:

=RIGHT(A1,9)

Condense into one formula

Finally once we've figured out all the steps we can bung all the code into one formula:

=RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),SUM(LEN(A1)-LEN(SUBSTITUTE(A1," ","")))/LEN(" "))))

And we are away. I told you it wasn't as easy as you first imagined!

Creating hyperlinks in Excel that are relative & actually work

Wed, 25 Feb 2015 08:00:00 +0000

If you've ever tried to create a hyperlinks in excel you'll know what a pain they are.

The hyperlink tool on the Insert ribbon is quite useful, but it is a pain to create a lot of them. The big problem however is that they are not relative - if you add more columns or rows suddenly your hyperlink is pointing to the wrong place.

You could use the custom hyperlink formula, this will let you create a lot of entries more quickly, however it is still not relative and has more issues.

=HYPERLINK("[Budget]June!E56", E56)

This is because the address is entered as text so is not a cell address. Also we now have the problem that the spreadsheet name is now also hardcoded so if you change the spreadsheet name you've now scuppered youself.

Pretty crappy really seeing as this is Micro$ofts own tutorial! So in the next evolution of its usuage would be to add the filename in dynamically using the following function:

=CELL("filename",C9)

However to be able to use it in the formula we need to extract just the filename so we have to use:

=LEFT(CELL("filename",C10),FIND("]",CELL("filename",C10)))

This is also very clumsy as we are still harcoded - not relative - and trying to dig ourselves out of the predicament we are going to make it even more complicated.

The solution

What we need is a subtle tweak, this is the answer:

=HYPERLINK("#"&CELL("address",M235),"Actuals Summary")

Nice, simple and hitting all the buttons.

Excel function to list worksheet names

Tue, 02 Sep 2014 16:08:57 +0100

Ever wanted to list all the tabs in a workbook or reference a specific sheet by its placing, look no further.....

This funky little custom VBA function can be used simply as a contents sheet, This is a particularily useful tool tool when used with the indirect function, it will allow you to soft-link to any cell within your worksheet.

There are a few things you should note though, it sometimes won't update after a sheet move/copy/delete/rename so a good old calc all - Ctrl-Alt-F9 won't go amiss.

Function WorksheetName(SheetNo As Integer)
	    'Function to return worksheet name for its given number order
	    'Copyright Marty Childs 2008
	    'Usage =WorksheetName(1) will return "Sheet1" in a blank workbook
	   
	    WorksheetName = ThisWorkbook.Sheets(SheetNo).Name
End Function

Follow these instructions to create the macro for Excel 2003 or 2007. Also the function needs to be saved into workbook itself so it is available to everyone and for 2007 users and above dont forget to save the file as a macro-enabled workbook.

Explode - the must have excel add in

Mon, 11 Aug 2014 08:00:00 +0100

Excel's built in formula auditing tools is and always has been rubbish, sure you may have got used to how they work and feel quite competent/happy with it - but the Explode excel addin will blow your mind!

If you work with excel a lot and you don't use the add in Explode you're in one of two camps:

not a power user, or
both amazed at how you've gone without it thus far in life; and super annoyed that it's taken this long to find it!

Explode is pretty old now (but as useful as ever) and the great peops over at XL-Logic.com who authored it have long since taken their website down, but you can still access it from their download page from here, (my mirror, ~~another mirror~~).

Update 9-Jun-2016: see my blog post, don't download the addin from google's first hit of Clubfinance Project Finance as is a really old version.

To install:
Extract explode.xla from the zip to any folder, then this (for Excel 2007) go to Excel Options, Add-Ins, then 'Manage: Excel Add-Ins', 'Go...'. Broswe to the file you just extracted and you are away.

To use:
Just right click a cell containing a formula and select 'Explode...' and you are away. You can then trace both the pecedents and dependants with ease.

A powerful, intuitive, easy to use, and FREE formula auditing tool. Designed by a professional financial modeller to be used by experts and newbs alike. Once you start using it, you'll wonder how you ever managed without it. Imagine using the audit arrow feature on a formula and then tracing each precedent or dependent to find out what the value is and then finally returning back to that origin cell. Imagine if you could automate that process and display all that information instantly in a well organized interactive format. Now that would be a great formula auditing tool! One day I stopped wishing for it and just built it. Now you can have it too.

Stay tuned for some more excel goodness, I've got a blog post about another super usefull util, Name Manager, coming up.

Make a cell look like a button in Excel

Tue, 22 Jul 2014 08:00:00 +0100

There a sometimes in excel where you want to create a button but you don't want to introduce VB into your workbook (there are many reasons not to) - heres how.

I'm a great advocate of not using VBA / macros when you don't have to - they are not widely understood, easy to break when you alter the workbook and introduce a plethora of security issues. And with the newer versions of excel they can prove troublsome with users not enabling macros and saving the workbook in a non macro-enabled format (i.e. .xlsm).

Here is how to:

first pick the main coulour of the button and fill the cell. To make your first attempt simply select a colour in the middle of a shade range;
the open up the 'Format Cells' dialog, the easiest way; to do this is right-clicking the cell where you will see it in the context menu;
switch to the 'Border' tab;
in the Color dropdown box pick the shade lighter than your main fill colour;
select the middle weight solid line from the style selection;
in the border box click the top line and the left line;
repeat the previous three steps with a darker shade and the bottom and left lines;
click OK to see your creation;

You may want to go back at this point to tweak your colours and/or line thickness to get the best results.

This trick is best used with a hyperlink to open another document/webpage; link to a specified place within the workbook or even to lanch a script or other program. See my blog post about how to properly set up a hyperlink.

Excel - Add comment directly into formulas

Sat, 18 Apr 2009 11:23:00 +0100

Ever known anybody who hardcodes numbers into formulas? Does it drives you mad because they leave no explanation of why? Well this may be the function you should tell them about.

You can use the N() function to add a comment inline with a formula. What it actually is mean't for is to try to convert a string into a number, but if you just put words in there the output of the formula is zero.

Although come to think of it, you've probably already told this person to explain their actions and they haven't listened to you so they probably won't now! Best to keep it to yourself for explaining stuff you don't want to use a comment for.

See example below:

=A1+A2*0.85+N("your comment")

Excel - Calculating Pi

Fri, 31 Oct 2008 17:46:00 +0000

If you are finding the inbuilt excel function for =Pi() is just a bit too dull, there a handy function to calculate Pi on the fly.

You may want to use it sparingly though, too many times in one spreadsheet or to many decimal places and you'll grind to a halt as it is a bit processor hungry!:

Function myPi(places)
'Places is the number of decimal place accuracy you want
x = 1
a = 4
b = -1
Do While Round(c, places) &lt;&gt; Round(a, places)
c = a
a = a + (4 / (x * 2 + 1)) * b
b = b * -1
x = x + 1
Loop
myPi = Round(a, places)
End Function

Excel - Unhide all sheets

Fri, 31 Oct 2008 11:02:00 +0000

This little sub quickly unhides all hidden sheets (including xlVeryHidden). I use it frequently as people tend to hide worksheets which are no longer needed rather than just delete them, also especially useful when trying to dissect other peoples creations.

It also has some sneaky uses, I regularily come across people who like to protect the content of their macros and spreadsheet from prying eyes. Usually they make the fatal mistake of making the visibility of tabs they don't want people to see as xlSheetVeryHidden, then password protect the macros. What a mistake indeed, the function below gets straight around this:

Sub unhide_all_sheets()
Dim wksht As Worksheet
For Each wksht In ActiveWorkbook.Worksheets
Sheets(wksht.Name).Visible = True
Next wksht
End Sub

I'm not even using an exploit for this, its just plain old user error - whist password protecting the macros blocks UI access to the sheet properties in Visual Basic, it does not lock access to the field as a command.

To properly protect the required tabs the user must Protect Workbook. Not that even this will stop someone who really wants to gain access ;-)