Return in VBA, does not do what I expect

Question

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

Possible duplicate of [How to Return a result from a VBA Function](https://stackoverflow.com/questions/2781689/how-to-return-a-result-from-a-vba-function) — Zev Spitz, May 15 '18 at 16:42

score 6 · Accepted Answer · answered Jun 28 '12 at 08:20

6

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.

answered Jun 28 '12 at 08:20

html_programmer

18,126
18
85
158

Does this return after the assignment? 1. I don't want to loop after I'm done to save performance 2. I want to provide another value if loop doesn't find the value – Viktor Mellgren Jun 28 '12 at 08:34
`Function myFunction(ByVal j As Integer) As Double Return 3.87 * j End Function` is same as `Function myFunction(ByVal j As Integer) As Double myFunction = 3.87 * j Exit Function End Function` – Viktor Mellgren Jun 28 '12 at 08:39
+ 1 :) @Vixen: Replace `Return row` with `GetRowToWriteOn = row : Exit For` – Siddharth Rout Jun 28 '12 at 08:40
@SiddharthRout: Exit For will not give me the same result, see my answer: – Viktor Mellgren Jun 28 '12 at 08:46
if you like to return from a function before you reach the end, use: Exit Function – Daniel Jawna Oct 24 '14 at 13:55

score 0 · Answer 2 · answered Jun 28 '12 at 08:48

0

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

answered Jun 28 '12 at 08:48

Viktor Mellgren

4,318
3
42
75

Further to my previous comment, tt depends on what you want. You can put `Exit For` as I suggested and then add this line `If GetRowToWriteOn = 0 Then GetRowToWriteOn = UBound(myarray, 1) + 1` just before `End Function` And yes, you do not need the `Exit Function` in the end as you rightly guessed :) – Siddharth Rout Jun 28 '12 at 08:53
Yeah, I know i could exit for and then do cheking, but this way I don't make that extra conditional, also it behaves more like a return I'm used to. – Viktor Mellgren Jun 28 '12 at 12:25

Return in VBA, does not do what I expect

2 Answers2

Linked