CODEWITHSUNDEEP
excelvbanull
Tristian
Tristian

Reputation: 3512

VBA How to return null from a function

I have this function 

Public Function parseEmployee(ByVal employeeId As Integer, _ 
                              ByVal ws As Worksheet) As employee
    Dim emp As New employee
    Dim empRow As Range
    If sheetContainsEmployee(employeeId, ws) Then
        Set empRow = ws.Rows(ws.Columns(ID_COLUMN).Find(employeeId).Row)
        emp.id = employeeId
        emp.Name = empRow.Cells(1, NAME_COLUMN).Value
    Else
        emp = Null ' Heres what I'd like to do
    End If

    parseEmployee = emp
End Function

And I'd like to return null in case that the employee is not found in the sheet, is that possible? I get an object or variable nblock not set. error

Upvotes: 9

Views: 39631

Answers (3)

AKirby50
AKirby50

Reputation: 184

I have found that returning Nothing from a function causes an exception.

What I did instead was to write a sub that takes a ByRef parameter that is your return value.

Instead of this:

Public Function test() as SomeClass
     set test = Nothing
End Function

I wrote this:

Public Sub test(ByRef result as SomeClass)
     result = Nothing
End Sub

Upvotes: 0

Heidi
Heidi

Reputation: 1

just use """", or "", which one will work, you could try, as:

Else Set emp = """"

note that there is no space between

Upvotes: -1

Mitch Wheat
Mitch Wheat

Reputation: 300769

Only a Variant can be Null, instead use Nothing:

Public Function parseEmployee(ByVal employeeId As Integer, _  
                              ByVal ws As Worksheet) As employee 
    Dim emp As New employee 
    Dim empRow As Range 
    If sheetContainsEmployee(employeeId, ws) Then 
        Set empRow = ws.Rows(ws.Columns(ID_COLUMN).Find(employeeId).Row) 
        emp.id = employeeId 
        emp.Name = empRow.Cells(1, NAME_COLUMN).Value 
    Else 
        Set emp = Nothing
    End If 

    Set parseEmployee = emp
End Function

The way you would test:

  Dim emp As employee 
  Set emp = parseEmployee( ... )

  If emp Is Nothing Then
      Debug.Print "No employee returned."
  End If

Ref: Nothing? Empty? Missing? Null?

Upvotes: 20

Related Questions