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)

Please Login or Register  to view this content.

using XL2000

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

Please Login or Register  to view this content.

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?