24 July 2006 by Ross McLean
I was surprised to find there was no way to display dependencies arrows for a selection using the Formula Auditing tool bar. I don’t use it a lot, but I would have though that this is the sort of thing it should be able to do. Now, I also know that there are quite a few decent Auditing tools out there which properly give you loads of feature that Excel does ship with, but it’s simple enough to tap in some VBA to get what I wanted”¦ and here it is!
[vba]Sub ForumulaAduitShowPrecedents()
Dim cell As Excel.range
For Each cell In Selection
cell.ShowPrecedents
Next
End Sub
Sub ForumulaAduitShowDependents()
Dim cell As Excel.range
For Each cell In Selection
cell.ShowDependents
Next
End Sub
Sub ForumulaAduitShowClear()
ActiveSheet.ClearArrows
End Sub[/vba]
Straight forward stuff, the result will look some thing like this:
Here’s a little tip, if the arrows are too grouped up make the coloums a little wider – what an insight!
Â
Tags: Best Practice, Examples, VBA
Categories: General, Spreadsheet Design •
No Comments »
1 June 2006 by Ross McLean
Don’t worry I’m still alive! Despite only making one post last month I have in fact been doing quite a lot of programming. I hope to have something reasonably interesting to post soon.
In the mean time, if you care to drag yourselves away from that Beat for a few moments, this might be worth a read!
Making Spread Sheets Correct
Tags: Best Practice, Examples
Categories: News! •
1 Comment »
21 March 2006 by Ross McLean
I am in the middle of updating a set of business tools used within the company I work for. The particular code I’m working on deals with post codes, it makes an attempt to correct invalid postcodes and points out one which seem suspect. A couple of things struck me about the time I will spend developing this code.
- If I spend 3 hours working on the code and it saves 10 mins for each person that uses it, I only need 18 people to use it and that time is fully recouped. Further, if I get paid £10 an hour it’s cost 30 pounds. If my boss gets paid £15 an hour, the time he saves will pay back the cost of development in 1.5 development life cycles savings to business occurs in half of the development time.
- Timeliness and befit to business. If my code runs in 10 seconds, that’s a 98% ([10/[10x60]]x100 = 1.6) time saving. This offers a huge benefit in the lead time of solution development for the end user.
What benefits does your work offer to your company? How do you quantify and justify your time?
Tags: Best Practice, VBA
Categories: Code, Spreadsheet Design •
3 Comments »
