Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    233

    Unanswered: personal function

    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

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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...

  3. #3
    Join Date
    Oct 2003
    Posts
    233
    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

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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.

    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.

    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.
    Last edited by Colin Legg; 11-27-09 at 19:56.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •