calling a VBA procedure whose name is contained in a variable

[ak_ls]

My Access adp application generates vba scripts (reading Excel worksheet and updating SQL server tables) and adds the generated scripts to a Module. The procedure names are stored in a table. I need to call these procedures on demand.

I could easily do this in Visual Foxpro using foxpro's & command. C/C++ provides pointers do the same thing.

Is there a way in VBA to acheive this functionality?

Not knowing how to do it, I am using lengthy select case statement for matching procedure names and than calling the procedure. Every time I create a new script, I have to modify the calling procedure's select case statement.

Thanks
Ashok

 

[gemma-the-husky]

look at the exec function

you have to generate a string from your function name (possibly procedure - i'm not sure offhand), including any arguments and pass that via the exec function

 

[LPurvis]

EXEC would be a good choice in T-SQL ;-)
I think Gemma's meaning Eval - VBA's expression (and hence function) evaluating function.
However I'd suggest Run to be your best bet. (It's a method of the Access application object - indeed all Office applications' object models offer it, don't faint at the Office-wide consistency anyone! ;-)

Cheers.

 

[ak_ls]

Thanks All

Application.Run seems to meet my current needs. The procedure or function mentioned by me is in a Module and not a stored procedure in SQL Server.

However, I am struggling with another requirement where the user enters the VBA code in a text box on Access Form and this code is to be executed. This VBA code may be just an expression using allowed VBA variables or part / full procedure or a function. And finally to merge the VBA code with a procedure or function at run time. I require this functionality to write a generic procedure whose code changes at run time depending upon certain settings and input by a user.

Any idea to meet this requirement?

Ashok

 

[LPurvis]

Changing source code at runtime?
Can you feel the shivers running down my back from there?

Creating new procedures - sure, possible.
Even in an MDE - you could use code to create a new MDB, add a procedure and then call that new external application and its procedure.
But internal manipulation of code is a) scary and b) impossible in a MDE distribution.

Scary? What happens if your application crashes mid-write. You're potentially left with inconsistent code - or just a crippled (corrupted) application altogether.
OK that's a worse case scenario - but I can't envisage a situation where I'd leave the user an avenue to write VBA code which would be incorporated as part of the application.
If they were advanced enough to write good VBA code then just let them at the modules.
(And it does need to be good code - because you're stuffing it into your own modules and executing it - is it sanitised in any way to make sure it's not malicious or just bad enough to be damaging? What's to stop them issuing commands like
CurrentDb.Execute "DROP Table " & CurrentDb(0).Name
not even because they're evil - but because "someone" told them to :-s)

If they aren't good enough to edit source code directly - I can't imagine why to consider including their code in your own.

If you absolutely must do this - then the external MDB is all I'd consider.
But if you want to elaborate on what you're actually needing to acomplish (rather than the means by which you intend to accomplish it) then perhaps an alternative can be offered.

Cheers.

 

[gemma-the-husky]

lpurvis

good spot

yes, i did mean eval function

i have started using it, and while its fiddly to use, it does work

is run similar in operation? i will investigate this suggestion.

 

[LPurvis]

Hi Gemma.

Eval is a great thing. Just use it with careful testing first.
Expression evaluation in Access (for Eval is a method of Access only) is a versatile and potentially inconsistent affair.
Run will only execute the specified procedure (returning appropraite values from a function too) but is limited only to procedures.
Eval is more flexible - many expressions can be interpreted by it. You can even (to a limited extent) include a call to Eval within an Eval function - you just need to careful construct your expressions.
As an interesting tale (come caveat) I've know an expression pasted into the Immediate window for eval to execute fail with error - and yet typing it identically worked. (It was weird!)
Using Eval to determine expression in forms can result in the expression being called more than one when only one call would be expected. Not a problem for expression evaluation - but in function calling that can be inefficient and potentially cause erroneous results.

However there are times when Eval is just vital.
When referring to unbound controls in a query - the expression service sometimes has problems determining data type. Declaring the parameter and specifying its type can help - but if not then wrapping that form expression in Eval is the solution.
Run won't help there at all.
And of course it's often used in such parameter evaluation for use in queries executed in the UI or in a recordset.

However when attemping to access form based code procedures - rather than a convoluted Eval expression, you're usually better off using the third option - CallByName.

Happy playing!
Cheers.

 

[DCrake]

A number of years age I was an accreditied Sage developer / reseller. When we wrote Access applications for clients who wanted to manipulate hooks we had written between Access and Sage it was necessary to establish which version of Sage they were using as Access needed to it to be referenced. In our modules when defining the Sage objects you had to enter the version number. This could not be done using variables, it had to be hard coded.

We did not want to have to write/maintain/support identical addons simply to change the Sage version number. So I wrote some code that first found out which Sage object was registered,, was it version 8 / 9 / 10/ etc. Then it opened up the specific module in design mode and performed a find and replace to change the code to the correct version. Once completed it saved the module. This enabled us to use on tool for all Sage versions. I don't know if anyone else has done something similar to this but this was the only code manipulation in real time that I have done.

David

 

[ak_ls]

Regarding being scary is an individual thing. There are numerous situations where scripts are generated at run time and executed without the user knowing anyting about it. SQL Server also generates numerous scripts which are modifiable manually. Are we forgetting that computers today can write programmes and then execute them. I have done similar thing at few occasions. Now I am looking a step further where a portion of the code is replaced at run time. This can work easily in run time interprated languages and not the compiled ones.

Now coming back to my requirement. Let me elaborate it a little further. At present the application generates code for full procedure (sometimes runnining into several hundred lines) for each input as Excel workbook. Whenever Excel format changes or validation or other criterian changes, the old procedure is manually deleted from the module and the new procedure code is regenerated by clicking on a button and added to the module automatically. The generated code consists two types of code.

1) The Code that is generated by the code generator procedure. It is used for input validation, error logging and inserting data into SQL server table. This code follows tested syntex and is based on a map table created and updated by user and validated by the developer.

