Wait! I can explain everything!

Managing multiple workbooks in Excel 2010

2013-06-17T15:47:00.000+01:00

Working with the Excel MDI

I have a project that I manage using multiple Excel Workbooks. To ease grouping the control of these workbooks, I have on a master Workbook, a sheet that provides buttons to open any of the workbooks in the group.

To work on the project I launch the master spreadsheet and open only the workbooks I currently need. I wouldn't want to open all of them at the same time, every time I dived into the project.

The buttons are linked to macros which provide the appropriate very simple VBA code to open the relevant Workbook.
e.g.

Sub WSIV() ' Link your master button to this subroutine using Assign Macro.
Workbooks.Open FileName:= _
"C:\Documents and Settings\me\My Documents\work\suppliers\invoices.xlsm"
End Sub

This has worked fine for many years, but the Multi Document Interface creates a tedious problem, in that as the number of workbooks that are open in the session increases, switching between them involves a hunt for the required window, usually totally obscured, by layers of other sheets.

If only I could see the required sheet and click on it, it would immediately bring it into focus.

Apart from sliding the upper windows out of the way to find the required sheet in the hope that the workbook is already opened and the next one revealed is the one being hunted. The alternative is to locate the sheet on the task bar, and if it is there click it, which is tedious in itself. It may only cost one click but also costs two distinct cursor positions, as well as the delay in building the array of miniature sheet views and selecting one.

I therefore searched for a better way and I believe I have found one. I worked out some code for the master sheet buttons that now open the sheet if not already open, and bring the sheet into focus if it is already open.

The result is that by keeping the master sheet in view or at least in the same position, and up against the edge of the window, on can quickly locate the button for the desired sheet, since it is always in the same position, double click it and the required sheet springs into view.

Getting the code working involved overcoming a couple of pitfalls. First of all there is no built in function to determine whether a Workbook is open. Also there is an apparent inconsistency in the naming of the workbook file - the workbook name doesn't include the full path, just the filename and extension.

Here is the code:

Sub WSIV()   ' Link your master button to this subroutine using Assign Macro.
If IsOpen("invoices.xlsm") Then
  ' Bring the already open Workbook into focus.
Workbooks("invoices.xlsm").Activate
Else     ' Open Workbook.. It will have focus.
Workbooks.Open FileName:= _
"C:\Documents and Settings\me\My Documents\work\suppliers\invoices.xlsm"
End If
End Sub
' Repeat as above for each button and filename
'...
'...
'...
' Common function to test if a given Workbook is already open.
Function IsOpen(FileName As String) As Boolean
Dim wb As Workbook
  ' Search all open Workbooks for the required one.
For Each wb In Application.Workbooks
If VBA.UCase(wb.Name) = VBA.UCase(FileName) Then
  ' Requested workbook is already open
IsOpen = True
Exit Function
End If
Next wb
' None found, requested workbook is not open
IsOpen = False
End Function

Life with a garden hose

2013-05-07T18:25:00.002+01:00

I recently bought a new garden hose to replace the leaky kinked one we've been wrestling with for some time.

It was quite expensive but claimed to have superior qualities. Worried I might be wasteful if I didn't look after it properly, I decided to do something about the annoying way the hose pipe snagged on the corner of the house when it was wound and unwound on its wall mounted spool.

This problem not only tends to damage the pipe but makes handling tedious and tough.

I had imagined fixing up some kind of roller on the brick corner of the house, but when I surveyed the materials I had to hand I found a simpler low tech solution.

The main part is a some scrap 32mm plastic waste pipe. I had several pieces - stashed away just because they might come in handy for something. I chose the grey one.

I cut it down to about 5 inches and then made a lengthways cut so the piece could be opened up. Opening it requires some force as it naturally wants to resume its natural cylindrical shape.

However that resistance to opening is used to hold the piece in place.

I drilled 4 holes in the brick 2 on each side of the corner, and fitted plated screws leaving them to protrude half an inch to provide a niche for the plastic pipe to clip on to.

As far as functional performance is concerned my work is done, the pipe glides round the corner with ease. I will look at tidying the appearance, after I've confirmed longer term stability of the solution and that no adjustments are required.

AVG 2013 nagging for payment

2012-10-16T15:31:00.000+01:00

How to stop the nagging!

Did you upgrade your AVG free antivirus software recently, and are now being told your free trial is about to expire?

You may have been distracted by the word 'free' in 'free trial' when upgrading and selected the wrong option, i.e. the time limited free trial of the full paid for version.

However all is not lost.

Simply go to Control Panel and select 'Change or Delete Programs'. From the list it presents, select AVG 2013 and then select the 'change' option.

An AVG installation configuration screen will appear, and one of the options has a button marked 'Downgrade'. This option will have the effect of downgrading the installation to the free version of AVG 2013, not to the previous version you had before you upgraded in the first place!

Select this and follow the instructions.

