?
Solved

Function Calls in a variable?

Posted on 2005-04-06
13
Medium Priority
?
239 Views
Last Modified: 2010-05-02
Is it possible to put function or sub names into a variable and call them from that variable? Basically what I've done is created a module that contains code to access a recordset, call a function that returns a string which becomes the body of an Outlook email object, and send that email object to each user returned in the recordset. However, I want to be able to use that module to send more than one email, so I want to create more functions that will return different email messages. Since I'm extremely new at this and am teaching myself, I'm not sure how I'd go about changing which function I'm calling. I can post the code if it will help anyone see what I'm doing and help me out with it.

Thanks
0
Comment
Question by:cjones_mcse
  • 6
  • 5
  • 2
13 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 13718027
>Is it possible to put function or sub names into a variable and call them from that variable?
SomeVariable = SomeFunction(optional parameters)

Make sure the data type of SomeVariable is the same as what SomeFunction returns.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 13718049
Oops, sorry, misread your question.   Hang on..
0
 
LVL 15

Expert Comment

by:ameba
ID: 13718095
' Form1 code
Option Explicit

Private Sub Form_Click()
    Dim i As Integer
   
    For i = 1 To 3
        CallByName Me, "sub_" & i, VbMethod
    Next i
End Sub

Public Sub Sub_1()
    Print "Sub_1 is executing!"
End Sub

Public Sub Sub_2()
    Print "Sub_2 is executing!"
End Sub

Public Sub Sub_3()
    Print "Sub_3 is executing!"
End Sub
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 10

Author Comment

by:cjones_mcse
ID: 13718637
I'm not sure how that helps me, ameba. I can't go renaming my function names to Func_1 through Func_3. Let me post the code I have for the module so you can see what I'm needing to do.

Option Compare Database

Dim objSITEMail01 As New Outlook.Application
Dim objEmail As Outlook.MailItem
Dim objSITEMail02 As New Outlook.Application
Dim objEmail2 As Outlook.MailItem
   
Dim strLtrContent As String
Dim rsSITEUser As New ADODB.Recordset

Public Sub SITENewAcctEmail()
       
    With rsSITEUser
        .LockType = adLockOptimistic
    End With
   
    rsSITEUser.ActiveConnection = CurrentProject.Connection
    rsSITEUser.Open "qryActivated"
   
    Do While Not rsSITEUser.EOF
   
        Call NewAcctEmail01(strLtrContent)
                               
        Set objEmail = objSITEMail01.CreateItem(olMailItem)
        objEmail.Recipients.Add rsSITEUser("Email")
        objEmail.Subject = "SITE Account Info 01"
        objEmail.Body = strLtrContent
        objEmail.Attachments.Add ("C:\SITE\IniLogin.doc")
       
        objEmail.Send
       
        rsSITEUser.MoveNext
    Loop
   
    MsgBox "SITE Account Email 01 sent to all users. SITE Account Email 02 will now be sent.", _
            vbExclamation, "Mail Sent"
           
    rsSITEUser.Requery
   
    Do While Not rsSITEUser.EOF
       
        Call NewAcctEmail02(strLtrContent)
           
        Set objEmail2 = objSITEMail02.CreateItem(olMailItem)
        objEmail2.Recipients.Add rsSITEUser("Email")
        objEmail2.Subject = "SITE Account Info 02"
        objEmail2.Body = strLtrContent
           
        objEmail2.Send
           
        rsSITEUser.MoveNext
    Loop
   
    Msg = "All Account Info Email's have been sent. Please check your inbox for non-deliverable notices."
    Title = "Email Sent"
    Result = MsgBox(Msg, vbExclamation, Title)
   
    rsSITEUser.Requery
   
    Do While Not rsSITEUser.EOF
       
        rsSITEUser!AcctInfoSent = True
        rsSITEUser!InfoSentDate = Date
        rsSITEUser!Validated = True
        rsSITEUser.MoveNext
    Loop
   
    rsSITEUser.Close
    Set rsSITEUser = Nothing
End Sub

