CODEWITHSUNDEEP
excelvba
Sai Grr
Sai Grr

Reputation: 23

I'm trying to convert this for loop into a function

My problem is the fact that I don't know what functions (in C++) or methods (in Java) are called in VBA.

They both exist and so I have looked up subs? Which I think are the parallel, but they don't return anything from what I understand.

I don't have any formal training in VBA, so I'm kind of bad at this. I want to create a function (or method or sub) that does what this for loop is doing:

useInput = titles.Text
useRec = rec.Text
lastRow = 1

For Each rngCell In rngData.Columns(1).Cells 'Traverse each row in Spreadsheet
If useInput = rngCell.Value And LCase(useRec) = LCase(Cells(lastRow, 2)) Then 'If what the user input into the textbox matches a value in the spreadsheet
    bothExist = True 'Set this to true, because both exist

    otherUserForm.Show 'Call another userform created

    titles = "" 'Set entries to null
    rec = ""
    Exit For 'Exit the for loop
    End If
    lastRow = lastRow + 1 'Counter
Next rngCell

So the whole point of the loop is to just traverse a spreadsheet and find the last row to enter information, however find if the titles and rec are pre existing, then it does what is shown.

My question is, what do I need to create so it returns, "lastRow?" Because I need whatever that number is.

Upvotes: 0

Views: 103

Answers (2)

Ken White
Ken White

Reputation: 125749

Functions in VBA are just that: functions. :-) The return syntax is strange, though, if you're used to Java or C/C++ (it's more old-style Pascal).

Public Function GetLastRow()
   `Logic to determine last row here
   GetLastRow = lastRow    ` Assignment to function name sets return value
End Function

Upvotes: 1

Floris
Floris

Reputation: 46435

Function myFunctionName()
    ' do stuff
    myFunctionName = "hello world"
End Function

You assign a return value to a function by assigning it to the name of the function. You can call it like this:

Sub myTest()
theReply = myFunctionName()
msgBox "the function returned " & theReply
End Sub

Functions return values, subs don't.

Upvotes: 1

Related Questions