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

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

       length = Len(pname)

       temp = ""

       For i = length To 1 Step -1

           If Mid(pname, i, 1) = Application.PathSeparator Then

               FileNameOnly = temp

               Exit Function

           End If

           temp = Mid(pname, i, 1) & temp

       Next i

       FileNameOnly = pname

End Function

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

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 x As Object

       On Error Resume Next

       Set x = ActiveWorkbook.Sheets(sname)

       If Err = 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 x As Workbook

       On Error Resume Next

       Set x = Workbooks(wbname)

       If Err = 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 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.

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 Budget is open, use this statement:

MsgBox IsInCollection(Workbooks, "budget.xls")

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

MsgBox IsInCollection(ActiveWorkbook.Worksheets, "Sheet1")

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

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.xls")

§                    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 99Budget.xls, located in the XLFiles\Budget directory on drive C.

Sub TestGetValue()

       p = "c:\XLFiles\Budget"

       f = "99Budget.xls"

       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()

       p = "c:\XLFiles\Budget"

       f = "99Budget.xls"

       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

       Application.ScreenUpdating = True

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.