Microsoft Office Tutorials and References
In Depth Information
Some Useful Functions for Use in Your Code
Some Useful Functions for Use in Your Code
In this section, I present some custom utility functions that you may find useful in your own
applications and that may provide inspiration for creating similar functions. These functions are most
useful when called from another VBA procedure. Therefore, they’re declared by using the
Private keyword and thus won’t appear in Excel’s Insert Function dialog box.
The examples in this section are available on the companion CD-ROM. The file is named
VBA utility functions.xlsm .
The FileExists function
This function takes one argument (a path with filename) and returns True if the file exists:
Private Function FileExists(fname) As Boolean
‘ Returns TRUE if the file exists
FileExists = (Dir(fname) <> “”)
End Function
The FileNameOnly function
This function accepts one argument (a path with filename) and returns only the filename. In other
words, it strips out the path.
Private Function FileNameOnly(pname) As String
‘ Returns the filename from a path/filename string
Dim temp As Variant
length = Len(pname)
temp = Split(pname, Application.PathSeparator)
FileNameOnly = temp(UBound(temp))
End Function
The function uses the VBA Split function, which accepts a string (that includes delimiter
characters), and returns a variant array that contains the elements between the delimiter characters.
In this case the temp variable contains an array that consists of each text string between the
Application.PathSeparater (usually a backslash character). For another example of the
Split function, see “Extracting the nth element from a string,” later in this chapter.
If the argument is c:\excel files\2010\backup\budget.xlsx , the function returns the
string budget.xlsx .
Search JabSto ::

Custom Search