# Convert Formula to VB Module

1. ## 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.

2. ## Re: Convert Formula to VB Module

This line of code should do it

``Please Login or Register  to view this content.``

3. ## Re: Convert Formula to VB Module

I've used that:

Formula:
`Please Login or Register  to view this content.`

And now I'm getting #VALUE

4. ## Re: Convert Formula to VB Module

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

``Please Login or Register  to view this content.``

5. ## Re: Convert Formula to VB Module

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

6. ## Re: Convert Formula to VB Module

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 ?

7. ## Re: Convert Formula to VB Module

I've uploaded the sample sheet

8. ## Re: Convert Formula to VB Module

Try this for your UDF

``Please Login or Register  to view this content.``

9. ## Re: Convert Formula to VB Module

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

10. ## Re: Convert Formula to VB Module

Ooh I managed it

Formula:
`Please Login or Register  to view this content.`

Is my final result. Thanks for your help :D

11. ## Re: Convert Formula to VB Module

Nice one.... Antime

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1