Array in Both Branches of an If Statement

[ron]

Here is an excerpt of a macro that processes an array of street names
later in the macro. The street names can either come from a set group
of street names or a user inputted street or group of streets.

z = 0

If Worksheets("Macro").CheckBox1.Value = True Then
street = Array(""Columbine", "Lois", "Poppy")
Else:
Do Until yy = 7 ' 7=no
z = z + 1
ReDim Preserve street(1 To z)
street(z) = InputBox("Enter the name of the street you
want to search.")
yy = MsgBox("Would you like to enter another street?",
vbYesNo)
Loop
End If

The above construction fails. The following modification allows it
run, but seens unwieldy. Is there a more efficient method, one that
would allow me to use the array "street" in both branches of the IF
statement?

z = 0

If Worksheets("Macro").CheckBox1.Value = True Then

mystreet = Array("Columbine", "Lois", "Poppy")

For z = 0 To 2
ReDim Preserve street(1 To z + 1)
street(z + 1) = mystreet(z)
Next
Else:
Do Until yy = 7 '7=no
z = z + 1
ReDim Preserve street(1 To z)

street(z) = InputBox("Enter the name of the street you
want to search.")
yy = MsgBox("Would you like to enter another street and
city?", vbYesNo)
Loop
End If


....TIA, Ron

 

[Dave Peterson]

This worked ok for me...

Dim StreetArray As Variant
Dim resp As Long
Dim sCtr As Long

If Worksheets("Macro").CheckBox1.Value = True Then
StreetArray = Array("Columbine", "Lois", "Poppy")
Else
sCtr = 0
ReDim StreetArray(1 To 1)
Do Until resp = vbNo
sCtr = sCtr + 1
ReDim Preserve StreetArray(1 To sCtr)
StreetArray(sCtr) _
= InputBox("Enter the name of the street you want to search.")
resp _
= MsgBox(Prompt:="Would you like to enter another StreetArray?", _
Buttons:=vbYesNo)
Loop
End If

But I don't think I'd use it. If the user hits the cancel button on the first
pass through in that loop, then you'll still be including that empty string in
the array you're building.

I think I'd do something like:

Dim StreetArray As Variant
Dim StreetName As String
Dim TestWks As Worksheet
Dim resp As Long
Dim sCtr As Long
Dim StreetArrayCreated As Boolean

StreetArrayCreated = False

sCtr = -1 'an indicator
If Worksheets("Macro").CheckBox1.Value = True Then
StreetArray = Array("Columbine", "Lois", "Poppy")
StreetArrayCreated = True
Else
sCtr = 0
ReDim StreetArray(1 To 1)
resp = vbYes
Do
StreetName _
= InputBox("Enter the name of the StreetArray you want to search.")
If Trim(StreetName) <> "" Then
StreetArrayCreated = True
sCtr = sCtr + 1
ReDim Preserve StreetArray(1 To sCtr)
StreetArray(sCtr) = StreetName
End If
resp _
= MsgBox(Prompt:="Would you like to enter another StreetArray?", _
buttons:=vbYesNo)
Loop Until resp = vbNo
End If

If StreetArrayCreated = True Then
For sCtr = LBound(StreetArray) To UBound(StreetArray)
MsgBox StreetArray(sCtr)
Next sCtr
Else
MsgBox "no elements in StreetArray Array"
End If

I'm not sure what StreetArray (I changed the name) represents--but if it is a
collection of worksheet names, I'd add some more checking.

In fact, you may want to add a multiselect listbox to the worksheet that
displays all the possible streets so that you can just get the input that way.

 

[ron]

This worked ok for me...

    Dim StreetArray As Variant
    Dim resp As Long
    Dim sCtr As Long

    If Worksheets("Macro").CheckBox1.Value = True Then
        StreetArray = Array("Columbine", "Lois", "Poppy")
    Else
        sCtr = 0
        ReDim StreetArray(1 To 1)
        Do Until resp = vbNo
            sCtr = sCtr + 1
            ReDim Preserve StreetArray(1 To sCtr)
            StreetArray(sCtr) _
              = InputBox("Enter the name of the street you want to search.")
            resp _
              = MsgBox(Prompt:="Would you like to enteranother StreetArray?", _
                       Buttons:=vbYesNo)
        Loop
    End If

But I don't think I'd use it.  If the user hits the cancel button on the first
pass through in that loop, then you'll still be including that empty string in
the array you're building.

I think I'd do something like:

    Dim StreetArray As Variant
    Dim StreetName As String
    Dim TestWks As Worksheet
    Dim resp As Long
    Dim sCtr As Long
    Dim StreetArrayCreated As Boolean

    StreetArrayCreated = False

    sCtr = -1 'an indicator
    If Worksheets("Macro").CheckBox1.Value = True Then
        StreetArray = Array("Columbine", "Lois", "Poppy")
        StreetArrayCreated = True
    Else
        sCtr = 0
        ReDim StreetArray(1 To 1)
        resp = vbYes
        Do
            StreetName _
             = InputBox("Enter the name of the StreetArray you want to search.")
            If Trim(StreetName) <> "" Then
                StreetArrayCreated = True
                sCtr = sCtr + 1
                ReDim Preserve StreetArray(1 To sCtr)
                StreetArray(sCtr) = StreetName
            End If
            resp _
              = MsgBox(Prompt:="Would you like to enteranother StreetArray?", _
                       buttons:=vbYesNo)
        Loop Until resp = vbNo
    End If

    If StreetArrayCreated = True Then
        For sCtr = LBound(StreetArray) To UBound(StreetArray)
            MsgBox StreetArray(sCtr)
        Next sCtr
    Else
        MsgBox "no elements in StreetArray Array"
    End If

I'm not sure what StreetArray (I changed the name) represents--but if it is a
collection of worksheet names, I'd add some more checking.

In fact, you may want to add a multiselect listbox to the worksheet that
displays all the possible streets so that you can just get the input thatway.















--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave...Thanks for your response. It clarified things and enabled me
to find the error that was preventing me from using the same array in
both branches of the IF statement (changing my dim statement from
dim street() as string
to
dim street() as variant
solved the problem). Thanks again...Ron

 

[Dave Peterson]

One version of excel (xl97????) has a problem with:
dim street() as variant
I'd use:
dim street as variant
(without the ()'s)

 

[Dave Peterson]

ps. The real problem is a combination of:

Dim street() as variant
street = array(....)

It's not just the declaration line that's a problem.