[AccessD] Is it Possible to Have a Variable in a VBA"CALL"Statement?

Stuart McLachlan stuart at lexacorp.com.pg
Fri Jan 27 20:47:56 CST 2012


It always pays to think about things a bit more before setting your methods in stone :-)
You can just do this:

SubMain()
PrepareReport "rpt1"
PrepareReport "rpt2"
End Sub

Sub  PrepareReport(rptname As String)
MsgBox "About to run " & rptname & "Code"
Application.Run rptname & "Code"
MsgBox "Done"
End Sub

Sub Rpt1Code()
MsgBox "Report 1 Stuff"
End Sub

Sub Rpt2Code()
MsgBox  "Report 1 Stuff"
End Sub





On 27 Jan 2012 at 20:17, Brad Marks wrote:

> Arthur and Stuart,
> 
> Thanks for your ideas and insights.
> 
> I am planning to spend some time experimenting with these ideas over the weekend.
> This will hopefully get me out of some work that my wife has planned for  me  :-)
> 
> 
> I appreciate your help.
> 
> Brad
> 
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 
> 
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com on behalf of Stuart McLachlan
> Sent: Fri 1/27/2012 7:49 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Is it Possible to Have a Variable in a VBA"CALL"Statement?
>  
> I would consider this much easier to follow. It encapsulates the conditional in it's own Sub 
> and it is a simple Copy/Paste of two lines followed by a minor edit each time you add a new 
> report:
> 
> Sub CommonCode(rptname as String)
> 'Do common stuff
> .....
> 'Do specific Stuff
> Call SpecificCode(rptname)
> 'Do more common stuff
> .....
> End Sub
> 
> Sub SpecificCode(rptName as String)
> Select Case rptName
>    Case "rptOne"
>       Call rptOne_CompoundFilters
> Case "rptTwo"
>       Call rptTwo_CompoundFilters
> Case "rptThree"
>       Call rptThree_CompoundFilters
> Case "rptFour"
>       Call rptFour_CompoundFilters
> End Select
> End Sub
> 
> 
> On 27 Jan 2012 at 19:13, Brad Marks wrote:
> 
> > 
> > I have written a number of small chunks of VBA code to support
> > "Compound Dynamic Report Filters".  Much of the code is "Common" code,
> > but some of the code needs to be customized slightly for each Report. 
> > 
> > In the "common" code that calls the "Specialized" code, I current use
> > IF statements to call the proper sub.  This will require one IF
> > statement for each report (someday we might have 50+ reports) 
> > 
> > By being able to use a variable in the VBA CALL statement, we will be
> > able to have just a few lines of code, instead of all the code needed
> > to support 50+ different reports. 
> > --
> Stuart McLachlan
> 
> Ph:    +675 340 4392 
> Mob: +675 7100 2028
> Web: http://www.lexacorp.com.pg
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> -- 
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
> 
> 
> 

--
Stuart McLachlan

Ph:    +675 340 4392 
Mob: +675 7100 2028
Web: http://www.lexacorp.com.pg



More information about the AccessD mailing list