personal function

mikezx10 · #1 (**permalink**) 11-24-09, 16:34

I created a function that returns the worksheet name, but for some reason when i open the file the functions cells all show 0, if i recall they still show 0, if i go to the cell hit f2 and enter the formula works or if i change the cell that the formula refers to it works. Whats up?

Public Function GetSheetName(codeName As String)
Dim wks As Worksheet
codeName = WorksheetFunction.Proper(codeName)
For Each wks In ActiveWorkbook.Sheets
If wks.codeName = codeName Then
GetSheetName = wks.Name
Exit Function
End If
Next

End Function

Colin Legg · #2 (**permalink**) 11-25-09, 13:40

Hi,

0 implies that the sheet can't be found. This could be caused by all sorts of things.

Another problem is that if a codename is updated in the VBE, the function will not update because the codenames aren't in the function's argument list. I think the best you can hope for is making it volatile. I've also corrected a few other things which could cause a problem in a UDF:

Code:

Option Explicit

Public Function GetSheetName(ByVal cName As String) As Variant
    
    Const NOTFOUND As String = "Not Found"
    Dim wks As Worksheet
    
    Application.Volatile
    
    On Error GoTo Errorhandler
    
    GetSheetName = NOTFOUND
    
    For Each wks In ThisWorkbook.Worksheets
        If UCase$(wks.codeName) = UCase$(cName) Then
            GetSheetName = wks.Name
            Exit For
        End If
    Next
    
ErrorExit:
    
    Exit Function
    
Errorhandler:
    
    GetSheetName = CVErr(xlErrValue)
    Resume ErrorExit
    
End Function

Does that make any difference?

(By the way, I'm not sure why you would want to return this information to a worksheet?!)

Hope that helps...

mikezx10 · #3 (**permalink**) 11-27-09, 16:09

I am not sure what you mean :

"Another problem is that if a codename is updated in the VBE, the function will not update because the codenames aren't in the function's argument list."

but i think the Application.Volatile is what i need, never heard of that before!

the reason i need the worksheet names is we are not supposed to have anything hard coded, so this way someone can change worksheet names and not break the code. The code reads data on one sheet and then uses that for processing all the others If they are detialed on the 1st sheet

Colin Legg · #4 (**permalink**) 11-27-09, 19:52

Quote:

I am not sure what you mean :

"Another problem is that if a codename is updated in the VBE, the function will not update because the codenames aren't in the function's argument list."

Changing a codename in the VBE will not raise a calculate event. The formula's result will only update when the formula is recalculated.

Quote:

the reason i need the worksheet names is we are not supposed to have anything hard coded, so this way someone can change worksheet names and not break the code. The code reads data on one sheet and then uses that for processing all the others If they are detialed on the 1st sheet

If you refer to the sheet codenames (rather than the sheet names) in the main code, then you won't have this problem? That's what I do and it works a treat.

Quote:

but i think the Application.Volatile is what i need, never heard of that before!

Making the function volatile is a 'last resort'. It will recalculate every time, even when a recalculation isn't required (no inputs have changed). This is obviously inefficient but for some functions it is required by definition, for example the NOW() function which returns the current date and time.

Internet Services

Web Development

Digital Marketing

Consumer Tech