passing a value back to original routine

[MJKelly]

Hi,

I use a peice of code 7 times (once for each day of the week), to
identify unallocated jobs.
I call this code 7 times from a routine (passing the dayname etc).

How can I add the total resulting unallocated jobs back to the
original routine, so I can use the value in an end message to the
user?

Should I just use a public variable?

Kind regards,
Matt

 

[RB Smissaert]

Make it a function:

Function GetUnAllocatedJobs(strDayName As String) As Long

Dim x as Long

'run code here to get unallocated jobs, producing a number x
GetUnAllocatedJobs = x

End Function


RBS

 

[Joel]

You can also pass a variable by reference

Sub Test1()
Dim a as Integer
a = 1
call Test2(a)
msgbox(a)
End Sub

Sub Test2(ByRef x as Integer)
x = x + 5
End Sub

 

[RB Smissaert]

Yes, I know, but I think in this case a function is better as it is clearer
what is going on and less confusing.

RBS

 

[MJKelly]

Thanks for the replies.

Could you just explain how I use the function?

macro1 does a number of things and calls the function 7 times...
At the end of macro1, I want to tell the user the total number of
unallocated jobs (all days added together).

So, would this be msgbox getunallocatedjobs?
I still only see this working by declaring a public variable to hold
the unallocated jobs for Monday, then add Tuesday count to it and so
on. This being the value of x for each time the function is called?


Thanks,
Matt

 

[RB Smissaert]

If the function is called 7 times in the same procedure then you can do
this:

Dim lSum As Long

lSum = lSum + GetUnAllocatedJobs("Monday")
lSum = lSum + GetUnAllocatedJobs("Tuesday")

etc.

If it is called in different procedures then lSum has to be a Public
(applies to whole workbook)
or Private (applies to that module only) variable.


RBS

 

[MJKelly]

If the function is called 7 times in the same procedure then you can do
this:

Dim lSum As Long

lSum = lSum + GetUnAllocatedJobs("Monday")
lSum = lSum + GetUnAllocatedJobs("Tuesday")

etc.

If it is called in different procedures then lSum has to be a Public
(applies to whole workbook)
or Private (applies to that module only) variable.

RBS

Excellent. Thanks very much.
Kindest regards,
Matt