Convert Formula to VB Module

Hi Guys,

I'm trying to convert a formula I've created, into a VB Function i.e.

Function hoursWorked(Shift)

My initial thought was to use 'Paste Formula, but then I can't define Shift anywhere.

Here is the formula anyway, I hope somebody can help:

=IF(SUM((VLOOKUP(MID(C22,7,2),hourtable,2,FALSE)+VLOOKUP(MID(C22,10,2),mintable,2,FALSE))-(VLOOKUP(MID(C22,1,2),hourtable,2,FALSE)+VLOOKUP(MID(C22,4,2),mintable,2,FALSE)))>=8,SUM((VLOOKUP(MID(C22,7,2),hourtable,2,FALSE)+VLOOKUP(MID(C22,10,2),mintable,2,FALSE))-(VLOOKUP(MID(C22,1,2),hourtable,2,FALSE)+VLOOKUP(MID(C22,4,2),mintable,2,FALSE)))-0.5,SUM((VLOOKUP(MID(C22,7,2),hourtable,2,FALSE)+VLOOKUP(MID(C22,10,2),mintable,2,FALSE))-(VLOOKUP(MID(C22,1,2),hourtable,2,FALSE)+VLOOKUP(MID(C22,4,2),mintable,2,FALSE))))

Basically I need to replace all references to C22, with Shift, and save it as a Module.

This line of code should do it

Please Login or Register  to view this content.

I've used that:

Formula:

Please Login or Register  to view this content.

And now I'm getting #VALUE

The syntax is wrong for a UDF .. Try:-

Please Login or Register  to view this content.

When I run that the debugger says the function isn't defined and Highlights VLookup. Would I need to specify WorksheetFunction more than once?

Ok... That's because every reference to an inbuilt worksheet function needs to be preceeded with "Worksheetfunction.". This could get very messy

It's probably more code efficient just to use VBA code rather than worksheet functions inside a UDF.. Can you upload a file ?

I've uploaded the sample sheet

Try this for your UDF

Please Login or Register  to view this content.

That's great gives the value back - but the reason for the IF, was when it returns 8 or more hours, take 0.5 off to allow for lunch. Not sure how that could be added?
I tried adding underneath
If HoursWorked >= 8 Then HoursWorked - 0.5 Else HoursWorked
but it didnt like that ^_^
This is great though :D

Ooh I managed it

Formula:

Please Login or Register  to view this content.

Is my final result. Thanks for your help :D

Nice one.... Antime