Results 1 to 4 of 4

Calling a Function in a Procedure

Threaded View

  1. #1
    Forum Contributor
    Join Date
    08-14-2006
    Location
    USA
    MS-Off Ver
    2019
    Posts
    686

    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
    Last edited by carsto; 02-02-2010 at 03:28 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1