Passing a variable from function to sub

mightymike

Board Regular
Joined
Feb 11, 2010
Messages
127
Can't work out why this is not working, can anybody help?

I have a routine which creates a pivot table, and on that table is data which I use to do a SQL query to retrieve data I need to use for something else.

Code:
Dim Approver As String
          Dim EMail As String

Approver = Sheets("Purchase Invoice Approval").Range("B1").Value
    
    If Approver <> "" Then
        EMail = GetEMailAddy(Approver)
    Else
        EMail = "Approver is blank"
    End If

So if I am correct, that sets the approver value.

Now we go and get the EMail addy

Code:
Function GetEMailAddy(Approver As String)
     Dim conn As Variant
     Dim rs As Variant
     Dim cs As String
     Dim query As String
     Dim row As Integer
     Dim EMail As String
     
     
     Set conn = CreateObject("adodb.connection")
     Set rs = CreateObject("adodb.recordset")
 
            cs = "DRIVER=SQL Server;"
            cs = cs & "DATABASE=Database;"
            cs = cs & "SERVER=Server"
 
     'parameters here are connectionString, username, password
     'quotes here for this code to work.
     conn.Open cs, "", ""
 
     query = "select ExDescription.[EMail] FROM ExDescription Where ExDescription.[Description] ='" & Approver & "';"
     rs.Open query, conn
     
      
     row = 0
     Do Until rs.EOF
       row = row + 1
       EMail = rs.Fields("EMail").Value
       'Test to see if it works - pastes result into current active worksheet
       'Cells(row, 1).Value = EMail
       rs.MoveNext
     Loop
         
     rs.Close
     Set rs = Nothing
 
     conn.Close
     Set conn = Nothing
 
End Function

Now, I have tested this function and it works fine, but it does not seem to be passing back the EMail value to the sub routine.

I just dont get any data back :(

Any ideas?
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881
Now, I have tested this function and it works fine, but it does not seem to be passing back the EMail value to the sub routine.

I just dont get any data back :(

Well, you are not passing it back. :)

Insert before the End Function the statement:

Code:
GetEMailAddy = Email

This is the returning value statement.
 

mightymike

Board Regular
Joined
Feb 11, 2010
Messages
127
Thank you vaskov17 & pgc01

When I look at it now I think...DUH! How could I not have worked that out :)

So simple when you know how.

Cheers guys.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,482
Messages
5,596,402
Members
414,063
Latest member
N_Bates

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
Top