Custom Functions

[DTTODGG]

Hello,

I'm trying to make 2 functions. These will be my first homemade functions,
so please be specific when helping me

First, in Module1 I tried:

Function OL(SCounts)
OL=8-MOD(SCounts,8)
End Function

This gave me errors. Can you nest a function "MOD" within a custom function?
It worked ok when I left out the MOD function (OL=8-2).

-----------------------------
Second, is there an already made function to convert text to mm:ss.hh?

I'm trying to work with stopwatch times (minutes, seconds, hundredths). I
need to be able to add, subtract, average, etc...

Thank you so much for sharing your wisdom.

 

[Gary''s Student]

Function OL(SCounts As Integer) As Integer
OL = 8 - SCcounts Mod 8
End Function

and in the worksheet used like =OL(64) which returns 8

 

[DTTODGG]

Gary, thank you for the prompt reply on my first question it works
wonderfully, unfortunately, I don't understand how.

Also, do you know an answer to my timing question or should I post that
separately?

I don't understand the function syntax.
If the MOD function help, it shows MOD(number,divisor)
How does SCounts Mod 8 work and how would I know this syntax?

Thank you.

 

[Pete_UK]

Mod in VBA is not the same as the worksheet function MOD. You need to
look in VBA Help (rather than Excel Help) to get the syntax. Other
functions which likewise have a different syntax include And and Or.

Hope this helps.

Pete

 

[DTTODGG]

Yes, this helps greatly. Why can't everything be the same syntax-wise? I
guess that's what makes this so much fun!
Thank you.

 

[Pete_UK]

You're welcome.

The syntax used in VBA is much more like most other languages - I
found the syntax in worksheet functions to be odd when first using XL.

Pete

 

[Gary''s Student]

Give us an example of the text string you are trying to convert.

 

[Rick Rothstein \(MVP - VB\)]

Perhaps worse than the syntax difference, their functionalities can be different too. For example, in a spreadsheet, =MOD(-10,8) will return 6 whereas in VBA, this...

MsgBox CStr(-10 Mod 8)

will display -2 as its answer. Another difference is how they handle floating point values. In a spreadsheet, =MOD(1.234,1) will return 0.234 whereas in VBA, this...

MsgBox CStr(1.234 Mod 1)

will return 0 (as it would for any value, floating point or not). The VBA Mod function rounds floating point values to whole numbers before performing its operation. The spreadsheet's AND and OR have some difference from the VBA versions (one being that in the spreadsheet, these are functions and in VBA they are operators). The key is to learn each world's functional elements and keep their differences in mind when you work with them. As to why the syntax (and functionalities) are different... they developed separately and were joined together sometime after each had matured.

Rick