VBA return methods in Access

Question

I am unsure as to how I can achieve a method which returns a string in vba. In c# for instance I can call a method like;

public string ReturnString()
{
    return "Hi";
}

However, if I want to call code in VBA which will also return a string, how do I achieve it? For example if I want my onclick event to return a string of emails;

Public Function EmailAll() As String
    Dim employeeSQL As String
    Dim employeeRS As DAO.Recordset

    'Define SQL to loop
    employeeSQL = "SELECT * FROM Employees"
    Set employeeRS = CurrentDb.OpenRecordset(employeeSQL)

    If Not employeeRS.BOF And Not employeeRS.EOF Then
        employeeRS.MoveFirst
        While (Not employeeRS.EOF)
            If Nz(employeeRS.Fields("email"), "") <> "" Then
                'Fields to return
                EmailAll = EmailAll & email & ";"
            End If
            employeeRS.MoveNext
        Wend
    End If

    employeeRS.Close
    Set employeeRS = Nothing
End Function

https://stackoverflow.com/questions/2781689/how-to-return-a-result-from-a-vba-function This should work for you. — SKS, Jan 27 '20 at 14:22
Putting aside that events don't return anything, what is the problem with your code? It should return the value correctly. VB(A) functions use the function name as return variable. — Vincent G, Jan 27 '20 at 14:25
Reading through the link SK provided; all that I needed to add was a string variable to the sub that was calling the function. Ie "Dim i As String i = EmailAll()" — Shadyjunior, Jan 27 '20 at 14:29
`Dim i as String`... uggh maybe consider a different variable name. — BigBen, Jan 27 '20 at 14:30
What you have looks fine. As noted, in VBA you can't go dim myStr as string = "124", you have to use two lines of code - but your approach should work fine, and how you return or get a value from a class written in VBA, or c# is the same. And how you get a value from a function in c# or VBA is again much the same - you simply assign the function name to some variable. — Albert D. Kallal, Jan 27 '20 at 23:05

score 0 · Answer 1 · answered Jan 27 '20 at 14:27

0

When using functions in VBA the value of the variable with the same name as your function is what is returned.

For example, the code below will return "Returned Value":

Public Function Testing() As String
    Testing = "Returned Value"
End Function

answered Jan 27 '20 at 14:27

Taazar

1,545
18
27

VBA return methods in Access

1 Answers1