Public Function NewAcctEmail01(strLtrContent As String)
    strLtrContent = "Dear " & rsSITEUser("FIRST_NAME") & " " & rsSITEUser("LAST_NAME") & _
                    vbCrLf & vbCrLf & _
                    "Thank you for evalutating SITE. Please feel free to give us any comments, " & _
                    "feedback, or suggestions on how we can improve the system. Your username " & _
                    "is included in this email. You will receive your temporary password in a " & _
                    "separate email within the next hour." & vbCrLf & _
                    vbCrLf & _
                    "Your Username is: " & rsSITEUser("Username") & vbCrLf & vbCrLf & _
                    "Once you have received your username and password:" & vbCrLf & _
                    "- Please follow the password change and certificate download procedures in " & _
                    "the attached document. Your first logon may take several minutes while " & _
                    "the system verifies the certificate." & vbCrLf & _
                    "- SITE training material can be found in the SITE training folder:" & vbCrLf & _
                    vbCrLf & "https://www.mysite.com/sites/trainingwg/Public%20Information/Forms/" & _
                    "AllItems.aspx?RootFolder=%2fsites%2ftrainingwg%2fPublic%20Information%2f" & _
                    "SITE%20Training" & vbCrLf & _
                    vbCrLf & _
                    "If you have questions or issues with SITE, please contact:" & vbCrLf & _
                    "John Doe (mailto:John.Doe@mysite.com)" & vbCrLf & _
                    "Jack Doe (mailto:Jack.Doe@mysite.com) " & _
                    "or" & vbCrLf & _
                    "Jane Doe (mailto:Jane.Doe@mysite.com)" & vbCrLf & vbCrLf
End Function

Public Function NewAcctEmail02(strLtrContent As String)
    strLtrContent = "Dear " & rsSITEUser("FIRST_NAME") & " " & rsSITEUser("LAST_NAME") & ":" & vbCrLf & _
                    vbCrLf & _
                    "Your SITE account password is: " & rsSITEUser("Password") & vbCrLf & _
                    vbCrLf & _
                    "The password is case sensitive, and you should have received the username in a " & _
                    "separate email. Your account setup was expedited so please wait 24 hours " & _
                    "before attempting to change your password." & vbCrLf & _
                    vbCrLf & _
                    "If you have questions or issues with SITE, please contact:" & vbCrLf & _
                    "John Doe (mailto:John.Doe@mysite.com)" & vbCrLf & _
                    "Jack Doe (mailto:Jack.Doe@mysite.com) or" & vbCrLf & _
                    "Jane Doe (mailto:Jane.Doe@mysite.com)" & vbCrLf & vbCrLf
End Function

Public Function PassReset(strLtrContent As String)
    strLtrContent = "Dear " & FIRST_NAME.Value & " " & _
                    LAST_NAME.Value & ":" & vbCrLf & vbCrLf & _
                    "Your SITE account password is: " & Password.Value & _
                    vbCrLf & vbCrLf & "Please wait 24 hours before attempting to change your password." & _
                    vbCrLf & vbCrLf & "If you have questions or issues with SITE, please contact:" & _
                    vbCrLf & "John Doe (mailto:John.Doe@mysite.com)" & vbCrLf & _
                    "Jack Doe (mailto:Jack.Doe@mysite.com) or" & vbCrLf & _
                    "Jane Doe (mailto:Jane.Doe@mysite.com)" & vbCrLf

End Function

Public Function PassExpire(strLtrContent As String)
    strLtrContent = "Dear " & FIRST_NAME.Value & " " & _
                    LAST_NAME.Value & ":" & vbCrLf & vbCrLf & _
                    "Your SITE account password will expire in 14 days. Please visit SITE and change " & _
                    "your password as soon as possible. Once your password expires, your account will be " & _
                    "disabled and you will have to contact a SITE Administrator to have your account " & _
                    "reinstated." & _
                    vbCrLf & vbCrLf & "If you have questions or issues with SITE, please contact:" & _
                    vbCrLf & "John Doe (mailto:John.Doe@mysite.com)" & vbCrLf & _
                    Jack Doe (mailto:Jack.Doe@mysite.com) or" & vbCrLf & _
                    "Jane Doe (mailto:Jane.Doe@mysite.com)" & vbCrLf

