Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF not available
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF not available
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF not available
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 "Stephen Rasey" wrote in message ... 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF not available
Is it in a sheet module rather than a general module? If it is, move it to
a general module. -- Regards, Tom Ogilvy "Stephen Rasey" wrote in message ... 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF not available
Try defining the Function as variant.
Stephen Rasey "Terry V" wrote in message ... 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 "Stephen Rasey" wrote in message ... 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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF not available
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" wrote in message ... 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 "Stephen Rasey" wrote in message ... 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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF not available
Yes, I have it in the Workbook module.
Thanks :) Terry "Tom Ogilvy" wrote in message ... Is it in a sheet module rather than a general module? If it is, move it to a general module. -- Regards, Tom Ogilvy "Stephen Rasey" wrote in message ... 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" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF not available
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 "Daniel.M" wrote in message ... 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" wrote in message ... 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 "Stephen Rasey" wrote in message ... 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" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF not available
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 "Terry V" wrote in message ... Yes, I have it in the Workbook module. Thanks :) Terry "Tom Ogilvy" wrote in message ... Is it in a sheet module rather than a general module? If it is, move it to a general module. -- Regards, Tom Ogilvy "Stephen Rasey" wrote in message ... 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" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF not available
No, that is your problem. It should be in a general module. Insert =
Module. -- Regards, Tom Ogilvy "Terry V" wrote in message ... Yes, I have it in the Workbook module. Thanks :) Terry "Tom Ogilvy" wrote in message ... Is it in a sheet module rather than a general module? If it is, move it to a general module. -- Regards, Tom Ogilvy "Stephen Rasey" wrote in message ... 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" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF not available
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. On Fri, 27 Aug 2004 18:55:41 GMT, "Terry V" wrote: Yes, I have it in the Workbook module. Thanks :) Terry "Tom Ogilvy" wrote in message ... Is it in a sheet module rather than a general module? If it is, move it to a general module. -- Regards, Tom Ogilvy "Stephen Rasey" wrote in message ... 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" wrote in message ... 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF not available
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. -- Regards, Tom Ogilvy "Terry V" wrote in message ... 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 "Stephen Rasey" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|