If during this process Windows pops up a message saying the firewall is disabled, you can follow that message and enable Windows firewall (so that takes over from AVG firewall)

When invited to reboot, do so, and all is done.

I am the Walrus decoded

2012-09-21T11:22:00.000+01:00

I am the Walrus

The lyrics of this song were never a mystery to me when I thought about it, at the time of the original debate going on back in the 60’s. However back then, I never gave it much thought, as I was sure I would not be alone in my thinking.

Only now, with the advent of the internet and powerful search engines, is it possible to get an idea about how widespread specific knowledge is, and having done much searching I can’t find any reference to anyone else have the same basic interpretation as mine.

Which can be both disturbing and rewarding. Am I so out of touch that no one my shares view? Or is my view correct and I am the only one who has it?

The only way to put the nagging thoughts to bed is to share my conclusions, which to me are so self- evidently true!

Maybe there is another person or two out there that is still searching for the true meaning, and will, courtesy of Google, stumble across this little write up. If so, and my conclusions make sense, please feel free to reference it in any public comment you make about it.

The very first line says it all really.

What is an eggman? It is the male component of a fertilised egg. i.e. a spermatozoon. Biology tells us that there are some several 100 million spermatozoa in a typical human male ejaculation. It also tells us that usually only one actually fertilises the egg, as the egg immediately puts up a chemical barrier to any further interaction.

“I am the eggman”. The eggman must be the one sperm that actually succeeded in fertilising the egg. The one among many.

So how can an eggman be a Walrus? Well it has to be the eggman, not any eggman, the one that actually went on, in combination with an egg to start a new life as a human. I believe the Walrus is what some liken the image of an old man to, i.e. bald head and whiskers. (And vice versa, actually). So the eggman at the end of its life becomes and old man i.e. Walrus. The eggman is destined to become a Walrus. Well in a male centric world it is.

It is known that the lyrics were written while under the influence of hypnotic or hallucinogenic drugs. The distortions of perception would apply to senses other than sound and colour, for instance size and shape.

“I am he as you are he as you are me and we are all together” therefore is written from the perspective of a very small sperm amongst a swarm of them.

“See how they run like pigs from a gun, see how they fly” is probably a description of ejaculation.

The only other line that can be clearly decoded in this theme, is “Semolina pilchard, climbing up the Eiffel tower”.

Forget about drug busting policemen, and other such weird theories.

Knowing what semolina is doesn’t help. You had to be served it, as you might have been, some time before the sixties. In your dish you would have seen a white runny creamy substance. It had quite a bland taste so as a treat it was enhanced by a small dollop of jam. It was a good quick and cheap way of filling up hungry young mouths. In the substance you could see small grains of a uniform size. I believe this to be allegorically related to semen. Both in appearance and name.

Pilchards, otherwise known as sardines are small fish and found in huge shoals, and can only be interpreted as spermatozoa in the ejaculate.

So why would ejaculate be climbing up the Eiffel tower? This is just more imagery on a different scale. Just look at the Eiffel tower - it actually isn’t that much of a stretch of the imagination, to see it morph into a female figure, legs wide apart, wearing fishnet stockings, attracting an eggman like a magnet.

That’s it really. Other lines are just padding, red herrings, or other sexual references.

Oh, and did I mention goo is a term for semen? "goo goo g'joob g'goo goo g'joob".
Obvious really!

Washing Machine Woes

2012-09-16T19:19:00.002+01:00

Complaints about excessive vibration when spinning.

Noticed the door seal was distorted, and also found dosing ball in the folds of the seal. Ordered new seal as I could not get the existing one to sit straight. Suspect the dosing ball had damaged the seal. Also the old one had become a bit grubby.

Continued to use the machine but vibration hadn't lessened. Noticed that although there wasn't any noticeable play in the drum, there was a bit of grinding noise when rotating it, so prepared to get new bearings. The machine is about 4 years old now, but I had to replace the drum bearings in its first year. I guess warranty would have covered it, but it is quite an easy job on these modern machines.

Went to fit them, but immediately found that the primary trouble was a broken spring on the tub suspension, causing the tub to sag. This was the explanation for the distortion of the door seal.

On further dismantling came to the conclusion that the drum bearing was fine. Definitely running smooth and no evidence of looseness.

So returned the unopened bearing kit and obtained a new spring, and rebuilt machine. When I put the drive belt in place I was immediately aware of the grinding noise again. I thought then that the drum bearing was being noisy when under the tension of the drive belt, but on slipping the belt off again it was obvious the noise was coming from the motor bearing.

However the motor, despite making the noise, seemed to spin fairly freely. I decided that although the motor should be replaced, it might be an expensive mistake if the drive electronics were also faulty. (There was concern because the machine actually blew the plug fuse, which was the event that made me roll up my sleeves to sort out the problems.) So continued the rebuild and put on a test wash. This completed OK, even with 1600 rpm spins, albeit with excessive noise coming from the motor.

