#Name Error in function, cannot run function with green arrow in editor.

I am very new to Excel Macros, and have some basic questions.

I have a excel sheet that has two functions. One calculates the time elapsed, and one is supposed to calculate the number of those hours that fall within a certain time period. However, I keep getting #name error as well as I cannot debut it with the editor after compiling it. I just need help on my major problems, as I am just a N00b teaching himself.

If you could take a look, that would be swell.
Peak Hours.xlsm

Here is just the code that doesn't work

Please Login or Register  to view this content.

Thanks a bunch,

Mike Stanek

Hi staplegunz,

VBA is a little different than Excel functions. VBA doesn't know what Time(0,0,0) is.
Try to Debug.Print your statements in the Immediate window to see if VBA likes them before putting them in the code.

A few pointers:

1 - your Module Name should not conflict with that of a Sub Routine / Function... in this instance your Module and Sub share the same name (change Module name)
2 - your Sub routine should in fact be a Function (change Sub to Function... at which point the End Sub will auto convert to End Function)
3 - as MarvinP infers Time should be TimeSerial (change all instances of Time to TimeSerial)
4 - Time is a Decimal value (Dates being Integers) and so when adding / subtracting etc you should be using standard mathematical operators (change all instances of & to +)

Once you adjust for the above you should get a value returned within your cell... whether that value is correct or not I've not checked.

Final pointers...

a) Debug.Print is used within VBA
b) Useful also to use Debug -> Compile to validate your code

A helpful site to understand debugging and the immediate window is at: http://www.cpearson.com/excel/Debug.htm