VBA String to Function Call
-
Is there a way to call a function based on a string in VBA? For example:
Dim func as String
func = "getResults"
Call func
The current solution is to do a huge Select Case sequence, which is aweful in my mind as there are about 100 possible functions right now.
-
I may not be remembering this accurately but I believe vba has an eval() function that you can use for this purpose.
-
The other thing to keep in mind is that if you start to go down this road, you can easily find yourself producing code worthy of the front page. Avoid, if you can, the Do("This") paradigm of programming.
-
@WeatherGod said:
Avoid, if you can, the Do("This") paradigm of programming.
Wouldn't Do("This") be the opposite of what he is proposing? The former is essentially when you cram multiple functions into one single procedure whereas the OP is wanting to call out to any number of different functions from a single point of invocation. I would guess it's for some sort of RPC system.
-
@morbiuswilters said:
I see it as a very subtle difference, and very easy to go the wrong way with this. When done right, especially if the different functions fit as a part of an established design, it can lead to very flexible programming. However, if the programmer goes overboard with it, we get situations like:@WeatherGod said:
Avoid, if you can, the Do("This") paradigm of programming.
Wouldn't Do("This") be the opposite of what he is proposing? The former is essentially when you cram multiple functions into one single procedure whereas the OP is wanting to call out to any number of different functions from a single point of invocation. I would guess it's for some sort of RPC system.
Put("Hell", "In a Handbasket");
Just voicing a word of caution, that's all...
-
@WeatherGod said:
I see it as a very subtle difference...
Perhaps. I was making the assumption the OP wanted to dynamically invoke functions based on some outside input. Basically some sort of RPC system.
@WeatherGod said:
...and very easy to go the wrong way with this.
It's very easy to go wrong with any technical project. Heck, that's why this site exists.
@WeatherGod said:
Just voicing a word of caution, that's all...
Wasn't doubting that. If the OP is doing RPC, though, there are probably bigger things to worry about. Like making sure only an allowed set of functions are callable. The problem is that this essentially results in code very similar to the switch statement the OP is using so there may be little advantage to using eval().
-
Its an automated testing script, which isn't mine. like all great catastraphes, I simply get to try to clean up the mess.
the system works like this: there is an actions table that has the test steps. action 1 - log in, action 2 - verify you're on the landing page, etc. this allows the user to add steps without programming, as most of the functions are of a generic 'fill field x with value y' vareity. it works, but there has to be a better way then multiple pages-long select case statements.
thanks for the tip on eval. i'll give that a try on monday.
-
@Merkidemis said:
There is a way to call a function based on a string though in VBA, but it is very complicated and requires you to do self-modifying code using the VBIDE object, so you should avoid that way if possible. A better way would be to use the ScriptControl object instead. Make sure your functions are in a class module, and then create a ScriptControl object, add an object of that class, and then call the function by name through the ScriptControl.Is there a way to call a function based on a string in VBA? For example:
Dim func as String
func = "getResults"
Call func
-
@zzo38 said:
@Merkidemis said:
There is a way to call a function based on a string though in VBA, but it is very complicated and requires you to do self-modifying code using the VBIDE object, so you should avoid that way if possible. A better way would be to use the ScriptControl object instead. Make sure your functions are in a class module, and then create a ScriptControl object, add an object of that class, and then call the function by name through the ScriptControl.Is there a way to call a function based on a string in VBA? For example:
Dim func as String
func = "getResults"
Call func
.. or, you could just use the Eval() function. :)
-
I'm trying to use the Eval method, but I am running into some issues. Things are always harder then they should be around here.
Some more disclosure is probably needed. I am working in TestPartner, an automated testing tool from Compuware, designed for testing web applications. It's scripting language of choice is VBA, as stated earlier, so there are a lot of limitiations that go with it. I am not sure what version of VBA it is using, but it certianly isn't current as Eval (presant as of Office 2003, it seems) is not available natively. It seems I can get it if I add the Microsoft Access 11 Object Library to the project, but I'm still having issues.
Here is some test code:
Sub test()
MsgBox "here"
End SubSub Main()
Eval ("test()")
End SubRunning Main causes an error:
Run-time error '2425':
The expression you entered has a function name that Microsoft Office Access can't find.
So, I am guessing I have the wrong library or am using it incorrectly, etc. My loathing for this language doesn't help me much, either. Any suggestions?
Here is what I am trying to fix/get rid of. And yes, this is only one of many:
Select Case Procedure_Name
Case "Procedure_Set_AppInfo_VIN"
Procedure_Set_AppInfo_VIN objtype, Object_Name, value
Case "Procedure_Handle_LienInfo"
Procedure_Handle_LienInfo
Case "Procedure_Check_LienInfo"
Procedure_Check_LienInfo
Case "Procedure_VehInfo_VIN_1"
Procedure_VehInfo_VIN_1
Case "Procedure_VehInfo_VIN_2"
Procedure_VehInfo_VIN_2
Case "Procedure_DriverCheck_Str"
Procedure_DriverCheck_Str
Case "Procedure_Get_PrelimSchedule"
Procedure_Get_PrelimSchedule
Case "Procedure_Handle_ViolationPreview"
Procedure_Handle_ViolationPreview
Case "Procedure_SummaryCapture_TX"
Procedure_SummaryCapture_TX
Case "Procedure_PrintCapture_TX"
Procedure_PrintCapture_TX
Case "Procedure_PrintCapture_AR"
Procedure_PrintCapture_AR
Case "Procedure_SummaryCapture_AR"
Procedure_SummaryCapture_AR
Case "Procedure_PrintCapture_AZ"
Procedure_PrintCapture_AZ
Case "Procedure_SummaryCapture_AZ"
Procedure_SummaryCapture_AZ
Case "Procedure_PrintCapture_WA"
Procedure_PrintCapture_WA
Case "Procedure_SummaryCapture_WA"
Procedure_SummaryCapture_WA
Case "Procedure_PrintCapture_NE"
Procedure_PrintCapture_NE
Case "Procedure_SummaryCapture_NE"
Procedure_SummaryCapture_NE
Case "Procedure_PrintCapture_IL"
Procedure_PrintCapture_IL
Case "Procedure_SummaryCapture_IL"
Procedure_SummaryCapture_IL
Case "Procedure_PrintCapture_IN"
Procedure_PrintCapture_IN
Case "Procedure_SummaryCapture_IN"
Procedure_SummaryCapture_IN
Case "Procedure_PrintCapture_SD"
Procedure_PrintCapture_SD
Case "Procedure_SummaryCapture_SD"
Procedure_SummaryCapture_SD
Case "Procedure_PrintCapture_VT"
Procedure_PrintCapture_VT
Case "Procedure_SummaryCapture_VT"
Procedure_SummaryCapture_VT
Case "Procedure_PrintCapture_CA"
Procedure_PrintCapture_CA
Case "Procedure_SummaryCapture_CA"
Procedure_SummaryCapture_CA
Case "Procedure_PrintCapture_OR"
Procedure_PrintCapture_OR
Case "Procedure_SummaryCapture_OR"
Procedure_SummaryCapture_OR '
Case "Procedure_PrintCapture_SC"
Procedure_PrintCapture_SC
Case "Procedure_SummaryCapture_SC"
Procedure_SummaryCapture_SC
Case "Procedure_PrintCapture_MO"
Procedure_PrintCapture_MO
Case "Procedure_SummaryCapture_MO"
Procedure_SummaryCapture_MO
Case "Procedure_SummaryCapture_CO"
Procedure_SummaryCapture_CO
Case "Procedure_PrintCapture_CO"
Procedure_PrintCapture_CO
Case "Procedure_PrintCapture_FL"
Procedure_PrintCapture_FL
Case "Procedure_SummaryCapture_FL"
Procedure_SummaryCapture_FL
Case "Procedure_PrintCapture_GA"
Procedure_PrintCapture_GA
Case "Procedure_SummaryCapture_GA"
Procedure_SummaryCapture_GA
Case "Procedure_SummaryCapture_KS"
Procedure_SummaryCapture_KS
Case "Procedure_PrintCapture_KS"
Procedure_PrintCapture_KS
Case "Procedure_SummaryCapture_DE"
Procedure_SummaryCapture_DE
Case "Procedure_PrintCapture_DE"
Procedure_PrintCapture_DE
Case "Procedure_SummaryCapture_CT"
Procedure_SummaryCapture_CT
Case "Procedure_PrintCapture_CT"
Procedure_PrintCapture_CT
Case "Procedure_SummaryCapture_OH"
Procedure_SummaryCapture_OH
Case "Procedure_PrintCapture_OH"
Procedure_PrintCapture_OH
Case "Procedure_SummaryCapture_ID"
Procedure_SummaryCapture_ID
Case "Procedure_PrintCapture_ID"
Procedure_PrintCapture_ID
Case "Procedure_SummaryCapture_NV"
Procedure_SummaryCapture_NV
Case "Procedure_PrintCapture_NV"
Procedure_PrintCapture_NV
Case "Procedure_SummaryCapture_NC"
Procedure_SummaryCapture_NC
Case "Procedure_PrintCapture_NC"
Procedure_PrintCapture_NC
Case "Procedure_SummaryCapture_NM"
Procedure_SummaryCapture_NM
Case "Procedure_PrintCapture_NM"
Procedure_PrintCapture_NM
Case "Procedure_SummaryCapture_PA"
Procedure_SummaryCapture_PA
Case "Procedure_PrintCapture_PA"
Procedure_PrintCapture_PA
Case "Procedure_SummaryCapture_TN"
Procedure_SummaryCapture_TN
Case "Procedure_PrintCapture_TN"
Procedure_PrintCapture_TN
Case "Procedure_PrintCapture_UT"
Procedure_PrintCapture_UT
Case "Procedure_SummaryCapture_UT"
Procedure_SummaryCapture_UT
Case "Procedure_PrintCapture_WI"
Procedure_PrintCapture_WI
Case "Procedure_SummaryCapture_WI"
Procedure_SummaryCapture_WI
Case "Procedure_PrintCapture_WY"
Procedure_PrintCapture_WY
Case "Procedure_SummaryCapture_WY"
Procedure_SummaryCapture_WY
Case "Procedure_PrintCapture_WV"
Procedure_PrintCapture_WV
Case "Procedure_SummaryCapture_WV"
Procedure_SummaryCapture_WV
Case "Procedure_SummaryCapture_ME"
Procedure_SummaryCapture_ME
Case "Procedure_PrintCapture_ME"
Procedure_PrintCapture_ME
Case "Procedure_SummaryCapture_VA"
Procedure_SummaryCapture_VA
Case "Procedure_PrintCapture_VA"
Procedure_PrintCapture_VA
Case "Procedure_SummaryCapture_MN"
Procedure_SummaryCapture_MN
Case "Procedure_PrintCapture_MN"
Procedure_PrintCapture_MN
Case "Procedure_SummaryCapture_MI"
Procedure_SummaryCapture_MI
Case "Procedure_PrintCapture_MI"
Procedure_PrintCapture_MI
Case "Procedure_SummaryCapture_IA"
Procedure_SummaryCapture_IA
Case "Procedure_PrintCapture_IA"
Procedure_PrintCapture_IA
Case "Procedure_SummaryCapture_MS"
Procedure_SummaryCapture_MS
Case "Procedure_PrintCapture_MS"
Procedure_PrintCapture_MS
Case "Procedure_ConfirmationInfo_Str"
Procedure_ConfirmationInfo_Str
Case "Procedure_Handle_Confirmation_PrintSR_22Form"
Procedure_Handle_Confirmation_PrintSR_22Form
Case "Procedure_Set_AmountPaid"
Procedure_Set_AmountPaid
Case "Procedure_Image_Check"
Procedure_Image_Check Object_Name
Case "Procedure_Autolink_RSQ"
Procedure_Autolink_RSQ
Case "Procedure_Set_SaveQuote"
Procedure_Set_SaveQuote
Case "Procedure_TermPreliminary"
Procedure_TermPreliminary
Case "Procedure_RefNo_Capture"
Procedure_RefNo_Capture
Case "Procedure_Handle_CustomizedPayOption"
Procedure_Handle_CustomizedPayOption
Case "Procedure_Handle_ConfirmCustomizedPayOption"
Procedure_Handle_ConfirmCustomizedPayOption
Case "Procedure_DriverAssignment"
Procedure_DriverAssignment
End SelectReplacing all of that with eval(Procedure_Name) would be awesome.
Don't get me started on the state specific methods.
-
Where is test() defined? Based on your error, I assume it is in a form or a report; if so, you need to fully qualify the method name. Eval() evaluates the expression independently of the current context, so any and all objects, variables, and so on are out of scope. Also, it appears that you have defined the sub as private, not public, so it still wouldn't be visible even if you did fully qualify the object reference.
If test() is defined as a public sub-routine/function in a module, you should have no problem accessing it using Eval().
-
By the way, looking further at your code, it looks really, really bad. It would be much better to consolidate the redundant procedures into single ones that accepts arguements.
i.e., instead of having:
GenerateReportMA()
GenerateReportRI()
GenerateReportCN()
..etc...
you would just have:
GenerateReport(State as String)
Chances are the code for all of those is exactly the same except for some filter or parameter or setting, and you should simply do that with function arguments.
I just glanced at your code but looks like you can eliminate probably hundreds of lines of code and really clean things up. I strongly recommend doing this instead of using Eval().
-
You're very correct, and oddly enough I am working on this as I speak. Many of the state functions are nearly identical, and so far I have reduced a 3000 line module (that contains all the state functions) to 600 by doing just as you suggested.
The greater issue is that the above example is but one of 5 instances of the "switch on function name" method of figuring out which method to call. This one is simply the greatest offender.
Oddly enough, they also do the same sort of thing for looking up which global variable to use. There are 146 globals...
-
@Merkidemis said:
Can you possibly store it in a array or collection instead?Oddly enough, they also do the same sort of thing for looking up which global variable to use. There are 146 globals...
-
@zzo38 said:
@Merkidemis said:
Please ignore the buzzword troll... Merkidemis, the uber-case statement you have is an example of what I was talking about. You have a variety of statements where some of them are similar, and others have nothing to do with each other. Therefore, this case-statement could potentially be used anywhere in program logic, which is a very bad thing. Definitely consolidate your functions. This will not only reduce your case-statement, but also eliminate many unneeded functions. In addition, if these functions are mostly identical and there turned out to be a bug in the logic, imagine how hard it would be to fix all of the functions properly. Probably the most important rule in programming is the DRY rule: Don't Repeat Yourself.
Can you possibly store it in a array or collection instead?Oddly enough, they also do the same sort of thing for looking up which global variable to use. There are 146 globals...
Definitely see if the globals can be moved to a configuration file, and loaded where-ever needed.
-
You might be looking for this one? (callByName)
http://support.microsoft.com/kb/186143
Not sure if it also works in VBA ;)
-
Yup, I am well aware of this and working to consolidate. The group that developed these scripts excels in the field of copy-paste code reuse. They had the database connection code 14 times in another module, for example. Their original scripts had every peice of code duplicated for every state we do business in. Imagine the fun of making a change to 35 scripts because the common login page changed.
One of my tasks on this project is doing exactly what we're both talking about, putting common functionality into a method and calling it, rather then repeating the code. A good programmer is a lazy programmer: if you need to do it more then once, put it in a method. The state specific methods mentioned are very simular to each other, or at least can be grouped. It will end up being something like "if the state is one of these 5, do this, else if its one of these 9, do this" and so forth. The two modules that have these functions together make up about 6000 lines of code, which is over a third of the entire script. I'll let you know what I get it down to when I'm done, which should hopefully be later today.
The OP is another step along the reduction process. I am not as intimately famiular with VBA as I'd like, being more of a Java guy, and hence the request for help. I am still having issues with getting the eval working, but I'll table that until the rest of the stuff is done as I can't afford to spend too much more time on it and delay the project.
For the globals, part of that was more ranting about how ... wonderful... this script is, but the collection suggestion was what I was leaning towards as well. First step though is to find if these are really needed or not. Some I imagine are used very sparcely, and could easily be more limited in scope. Others would be more widely used and could either be left alone or moved to a collection.
-
CallByName seems to work, thanks Daid!
