Use Excel/Access VBA to check if sheet exists

Does a worksheet exist? Here is a simple way to test, so you can use it in your VBA or macro for Microsoft Excel or Access.
Put this function somewhere in your modules so that it can be used in your Sub():


Function SheetExists(wksName As String, wbk As Excel.Workbook) As Boolean
SheetExists = False
On Error GoTo thereisnosheet
'If the sheet isn't there, this IF will throw an error and be caught
If Len(Sheets(wksName).Name) > 0 Then
    SheetExists = True
    Exit Function
End If
thereisnosheet:
End Function

To check if a sheet exists on your declared workbook:

If SheetExists(c & "_O", wbk) = False Then

   'do some cool stuff

Else

   'Do something different - maybe create the sheet?

End If

Notice that you need to be working with a workbook already, such as by opening one like this:

Dim wbk As Excel.Workbook

Set wbk = Excel.Workbooks.Open("U:\Excel analysis\Weekday profiles.xlsm")

Excel.Application.Visible = True

Runtime error 1004 : Unable to get the Average property of the WorksheetFunction class

Runtime error 1004 : Unable to get the Average property of the WorksheetFunction class

This is an easy one. I was getting a runtime error 1004 when I was using WorksheetFunctions on Excel 2007 with certain spreadsheets/workbooks on Sharepoint.

The code:
'Put the average into a variable
'yearRange is a string containing a range address like "E77:BI77"
calculatedAvg = Application.WorksheetFunction.Average(Sheets("Supply").Range(yearRange))

As it turns out the problem wasn't with the code or syntax, but the contains of the target range. If there are any non-numeric items in there (like text, #N/A, #DIV/0, or a "" from an IF statement in my case) it won't work and throws an error.

I changed my IF statements to return 0s instead of "" and now it works!