Help with UDF #value error

[Parv]

Hi guys,

I've time data in text form i.e. "hh:mm:ss:fff" example: "17:12:36:250"

Now I'm trying to write a UDF as shown below:

Please Login or Register  to view this content.

Now when I use the formula, I get #VALUE error. Why so?

Any help would be highly appreciated.

Thanks

[Søren Larsen]

When you use MID(), LEFT() and so on, you would have a string returned. So you are trying to use algebra on text. If you multiply e.g. Mid(ttext, 4, 2) by 1 like this:

Mid(ttext, 4, 2)*1

Then it should work.?

[Parv]

^^ thanks for the reply.

I did not quite get you. I'm essentially trying to use algebra on returned string.

And I know that "Mid(ttext, 4, 2)*1" will work and give me 12 as output.

The problem I asked is , it's not working with the formula written.

Suppose I want to extract only the "hh:mm:ss" and ignore the millisecond part, it works fine

Please Login or Register  to view this content.

This would return me 0.717083333, and formatting the cell to "hh:mm:ss.000" would return me "17:12:36.000"

Now the question remains, why it's not working with the milliseconds?

[Søren Larsen]

Yes, trying to figure it out, but have not gotten there yot.

[shg]

Maybe ...

Please Login or Register  to view this content.

[Søren Larsen]

I see that we are in the good hands of shg...

@ shg: Do you have an explanation why the above does'nt seem to work?:

Please Login or Register  to view this content.

[shg]

Here's a clue:

Enter

? 1000*3600

in the Immediate window. What happens? How can you fix it?

[Søren Larsen]

Here's a clue:

Enter

? 1000*3600

in the Immediate window. What happens? How can you fix it?

Ahh, thanks a lot shg; overflow error. I found that this solved it:

Please Login or Register  to view this content.

Am I right in thinking that, in the original code, the calculation steps of the UDF causes 3600000 to be stored in an integer variable not able to store a constant that long, whereas if I changed the original code to this:

Please Login or Register  to view this content.

Then 3600000 is never stored during the calculation?

[shg]

Then 3600000 is never stored during the calculation?

[Søren Larsen]

...not sure what you mean, and perhaps I explained myself badly. But if you have no objections to the rest, then I'm good.?

[shg]

Numeric constants in VBA are Integer, Long, or Double, according to size. When they get involved with arithmetic among mixed types, all are converted to the size of the largest 'participant.' When you try to multiple 1000*3600, both Integer (by size) you get overflow.

You can specify the type explicitly using type declaration characters:

1% is an Integer
1& is a Long
1# is a Double

and also ...

1! is a Single

Your CLng does essentially the same thing.

[Søren Larsen]

Your CLng does essentially the same thing.

Yes, got that from the VBE Help. So to complete this thread, then using the type declaration characters, then the original could be changed to this:

Please Login or Register  to view this content.

Thanks for the help, and @Parv: Sorry for hijacking your thread!

[shg]

Or just change 3600 * 1000& to 3600000

[Parv]

I see that we are in the good hands of shg...

@ shg: Do you have an explanation why the above does'nt seem to work?:

Please Login or Register  to view this content.

Very efficient way of doing it, really great work :-)

Numeric constants in VBA are Integer, Long, or Double, according to size. When they get involved with arithmetic among mixed types, all are converted to the size of the largest 'participant.' When you try to multiple 1000*3600, both Integer (by size) you get overflow.

You can specify the type explicitly using type declaration characters:

1% is an Integer
1& is a Long
1# is a Double

and also ...

1! is a Single

Your CLng does essentially the same thing.

I knew that it's overflow problem, that's why I had declared the ptime as double.
But I didn't know how to explicitly declare the data type for constants. Thank you very much.

Yes, got that from the VBE Help. So to complete this thread, then using the type declaration characters, then the original could be changed to this:

Please Login or Register  to view this content.

Thanks for the help, and @Parv: Sorry for hijacking your thread!

Thanks for summarising the solution. and never mind