How to return a variable string from a function and reference the string in a sub

Question

I'm currently working on a VBA sheet which will email out certain cell values based on the true/false value of a relative toggle button. I am working with a rather large amount of toggle buttons and was looking into adding a function to return a string of the cell values with the corresponding toggle button values. However, my current string's return variable is blank.

I currently have the following email sub which is called via a command button:

Public Sub Send_Email_Using_VBA()
Dim Email_Subject, Email_Send_From, Email_Send_To, _
Email_Cc, Email_Bcc, Email_Body, First_Name, Second_Name, Third_Name As String
Dim example As Range
Dim callOutString As String
Dim Mail_Object, Mail_Single As Variant

Email_Subject = "Check Sheet Completed"
Email_Send_From = "[email protected]"
Email_Send_To = "[email protected]"
Email_Cc = ""
Email_Bcc = ""
Email_Body = ""
On Error GoTo debugs
Set Mail_Object = CreateObject("Outlook.Application")
Set Mail_Single = Mail_Object.CreateItem(0)
With Mail_Single
.Subject = Email_Subject
.To = Email_Send_To
.CC = Email_Cc
.BCC = Email_Bcc
.Body = Email_Body & bodyString 'trying to call/add resulting string from the below sub
.Send
End With
debugs:
If Err.Description <> "" Then MsgBox Err.Description
End Sub

And I am attempting to call/return the string value of "bodyString" from the following sub:

'Checks sheet for true toggle boxes which need immediate attention or notice and adds those descriptors to the email body.
Private Sub CommandButton5_Click()

Dim bodyString As String
Dim x As Integer
x = 1

'togglebutton2 check to see if active
If ToggleButton2 = True Then
    If x = 1 Then
        bodyString = "This item: " & Range("A14").Value & " is Bad"
    End If
    x = x + 1
End If

If x = 1 Then
    MsgBox "No items need attention"
    bodyString = "No items need attention"
Else
    MsgBox x - 1 & " Items Need Attention:" & vbCrLf & vbCrLf & bodyString
    bodyString = x - 1 & " Items Need Attention:" & vbCrLf & vbCrLf & bodyString
End If

End Sub

In the above final If/Else statement, I am using the MsgBox function to display the bodyString to ensure that it is storing the correct data.

For example, the MsgBox will display: No items need attention. However the bodyString variable returns a blank.

Normally you would have a Function that returns a string (i.e. Function DoThis(strInput as String) As strBodyString ) But you can't modify the system Event (i.e. Click event) to return a value. I don't see where you call 'Send_Email_Using_VBA', nor do I see where you pass a value to it, but that's ok. You could declare your variable at the Form level, thus it's available everywhere in that form. — Wayne G. Dunn
Thanks for the feedback Wayne, I'll have to look more into Functions and their capabilities. — a.simko

STLDev STLDev · Accepted Answer · 2017-03-28T20:13:14

The variable bodyString is defined within the event handler `Sub CommandButton5_Click(), and its value cannot be returned out of this subroutine.

To address this issue, move the declaration of bodyString out of the Sub CommandButton5_Click() routine and place it such that it's declared at the module-level (outside of all functions and subroutines). Then Sub Send_Email_Using_VBA() will be able to access that variable and its contents will be maintained.

How to return a variable string from a function and reference the string in a sub

1 Answers