Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

User defined functions not recognized

  Asked By: Joel    Date: Jan 06    Category: MS Office    Views: 1818
  

I am trying to create a simple function that returns the name of the
sheet in which the function is entered but for some reason excel
(2003) does not recognize it.

I am assuming the code is correct because I've run a variation of it
successfully as a macro. And, I even pulled some code from an
internet site that was supposed to perform the same function. Any
ideas?

Here is the code I pulled from the internet:

Function Sheetname(numWanted As Byte) As String
Select Case numWanted
Case 1
Sheetname = ActiveSheet.name
Case 2
Sheetname = ThisWorkbook.name
Case 3
Sheetname = ThisWorkbook.FullName
Case Else
Sheetname = ActiveSheet.name
End Select
End Function

Since I'm new, just wanted to say that I'm glad I found this
group. I spend a fair amount of time creating fairly complex
financial and data management models and don't work with or know
anyone who has vba experience. Hopefully, I will get some good info
from this group as well as be able to contribute to solving some of
the problems faced by other members.

Share: 

 

6 Answers Found

 
Answer #1    Answered By: Andrew Bryant     Answered On: Jan 06

Because you said that you are trying to create  a function  that returns  the name
of the sheet "in which the function is entered" it is not necessary to create a
Select Case Statement.

Function Sheetname() As String
Sheetname = ActiveSheet.Name
End Function

Should do it for you. Let me know if I misunderstood what you need the function
to be able to do.

 
Answer #2    Answered By: Becky Baker     Answered On: Jan 06

I had a thought...

The user  defined function  was entered  in the code  for a specific sheet ( I also tried the entire workbook). Not gonna work.

If the same code is inserted into a module, it works just fine.

So, not surprisingly, it was user error.

 
Answer #3    Answered By: Stacie Martin     Answered On: Jan 06

What you typed was my original function  and the one that I ran as a macro  with some minor modifications.

The problem is that Excel won't recognize  the user  defined function "sheetname". Whenever I enter "=sheetname()" into a cell, Excel returns  "#Name?"

I'm guessing the error is probably related to Excel itself and not the vba  code.

 
Answer #4    Answered By: Adali Fischer     Answered On: Jan 06

where is your sheetname() function... if it's in a module...you might should define it with the public keyword, not sure I'll have to play with it... let me know how/where it's defined

 
Answer #5    Answered By: Olga Kates     Answered On: Jan 06

Why don't you try using the Paste function  feature and make sure you are getting
to the proper function? When I put the function in my Personal Macro Library -
the Paste Function puts in =Personal.xls!SheetName() in the cell which then
returns Sheet1 in the cell.

 
Answer #6    Answered By: Milind Mishra     Answered On: Jan 06

I got the function  to work  by placing it in a module. Prior to that it was in the code  for the specific sheet (which doesn't work apparently).

 
Didn't find what you were looking for? Find more on User defined functions not recognized Or get search suggestion and latest updates.




Tagged:        

 
 
 
 

Related Post