Call a VBA Function into a Sub Procedure

Question

I know this is a simple question for someone out there, but I have never really used function module at all because I did not understand what they were.

So I have a whole bunch of things I can use this for (cut down on redundancy), but I want to know how I call into a sub (like a button click) procedure from a form.

I tried this...

Sub Command_Click()
    Call "pptCreator"
End Sub

I know that is pretty bad, but I have no idea how to bring this into a procedure.

Photometer

Answer 1

Here are some of the different ways you can call things in Microsoft Access:

To call a form sub or function from a module

The sub in the form you are calling MUST be public, as in:

Public Sub DoSomething()
  MsgBox "Foo"
End Sub

Call the sub like this:

Call Forms("form1").DoSomething

The form must be open before you make the call.

To call an event procedure, you should call a public procedure within the form, and call the event procedure within this public procedure.

To call a subroutine in a module from a form

Public Sub DoSomethingElse()
  MsgBox "Bar"
End Sub

...just call it directly from your event procedure:

Call DoSomethingElse

To call a subroutine from a form without using an event procedure

If you want, you can actually bind the function to the form control's event without having to create an event procedure under the control. To do this, you first need a public function in the module instead of a sub, like this:

Public Function DoSomethingElse()
  MsgBox "Bar"
End Function

Then, if you have a button on the form, instead of putting [Event Procedure] in the OnClick event of the property window, put this:

=DoSomethingElse()

When you click the button, it will call the public function in the module.

To call a function instead of a procedure

If calling a sub looks like this:

Call MySub(MyParameter)

Then calling a function looks like this:

Result=MyFunction(MyFarameter)

where Result is a variable of type returned by the function.

NOTE: You don't always need the Call keyword. Most of the time, you can just call the sub like this:

MySub(MyParameter)

Alaynaalayne

So does that mean instead of "calling" the function into the form's sub, I would being "calling the form into the function module?? I think i understand, but am still a little confused. I have a powerpoint automation procedure that I want to use all throughout the db, because the purpose of this db is tracking metrics, and the end game for the client is presentations (mostly power point). I have different sets of code scattered throughout different forms that acomplish this for me, but mostly it's all repetitive. From a recent explaination the Functions in modules will help me..... – Photometer

eliminate most of the repetitive coding by calling a "certain set of code" from a module function. But it operates off the forms. So the event trigger is still "button clicks" from the forms. It sounds like I have the idea in reverse, that I have to call the form sub to the function? instead of the other way around. Sorry I know its pretty obvious I am at best a novice at this... thanks very much for your help. – Photometer

In your case I think you just need to put your sub in a module, make the sub public, and then call it: call pptCreator. See "To call a subroutine in a module from a form" in this answer. – Alaynaalayne

Answer 2

if pptCreator is a function/procedure in the same file, you could call it as below

call pptCreator()

Scifi

i wrote the function in a module and saved it as pptCreator. then I go to one of the forms that I want to use this in conjunction with, and the event trigger is a button click. so... Public Sub Command1_click () call pptCreator end sub is this correct? i tried using it and I get a compile error stating that expected end of statements; not module thanks for the help – Photometer

Answer 3

Calling a Sub Procedure – 3 Way technique

Once you have a procedure, whether you created it or it is part of the Visual Basic language, you can use it. Using a procedure is also referred to as calling it.

Before calling a procedure, you should first locate the section of code in which you want to use it. To call a simple procedure, type its name. Here is an example:

Sub CreateCustomer()
    Dim strFullName As String

    strFullName = "Paul Bertrand Yamaguchi"

msgbox strFullName
End Sub

Sub Exercise()
    CreateCustomer
End Sub

Besides using the name of a procedure to call it, you can also precede it with the Call keyword. Here is an example:

Sub CreateCustomer()
    Dim strFullName As String

    strFullName = "Paul Bertrand Yamaguchi"
End Sub

Sub Exercise()
    Call CreateCustomer
End Sub

When calling a procedure, without or without the Call keyword, you can optionally type an opening and a closing parentheses on the right side of its name. Here is an example:

Sub CreateCustomer()
    Dim strFullName As String

    strFullName = "Paul Bertrand Yamaguchi"
End Sub

Sub Exercise()
    CreateCustomer()
End Sub

Procedures and Access Levels

Like a variable access, the access to a procedure can be controlled by an access level. A procedure can be made private or public. To specify the access level of a procedure, precede it with the Private or the Public keyword. Here is an example:

Private Sub CreateCustomer()
    Dim strFullName As String

    strFullName = "Paul Bertrand Yamaguchi"
End Sub

The rules that were applied to global variables are the same:

Private: If a procedure is made private, it can be called by other procedures of the same module. Procedures of outside modules cannot access such a procedure.

Also, when a procedure is private, its name does not appear in the Macros dialog box

Public: A procedure created as public can be called by procedures of the same module and by procedures of other modules.

Also, if a procedure was created as public, when you access the Macros dialog box, its name appears and you can run it from there

Dollar

Answer 4

Procedures in a Module start being useful and generic when you pass in arguments.

For example:

Public Function DoSomethingElse(strMessage As String)  
    MsgBox strMessage
End Function

Can now display any message that is passed in with the string variable called strMessage.

Jackstraws

ok. how do I call a "function" into a form's sub event (like a button click)? the module contains a function. can i call that the same way as a sub? i have tried over and over and i get a host of error messages. this is code that i found, and I am not sure if it will be useful to me or not, but I cannot even get past compile errors. thanks – Photometer

A forms sub event as you call it is not a function it is a sub procedure. Also an event in the form is Private so it can only be called from within the form class. To call a form from out side then a procedure has to be declared as Public. Also Remember a function always has a return value and a sub procedure does not. – Jackstraws

Answer 5

To Add a Function To a new Button on your Form: (and avoid using macro to call function)

After you created your Function (Function MyFunctionName()) and you are in form design view:

1. Add a new button (I don't think you can reassign an old button - not sure though).

2. When the button Wizard window opens up click Cancel.

3. Go to the Button properties Event Tab - On Click - field.

4. At that fields drop down menu select: Event Procedure.

5. Now click on button beside drop down menu that has ... in it and you will be taken to a new Private Sub in the forms Visual Basic window.

6. In that Private Sub type: Call MyFunctionName
   It should look something like this:

   Private Sub Command23_Click()
   
       Call MyFunctionName
   
   End Sub
   

7. Then just save it.

Gerbil