2) The Code to handle specific requirements such as input filter condition, specific calculations before and after the recordset update method and adding Dim and variable initialization statements to meet the custom code requirements. The custom code is written by a developer and is stored in a table. This portion of the code does not change frequently.

At present, the developer generates the code and tests it before releasing to the users.

Now, I am looking for the possibility of adding the second part of the code at run time to a generic procedure which has already been developed and tested.

I have done similar type of work in foxpro in a payroll software where calculations logic code was kept in database and used depending upon the payroll configuration. I have also used similar method in generating code for reports by using foxpro's & command which allows code substitution at run time.

Hope this will help.

Ashok

 

[LPurvis]

I'm afraid you're not going to convince me on this one.
We're talking about Access here.
The binary data that represents its internal objects is, regrettably, prone to corruption at times. The VBA script you enter can become disassociated with the compiled code.

If this code manipulation is being performed entirely externally (as per my suggestion) then I really don't have a problem with it.
But otherwise, within the running application, it has an intrinsic element of risk.
(If I create an external object - refer to it via automation and something goes wrong - I get an trappable error and can discard the object to make anew next time. If it's in the running application I might never get back in it again. Obviously that's fixable with a replacement FE - but still... And of course - a vast majority of what I distribute is as MDE ;-)

Your description of what's being performed doesn't really seem such that anything can be suggested by way of alternative method.
(Validation, for example, can take many forms. It's based on table data but that's generated into a hard coded module?).

As for manipulating procesures (for you'll get nothing more advanced than Run or Eval for running ad hoc code - VB supports an undocumented method which doesn't work for live VBA apps as I recall) you can try the VBA extensibility objects - though they require VBE access.
At a rough patch together (I've only used it sparingly in the past) you'll have to work through it making it appropriate for your needs...

Public Function fAddProcedure(strModule As String, strProcedure As String, strCode As String)
On Error Resume Next
 
    Dim objMod As Object 'VBIDE.CodeModule
    Dim lngLine As Long
 
    Const vbext_pk_Proc = 0
 
    Set objMod = Application.VBE.ActiveVBProject.VBComponents(strModule).CodeModule
    If Err Then
        Set objMod = Application.VBE.ActiveVBProject.VBComponents.Add(vbext_ct_StdModule).CodeModule
        objMod.Name = strModule
    End If
    lngLine = objMod.ProcStartLine(strProcedure, vbext_pk_Proc) + _
                        objMod.ProcCountLines(strProcedure, vbext_pk_Proc)
    If Err Then
        'Procedure does not exist, create it at the end of the module
        lngLine = objMod.CountOfLines
        objMod.InsertLines lngLine + 1, "Sub " & strProcedure & "()"
        objMod.InsertLines lngLine + 2, strCode
        lngLine = objMod.CountOfLines
        objMod.InsertLines lngLine + 1, "End Sub"
    Else
        objMod.InsertLines lngLine - 1, strCode
    End If
 
End Function

Cheers.

 

[ak_ls]

LPurvis,

Thanks for the feedback.

Ashok

 

[datAdrenaline]

>> I'm afraid you're not going to convince me on this one. <<

I am with Leigh this, you should not be modifying code as part of your application. I will offer you this though ....

I have had a situation were I have written several small functions, but my users could decide the sequence or need of them ... then defined the sequence in a table and I used a recordset to loop through the configuration table, I then used to Eval() function to execute the functions that were specified in the configuration table.

But do take note that I did not modify the VBA code. VBA code is saved as a BLOB in the Access file, and as Leigh has indicated it can get corrupt. Also, when you modify code (manually or automatically) you put the code .. ALL the code in the BLOB in an UNCOMPILED state, and as such runs slower than compiled code.

Now ... with all this said and EVERY SINGLE WARNING AND CAUTION AND RED FLAG AGAINST MODIFYING CODE AT RUN TIME ... I have written code to write code at DESIGN time (ie: adding an OnNoData event on all reports) ... but mine method, although similar to Leigh's fine example, goes about it a little differently.

Public Function fAddProcedure() As Byte
    
    Dim mdl As Module
    Dim strProcedure
    Dim lngStartLine As Long, lngStartColumn As Long _
        , lngEndLine As Long, lngEndColumn As Long
    
    DoCmd.OpenModule "Module1"
    Set mdl = Modules("Module1")
    
    strProcedure = vbCrLf & vbCrLf & _
                   "Public Function fMyFunction() As Byte" & vbCrLf & _
                   "    Dim strMessage As String" & vbCrLf & _
                   "    MsgBox ""You should not modify VBA as runtime!!!""" & vbCrLf & _
                   "End Function"
    
    With mdl
        
        'Delete the old procedure, if one exists
        If .Find("fMyFunction() As Byte" _
                  , lngStartLine, lngStartColumn _
                  , lngEndLine, lngEndColumn) = True Then _
            .DeleteLines .ProcStartLine("fMyFunction", 0), _
                         .ProcCountLines("fMyFunction", 0)
        
        'Add the new procedure to the end of the module
        .InsertText strProcedure
        
    End With
    
    DoCmd.Close acMacro, "Module1", acSaveYes
    
End Function

The .InsertText method of the module object inserts your text at the end of the module ...

There are tons of methods associated with a Module object that can be used.

Oh ... one more thing, did I say that you shouldn't do this at run-time (ie: part of an application) ? ...