[vba] Call a VBA Function into a Sub Procedure

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.

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.

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

call pptCreator()

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

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.