Passing Array Values to a Sub

[Gordon Bentley-Mix on news.microsoft.com]

I have a UserForm that contains functionality to collected multiple sets of
data points for similar things; e.g. the details of parties to a contract or
the details of rented goods. I collect the information in an array, which is
declared as Public (because I follow good coding practices and use separate
modules for UserForm-related and document-related operations and the data is
used in both places).

An example of the code for doing this, which is called in the Click event of
a button on the UserForm:

Private Sub AddCustomer()
CustomerCount = CustomerCount + 1
ReDim Preserve CustomersArray(1, 1 To CustomerCount) As Variant
CustomersArray(0, CustomerCount) = Trim(txtCustomerName.Value)
CustomersArray(1, CustomerCount) = Trim(txtCustomerNumber.Value)
End Sub

Further, I save the values from the arrays into document variables so I can
retrieve them to rerun the code. An example of the code that does this:

Private Sub SaveCustomerVariables()
Dim i As Long
Dim n As Long
Dim myVariable As String
For i = 1 To UBound(CustomersArray, 2)
For n = 0 To UBound(CustomersArray, 1)
myVariable = "Customer" & i & "Variable" & n
SaveVariableValue myVariable, CustomersArray(n, i)
Next n
Next i
End Sub

And an example of the code that loads them back into the UserForm on a rerun:

Private Sub LoadCustomerVariables()
Dim i As Long
Dim n As Long
Dim myVariable As String
ReDim CustomersArray(1, 1 To CustomerCount) As Variant
For i = 1 To CustomerCount
For n = 1 To 2
myVariable = "Customer" & i & "Variable" & n
CustomersArray(n - 1, i) = fcnLoadTextVariableValue(myVariable)
Next n
Next i
CheckCustomerButtons
LoadCustomersList
End Sub

And if the template is being rerun, I also have code that deletes any
existing doc vars prior to saving the new ones. An example of the code that
does this:

Private Sub DeleteCustomerVariables()
Dim PreviousCustomerCount As Long
Dim i As Long
Dim n As Long
Dim myVariable As String
With myDoc
If fcnFindVariable("CustomerCount") = True Then
PreviousCustomerCount =
fcnLoadNumericVariableValue("CustomerCount")
For i = 1 To PreviousCustomerCount
For n = 1 To 2
myVariable = "Customer" & i & "Variable" & n
DeleteVariable myVariable
Next n
Next i
End If
End With
End Sub

Now here's where things get fun.

Any one template may have as many as five (or more) instances of similar
functionality, which means five arrays and - more importantly - five separate
routines each for saving, retrieving and deleting the doc vars related to
each instance of the functionality. Obviously these are prime candidates for
using a 'generic' routine that accepts arguments so as to cut down on the
number of lines of code and make maintenance easier. And to that end, I've
developed such a routine for deleting the variables, which looks like this:

Public Sub DeleteArrayVariables(myName As String, myCount As Long)
Dim CountVariableName As String
Dim PreviousCount As Long
Dim i As Long
Dim n As Long
Dim myVariableName As String
CountVariableName = myName & "Count"
With myDoc
If fcnFindVariable(CountVariableName) = True Then
PreviousCount = fcnLoadNumericVariableValue(CountVariableName )
For i = 1 To PreviousCount
For n = 0 To myCount
myVariableName = myName & i & "Variable" & n
DeleteVariable myVariableName
Next n
Next i
End If
End With
End Sub

and gets called like this:

Private Sub DeleteCustomerVariables()
DeleteArrayVariables "Customer", 1
End Sub

However, what I'm struggling with is the routines for saving and retrieving
the array values - especially around how to identify the array used in the
routine. It seems to me that since the arrays are declared as Public, there
should be a way to just pass in (or build) the name of the array rather than
pass in the entire array. Suggestions?
--
Cheers!

Gordon Bentley-Mix
Word MVP

