Best way to have a subroutine modify more than one variable

11 Oct 2005 5:07 PM

LurfysMa

I have a program that manages a database. It responds to user actions
that affect individual record in the database. One of the functions
involves a bunch of calculations that results in new values to several
fields in that database (straight average, rolling average, weighted
average, etc.).

I would like to put all of these calculations into a subroutine, but I
am not sure of the best way to code it.

If only one value was getting updated, I would call the program call
the subroutine, pass it whatever values it needs, and have it return
the new value, which the calling program would then store in the
database.

In this situation, several fields need to be updated. I know I can
pass the database record set itself and let the subroutine do the
updates, but I prefer to keep the new database values in variables and
write them out all at once at the end in one place.

So, I could pass the field variables ByRef and then any changes the
subroutine makes would affect the actual variables. This seems "dirty"
to me. Is there a better way?

Thanks

--
For email, use Usenet-20031220@spamex.com

11 Oct 2005 5:23 PM

Ken Halter

"LurfysMa" <invalid@invalid.invalid> wrote in message
news:orrnk194frfa7odm17k2s8v35ntd878ahq@4ax.com...
>I have a program that manages a database. It responds to user actions
> that affect individual record in the database. One of the functions
> involves a bunch of calculations that results in new values to several
> fields in that database (straight average, rolling average, weighted
> average, etc.).
>
> I would like to put all of these calculations into a subroutine, but I
> am not sure of the best way to code it.

I like to pass classes around. A class can keep the sub from directly
modifying the data if required.

Like anything else, there are dozens of options. You can pass arrays, set up
a function to return arrays, use ParamArray to send a variable number of
arguments, etc, etc. Passing a class is pretty straight forward.... You can
even code it as a function that returns a class. That class can be
PublicNotCreatable if you want to control who's creating who.

--
Ken Halter - MS-MVP-VB - http://www.vbsight.com
DLL Hell problems? Try ComGuard - http://www.vbsight.com/ComGuard.htm
Please keep all discussions in the groups..

11 Oct 2005 5:52 PM

Someone

2 easy ways:

1 - ByRef:

Public Function GetResults(ByRef p1 As Double, ByRef p2 As Double) As Double

2 - Using a UDF:

Public Type MyResultsT
    p1 As Double
    p2 As Double
    p3 As Double
End Type

Public Function GetResults() As MyResultsT
    Dim ret As MyResultsT

    ret.p1 = 1
    ret.p2 = 2
    ret.p3 = 3

    GetResults = ret
End Function

How to call:

Private Sub Form_Load()
    Dim ret As MyResultsT
    ret = GetResults2()
    Debug.Print ret.p1, ret.p2, ret.p3
End Sub

The UDF method will also work in the same way if you converted it to a
class. This depends on your need.

Show quoteHide quote

"LurfysMa" <invalid@invalid.invalid> wrote in message
news:orrnk194frfa7odm17k2s8v35ntd878ahq@4ax.com...
>I have a program that manages a database. It responds to user actions
> that affect individual record in the database. One of the functions
> involves a bunch of calculations that results in new values to several
> fields in that database (straight average, rolling average, weighted
> average, etc.).
>
> I would like to put all of these calculations into a subroutine, but I
> am not sure of the best way to code it.
>
> If only one value was getting updated, I would call the program call
> the subroutine, pass it whatever values it needs, and have it return
> the new value, which the calling program would then store in the
> database.
>
> In this situation, several fields need to be updated. I know I can
> pass the database record set itself and let the subroutine do the
> updates, but I prefer to keep the new database values in variables and
> write them out all at once at the end in one place.
>
> So, I could pass the field variables ByRef and then any changes the
> subroutine makes would affect the actual variables. This seems "dirty"
> to me. Is there a better way?
>
> Thanks
>
>
> --
> For email, use Usenet-20031220@spamex.com