Solved: Two arrays and one set of code

[paul_0722]

I'm a bit of newbie and I cannot get my head around how to do this...

I have two arrays and one set of code that processes the data in the same way. I'm on the verge of copying the 50+ lines of code twice - once for each set of array data - and I just *know* there is a better way to do this, but not sure what it is...

Just to explain, this is a bus schedule program that returns the time of the next bus. The input arrays are the Monday-Friday schedule (mf) array and the Saturday/Sunday (ss) array. The next bus arrive time code is the same regardless of day but the schedules are quite different.

Here is what I might do (probably wrong):

[VBA]
Dim one(3) as variant
Dim two(3) as variant
Dim a as integer 'this value will come from a user form
Dim b as variant
Dim c as variant
one(1) = "mon"
one(2) = "tue"
one(3) = "wed"
two(1) = "sat"
two(2) = "sun"
two(3) = "wkd"
If a = "mf" then
b = one(1) & " " & one(2)
c = one(1) & " " & one(3)
<<...and insert lots more code using array one here!!>>
End If
If a = "ss" than b = two(b)
b = two(1) & " " & two(2)
c = two(1) & " " & two(3)
<<...and insert lots more code using array two here!!>>
End If
[/VBA]

and here is what I want to do without copying the code block twice but the ?? indicate where I run out of ideas..

[VBA]
Dim one(3) as variant
Dim two(3) as variant
Dim a as integer 'this value will come from a user form
Dim b as variant
Dim c as string
one(1) = "one"
one(2) = "two"
one(3) = "three"
two(1) = "Bill"
two(2) = "Sam"
two(3) = "Larry"
If a = "mf" then b = ??
If a = "ss" than b = ??
b = ??(1) & " " & ??(2)
c = ??(1) & " " & ??(3)
<<...and insert lots more code using array one or two as determined by variable a here!!>>
End If
[/VBA]

I have an idea that the code block should be a function but have not been brave enough to try that approach yet - is that a good way to go? Any help appreciated...

[Bob Phillips]

[vba]

Sub Paul_0722()
Dim one(3) As Variant
Dim two(3) As Variant
Dim a As Integer 'this value will come from a user form
Dim b As Variant
Dim c As Variant

one(1) = "mon"
one(2) = "tue"
one(3) = "wed"
two(1) = "sat"
two(2) = "sun"
two(3) = "wkd"
Call ProcessArray(a, b, c, one, two)

one(1) = "one"
one(2) = "two"
one(3) = "three"
two(1) = "Bill"
two(2) = "Sam"
two(3) = "Larry"
Call ProcessArray(a, b, c, one, two)
End Sub

Private Sub ProcessArray(value1, value2, value3, array1, array2)
If value1 = "mf" Then
value2 = array1(1) & " " & array1(2)
value3 = array1(1) & " " & array1(3)
' <<...and insert lots more code using array one here!!>>
End If
If value1 = "ss" Then value2 = two(value2)
value2 = array2(1) & " " & array2(2)
value3 = array2(1) & " " & array2(3)
' <<...and insert lots more code using array two here!!>>
End If
End Sub
[/vba]

[paul_0722]

Thanks - I'll give this a try. I'm surprised that the code blocks are still repeated in the second sub. The code is exactly the same and uses the same index numbers of the array for each statement - only difference is the name of the array. For instance line 14 of the code block may call for array(17) with "array" (name of array) equal to "mf" or "ss". Still necessary to type in the code twice in the ProcessArray sub?

Thanks again for help!

[Bob Phillips]

It doesn't have to be, it is difficult to be precise with generic code like that, but you can take it further if it will take it.

[paul_0722]

Ok, thanks for confirming it can be done - gives me a little mroe confidence to try keeping the code efficient.

Thanks!