If Object is nothign Function

[JPARKHURST]

I would like to have a function which takes in a variable (say, worksheet), and determines if that worksheet is populated.

This is what I had, and I thought it was working fine, but i was wrong.

My purpose is to have a 'checker' which lets me know if I have already created an object, and if so if it is populated? I am trying to be able to create worksheet objects which I can use across my project (all within one workbook). I would much rather do this than have 10 objects with different names because I want to access a sheet in 3 different modules.

"Object Variable or with block not set"

Public Sub CnSwS(wb101 As Workbook, ws101 As Worksheet, sws101 As String)
  'Check to see if Worksheet variables have been established, and if not, establish them!
    If ws101 Is Nothing Then
        Set ws101 = wb101.Sheets(sws101)
    End If
End Sub

 

[Jerry Sullivan]

Hi JPARKHURST,

When you say you want to check if that worksheet is populated, are you checking if that sheet exists, or if it contains data?

Assuming that you just want to check if a sheet exists, you could use this simple function that returns True.

Function SheetExists(sWorkbookName As String, sSheetName As String) As Boolean
 On Error Resume Next
 SheetExists = Workbooks(sWorkbookName).Sheets(sSheetName).Index > 0
End Function

The function can be called as in this example...

Sub Test()
 If SheetExists(sWorkbookName:="MyBook1.xlsx", sSheetName:="Summary") Then
   MsgBox "Found"
 Else
   MsgBox "Not found"
 End If

End Sub

The code assumes the workbook is open in the application. If a workbook with that name is closed, the function will return False.

 

[JPARKHURST]

Thanks for the reply!

What I was/am wondering isn't about the sheet itself (these sheets are stable), but if an object has been created and set.

So, I have ws1 as worksheet

- Has it been declared? The answer to this one is yes, from what I have read. Any declared variable is declared at the opening of the document (or firing of the macro script, i'm assuming those two are connected.

- Has ws1 been set? This is the question I now wish to ask. I guess I could test ws1.name and it will error, giving me an answer. Is there a more elegant one?

Thanks for your help!

Jon

Hi JPARKHURST,

When you say you want to check if that worksheet is populated, are you checking if that sheet exists, or if it contains data?

Assuming that you just want to check if a sheet exists, you could use this simple function that returns True.

The code assumes the workbook is open in the application. If a workbook with that name is closed, the function will return False.

 

[Jerry Sullivan]

Thanks for clarifying your question.

Has it been declared?: One can certainly write code using variables that have not been declared. This one-line Sub will generate a Run-time error 424- Object required.

Sub Test2()
 MsgBox ws1.Name  
End Sub

IMO, the best way to avoid this error is to put an Option Explicit statement at the top of each code module, and then compile the VBA project before saving your file. That will allow you to find and correct this type of error at Design time instead of having a Runtime error arise.

Has ws1 been set? The simplest test for whether an object has been assigned to a variable is to test if the Variable Is Nothing..

Sub Test3()
 Dim ws1 As Worksheet
 
 If ws1 Is Nothing Then
   MsgBox "no object assigned to ws1. assigning it now."
   Set ws1 = ActiveWorkbook.Worksheets(1)
 End If
   
 MsgBox ws1.Name
 
End Sub

While the code above will check whether an object has been assigned to a variable, it doesn't guarantee that the assigned object is valid. In Test4 below, ws1 is successfully mapped to the Worksheet(1). After the worksheet is deleted, ws1 still has an object assigned- but it's an invalid object that generates an error when the code tries to read its Name property.

***Test4 and Test5 below will delete the first worksheet in your activeworkbook. Before running them add a few blank sheets at the front of your active workbook so you don't delete a sheet with data.

Sub Test4()
 Dim ws1 As Worksheet
 
 Set ws1 = ActiveWorkbook.Worksheets(1)
 
 '--delete first worksheet
 Application.DisplayAlerts = False
 ActiveWorkbook.Worksheets(1).Delete
 Application.DisplayAlerts = True
 
 If ws1 Is Nothing Then
   MsgBox "no object assigned to ws1. assigning it now."
   Set ws1 = ActiveWorkbook.Worksheets(1)
 Else
   MsgBox "ws1 had object assigned"
 End If
     
 MsgBox ws1.Name 'will generate a run-time error
 
End Sub

If needed, you could use a function to check whether ws1 is valid by checking that it has a Name property.

Function IsValidWorksheet(ws As Worksheet) As Boolean
 On Error Resume Next
 IsValidWorksheet = ws.Name = ws.Name
End Function

Sub Test5()
 Dim ws1 As Worksheet
 
 Set ws1 = ActiveWorkbook.Worksheets(1)
 
 '--delete first worksheet
 Application.DisplayAlerts = False
 ActiveWorkbook.Worksheets(1).Delete
 Application.DisplayAlerts = True
 
 If IsValidWorksheet(ws:=ws1) Then
   MsgBox "ws1 had valid object assigned"
 Else
   MsgBox "no valid object assigned to ws1. assigning it now."
   Set ws1 = ActiveWorkbook.Worksheets(1)
 End If
     
 MsgBox ws1.Name
 
End Sub

 

[JPARKHURST]

That's a great reply. Thank you for explaining and giving a few examples.