Passing arrays to sub-routines

[Guest]

I have a series of invoice reports. A box at the top displays the name and address of the invoicees. These are retrieved from a bound table which includes a typical set of name and address fields entitled Name_1, Name_2, Address_1, Address_2, Address_3, Town/City, County and Post_Code. The Name fields are concatenated on the report so that the recipient’s name is printed in the usual first-name-followed-by-surname format, in a single field. This is quite straightforward and causes no difficulty. The next bit is less easy, however. It is not desirable to print the remaining address fields straight off the table because some of them are often blank and the resulting direct output therefore unsightly. (CanShrink is of no use because it appears only possible to use it if there is nothing else printed on the same line as any of the fields that are to be shrunk to zero size.

I have therefore written a public ‘compacting’ sub which runs a bubble sort on the fields and packs the address fields into a contiguous block, leaving the blanks at the bottom, so that they do not appear on the print. The address fields are copied into a local 5-element variant array which is then passed to the compacting sub. On return from this sub, the elements of the compacted array are then allocated to the local fields on the report. This works just fine but the code is clumsy and I cannot, at the moment, see a way of making it any more succinct

The present code reads:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer
Dim Address(5) As Varian

Address(0) = Address_
Address(1) = Address_
Address(2) = Address_
Address(3) = Town_Cit
Address(4) = Count
Address(5) = Post_Cod
'It is necessary to copy the address fields into a private transfe
'array otherwise Compact_Address will attempt to repack the addres
'fields of tbl_Name_Address

Compact_Address Address(0), Address(1), Address(2), Address(3), Address(4), Address(5
Address_A = Address(0
Address_B = Address(1
Address_C = Address(2
Address_D = Address(3
Address_E = Address(4
Address_F = Address(5

'Address_A to Address_F are the field names on the report, where i
'does not appear to be possible to declare them as an array
End Su

The compacting sub is:

Public Sub Compact_Address(ParamArray InAddress() As Variant
Dim I As Integer, J As Intege
Dim Quit As Boolea

J =
For I = 0 To
If IsNull(InAddress(I)) = False Then J = J +
Nex
'J counts the number of non-null address fields in InAddress(
D
For I = 0 To 4 'End with the penultimate entr
If IsNull(InAddress(I)) The
InAddress(I) = InAddress(I + 1
InAddress(I + 1) = Nul
End I
Nex
Quit = Tru
For I = 0 To J -
If IsNull(InAddress(I)) Then Quit = Fals
Nex
'With more than one null address field, the bubble sort need
'more than one pass to pack the address array. The latte
'test determines whether the sort has finished by looking fo
'null fields in the first J fields. (Note that I starts fro
'a datum of 0 whereas J starts from 1.) When the sort ha
'finished, the address fields will have been packed into th
'the first J fields of the address array, which will contai
'no nulls
Loop Until Qui

End Su

Ideally, I would like to be able to write something like the following:

Compact_Address(RepArray(), Address_1, Address_2, Address_3, Town_City, County, Post_Code

(In fact this would probably have to be â€
Compact_Address RepArray(), ByVal Address_1, ByVal Address_2 etc.,
but I have omitted the ByVals, for the sake of brevity

RepArray() is the local address of the address field array on the report

I am unable to make this work, firstly because I cannot declare the address fields directly as an array, on the report, and secondly because I cannot avoid the use of the intermediary array Address() in the report detail formatting code. Even so, I am disappointed to note that I cannot pass Address() en bloc. If I write ‘Compact_Address Address()’, for example, I simply get a ‘subscript out of bounds’ error in the compacting sub

There must surely be a more elegant solution, or should I just be content that the thing delivers the goods, however clumsily?

 

[Allen Browne]

It is possible to pass a ParamArray, but an easier solution is to make use
of a slight inconsistency in how the two concatenation operators work:
"A" & Null => "A"
"A" + Null => Null

That means you can use a single text box 5 lines tall, and set its Control
Source to an expression such as this:

=[CompanyName]+Chr(13)+Chr(10) &
"Attention: "+[ContactPerson]+Chr(13)+Chr(10) &
[Address]+Chr(13)+Chr(10) &
Trim([City]+" " & [State]+" " & [Zip]) &
Chr(13)+Chr(10)+[FirstOfCountry]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a series of invoice reports. A box at the top displays the name

and address of the invoicees. These are retrieved from a bound table which
includes a typical set of name and address fields entitled Name_1, Name_2,
Address_1, Address_2, Address_3, Town/City, County and Post_Code. The Name
fields are concatenated on the report so that the recipient's name is
printed in the usual first-name-followed-by-surname format, in a single
field. This is quite straightforward and causes no difficulty. The next
bit is less easy, however. It is not desirable to print the remaining
address fields straight off the table because some of them are often blank
and the resulting direct output therefore unsightly. (CanShrink is of no
use because it appears only possible to use it if there is nothing else
printed on the same line as any of the fields that are to be shrunk to zero
size.)

I have therefore written a public 'compacting' sub which runs a bubble

sort on the fields and packs the address fields into a contiguous block,
leaving the blanks at the bottom, so that they do not appear on the print.
The address fields are copied into a local 5-element variant array which is
then passed to the compacting sub. On return from this sub, the elements of
the compacted array are then allocated to the local fields on the report.
This works just fine but the code is clumsy and I cannot, at the moment, see
a way of making it any more succinct.

The present code reads:-

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim Address(5) As Variant

Address(0) = Address_1
Address(1) = Address_2
Address(2) = Address_3
Address(3) = Town_City
Address(4) = County
Address(5) = Post_Code
'It is necessary to copy the address fields into a private transfer
'array otherwise Compact_Address will attempt to repack the address
'fields of tbl_Name_Address.

Compact_Address Address(0), Address(1), Address(2), Address(3), Address(4), Address(5)
Address_A = Address(0)
Address_B = Address(1)
Address_C = Address(2)
Address_D = Address(3)
Address_E = Address(4)
Address_F = Address(5)

'Address_A to Address_F are the field names on the report, where it
'does not appear to be possible to declare them as an array.
End Sub

The compacting sub is:-

Public Sub Compact_Address(ParamArray InAddress() As Variant)
Dim I As Integer, J As Integer
Dim Quit As Boolean

J = 0
For I = 0 To 5
If IsNull(InAddress(I)) = False Then J = J + 1
Next
'J counts the number of non-null address fields in InAddress()
Do
For I = 0 To 4 'End with the penultimate entry
If IsNull(InAddress(I)) Then
InAddress(I) = InAddress(I + 1)
InAddress(I + 1) = Null
End If
Next
Quit = True
For I = 0 To J - 1
If IsNull(InAddress(I)) Then Quit = False
Next
'With more than one null address field, the bubble sort needs
'more than one pass to pack the address array. The latter
'test determines whether the sort has finished by looking for
'null fields in the first J fields. (Note that I starts from
'a datum of 0 whereas J starts from 1.) When the sort has
'finished, the address fields will have been packed into the
'the first J fields of the address array, which will contain
'no nulls.
Loop Until Quit

End Sub

Ideally, I would like to be able to write something like the following:-

Compact_Address(RepArray(), Address_1, Address_2, Address_3, Town_City, County, Post_Code)

(In fact this would probably have to be -
Compact_Address RepArray(), ByVal Address_1, ByVal Address_2 etc.,
but I have omitted the ByVals, for the sake of brevity)

RepArray() is the local address of the address field array on the report.

I am unable to make this work, firstly because I cannot declare the

address fields directly as an array, on the report, and secondly because I
cannot avoid the use of the intermediary array Address() in the report
detail formatting code. Even so, I am disappointed to note that I cannot
pass Address() en bloc. If I write 'Compact_Address Address()', for
example, I simply get a 'subscript out of bounds' error in the compacting
sub.

There must surely be a more elegant solution, or should I just be content

that the thing delivers the goods, however clumsily?

 

[Guest]

Allen

Thank you for a perfect solution. I should have talked to you before I wrote the page of redundant code that has now been consigned to the wpb!