CallByName seems to work, thanks Daid!
Merkidemis
@Merkidemis
Best posts made by Merkidemis
Latest posts made by Merkidemis
-
RE: VBA String to Function Call
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.
-
RE: VBA String to Function Call
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...
-
RE: VBA String to Function Call
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.
-
RE: VBA String to Function Call
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.
-
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.
-
It's Yes! Hurray!
I have been tasked with cleaning up some automated test scripts written in VBA. First WTF of course is why on earth a company (and many of you know who this company is) would choose VBA as their language of coice. The second is the fact this this script suite was developed off shore by people with little programming background, and no knowledge of the tool. They just picked it up as they went along. I have two lines of code that should illistrate the quality of the entire suite:
If BillingOption_Status = "Y" Then
End If