|
Subject:
|
Function/Subroutine Pointer Parameter
|
|
Posted By:
|
pjm
|
Post Date:
|
7/21/2006 11:01:31 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-
|
|
Reply By:
|
pjm
|
Reply Date:
|
10/2/2006 1:16:45 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-
|
|
Reply By:
|
Bob Bedell
|
Reply Date:
|
10/2/2006 10:40:16 PM
|
Hi Phil,
You could solve your scope problem and your parameter passing problem in one shot by using CallByName().
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:
' 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
|
|
Reply By:
|
pjm
|
Reply Date:
|
10/3/2006 9:07:30 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-
|
