Passing parameters in VBA events

Question

I am relatively new to Access though I do have some experience in VB, my question is probably very simple though I don't seem to know the terminology to search in order to find an answer I can use.

I am in the process of creating an "OnChange" event for a tab control I am using, I would like to pass an undetermined amount of integers to the function. IE: =myFunction(1,4,6) OR =myFunction(ArrayList[1,2,4])

I would either create an overloaded function to work with these numbers, or if possible I would like to pass them as an array of integers. Though for the life of me I cannot figure out exactly how to do this. The reason I have taken this path is to make my function as universal as possible, basically just having to change what numbers I send to the function to change its behaviour.

This is some rough coding of what I am try to do, though I have no idea how to pass anything besides something like =myFunction([Form])

Public Function Refresh(tabsToCheck As ArrayList)

    For Each o In tabsToCheck
        If Me.DevForm.Value = o Then
            RefreshAllForms
        End If
    Next o

End Function

Public Function RefreshAllForms()
    Dim f As Form
    For Each f In Access.Forms
        f.Refresh
    Next
End Function

---

Update

---

I thought I would update with my finalized code in case anyone needs this in the future thanks for your help!

 Public Function RefreshControlTab(ctrl As Access.Control, ParamArray TabsToRefresh())
    Dim i As Long
    Dim lngUBound As Long

    If UBound(TabsToRefresh) >= 0 Then
        lngUBound = UBound(TabsToRefresh)
        For i = 0 To lngUBound
            If ctrl.Value = (TabsToRefresh(i) - 1) Then
            RefreshAllForms
            End If
        Next
    End If
End Function


Public Function RefreshAllForms()
    Dim f As Form
    For Each f In Access.Forms
        f.Refresh
    Next
End Function

So one change you would say '=RefreshControlTab([DevForm],3,4)' and when the 3rd or 4th tab is selected a refresh will be performed.

Answer 1

"I would like to pass some an undetermined amount of integers to the function."

That sounds like a ParamArray to me. See the simple function below. It will return the sum of a set of numbers.

Public Function AddThem(ParamArray MyNumbers()) As Long
    Dim i As Long
    Dim lngReturn As Long
    Dim lngUBound As Long

    If UBound(MyNumbers) >= 0 Then
        lngUBound = UBound(MyNumbers)
        For i = 0 To lngUBound
            lngReturn = lngReturn + MyNumbers(i)
        Next
    End If
    AddThem = lngReturn
End Function

Note the ParamArray is an array of Variant values. So within the function you would need to verify the values are numbers to avoid trouble ... one example of trouble would be a "type mismatch" error when calling the function with string values: AddThem("a", "b")