Can I pass one or two values to a UDF as the first parameter?

[JenniferMurphy]

Many years ago, I wrote my FmtTime UDF. Here's the function declaration statement:

Public Function FmtTime(ByRef pTime As Double, _
                        Optional ByRef pDP As Long = 1, _
                        Optional ByRef pInUnits As String = "Secs", _
                        Optional ByRef pNegOK As Boolean = False _
                        ) As String

Most of the calls calculate a difference between two date-time values and pass that to FmtTime. Here's an example:

=FmtTime(Endtime-Starttime)

I just encountered a situation where one of the date-time values is missing. This means that I have to test both values in the calling cell, which results in a horribly complex expression. I'd like to modify the UDF to accept both values. That way, the UDF can check both and values and return a result if both date-times are valid or an error if either one is not.

I know the way to handle this is to change the syntax to something like this:

Public Function FmtTime(ByRef pStartTime As Double, ByRef pEndTime _
                        Optional ByRef pDP As Long = 1, _
                        Optional ByRef pInUnits As String = "Secs", _
                        Optional ByRef pNegOK As Boolean = False _
                        ) As String

The problem is that this UDF is called literally hundreds of times in dozens of workbooks. Manually updating them all would be a headache. As an alternative, is there a way to pass one or two numbers as the first parameter? None of these options work:

=FmtTime(B7 C7)
=FmtTime((B7 C7))
=FmtTime((B7,C7))

Thanks

 

[RoryA]

In a word, no. I don't see why handling it in the cell is complex - you just need to check if the COUNT of the cells is 2.

 

[JenniferMurphy]

In a word, no. I don't see why handling it in the cell is complex - you just need to check if the COUNT of the cells is 2.

You mean something like this?

=IF(COUNT(D7, B7)=2,fmttime(D7-B7),"n/a")

That works, but it is not as clean (simple) as this:

=fmttime(D7,B7)

This UDF has several other parameters such as the input units, the number of decimal points to return, and what to return if one of the dates is missing. Now I need to think about whether it is worth it to fix the UDF and then edit all of those workbooks. (sigh)

Thanks for the help.

 

[Domenic]

You mean something like this?

=IF(COUNT(D7, B7)=2,fmttime(D7-B7),"n/a")

Yes, that's exactly right. That's the solution I would suggest as well.

 

[JenniferMurphy]

Yes, that's exactly right. That's the solution I would suggest as well.

Ok

 

[SidharthS]

You can change ur UDF To this format "=fmttime(D7,B7)" then use Find and Replace for each workbooks for "-" and replace it with "," If not possible by Find and Replace Write one VBA to do the same.

 

[JenniferMurphy]

You can change ur UDF To this format "=fmttime(D7,B7)" then use Find and Replace for each workbooks for "-" and replace it with "," If not possible by Find and Replace Write one VBA to do the same.

Yes, I was planning on doing something like that. The problem is that I have almost 2,000 workbooks scattered all over my hard disk. Running a Find and Replace on each one would be a major pain in the ***.

I did find a solution. FileLocator from Mythicsoft can search inside Excel workbooks. In under 5 minutes, it found 55 workbooks with calls to this UDF.

Filelocator Pro – Mythicsoft

This is now one of my top utility programs. I use it all the time.