PDA

View Full Version : Passing variables not working



talytech
03-12-2009, 09:03 AM
I've checked the forum for this particular issue and I found an example of what I want to do, but I can't make sense out of it and I can't get my code to work. Can somebody please help me?

I have a userform with a button on it that loops through rows on a spread sheet. then it takes the information from the cell and set it to a variable. then I call a function called sendNotice that sends an email with the variable "myName" in the body. I don't know how to pass the variable to my function. Here's my code that doesn't work and for the sake of shortening the code I didn't include all the declared variables.


Private Sub cmdUpdateDB_Click()
dim myName as string

lastrow = Cells(Rows.Count, "b").End(xlUp).Row

myName = ""

For I = 20 To lastrow
'Get Fields
ctrfname = Cells(I, "b").Value
ctrlname = Cells(I, "f").Value
ctrmid = Cells(I, "e").Value
ctrSSN = Cells(I, "i").Value
ctrDOB = Cells(I, "j").Value
ctrVendor = Cells(I, "k").Value
If myName = "" Then
myName = "* " & ctrfname & Chr(32) & ctrlname & ", " & ctrVendor
Else
myName = myName & Chr(13) & "* " & ctrfname & Chr(32) & ctrlname & ", " & ctrVendor
End If
Next

Call SendNotice

End Sub

--------------------------------------------------------------------


Function SendNotice(myName as string)
Set OLF = GetObject("", _
"Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
Set olMailItem = OLF.Items.Add ' creates a new e-mail message
With olMailItem
.Subject = "EXCEPTION REQUEST FOR TEMPORARY FACILITIES ACCESS (" & exceptapprvd & ")" ' message subject

Set ToContact = .Recipients.Add("somename@fanniemae.com") ' add a recipient
Set ToContact = .Recipients.Add("somename@fanniemae.com")

Set ToContact = .Recipients.Add("" & mgruserid & "")
ToContact.Type = olCC ' set latest recipient as CC

Set ToContact = .Recipients.Add("" & vpUserID & "")
ToContact.Type = olCC ' set latest recipient as CC

.Body = myName
.Display 'Send ' sends the e-mail message (puts it in the Outbox)
End With
ENd Function

CreganTur
03-12-2009, 09:11 AM
You need to provide a parameter value when you call the SendNotice function. Like this:
Call SendNotice myName

This calls the function and provides the value of the variable myName to satisfy the function's parameter.

Bob Phillips
03-12-2009, 09:20 AM
You need to provide a parameter value when you call the SendNotice function. Like this:
Call SendNotice myName

This calls the function and provides the value of the variable myName to satisfy the function's parameter.

You mean like this

Call SendNotice(myName)

Bob Phillips
03-12-2009, 09:23 AM
BTW, I personally think it is a bad idea to have the name of an argument in the called procedure the same as the name of the variable passed in the calling procedure. It just might lead people to think it is the same variable, and it is not.

Someone will probably pount to a dozen instances where I have done it now :)

Bob Phillips
03-12-2009, 09:24 AM
Also, just noticed that myName is used to load the email Body. Not exactly a descriptive argument name!

talytech
03-12-2009, 09:58 AM
I tried that and I get the following compile error:

"ByRef argument type mismatch"

I don't know what's going on.

Bob Phillips
03-12-2009, 10:23 AM
Where did you get that, which line, the call? It doesn't happen for me.

talytech
03-12-2009, 11:05 AM
OK .. my code is long so I tried to shorten it and give you the simple form but maybe I've missed something. Hopefully this helps.

1. On my userform I have variables set to the cells on my spreadsheet and the variable "myName". For example ctrFname = whatever the value is in the associated cell. And myName = ""

2 Then if the variable "myName" = "" then set myName = ctrFname. Else set myName = myName & chr(13) & ctrFname

3. then the code goes to the next row and does the same thing.

4. then after I get the value of myName, I want to pass that value to my function called SendNotice and have it show up as the body for my email. For example: I have 3 rows so "myName" should look something like this:
Marcy
Jack
Tasha


Dim myName, results As String

lastrow = Cells(Rows.Count, "b").End(xlUp).Row

'CHECK FOR Exception record
myName = ""

For I = 20 To lastrow
'Get Fields

ctrfname = Cells(I, "b").Value
ctrlname = Cells(I, "f").Value
ctrmid = Cells(I, "e").Value
ctrSSN = Cells(I, "i").Value
ctrDOB = Cells(I, "j").Value
ctrVendor = Cells(I, "k").Value