So now the hunt is on for a new motor. Having tried my local supplier who had no stock, I reverted to my usual online supplier. Pleased to find the required item available for delivery the next day, with not unreasonable extra charge for next day delivery. I can think about something else now while I await its arrival.

Next day comes, and a phone call from the supplier saying they had a problem in the warehouse, but the item would definitely be delivered tomorrow. Well at least I know I don't have to wait in for the delivery today. Meanwhile the dirty laundry piles up.

The new motor finally arrives. A few minutes to fit it, and then an hour or so to fully test. Finally all is well except there is a hint of a worrying squeak when the drum passes though one position. Are the drum bearings showing signs of old age?

Well a few more weeks gone by, and all is well. Backlog cleared. Job done, until the next time!

Plumbing tips

2012-09-12T17:08:00.002+01:00

I am soldering up some pipes as part of a bathroom revamp. When you don't do that kind of thing too often there are a few detail things that are worth knowing about before you start.

In principle, the job is easy. Use solder ring connectors. Clean the pipe ends. Apply flux. Join the bits together. Heat with blow torch. Job done.

Except, there are always points to watch that, if you don't, will trip you up.

First of all, you must get your pipe cut to the correct lengths. Obvious really, but practicalities get in the way of the simple vision you might have of measure, mark and cut.

Get an indelible marker. A sharpie is your friend. Inevitably you are going to be running the pipes around walls and floors that are real rather than being geometrically perfect, so always measure in situ where possible. i.e. place the pipe you are going to use in position and mark it there. Warning, don't go overboard with the marker, as it can contaminate the joint and it will leak.

Of course we all want the pipes, even if they are hidden, to run in parallel with other pipes and room features, and the only sure way is to in effect create a jig in situ. Therefore fit pipe clips to the walls first to hold the pipe in position while measuring. If you are fitting pipes in multiples e.g. a pair (flow/return or hot/cold) use dual pipe clips to hold the pipes together with exact spacing while measuring up.

When assembling the various pipes and joints for a trial fitting, use pvc tape to hold the pipe fully home in the fitting. This will prevent erroneous measurements being made.

Don't put up with a worn cutters. A lot of cutters have replaceable cutting wheels. This is a good feature to look for when buying. When cutting a pipe you want to be concentrating on the accuracy of the cut rather than fighting a cutter that is worn or even broken. The cutter binds because it is blunt, or the pipe is not perfectly round, or the partial cut is not uniformly deep, you find you are applying extra force to turn it. In turn this can increase the risk that some of the force is directed sideways and the tool slipping off or a part oft he cutting edge breaking off.

If you have a lot of cuts to make, the effort to make each one should be minimised by using an efficient tool and mastering a good technique. You can find this out yourself, as long as you are observant, focus on continual improvement and not settling for second best.

At all costs keep things tidy as you work. For short lengths of pipe I dip the end in the flux tub and rotate it, making sure I only coat the outside of the pipe. For longer lengths I get some flux on piece of toilet paper and wipe that on the pipe, using the paper just once. Keep the discarded paper in a bag and away from naked flame.

The main focus of attention should be complete and even coverage of the pipe end to the depth of the solder ring fitting, and the avoidance of dust or grit. Clearly the flux paste can easily pick up dirt, and the main consequence is that the pipe becomes a tight fit in the fitting, to the extent that it wont go all the way home.

As a precaution it is worth making a mark on the pipe where the fitting comes up to when the pipe is fully home, as this will provide a quick visual check right up to the point when the solder is cooling after making the joint.

Ideally use a blowtorch with a deadman trigger, so if you are working in a very tight situation where you might bump into something and knock it from you hand, or somewhere where you might drop the torch in an inaccessible location it will extinguish as soon as it leaves you hand.

About Luton

2012-09-04T15:59:00.002+01:00

The first TV relay tower in Luton
(As detailed in a BBC 1974 publication)

Luton

Channels: BBC-I 55; BBC2 62; IBA 59; fourth programme 65

Receiving aerial group: C/D Mean height of transmitting aerial: 154 feet

Transmitter site: Winsdon Hill, on the southwestern outskirts of Luton

Max. erp: 0.12 kW Polarization: vertical

(47m) agl, 670 feet (204m) aod

National Grid Reference: TL 081210

http://www.streetmap.co.uk/map.srf?X=508150&Y=221050&A=Y&Z=110

The service area includes almost the whole of those parts of Luton in which the service from Crystal Palace orSandy Heath is unsatisfactory

Getting your point over

2012-09-04T11:08:00.000+01:00

OK, here goes...

A big bug bear with me is the frustration I experience at times when trying to explain something, and not being able to communicate effectively.

No matter how hard I try, when I focus on testing understanding, I tend to cause aggravation in the listener, which in turn frustrates me even more.

I try to be relaxed and informal about it, as to be too intense can cause resentment. But on the other hand, treating a serious subject in light vein is probably not appropriate.

Hopefully this blog will expose the difficulties and thereby be part of the solution.