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?
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?