Function/Subroutine Pointer Parameter

pjm · July 21st, 2006, 11:01 AM

I would like to pass a pointer to a function/subroutine written in VBA (I know you can pass one to a builtin DLL routine) and run it with data specified within the calling routine.

Let me flesh it out a bit more for you with the following code:

Public Sub DoIt(TableName As String, Optional CodeStub As Integer = 1)
'== Skeleton code for processing all records in a table

Dim db As Database
Dim rs As Recordset

Dim nRecords As Long, r As Long

    If CodeStub < 1 Or CodeStub > 9 Then Exit Sub

    Set db = CurrentDb()
    Set rs = db.OpenRecordset(TableName, dbOpenDynaset)

    rs.MoveLast
    nRecords = rs.RecordCount
    rs.MoveFirst

    For r = 1 To nRecords
        rs.Edit

        Select Case CodeStub
            Case 1
                GenericCodeStub1 rs, r
            Case 2
                GenericCodeStub2 rs, r
            Case 3
                GenericCodeStub3 rs, r
            Case 4
                GenericCodeStub4 rs, r
            Case 5
                GenericCodeStub5 rs, r
            Case 6
                GenericCodeStub6 rs, r
            Case 7
                GenericCodeStub7 rs, r
            Case 8
                GenericCodeStub8 rs, r
            Case 9
                GenericCodeStub9 rs, r
        End Select

        rs.Update
        rs.MoveNext
    Next r

    rs.Close
    db.Close

End Sub

Public Sub GenericCodeStub1(rs As Recordset, recnum As Long)
'== Code to process individual records of a table
End Sub

Public Sub GenericCodeStub2(rs As Recordset, recnum As Long)
'== Code to process individual records of a table
End Sub
...

Public Sub GenericCodeStub9(rs As Recordset, recnum As Long)
'== Code to process individual records of a table
End Sub

As far as it goes this works fine. I can simply place code to focus on one record at a time in one of the GenericCodeStub? routines and call Doit with the appropriate number. Nevertheless, this is not a very elegant (or totally practical) solution.

Does anybody have some insight into this or is this just not possible in VBA?

-Phil-

pjm · October 2nd, 2006, 01:16 PM

I have discovered the Eval function! This enables me to solve my problem with the following code:

Public rsGeneric As Recordset
Public nGeneric As Long

Public Sub DoIt(TableName As String, CodeStub As String)
' Assumes that the function CodeStub is defined as follows:
'
' Public Function <CodeStub name> () as Boolean
' ...
' <CodeStub name> = True
' End Function
'
' and that it uses the 2 global variables:
' rsGeneric - recordset pointer for the table
' nGeneric - row number

Dim db As Database

Dim nRecords As Long

    If Not TableExists(TableName) Then Exit Sub
    If (CodeStub & "") = "" Then Exit Sub

    Set db = CurrentDb()
    Set rsGeneric = db.OpenRecordset(TableName, dbOpenDynaset)

    rsGeneric.MoveLast
    nRecords = rsGeneric.RecordCount
    rsGeneric.MoveFirst

    For nGeneric = 1 To nRecords
        rsGeneric.Edit
        Eval (CodeStub & "()") ' The return value of the function is not used

        rsGeneric.Update
        rsGeneric.MoveNext
    Next nGeneric

    rsGeneric.Close
    db.Close

End Sub

I'm not 100% happy with having to use global variables, but the eval function doesn't seem to completely handle parameters. Note that I haven't included the definition of the TabeleExists function, but that was very simple and is left as an exercise.

-Phil-

Bob Bedell · October 2nd, 2006, 10:40 PM

Hi Phil,

You could solve your scope problem and your parameter passing problem in one shot by using CallByName().

Code:

Private Sub Command0_Click()
    Call DoIt("tblRecords", "GenericCodeStub1")
End Sub

Sub DoIt(TableName As String, CodeStub As String)

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim nRecords As Long, r As Long

    Set db = CurrentDb()
    Set rs = db.OpenRecordset(TableName, dbOpenDynaset)

    rs.MoveLast
    nRecords = rs.RecordCount
    rs.MoveFirst

    For r = 1 To nRecords
        rs.Edit
        Call CallByName(Me, CodeStub, VbMethod, rs, r)
        rs.Update
        rs.MoveNext
    Next r

    rs.Close
    db.Close
End Sub

Public Sub GenericCodeStub1(rs As DAO.Recordset, recnum As Long)
'== Code to process individual records of a table
End Sub

CallByName()'s last argument is a paramarray, so you can pass as many parameters as you like. Only catch is the first argument of CallByName() takes an object reference, so it can only be used in some type of class module (an Access form will do). You can't use it in standard modules.

If form some reason you don't want your DoIt method in a form module, create a custom class and call DoIt with something like:

Code:

 ' Instantiate class
 Dim objMyObject As clsMyClass
 Set objMyObject = clsMyClass

 ' Call DoIt()
 Call objMyObject.DoIt("tblRecords", "GenericCodeStub1")

CallByName() would then have its object reference. Only catch is all your CodeStub methods in clsMyClass would need to be declared with public scope so you'd loose encapsulation. They'd all be exposed. Which maybe isn't a problem. But CallByName() can only see public class members.

HTH,

Bob

pjm · October 3rd, 2006, 09:07 AM

Even better!

Eventually I intend to place DOIT in a library db (along with some other useful code that I've developed) so that I can stop all the copy/paste when I create a new db.

-Phil-