A while back the responsibility for maintaining a staff rota fell to one of the members of my team. They asked it I could provide something in Excel that would make it easier to compile, publish the required html and keep track of the rota. We both had a search around but were surprised to find very little on the web, so I came up with a workbook from scratch.

I ended up using a Form interface, some VBA, activeX controls to produce graphs and 5 worksheets to hold the data.

Download: web_rota.xls

If you'd like the password to be able to access the VBA and hidden worksheets just drop me an e-mail.

We needed to convert a large number of ordnance survey grid references (NGR) into national X Y coordinates. An OS grid reference will take the format SW123123.

The 'letter' part needed converting to the relevent X Y coordinate for that part of the country - the X coordinate for SW is 1, the Y coordinate is 0. The 'digit' part needed splitting in half - the first 3 digits are the X coordinate, the second 3 digits are the Y coordinate - so X = 123, Y = 123. The X & Y from the letter part then needed combining with the relevent X and Y from the digit part.

To further complicate matters, the received NGRs had 2, 4, 6, 8, 10 or 12 digits. The conversion needed to able to split the digits into the X and Y halves irrespective of how many digits there were, and then standardise them by adding zero's so that each X and Y had 6 digits in total - ie 112300 012300.

Another column was then required to provide a count of how many digits were provided in the first place so that we can guage the precision of the grid reference.

I ended up using a number of extended formulas making use of Excels Vlookup, Left, Right, Len, Concatenate and If Functions. The result seemed to do the job.


Download: NGR.xls

We needed to be able to carry out calculations within Excel on times which included a frames element (eg in the format hh:mm:s:ff)

Excel didn't seem to have a ready made time format or formula to deal with this so I was asked to come up with some functions to do the job.

Function to calculate the number of Frames from a Time (h:mm:ss:ff)

Function frames(c As Variant) As Variant
Dim f As Single

'Frames per second
f = 25

frames = (Left(c, 1) * (f * 60 * 60)) + (Mid(c, 3, 2) * (f * 60)) + (Mid(c, 6, 2) * f) + (Right(c, 2) * 1)
End Function

Function to calculate the number of Frames from a Time (hh:mm:ss:ff)

Function frames2(c As Variant) As Variant
Dim f As Single

'Frames per second
f = 25

frames2 = (Left(c, 2) * (f * 60 * 60)) + (Mid(c, 4, 2) * (f * 60)) + (Mid(c, 7, 2) * f) + (Right(c, 2) * 1)
End Function

A Function to calculate the Time from the number of Frames

Function timef(c As Variant) As Variant
Dim x As Long
Dim x1 As Long
Dim x2 As Long
Dim x3 As Long
Dim x4 As Long
Dim x5 As Long
Dim x6 As Long
Dim x7 As Variant
Dim f As Single
Dim y As Variant
Dim y1 As Variant
Dim y2 As Variant
Dim y3 As Variant
Dim y4 As Variant

'Frames per second
f = 25

x = WorksheetFunction.RoundDown(((c / (f * 60 * 60))), 0)
x1 = c - (x * (f * 60 * 60))
x2 = WorksheetFunction.RoundDown((x1 / (f * 60)), 0)
x3 = x1 - (x2 * (f * 60))
x4 = WorksheetFunction.RoundDown((x3 / f), 0)
x5 = x3 - (x4 * f)
x6 = x5

y = WorksheetFunction.Text(x, "00")
y1 = WorksheetFunction.Text(x2, "00")
y2 = WorksheetFunction.Text(x4, "00")
y3 = WorksheetFunction.Text(x6, "00")
y4 = y & ":" & y1 & ":" & y2 & ":" & y3

timef = y4
End Function

Download: Frames.bas

One of my roles has been to provide general support for Excel to our users. Luckily I don't seem to get asked that much as most people seem to know what they're doing when using excel. A couple of the more unusual things that I've done are include above.

Poley