Please post all follow-ups to the newsgroup.

Read the original version of this post in the Office Discussion Groups - no
membership required!

 

[Cindy M.]

Hi Gordon,

However, what I'm struggling with is the routines for saving and retrieving
the array values - especially around how to identify the array used in the
routine. It seems to me that since the arrays are declared as Public, there
should be a way to just pass in (or build) the name of the array rather than
pass in the entire array. Suggestions?

Decide on an "identifier" for each array (a string, such as "Customers") and
pass that to the procedure. Use a Select Case in the procedure to determine
which string was passed.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[Gordon Bentley-Mix on news.microsoft.com]

Thanks Cindy. Although I didn't use your suggestion - I wanted the process to
be as "generic" as possible and not limited to certain specific arrays - it
did get me thinking of other ways to approach the problem. So after a couple
of nights' sleep, I've managed to come up with the following code for saving
values from an array into document variables:

Public Sub SaveArrayVariables(ByRef myName As String, ByRef mySourceArray()
As Variant)
Dim i As Long
Dim n As Long
Dim myVariableName As String
For i = 1 To UBound(mySourceArray, 2)
For n = 0 To UBound(mySourceArray, 1)
myVariableName = myName & i & "Variable" & n
SaveVariableValue myVariableName, mySourceArray(n, i)
Next n
Next i
End Sub

This just gets called like so:

Private Sub SaveCustomerVariables()
SaveArrayVariables "Customer", CustomersArray()
End Sub

That then gave me some ideas for reloading the values from the doc vars back
into the array, and I came up with this:

Public Sub LoadArrayVariables(ByRef myName As String, ByRef myTargetArray()
As Variant)
Dim i As Long
Dim n As Long
Dim myVariableName As String
For i = 1 To UBound(myTargetArray, 2)
For n = 0 To UBound(myTargetArray, 1)
myVariableName = myName & i & "Variable" & n
myTargetArray(n, i) = fcnLoadTextVariableValue(myVariableName)
Next n
Next i
End Sub

Which gets called like this:

Private Sub LoadCustomerVariables()
ReDim CustomersArray(1, 1 To CustomerCount) As Variant
LoadArrayVariables "Customer", CustomersArray()
CheckCustomerButtons
LoadCustomersList
End Sub

The code does pass the arrays around, but that's OK - especially in
LoadArrayVariables where I need to modify the array anyway. (Which reminds
me: I need to look at how I'm passing the other values into the various subs,
as I think most of them should be ByVal; can't seem to get that straight in
my head...)

Thanks again for helping me to look in the right direction.
--
Cheers!

Gordon Bentley-Mix
Word MVP

Please post all follow-ups to the newsgroup.

Read the original version of this post in the Office Discussion Groups - no
membership required!

 

[Cindy M.]

Hi Gordon,

The code does pass the arrays around, but that's OK

(Which reminds
me: I need to look at how I'm passing the other values into the various subs,
as I think most of them should be ByVal; can't seem to get that straight in
my head...)

Some things can only be passed ByRef - it depends on the data type... Rule-of-
thumb: if it's numeric (which includes Boolean, Byte, etc), you can pass byVal.
Other things should be passed ByRef.

I may have this wrong, as my mind currently thinks more in ".NET" but... As I
recall, in VBA strings can be passed ByVal, but it involves some overhead in the
processing (because natively it would be ByRef), so they should be passed ByRef.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[Gordon Bentley-Mix]

Cindy,

The only reason I didn't want to pass in the arrays is because they're
already declared as Public (for other reasons), so passing them as an
argument seemed a bit redundant. I was hoping there might be a way to just
pass in the name of the array and pick it up from there, but I think I was
just being picky; there's not much difference between passing in some kind
of identifier and passing in the array itself.

As for ByRef v ByVal, the only thing I'm passing ByRef is the array; the
rest go ByVal because I don't need to modify them in any way.

Thanks again for the help.