UDF not available

[Terry V]

Hello
I have created a function and for some reason, on my worksheets, the
function does not show up.

should it not show up in the User Defined functions in the "paste function"
? (XL 2K)

Thank you
Terry

 

[Stephen Rasey]

Is it a Function and not a Sub? It is not a Private Function is it?

Do you know that it compiles? Can you execute a test Sub that calls the
function in VBA?

Is the workbook that it is in open?

Stephen Rasey
Houston
http://excelsig.org

 

[Terry V]

Stephen:
The workbook is open, it is a public function, no it does not compile in the
immediate window (it says "Sub or Function not defined" --- not sure why it
says this).

This is the code, very short. It is simply meant to format a cells content
to "mm:ss" based on a date (always the current day) put into a cell

Public Function rmin(rnge As Range) As Date
'Dim rnge As Range
rmin = Date & Format(rnge, "mm:ss")
End Function

Takes the date (always the current day) from the given cell (rnge) and
displays only the minutes:seconds

In the immediate window, Im typing:
rmin("A3") ' A3 does contain a date.

Thank you so much
Terry

 

[Tom Ogilvy]

Is it in a sheet module rather than a general module? If it is, move it to
a general module.

 

[Stephen Rasey]

Try defining the Function as variant.
Stephen Rasey

 

[Daniel.M]

Hi,

Well, for one thing (besides your problem), your UDF returns a string and is
defined as returning a date.

Try:

Public Function rmin(rnge As Range) As Date
rmin = Date + (rnge - Int(rnge))
End Function

And format your cell appropriately.

Regards,

Daniel M.

 

[Terry V]

Yes, I have it in the Workbook module.

Thanks
Terry

 

[Terry V]

I think part of the problem is the format in the immediate window.
The cell Im referring to is on sheet1.
Im trying rmin(Sheet1!A3) and many other variations.
I can't find the proper format for the immediate window in the help.
But It still gives me the same error "... not defined"

Thank you
Terry

 

[Chip Pearson]

Terry,

The code should reside in a regular code module, not the
ThisWorkbook code module and not in one of the Sheet code module.
In VBA, go to the Insert menu, choose Module, and cut and paste
the code in to that module.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Tom Ogilvy]

No, that is your problem. It should be in a general module. Insert =>
Module.

 

[Myrna Larson]

That's your problem. ThisWorkbook is a module for event procedures of the
workbook object. You need to insert a Module in your workbook and move the
code there.

 

[Tom Ogilvy]

Your function worked for me

==rmin(Sheet3!C5)

in a cell on Sheet2

I had to fix it so there is a space between the date and time or is returned
#Value

However, your mm:ss format doesn't return a correct time.

? format(0.445833333333333,"mm:ss")
12:00

where the actual time value is:
? format(0.445833333333333,"hh:mm:ss")
10:42:00

Since you are returning a date, you have to format the cell with the formula
to display as a date. Otherwise you would want to return a string, but you
wouldn't be able to treat it as a date in your worksheet.