CODEWITHSUNDEEP
vbaexcelreturn
Viktor Mellgren
Viktor Mellgren

Reputation: 4506

Return in VBA, does not do what I expect

Public Function GetRowToWriteOn(ByVal SheetName As String, ByVal id As Integer) As Integer
    LastRow = ActiveSheet.UsedRange.Rows.Count
    myarray = Sheets(SheetName).Range("d7:d" & LastRow).Value
    For row = 1 To UBound(myarray, 1)
        If (myarray(row, 1) = id) Then
            Return row
        End If
    Next
End Function

The IDE says expected end of statement, how do I do what I want? (Return the row where id is the same?)

I'm not familiar at all with VBA, but when I look this example from microsoft this should work? :

The Return statement simultaneously assigns the return value and exits the function. The following example shows this.

Function myFunction(ByVal j As Integer) As Double
   Return 3.87 * j
End Function

Upvotes: 1

Views: 9386

Answers (2)

Viktor Mellgren
Viktor Mellgren

Reputation: 4506

Public Function GetRowToWriteOn(ByVal SheetName As String, ByVal idnr As Integer) As Integer
    LastRow = ActiveSheet.UsedRange.Rows.Count
    myarray = Sheets(SheetName).Range("d7:d" & LastRow).Value
    For row = 1 To UBound(myarray, 1)
        If (myarray(row, 1) = idnr) Then
            GetRowToWriteOn = row
            Exit Function
        End If
    Next
    GetRowToWriteOn = UBound(myarray, 1) + 1
    Exit Function 'Probably not needed :)
End Function

Upvotes: 0

Trace
Trace

Reputation: 18889

In VBA, returning a value is not done through the return keyword as it is custom in other languages. Try: 

GetRowToWriteOn = row

The function name acts as a variable on its own.

Upvotes: 6

Related Questions