Function/Subroutine Pointer Parameter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.

I would really like to do away with that case statement and just pass a
reference to a subroutine (or function) instead of the integer CodeStub. Then
within DOIT I can call the subroutine (which will be defined with the same
parameters as the GenericCodeStub routines).

Does anybody have some insight into this or is this just not possible in VBA?
 
Phil said:
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.

I would really like to do away with that case statement and just pass a
reference to a subroutine (or function) instead of the integer CodeStub. Then
within DOIT I can call the subroutine (which will be defined with the same
parameters as the GenericCodeStub routines).
Click to expand...


Check the Run method in VBA Help. I think it might be at
least part of what you are looking for here.

While you're in Help check the Eval funtion. I don't think
Eval will be appropriate for this problem, but you should be
aware of its capabilities.
 
Back
Top