I guess what I need to do is somehow pass a value to SITENewAcctEmail (I'll make it a function) so that dependent on that value, it knows which of the other functions to call. Then I also need to know how to make that call, which was essentially what the original question was.
0
 
LVL 10

Author Comment

by:cjones_mcse
ID: 13718652
And yes, I'll have to modify the code in SITENewAcctEmail to work a little differently.
0
 
LVL 15

Expert Comment

by:ameba
ID: 13718861
I'm not sure where are the Function or Sub names.

For CallByName, function/sub must be Public and defined in object module (class or form). I used a form, if they are in a class, you should make an instance of a class.
Arguments can be passed to callbyname, too, (as a last argument in CallByName), but I suggest returning values like functions do (in 'retval'):

Option Explicit

Private Sub Form_Click()
    Dim s As String, res As Boolean
    s = CallByName(Me, "Fun_1", VbMethod)
    MsgBox s
End Sub

Public Function Fun_1() As String
    Fun_1 = "Hello from Fun_1"
End Function
0
 
LVL 10

Author Comment

by:cjones_mcse
ID: 13719388
Okay, how about passing a value TO a function? How do I do that? Based on which button is clicked in the form, I'll have to pass a value TO the SITENewAcctEmail() function so that it knows which email body to use.
0
 
LVL 15

Expert Comment

by:ameba
ID: 13719484
' pass and modify arguments
Option Explicit

Private Sub Form_Click()
    Dim s1 As String, s2 As String, ret As Boolean
    s1 = "argument1"
    s2 = "argument2"
    ret = CallByName(Me, "Fun_1", VbMethod, s1, s2)
    MsgBox s1 & vbCr & vbCr & s2 & vbCr & vbCr & ret
End Sub

Public Function Fun_1(arg1 As String, arg2 As String) As Boolean
    arg1 = "***" & arg1
    arg2 = "****" & arg2
    Fun_1 = True
End Function
0
 
LVL 15

Expert Comment

by:ameba
ID: 13719694
you probably wanted something like this:

' add two buttons
Option Explicit

Private Sub Command1_Click()
    SITENewAcctEmail "Fun_1"
End Sub
Private Sub Command2_Click()
    SITENewAcctEmail "Fun_2"
End Sub

Function SITENewAcctEmail(ByVal functionToUse As String)
    Dim x As String
    CallByName Me, functionToUse, VbMethod, x
    MsgBox x
End Function

' your functions
Public Function Fun_1(arg As String)
    arg = "** body1 **"
End Function

Public Function Fun_2(arg As String)
    arg = "*** body2 ****"
End Function
0
 
LVL 10

Author Comment

by:cjones_mcse
ID: 13720048
Okay, maybe we need a third party to intervene...I'm confused as heck.  Also, I ran your code and it doesn't pass the values of arg1 and arg2 back to Form_Click. I get a Message Box with argument1, argument2, and True. That should be ***argument1, ****argument2, and True if it passed those values back.
0
 
LVL 15

Expert Comment

by:ameba
ID: 13720510
> That should be ***argument1, ****argument2, and True

It is... arguments are modified... copy code to form and retry.

if argument is:    arg As String
it will be passed [in+out]

if argument is:    ByVal arg As String
it will be passed one way [in], function will not modify it.

return value is also passed one-way -> [out].  I suggested that method for your functions, see http:Q_21378696.html#13718861
0
 
LVL 10

Author Comment

by:cjones_mcse
ID: 13757330
I haven't forgotten this. Just haven't had the chance to try any of this out yet. Hopefully will get back to it very soon.
0
 
LVL 15

Accepted Solution

by:
ameba earned 375 total points
ID: 13757490
No problem. There are also other methods to put function calls to variables, at least 4 of them, but CallByName is easier.  You can also use If statement to select the correct function:
     For i = 1 To 2
         Do
              '...
              '...
              If i = 1 Then
                  Call Func1
              Else
                  Call Func2
              End If
         Loop
     Next
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question