Reusing VBA Code (1 Viewer)

Lateral

Registered User.
Local time
Today, 10:22
Joined
Aug 28, 2013
Messages
388
Hi guys,


Whilst I am still learning about Access and VBA I'm not a total "newbie" and have developed a large application.


I now want to tidy up the code so as I have a lot of code duplicated from form to form. For example, I have a form (called Form1) that has a number of buttons that have "On Click" events that initiate some VBA code such as generate a report etc. I then want to create another form (Form2) that does some other stuff as well as have a button that also runs the same report that was able to be run on Form1.


In order to do this I just copy and past the code from Form1 to Form2 button On Click event...


This all works fine except when I have to change the VBA code I need to change it in 2 places.


I thought that I could create a new module and then create a Function and then call that function but I am getting errors when I compile the VBA code saying "Invalid Use of ME keyword" as I assume due to me creating variables and then setting them to controls on the forms:



Dim mCompanyName As Variant
Dim mReminderCount As Variant

mReminderCount = Me.ReminderCount
mCompanyName = Me.CompanyName


Am I missing something?


Is there a way to tell VBA to automatically "include" a chunk of code?



Thanks for your help


Cheers
Greg
 

MarkK

bit cruncher
Local time
Today, 10:22
Joined
Mar 17, 2004
Messages
8,178
'Me' is an object reference, and so only makes sense if you are programming with class modules, which includes Access.Form objects. In a standard module there is no 'thing,' no object, just a collection of executable routines, and so the concept of Me as an object reference is not defined.

Try creating a class, and see how that differs from what you are doing. At least in that case the Me reference will not raise that error.

hth
Mark
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 04:22
Joined
Jan 20, 2009
Messages
12,849
Me cannot be used in a standard module because it refers to the associated Object .

Instead you can pass the reference to the Form in the call to the function and substitute that for Me in the function.

Code:
Public Function myfunction(ByRef frm As Form) As sometype
 
Dim mCompanyName As Variant
Dim mReminderCount As Variant

     mReminderCount = frm.ReminderCount
     etc
I assume you are using variants to handle Nulls.

It could be a Sub if you don't need the return.

Alternatively and more flexibly you can pass the values to the Function or Sub.
Code:
 Public Function myfunction(ByVal mCompanyName As String, ByVal mReminderCount AS Integer) As sometype
 
     use the variables that have been passed
Call from the form module with:
Code:
myfunction(Me.CompanyName, Me.ReminderCount)
Or you can directly use the function in the EventProcedure without going to the code module.
Code:
=myfunction(CompanyName, ReminderCount)
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 17:22
Joined
Jul 9, 2003
Messages
16,244
Regarding the title of the thread:- "Reusing VBA Code"

I thought I should mention a very simple and easy way...

See the first video on my website HERE:- Nifty Tips

And you want the first video:- Insert VBA Code Snippets
 
Last edited:

Lateral

Registered User.
Local time
Today, 10:22
Joined
Aug 28, 2013
Messages
388
Hi guys,


Firstly, thanks for the replies.


I think I also need to further clarify what I am looking for.


I'm 60 years old and come from a mainframe computing background and am familiar with a range of programming languages.



Most of these had ways to enable the developer to create a library of "functions or routines" that could be saved in some sort of library. For example, these could include code to read a customers address record from a database (ReadCustAddress), update the name (UpdateCustName) etc.



The developer would then write could code and if he wanted to gain access to the Customer address details he would simply add something like the following to his source code:

include ReadCustAddress

When he compiled the source code, all of the code in the ReadCustAddress function would be inserted into the main code as if he had written it himself.

I am looking for the same functionality so that I can easily reuse VBA code.

I hope I have explained myself properly.

Cheers
Greg
 

sonic8

AWF VIP
Local time
Today, 18:22
Joined
Oct 27, 2015
Messages
998
include ReadCustAddress

When he compiled the source code, all of the code in the ReadCustAddress function would be inserted into the main code as if he had written it himself.
This concept is not possible in VBA.

There are tools (e.g. MZTools) available that allow you to define code templates and insert them into our code with a mouse click at design time.

But, the more generally used approach is to create functions, sub-procedures or whole classes that are called/used/instantiated. In my opinion that is a much more comprehensible and maintainable approach than using includes or code templates. At least for most situations where code reuse is advisable.


I did a video on procedures for code reuse. Maybe that makes the concept a little clearer.
 

Lateral

Registered User.
Local time
Today, 10:22
Joined
Aug 28, 2013
Messages
388
Thanks Sonic8,


I'll have a look at the video...


Cheers
Greg
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:22
Joined
Feb 28, 2001
Messages
26,999
Lateral,

I have seen the "Include" function in some variants of BASIC. In one variant, the INCLUDE plunks a bunch of code in the spot of the INCLUDE statement exactly in the same way that one might imagine a cut/paste in that spot.

VBA doesn't do things that way. The "INCLUDE" functionality is kind-of, maybe, sort-of present if you realize that VBA "References" (Code Window >> Tools >> References) can include both OBJECT libraries and TEXT libraries - if they conform to the Windows formats for those items. And therein lies the big "gotcha" - the Windows formats are not like the mainframe formats. Therefore, the kind of inclusion you discuss is just not there.

To the good, at development time, you can CERTAINLY have more than one general module that is your library of functions, routines, and structure definitions. There is no barrier to a "divide and conquer" approach to modules. My big integrated Office project involved libraries of VBA code that I imported wholesale (i.e. told Access to pull in the text of that particular source-code file and make it part of this project.) I had Word, Excel, Outlook, FileSystemObject, Special Math, User Security, String Parsing, and Control Management modules, plus a few common routines used by more than one form's class module.

Having used the "INCLUDE" mechanism under OpenVMS (which many count as mainframe), I can also tell you that if you structure that incorrectly, you STILL face the issue of having the object name not included correctly. That's just a text substitution.

The solutions are invariably based on a limited number of solutions:

1. Copy modules and as part of the formal call sequence, include "Me" as one of the actual arguments to the call, and include "ByRef fFrm as Access.Form" as the corresponding formal argument to the call's declaration. Then you can use fFrm in place of Me inside that module.

2. Obtain a VB compiler and build your own Dynamic Link Library. (But you will still have a similar call linkage issue.)

3. There are ways to make a module open another module in design mode and insert text. Not that I would ever recommend it ('cause it's too much of a P.I.T.A.), but you CAN "roll your own" substitution procedure. There are implied line numbers in each line of a module. You could search your modules for the INCLUDE statement in text, and if you find it, delete that line and insert some other lines in place.
 

Lateral

Registered User.
Local time
Today, 10:22
Joined
Aug 28, 2013
Messages
388
Thanks DocMan.


You have certainly given me some things to mull over.....


Thanks again to everyone for your replies.


Cheers
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:22
Joined
May 21, 2018
Messages
8,463
VBA is event driven and object oriented programming (although a limited form of OOP). Code (unless trivial) should be written once and used as many times as needed. There are lots of techniques to do this. Most of my event procedures have a single line of code and that is a call to another sub routine. That allows multiple events to call the same code. If similar code is used by different forms then that code is moved to a standard or class module. Rarely do I ever have any code that does not accept arguments, in order to make it reusable. Nothing is hard-wired. The more vba you write the less amount of code is needed. You and up with less code in the form and more code in standard and class modules.
 

Users who are viewing this thread

Top Bottom