If myName = "" Then
myName = "* " & ctrfname & Chr(32) & ctrlname & ", " & ctrVendor
Else
myName = myName & Chr(13) & "* " & ctrfname & Chr(32) & ctrlname & ", " & ctrVendor
End If

Next

Call SendNotice(myName)



Function SendNotice(myName as string)

Dim OLF As Outlook.MAPIFolder, olMailItem As Outlook.MailItem
Dim ToContact As Outlook.Recipient


Set OLF = GetObject("", _
"Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
Set olMailItem = OLF.Items.Add ' creates a new e-mail message
With olMailItem
.Subject = "EXCEPTION REQUEST FOR TEMPORARY FACILITIES ACCESS (" & exceptapprvd & ")" ' message subject

Set ToContact = .Recipients.Add("somename@fanniemae.com") ' add a recipient
Set ToContact = .Recipients.Add("somename@fanniemae.com")

Set ToContact = .Recipients.Add("" & mgruserid & "")
ToContact.Type = olCC ' set latest recipient as CC

Set ToContact = .Recipients.Add("" & vpUserID & "")
ToContact.Type = olCC ' set latest recipient as CC

.Body = myName
.Display 'Send ' sends the e-mail message (puts it in the Outbox)
End With

Kenneth Hobs
03-12-2009, 11:53 AM
It is a bit hard to troubleshoot since I don't have your data and not all varialbes declared.

Can you post a sample xls or sample data for the loop range?

What should the string that you are passing look like based on the data?

Have you tried sending a hard coded string to see if it does what you want.

Have you considered using CDO?

talytech
03-12-2009, 12:17 PM
OK .. I fixed it. I had to tell my function how I wanted to use the variable .. like this:

Function SendNotice(ByVal msgNames As String)

That worked.

Now I have another issue ... what if I want to pass values between two subs? For instance, on my UserForm .. I call up another userform with a listbox. I set the same variables on that form but I want the values to be passed to my first userform so that it then can be passed to SendNotice function. Is that doing way too much? I am almost finished this project .. please tell me that is possible.

talytech
03-12-2009, 12:48 PM
Actually, I didn't explain my next issue good enough.

1. From one user form (UserForm A), I have a button that loops through the rows just like in my previous thread and then calls or Shows another UserForm (UserForm B)

2. On userForm B on click of a button, I have a variable called "updateName" which is set to whatever the value is in the cell of the corresponding row.
ctrlname = Cells(myRow, "f").Value
updateName = ctrfname

3. The code closes userForm B and continues with the next row in the loop.

4. I want the value of updateName from userForm B to be passed to UserForm A for later use ... to be used after my Loop finishes. So like in my previous thread the names can show up in a list and be passed to the SendNotice function.

Bob Phillips
03-12-2009, 01:22 PM
Declare a public variable, and store the value there. It is the simplest way.

talytech
03-12-2009, 02:03 PM
I tried to declare the public variable but it gives me an error: "Invalid attribute in Sub or Function".

where should I be putting the declaration? This is what I tried to do:
Private Sub ExistBI_Click()
Public updateName As String

Bob Phillips
03-12-2009, 02:15 PM
Public variables are declared in a standard code module, before any procedure.

mdmackillop
03-12-2009, 04:50 PM
From Post #8
Dim myName, results As String

This is only dimming "Results" as String, not both variables. myName is Variant, hence the mismatch.

Dim myName as String, results As String

talytech
03-13-2009, 07:45 AM
OK .. thanks guys .. I figured it out and it works. Thanks for all of your help. You all helped me to complete my project. However, there is one last thing I would like to do. Between my loops the code takes a while to finish the events. I don't want the user to think that it's not working because it looks like it isn't doing something. Is there a way I can change the mouse pointer from an arrow to an hourglass so that the user knows the program is still running? I'm searching the forum for that now. but any suggestions would be great. Thanks again for all of your help.

Bob Phillips
03-13-2009, 08:21 AM
Application.Cursor=xlWait

'then

Application.Cursor=xlDefault

mdmackillop
03-13-2009, 08:31 AM
You can also put a message in the Statusbar



StatusBar Property Example
This example sets the status bar text to "Please be patient..." before it opens the workbook Large.xls, and then it restores the default text.

oldStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.StatusBar = "Please be patient...
"Workbooks.Open filename:="LARGE.XLS"
Application.StatusBar = False
Application.DisplayStatusBar = oldStatusBar