Some Useful Functions for Use in Your Code | Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

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 are declared by using the Private keyword and thus will not appear in Excel's Insert Function dialog box.

CD-ROM

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 i As Integer, length As Integer, temp As String     Dim Cnt As Integer '   Count the path separator characters     Cnt = 0     For i = 1 To Len(pname)         If Mid(pname, i, 1) = Application.PathSeparator Then             Cnt = Cnt + 1         End If     Next i     FileNameOnly = Split(pname, Application.PathSeparator, Cnt) End Function

If the argument is c:\excel files\2007\backup\budget.xls , the function returns the string budget.xls .

The FileNameOnly function works with any path and filename (even if the file does not exist). If the file exists, the following function is a simpler way to strip off the path and return only the filename.

 Private Function FileNameOnly2(pname) As String     FileNameOnly2 = Dir(pname) End Function

The PathExists function

This function accepts one argument (a path) and returns True if the path exists:

 Private Function PathExists(pname) As Boolean '   Returns TRUE if the path exists   If Dir(pname, vbDirectory) = "" Then     PathExists = False   Else     PathExists = (GetAttr(pname) And vbDirectory) = vbDirectory   End If End Function

The RangeNameExists function

This function accepts a single argument (a range name ) and returns True if the range name exists in the active workbook:

 Private Function RangeNameExists(nname) As Boolean '   Returns TRUE if the range name exists     Dim n As Name     RangeNameExists = False     For Each n In ActiveWorkbook.Names         If UCase(n.Name) = UCase(nname) Then            RangeNameExists = True            Exit Function         End If     Next n End Function

Another way to write this function follows . This version attempts to create an object variable using the name. If doing so generates an error, then the name does not exist.

 Private Function RangeNameExists2(nname) As Boolean '   Returns TRUE if the range name exists     Dim n As Range     On Error Resume Next     Set n = Range(nname)     If Err.Number = 0 Then RangeNameExists2 = True _         Else RangeNameExists2 = False End Function

The SheetExists function

This function accepts one argument (a worksheet name) and returns True if the worksheet exists in the active workbook:

 Private Function SheetExists(sname) As Boolean '   Returns TRUE if sheet exists in the active workbook     Dim  As Object     On Error Resume Next     Set  = ActiveWorkbook.Sheets(sname)     If Err.Number = 0 Then SheetExists = True _         Else SheetExists = False End Function

The WorkbookIsOpen function

This function accepts one argument (a workbook name) and returns True if the workbook is open :

 Private Function WorkbookIsOpen(wbname) As Boolean '   Returns TRUE if the workbook is open     Dim  As Workbook     On Error Resume Next     Set  = Workbooks(wbname)     If Err.Number = 0 Then WorkbookIsOpen = True _         Else WorkbookIsOpen = False End Function

Retrieving a value from a closed workbook

VBA does not include a method to retrieve a value from a closed workbook file. You can, however, take advantage of Excel's ability to work with linked files. This section contains a custom VBA function ( GetValue , which follows) that retrieves a value from a closed workbook. It does so by calling an XLM macro, which is an old-style macro used in versions prior to Excel 5. Fortunately, Excel still supports this old macro system.

 Private Function GetValue(path, file, sheet, ref) '   Retrieves a value from a closed workbook     Dim arg As String '   Make sure the file exists     If Right(path, 1) <> "\" Then path = path & "\"     If Dir(path & file) = "" Then         GetValue = "File Not Found"         Exit Function End If '   Create the argument     arg = "'" & path & "[" & file & "]" & sheet & "'!" & _         Range(ref).Range("A1").Address(, , xlR1C1) '   Execute an XLM macro     GetValue = ExecuteExcel4Macro(arg) End Function

Testing for Membership in a Collection

The following function procedure is a generic function that you can use to determine whether an object is a member of a collection:

 Private Function IsInCollection(Coln As Object, _   Item As String) As Boolean     Dim Obj As Object     On Error Resume Next     Set Obj = Coln(Item)     IsInCollection = Not Obj Is Nothing End Function

This function accepts two arguments: the collection (an object) and the item (a string) that might or might not be a member of the collection. The function attempts to create an object variable that represents the item in the collection. If the attempt is successful, the function returns True ; otherwise , it returns False .

You can use the IsInCollection function in place of three other functions listed in this chapter: RangeNameExists , SheetExists , and WorkbookIsOpen . To determine whether a range named Data exists in the active workbook, call the IsInCollection function with this statement:

 MsgBox IsInCollection(ActiveWorkbook.Names, "Data")

To determine whether a workbook named Budge t is open, use this statement:

 MsgBox IsInCollection(Workbooks, "budget.xlsx")

To determine whether the active workbook contains a sheet named Sheet1 , use this statement.

 MsgBox IsInCollection(ActiveWorkbook.Worksheets, "Sheet1")

The GetValue function takes four arguments:

path : The drive and path to the closed file (for example, "d:\files")
file : The workbook name (for example, "budget.xlsx")
sheet : The worksheet name (for example, "Sheet1")
ref : The cell reference (for example, "C4")

The following Sub procedure demonstrates how to use the GetValue function. It simply displays the value in cell A1 in Sheet1 of a file named 2007budget.xlsx , located in the XLFiles\Budget directory on drive C.

 Sub TestGetValue()     Dim p As String, f As String     Dim s As String, a As String     p = "c:\XLFiles\Budget"     f = "2007budget.xlsx"     s = "Sheet1"     a = "A1"     MsgBox GetValue(p, f, s, a) End Sub

Another example follows. This procedure reads 1,200 values (100 rows and 12 columns ) from a closed file and then places the values into the active worksheet.

 Sub TestGetValue2()     Dim p As String, f As String     Dim s As String, a As String     Dim r As Long, c As Long     p = "c:\XLFiles\Budget"     f = "2007Budget.xlsx"     s = "Sheet1"     Application.ScreenUpdating = False     For r = 1 To 100         For c = 1 To 12             a = Cells(r, c).Address             Cells(r, c) = GetValue(p, f, s, a)         Next c     Next r End Sub

Note	The `GetValue` function does not work if used in a worksheet formula. Actually, there is no need to use this function in a formula. You can simply create a link formula to retrieve a value from a closed file.

CD-ROM

This example is available on the companion CD-ROM. The file is named value from a closed workbook.xlsm . The example uses a file named myworkbook.xlsx for the closed file.