Checking if Worksheet variable is inizialized

hammaway

New Member
Joined
Jun 30, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
I am trying to figure out a way to check if a Worksheet variable has been inizialized/assigned.
I am returning an object Worksheet from a function, and I need to handle errors.
What happens is that if an error occurs, the worksheet is not assigned and the variable that is supposed to get the value when calling the function raises an error "Object variable or With block variable not set"

VBA Code:
Dim readSheet As Worksheet
readSheet = getWorkSheet(fileName)



VBA Code:
Function getWorkSheet(path As String) As Worksheet
    Dim wb As Workbook
    
    On Error GoTo handleOpen
    Set wb = Workbooks.Open(path, ReadOnly = False)
    getWorkSheet = wb.Worksheets(1)
    wb.Close savechanges = False
    Exit Function

handleOpen:
    wb.Close savechanges = False
    'I need to handle the getWorksheet variable not inizialized here
    
End Function
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,039
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
I am surprised that even runs as there are a number of errors, try
VBA Code:
Function getWorkSheet(path As String) As Worksheet
    Dim wb As Workbook
    
    On Error GoTo handleOpen
    Set wb = Workbooks.Open(path, ReadOnly:=False)
    Set getWorkSheet = wb.Worksheets(1)
    wb.Close savechanges:=False
    Exit Function

handleOpen:
    wb.Close savechanges:=False
    'I need to handle the getWorksheet variable not inizialized here
    
End Function
and
VBA Code:
        Dim readSheet As Worksheet
Set readSheet = getWorkSheet(fileName)
 

hammaway

New Member
Joined
Jun 30, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Hi & welcome to MrExcel.
I am surprised that even runs as there are a number of errors, try
VBA Code:
Function getWorkSheet(path As String) As Worksheet
    Dim wb As Workbook
   
    On Error GoTo handleOpen
    Set wb = Workbooks.Open(path, ReadOnly:=False)
    Set getWorkSheet = wb.Worksheets(1)
    wb.Close savechanges:=False
    Exit Function

handleOpen:
    wb.Close savechanges:=False
    'I need to handle the getWorksheet variable not inizialized here
   
End Function
and
VBA Code:
        Dim readSheet As Worksheet
Set readSheet = getWorkSheet(fileName)

Hi, thanks for the support.
Any advice on how to handle non initialised Worksheet variables?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,828
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Your function isn't going to do you a lot of a good as you can't use a worksheet variable if the parent workbook is closed.
 

hammaway

New Member
Joined
Jun 30, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Your function isn't going to do you a lot of a good as you can't use a worksheet variable if the parent workbook is closed.
I agree. I am asking how do I check if the variable has been initialized or not (= parent workbook opened and worksheet variable assigned) so I can avoid trying to manupulate a variable that doesn't exist.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,828
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
For a case like this you have to use an error handler. But why have you written a function that can't possibly ever work?
 

Forum statistics

Threads
1,148,327
Messages
5,746,128
Members
423,994
Latest member
blzxatly

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top