Using a function in a worksheet

[tiredofit]

This adds two numbers:

Function AddTwoNumbers(ws As Worksheet, rng1 As String, rng2 As String) As Integer
    
    AddTwoNumbers = ws.Range(rng1) + ws.Range(rng2)
    
End Function

and I can use it as follows:

Sub Add()


    Dim Result
    
    Result = AddTwoNumbers(Sheet2, "a1", "a2")
    
End Sub

but if I try to use it in a cell on a worksheet, such as:

=AddTwoNumbers(Worksheets("Sheet2"),"a1","a2")

it fails.


How can I adapt it?


Thanks

 

[dave2018]

I don't think an excel sheet can use the worksheet object...

try this:

Function AddTwoNumbers(ws As String, rng1 As String, rng2 As String) As Integer
    
    AddTwoNumbers = Sheets(ws).Range(rng1) + Sheets(ws).Range(rng2)
    
End Function

 

[tiredofit]

Thanks

 

[dave2018]

np. you might want to put in some error-checking in case the sheet doesnt exist.

Function AddTwoNumbers(ws As String, rng1 As String, rng2 As String)
    
    Dim WorksheetExists As Boolean
    Dim sht As Worksheet
    
    On Error Resume Next
    Set sht = Sheets(ws)
    On Error GoTo 0
    WorksheetExists = Not sht Is Nothing
    
    If WorksheetExists = True Then
        AddTwoNumbers = Sheets(ws).Range(rng1) + Sheets(ws).Range(rng2)
    Else
        AddTwoNumbers = "Sheet doesn't exist."
    End If
    
    
End Function

and maybe also for the ranges...