Calling a module from a command button

kklewis

New Member
Joined
Dec 5, 2005
Messages
17
I have written a module in Access VBA that creates a query. It is written in the "modules" section. It runs correctly when Im in VBA. I created an "event procedure" by:
right clicking on the command button, then selecting properties and under the "on click" field I double clicked the "..." button which brough up VBA and the following code:
Private Sub PullData_Query1_Click()

End Sub

This code is not in the module section, but as code behind the form where my command button is located.
I put my module name in between the first and last line, but when i try and run the query from the command button itself I get the following message: "Sub of function not defined". If I copy the code below the code for the button it runs fine. The only problem is that I run this module on mulitple sheets and I don't want to have to keep copying and pasting the module to each sheet. Is there any way to trouble shoot this? Thanks.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Well for a start you don't run or call modules.

Modules are containers for subs/functions etc.

What you need to run/call is the sub.
 
Upvote 0
Sorry ... im new to VBA and lacking the vocabulary.
I am trying to call a sub that is saved in a module from the code for a command button which is in the forms section (Class Objects). How can I save one sub in a module, but call it using command buttons?
 
Upvote 0
Instead of the name of the module put the name of the sub in the code above.
 
Upvote 0
I was calling the sub, not the module.

I have a sub called CRPullData saved in the module named "Module1". The first line of the sub is "Private Sub Analysis_Results_Click()". In the Class Objects section I have the code for the command button, which is located on the PullDataForm. The code is in Form_PullDataForm and looks like this:
Private Sub PullData_Query1_Click()
CRPullData
End Sub

This code runs fine if the CRPullData sub is copied below this code, but does not run (get error message "Sub or Function not defined") if the sub is saved in Module1.

Is there a away to call a sub from a module?
Thanks.
 
Upvote 0
I was calling the sub, not the module.

I have a sub called CRPullData saved in the module named "Module1". The first line of the sub is "Private Sub Analysis_Results_Click()". In the Class Objects section I have the code for the command button, which is located on the PullDataForm. The code is in Form_PullDataForm and looks like this:
Private Sub PullData_Query1_Click()
CRPullData
End Sub

This code runs fine if the CRPullData sub is copied below this code, but does not run (get error message "Sub or Function not defined") if the sub is saved in Module1.

Is there a away to call a sub from a module?
Thanks.
 
Upvote 0
First thing I'd ask is - is there any chance the function/subroutine that you're calling is set to a local scope or that the default is PRIVATE?

Routines in a different module are only accessible if they are set PUBLIC (there are other keywords like Global but Public is the most commonly used).
On the off chance that the default is set to PRIVATE, try explictly putting the keyword PUBLIC ahead of the routine.

Second - you could also try the syntax:

Call routineNameHere

And yes, you can call a Sub or Function with identical syntax, doesn't matter. The main difference is that a Function is able to return a variable(s) back to the calling routine. It's possible to pass objects (which includes things like arrays)

The need to use Private/Public is actually part of a concept known as Encapsulation which is a key component of Object Oriented code design. VB in general has sort of a half-azzed implementation of it with it's Classes and Modules, but the principle is still the same...I really can't do justice to everything under this topic without writing a massive article.
 
Upvote 0
Thanks for your help ... the problem was that the sub's were Private. I set them to public and the command buttons work perfectly now.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top