Passing variables to a sub routine

[redrob28]

Hi

Bit of a beginner so bear with me.
I am trying to do something very basic and cannot quite see what I am doing wrong so any help would be appreciated.

I have a public sub routine which requires parameters to be passed to it when I call it from an access form. When I try to enter the code to call the sub I get a compile error. It really seems quite simple so I am sure someone can tell me where I am going wrong. I've also tried calling it from another sub in the same module but get the same compile error - see below.

 Sub EmailData(Datafile As String, To_mail As String, CC_mail As String, Subject_mail As String)
'code to use variables passed in
End Sub
 
 Sub Test()
 Dim MTable As String
 Dim To_m As String
 Dim CC_m As String
 Dim Subj_mail As String
 
 
 MTable = "MH_Email_Data"
 To_m = "emailaddress1"
 CC_m = "emailaddress2"
 Subj_mail = "Today's helpful report"
 
 EmailData(MTable,To_m,CC_m,Subj_mail)
 
 
   End Sub

Thanks for any help.

 

[Rabbie]

Just replace

EmailData(MTable,To_m,CC_m,Subj_mail)

with

Call EmailData(MTable,To_m,CC_m,Subj_mail)

and see if that works

 

[TJPoorman]

You may need to declare it explicitly as Public (i.e. Public Sub EmailData)

But I suspect the problem is with your parenthesis. Only put parenthesis around your variables if you are returning a value.

So if your function was defined like:

Sub EmailData(Datafile As String, To_mail As String, CC_mail As String, Subject_mail As String) As String

And as part of your code you made the value of EmailData return that it was successful, then you would need to put parenthesis around the variables.

Try:

EmailData MTable, To_m, CC_m, Subj_mail

 

[Galaxiom]

You may need to declare it explicitly as Public (i.e. Public Sub EmailData)

Subs and Functions in Standard Modules default to Public. However ti is good practice to declare their scope.

But I suspect the problem is with your parenthesis. Only put parenthesis around your variables if you are returning a value.

So if your function was defined like:

Sub EmailData(Datafile As String, To_mail As String, CC_mail As String, Subject_mail As String) As String

A Sub cannot have a return value.

Parentheses around the parameter list is function syntax. Leave them off for Subs.

 

[redrob28]

Hi

Thanks leaving out the parentheses worked a treat.
Funny how the helpful VBA prompt as you type the variables in suggests to you that the format requires parentheses. I suspected it would be something basic!