Calling a Function in a Procedure

I need to extract the filename “My Excel File” from Worksheets(“Sheet1”).Range(“A1”), whose value = C:\Documents and Settings\user1\Desktop\My Excel File


I found on Chip Pearson’s site a Function TrimToChar which, using SearchFromRight and a TrimChar of “\” will trim OFF the filename “My Excel File”, leaving the Path.
But, I think I could use his function to tell me the number of characters in the Path and then use that to extract the remaining characters from the total character length (Mid Function)

Problem is, I don’t have a clue how to call a Function in VBA to work on Worksheets(“Sheet1”).Range(“A1”). How do you set the InputText , TrimChar, & SearchFromRight?????

(eventually, this will be in a loop, where I extract the filename from a Dynamic Named Range (list) in Column A)

Public Function TrimToChar(InputText As String, TrimChar As String, _
    Optional SearchFromRight As Boolean = False) As String
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' TrimToChar
' By Chip Pearson, www.cpearson.com , chip@cpearson.com
' http://www.cpearon.com/excel/SizeString.htm#TrimToChar
'
' This function returns a portion of the string Text that is to the left of
' TrimChar. If SearchFromRight is omitted or False, the returned string
' is that string to the left of the FIRST occurrence of TrimChar. If
' SearchFromRight is True, the returned string is that string to the left of the
' LAST occurrance of TrimToChar. If TrimToChar is not found in the string,
' the entire Text string is returned. TrimChar may be more than one character.
' Comparison is done in Text mode (case does not matter).
' If TrimChar is an empty string, the entire Text string is returned.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim Pos As Integer
    
    ' Test to see if TrimChar is vbNullString. If so, return the whole string. If we
    ' don't test here and used InStr as in the next logic block, an empty string would
    ' be returned.
    If TrimChar = vbNullString Then
        TrimToChar = InputText
        Exit Function
    End If
    
    ' find the position in Text of TrimChar
    If SearchFromRight = True Then
        Pos = InStrRev(InputText, TrimChar, -1, vbTextCompare)
    Else
        Pos = InStr(1, InputText, TrimChar, vbTextCompare)
    End If
    ' return the sub string
    If Pos > 0 Then
        TrimToChar = Left(InputText, Pos - 1)
    Else
        TrimToChar = InputText
    End If

End Function

using XL2000

If all you want is the filename at the end, I'd just use Split:

Dim strFile as String, varData
varData = Split(Worksheets("Sheet1").Range("A1").Value, "\")
strFile = varData(Ubound(varData))

Sure - Split breaks a piece of text up into an array using the specified delimiter. Using a backslash as the delimiter, your file name will always be the last item in the array, so we use Ubound to get the index of the last item.
Does that make sense?