Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function giving #NAME? error
I had a previous post with a few questions. Most of them were answered.
But, I'm still having trouble. Here's what I have in detail: In 'Module1' Public Fuction Comission(MyNum) Comission = MyNum * 0.6 End Function (I then hit save, exit VBE, save workbook) types in =comission(A1) I get #NAME? error still... Thanks in advance for your help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function giving #NAME? error
Hi I Need,
Try replacing : Public Fuction Comission(MyNum) with Public Function Comission(MyNum) (FuNction) --- Regards, Norman "I need help please" wrote in message ... I had a previous post with a few questions. Most of them were answered. But, I'm still having trouble. Here's what I have in detail: In 'Module1' Public Fuction Comission(MyNum) Comission = MyNum * 0.6 End Function (I then hit save, exit VBE, save workbook) types in =comission(A1) I get #NAME? error still... Thanks in advance for your help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function giving #NAME? error
Does it say Function or Fuction?
"I need help please" wrote: I had a previous post with a few questions. Most of them were answered. But, I'm still having trouble. Here's what I have in detail: In 'Module1' Public Fuction Comission(MyNum) Comission = MyNum * 0.6 End Function (I then hit save, exit VBE, save workbook) types in =comission(A1) I get #NAME? error still... Thanks in advance for your help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function giving #NAME? error
Probably just a typo in your post but you mis-spelled Function (Fuction)
-- HTH... Jim Thomlinson "I need help please" wrote: I had a previous post with a few questions. Most of them were answered. But, I'm still having trouble. Here's what I have in detail: In 'Module1' Public Fuction Comission(MyNum) Comission = MyNum * 0.6 End Function (I then hit save, exit VBE, save workbook) types in =comission(A1) I get #NAME? error still... Thanks in advance for your help. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function giving #NAME? error
Try this:
Public Function Comission(MyNum as WHAT) Comission = MyNum * 0.6 End Function "I need help please" wrote: I had a previous post with a few questions. Most of them were answered. But, I'm still having trouble. Here's what I have in detail: In 'Module1' Public Fuction Comission(MyNum) Comission = MyNum * 0.6 End Function (I then hit save, exit VBE, save workbook) types in =comission(A1) I get #NAME? error still... Thanks in advance for your help. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function giving #NAME? error
Hi I Need,
I should have added that if you had used the Option Explicit statement at the head of your module, this error would have been highlighted. For additional information on the use of Option Explicit, see Chip Pearson at: Using Variables (Properly) In VBA http://www.cpearson.com/excel/variables.htm -- --- Regards, Norman |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function giving #NAME? error
Try to write FUNCTION instead of fuction, and better say
Public Function Comission(MyNum) As Double Comission = MyNum * 0.6 End Function HTH "I need help please" wrote: I had a previous post with a few questions. Most of them were answered. But, I'm still having trouble. Here's what I have in detail: In 'Module1' Public Fuction Comission(MyNum) Comission = MyNum * 0.6 End Function (I then hit save, exit VBE, save workbook) types in =comission(A1) I get #NAME? error still... Thanks in advance for your help. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function giving #NAME? error
Sorry - that was a typo in the post - I used copy & paste for this one!
Public Function Comission(MyNum) Comission = MyNum * 0.6 End Function and the (MyNum As WHAT) didn't seem to work either "I need help please" wrote: I had a previous post with a few questions. Most of them were answered. But, I'm still having trouble. Here's what I have in detail: In 'Module1' Public Fuction Comission(MyNum) Comission = MyNum * 0.6 End Function (I then hit save, exit VBE, save workbook) types in =comission(A1) I get #NAME? error still... Thanks in advance for your help. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function giving #NAME? error
Hi I Need,
Your function works for me. Is it perhaps possible that, whilst Commision only has a single m, in your function, in the worksheet it entered with the normal double m. --- Regards, Norman "I need help please" wrote in message ... Sorry - that was a typo in the post - I used copy & paste for this one! Public Function Comission(MyNum) Comission = MyNum * 0.6 End Function and the (MyNum As WHAT) didn't seem to work either |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function giving #NAME? error
Hi,
Make sure you have your function inside a module. If you have it inside a worksheet or in the ThisWorkbook module, Excel won't find it and give you #NAME.. -- Hope that helps. Vergel Adriano "I need help please" wrote: Sorry - that was a typo in the post - I used copy & paste for this one! Public Function Comission(MyNum) Comission = MyNum * 0.6 End Function and the (MyNum As WHAT) didn't seem to work either "I need help please" wrote: I had a previous post with a few questions. Most of them were answered. But, I'm still having trouble. Here's what I have in detail: In 'Module1' Public Fuction Comission(MyNum) Comission = MyNum * 0.6 End Function (I then hit save, exit VBE, save workbook) types in =comission(A1) I get #NAME? error still... Thanks in advance for your help. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function giving #NAME? error
Try to copy-paste my answer. It works for me!
HTH "I need help please" wrote: Sorry - that was a typo in the post - I used copy & paste for this one! Public Function Comission(MyNum) Comission = MyNum * 0.6 End Function and the (MyNum As WHAT) didn't seem to work either "I need help please" wrote: I had a previous post with a few questions. Most of them were answered. But, I'm still having trouble. Here's what I have in detail: In 'Module1' Public Fuction Comission(MyNum) Comission = MyNum * 0.6 End Function (I then hit save, exit VBE, save workbook) types in =comission(A1) I get #NAME? error still... Thanks in advance for your help. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function giving #NAME? error
What you have should work. Everything that is being suggested will make the
function potentailly more efficient or such but what you have is technically correct. Here is something to check. If you selet Insert - Function from the menu and then specify "User Defined" under "or select a Category" does your function show up??? It should. -- HTH... Jim Thomlinson "I need help please" wrote: Sorry - that was a typo in the post - I used copy & paste for this one! Public Function Comission(MyNum) Comission = MyNum * 0.6 End Function and the (MyNum As WHAT) didn't seem to work either "I need help please" wrote: I had a previous post with a few questions. Most of them were answered. But, I'm still having trouble. Here's what I have in detail: In 'Module1' Public Fuction Comission(MyNum) Comission = MyNum * 0.6 End Function (I then hit save, exit VBE, save workbook) types in =comission(A1) I get #NAME? error still... Thanks in advance for your help. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function giving #NAME? error
Thanks Jim,
I tried that - it found my function, but it was called ='Study Information.Testing.xls'!Module1.Comission() and said that it did not take any parameters! And then it still gave me the #NAME? error! I can't spell to save my life, so I will copy paste the function again Public Function Comission(MyNum) Comission = MyNum * 0.6 End Function It is stored in: Module 1 (here's what it looks like in VB Editor) VBAProject(StudyInformation.testing.xls) + Microsoft Excel Objects - Modules |_ Module1 So, it knows I have the function, but I bet its somehow in the wrong place or something. But, it said no parameters / arguments - and clearly I have 1... "Jim Thomlinson" wrote: What you have should work. Everything that is being suggested will make the function potentailly more efficient or such but what you have is technically correct. Here is something to check. If you selet Insert - Function from the menu and then specify "User Defined" under "or select a Category" does your function show up??? It should. -- HTH... Jim Thomlinson "I need help please" wrote: Sorry - that was a typo in the post - I used copy & paste for this one! Public Function Comission(MyNum) Comission = MyNum * 0.6 End Function and the (MyNum As WHAT) didn't seem to work either "I need help please" wrote: I had a previous post with a few questions. Most of them were answered. But, I'm still having trouble. Here's what I have in detail: In 'Module1' Public Fuction Comission(MyNum) Comission = MyNum * 0.6 End Function (I then hit save, exit VBE, save workbook) types in =comission(A1) I get #NAME? error still... Thanks in advance for your help. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function giving #NAME? error
Hi Sharon,
I copy & pasted that into Module1 - but I got the same problem (only difference I noticed was As Double- but I copy & pasted just incase) I also tried what Jim said - Insert Function User Defined It lists the function as ='Study Information.Testing.xls'!Module1.Comission() and the little help box pops up - says that there are no parameters. I am thinking I made the module in the wrong place? This is how it looks in VB Editor VBProject(Study Information.Testing.xls) + Microsoft Excel Objects - Modules |_ Module1 *this is where I have my code* And here is what I now have as the function: Public Function Comission(MyNum) As Double Comission = MyNum * 0.6 End Function "sharon" wrote: Try to write FUNCTION instead of fuction, and better say Public Function Comission(MyNum) As Double Comission = MyNum * 0.6 End Function HTH "I need help please" wrote: I had a previous post with a few questions. Most of them were answered. But, I'm still having trouble. Here's what I have in detail: In 'Module1' Public Fuction Comission(MyNum) Comission = MyNum * 0.6 End Function (I then hit save, exit VBE, save workbook) types in =comission(A1) I get #NAME? error still... Thanks in advance for your help. |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function giving #NAME? error
So you are trying to use the function defined in one workbook in another
workbook? In the functions list just select the function and it should walk you through adding the function to your workbook. Here is a little info on Functions. Functions Return values as opposed to Subs which perform actions. Functions can take arguments. When you pass in an argument you should specify exactly what data type the argument is. Additionally since functions return values you should specify what is being returned. If you don't speicify then the function assumes Variant data type. So what you have is essentially Public Function Comission(ByRef MyNum as Variant) as Variant Comission = MyNum * 0.6 End Function Variants are inefficient so you are better off to specify the data types. Additionally you should specify Pass by Value or Pass by Reference. I leave that to you to investigate. Your function should be... Public Function Commission(ByVal MyNum as range) as Double ComMission = MyNum.Value * 0.6 End Function -- HTH... Jim Thomlinson "I need help please" wrote: Thanks Jim, I tried that - it found my function, but it was called ='Study Information.Testing.xls'!Module1.Comission() and said that it did not take any parameters! And then it still gave me the #NAME? error! I can't spell to save my life, so I will copy paste the function again Public Function Comission(MyNum) Comission = MyNum * 0.6 End Function It is stored in: Module 1 (here's what it looks like in VB Editor) VBAProject(StudyInformation.testing.xls) + Microsoft Excel Objects - Modules |_ Module1 So, it knows I have the function, but I bet its somehow in the wrong place or something. But, it said no parameters / arguments - and clearly I have 1... "Jim Thomlinson" wrote: What you have should work. Everything that is being suggested will make the function potentailly more efficient or such but what you have is technically correct. Here is something to check. If you selet Insert - Function from the menu and then specify "User Defined" under "or select a Category" does your function show up??? It should. -- HTH... Jim Thomlinson "I need help please" wrote: Sorry - that was a typo in the post - I used copy & paste for this one! Public Function Comission(MyNum) Comission = MyNum * 0.6 End Function and the (MyNum As WHAT) didn't seem to work either "I need help please" wrote: I had a previous post with a few questions. Most of them were answered. But, I'm still having trouble. Here's what I have in detail: In 'Module1' Public Fuction Comission(MyNum) Comission = MyNum * 0.6 End Function (I then hit save, exit VBE, save workbook) types in =comission(A1) I get #NAME? error still... Thanks in advance for your help. |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function giving #NAME? error
Well - I had a brainstorm last night - this morning I tried it...
I made a fresh excel workbook, and put in my function - and it worked like a charm!! So I guess I'm just going to copy & paste as best as I can and just scrap the old workbook. But thank so much for your informative posts, I have learned a lot with this : ) "Jim Thomlinson" wrote: So you are trying to use the function defined in one workbook in another workbook? In the functions list just select the function and it should walk you through adding the function to your workbook. Here is a little info on Functions. Functions Return values as opposed to Subs which perform actions. Functions can take arguments. When you pass in an argument you should specify exactly what data type the argument is. Additionally since functions return values you should specify what is being returned. If you don't speicify then the function assumes Variant data type. So what you have is essentially Public Function Comission(ByRef MyNum as Variant) as Variant Comission = MyNum * 0.6 End Function Variants are inefficient so you are better off to specify the data types. Additionally you should specify Pass by Value or Pass by Reference. I leave that to you to investigate. Your function should be... Public Function Commission(ByVal MyNum as range) as Double ComMission = MyNum.Value * 0.6 End Function -- HTH... Jim Thomlinson "I need help please" wrote: Thanks Jim, I tried that - it found my function, but it was called ='Study Information.Testing.xls'!Module1.Comission() and said that it did not take any parameters! And then it still gave me the #NAME? error! I can't spell to save my life, so I will copy paste the function again Public Function Comission(MyNum) Comission = MyNum * 0.6 End Function It is stored in: Module 1 (here's what it looks like in VB Editor) VBAProject(StudyInformation.testing.xls) + Microsoft Excel Objects - Modules |_ Module1 So, it knows I have the function, but I bet its somehow in the wrong place or something. But, it said no parameters / arguments - and clearly I have 1... "Jim Thomlinson" wrote: What you have should work. Everything that is being suggested will make the function potentailly more efficient or such but what you have is technically correct. Here is something to check. If you selet Insert - Function from the menu and then specify "User Defined" under "or select a Category" does your function show up??? It should. -- HTH... Jim Thomlinson "I need help please" wrote: Sorry - that was a typo in the post - I used copy & paste for this one! Public Function Comission(MyNum) Comission = MyNum * 0.6 End Function and the (MyNum As WHAT) didn't seem to work either "I need help please" wrote: I had a previous post with a few questions. Most of them were answered. But, I'm still having trouble. Here's what I have in detail: In 'Module1' Public Fuction Comission(MyNum) Comission = MyNum * 0.6 End Function (I then hit save, exit VBE, save workbook) types in =comission(A1) I get #NAME? error still... Thanks in advance for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ASSIGNING THE VALUE TO A NAMED RANGE GIVING OBJECT DEFINED ERROR | Excel Programming | |||
Excel - User Defined Function Error: This function takes no argume | Excel Programming | |||
Error messages from user defined Excel worksheet function | Excel Programming | |||
#Name? Error in User Defined VB Function | Excel Worksheet Functions | |||
User-defined data type; Error: Only User-defined types... | Excel Programming |