topic Re: Loop inside a loop or Nested Loop in VBA in ArcObjects SDK Questions

Loop inside a loop or Nested Loop in VBA

MichelleCouden1 — Fri, 05 Apr 2013 12:07:09 GMT

I need help with a loop. I need it to do 2 things and I'm not sure how to write a double loop. Would my code look like this :

If cboStations.Text = "Urban" then
If cboYears.Text = "2010" then
cboDistricts.AddItem "Abilene"

Re: Loop inside a loop or Nested Loop in VBA

KenBuja — Sat, 11 Dec 2021 11:04:20 GMT

Based on your question over on GIS.StackExchange and some of your previous questions, you could do something like this

Private Sub cboYear_Change()
    dim myArray() as String
    cboDistrict.Clear

    If cboStations.Text = "Urban" Then
        If cboYear.Text = "2010" Then
           myArray = Array ("Abilene", "Amarillo", "Austin", "San_Antonio", "Waco", "Wichita_Falls" )
        Elseif cboYear.Text = "2011" Then
           myArray = Array ("Beaumont", "Houston")
        Elseif cboYear.Text = "2012" Then 'or just Else
            myArray = Array ("Brownwood", "Bryan", "Childress", "Corpus_Christi", "El_Paso", Lubbock, "Odessa", "Yoakum")
        End If
    Elseif cboStations.Text = "Rural"
         If cboYear.Text = "2010" Then
           myArray = Array ("Abilene", "Waco", "Wichita_Falls" )
        Elseif cboYear.Text = "2011" Then
           myArray = Array ("Beaumont")
        Elseif cboYear.Text = "2012" Then 'or just Else
            myArray = Array ("Brownwood", Lubbock, "Odessa", "Yoakum")
        End If

    End If
     
    For Each myElement in myArray
        cboDistrict.AddItem myElement
    Next
    
End Sub

Re: Loop inside a loop or Nested Loop in VBA

MichelleCouden1 — Fri, 05 Apr 2013 14:28:59 GMT

OK, Ken before I write in you code and delete mine. I have a question about my code. Why would the district combobox not be clearing in this section?

If cboStations.Text = "Urban" Then
         cboYear.Clear
      cboYear.AddItem "2010"
      cboYear.AddItem "2011"
      cboYear.AddItem "2012"
        ElseIf cboYear.Text = "2010" Then
           cboDistrict.Clear
        cboDistrict.AddItem "Abilene"
        cboDistrict.AddItem "Amarillo"
        cboDistrict.AddItem "Austin"
        cboDistrict.AddItem "San_Antonio"
        cboDistrict.AddItem "Waco"
        cboDistrict.AddItem "Wichita_Falls"
           ElseIf cboYear.Text = "2011" Then
              cboDistrict.Clear
           cboDistrict.AddItem "Beaumont"
           cboDistrict.AddItem "Houston"
             ElseIf cboYear.Text = "2012" Then
                cboDistrict.Clear
             cboDistrict.AddItem "Brownwood"
             cboDistrict.AddItem "Bryan"
             cboDistrict.AddItem "Childress"
             cboDistrict.AddItem "Corpus_Christi"
             cboDistrict.AddItem "El_Paso"
             cboDistrict.AddItem "Lubbock"
             cboDistrict.AddItem "Odessa"
             cboDistrict.AddItem "Yoakum"
       End If

Re: Loop inside a loop or Nested Loop in VBA

KenBuja — Fri, 05 Apr 2013 14:42:08 GMT

The easiest thing would be to move cboDistrict.Clear out of the loop altogether.

However, your syntax is incorrect for the decisions. The If loop will first evaluate what is selected in the cboStations control. If that is "Urban" it will add in the years then exit from the loop. It will never try to evaluate what has been selected in the cboYear control (and you don't have the line cboDistrict.Clear in that first evaluation).

You mistakenly put a / in the first CODE block. If you remove that, it will make it easier to read.

Re: Loop inside a loop or Nested Loop in VBA

MichelleCouden1 — Fri, 05 Apr 2013 15:17:39 GMT

OK, one more question. Where do I put the code? In cboStations_Change() or cboYear_Change().

Re: Loop inside a loop or Nested Loop in VBA

KenBuja — Sat, 11 Dec 2021 11:04:22 GMT

This code would go into the cboYear_Change sub. In the cboStations_Change sub, I would image you'd have this code

cboYear.Clear

If cboStations.Text = "Urban" Then
           cboYear.AddItem "2010"
           cboYear.AddItem "2011"
           cboYear.AddItem "2012"
Elseif cboStations.Text = "Rural" Then
           cboYear.AddItem "2011"
End If

Re: Loop inside a loop or Nested Loop in VBA

MichelleCouden1 — Fri, 05 Apr 2013 15:39:09 GMT

OK, I am getting a type mismatch???? I will attach my whole code. See Attached. It's the way I have the Annual statement worded.

Re: Loop inside a loop or Nested Loop in VBA

KenBuja — Sat, 11 Dec 2021 11:04:25 GMT

What line are you getting this on?

Also it looks like you want to combine the two If statements. If you are using both arrays to populate cboDistrict (you left off that code) then only use one array.

If cboStations.Text = "Annual" Then
   myArray = Array("Abilene", ...
Else If cboStations.Text = "Urban" Then  ' or just Else If if you won't have additional stations
   If cboYear.Text = "2010" Then
      myArray = Array("Abilene", ...
      etc
End If

For Each myElement in myArray
    cboDistrict.AddItem myElement
    Next

Re: Loop inside a loop or Nested Loop in VBA

MichelleCouden1 — Fri, 05 Apr 2013 18:11:03 GMT

I'm sorry Ken, I thought I included that. I'm getting this error on the Stations.Text = "Annual" etc....
Is there maybe a chance I can get the other loop to work? Because everything fills in correctly but the urban district names. It would be easier to fix. OR is it just better to change it all out.

Re: Loop inside a loop or Nested Loop in VBA

JasonPike — Fri, 05 Apr 2013 18:52:15 GMT

Nested parens:
http://laughingsquid.com/jacob-appelbaum-donald-knuth-demonstrate-the-recursive-homeboys-principle/

Re: Loop inside a loop or Nested Loop in VBA

MichelleCouden1 — Fri, 05 Apr 2013 19:00:08 GMT

That has no help!!

Re: Loop inside a loop or Nested Loop in VBA

MichelleCouden1 — Sat, 11 Dec 2021 11:04:28 GMT

Hey Ken, I am getting a Compile error: "For Each control variable on arrays must be Variant". I had to declare your myElement cause that was the first error. I claimed it as Integer. What else would you put it as?

       ElseIf cboStations.Text = "Urban" Then
        If cboYear.Text = "2010" Then
           myArray = Array("Abilene", "Amarillo", "Austin", "San_Antonio", "Waco", "Wichita_Falls")
        ElseIf cboYear.Text = "2011" Then
           myArray = Array("Beaumont", "Houston")
        ElseIf cboYear.Text = "2012" Then 'or just Else
            myArray = Array("Brownwood", "Bryan", "Childress", "Corpus_Christi", "El_Paso", "Lubbock", "Odessa", "Yoakum")
        End If
   
    End If
    
    For Each myElement In myArray
        cboDistrict.AddItem myElement
    Next
End Sub

Re: Loop inside a loop or Nested Loop in VBA

KenBuja — Sat, 11 Dec 2021 11:04:30 GMT

I left out one statement


    dim myArray() as String
    dim myElement as Variant

    cboDistrict.Clear

    If cboStations.Text = "Urban" Then
       etc.
    End If           

    For Each myElement in myArray
        cboDistrict.AddItem myElement     
    Next

Re: Loop inside a loop or Nested Loop in VBA

MichelleCouden1 — Sat, 11 Dec 2021 11:04:33 GMT

I feel like were getting somewhere, which is better than what I was getting. I am not typing the Array line properly it is giving me a Type mismatch error. It's on the myArray line for all of them.

 Dim myArray() As String
      Dim myElement As Variant
      cboDistrict.Clear
      
      If cboStations.Text = "Annual" Then
         If cboYear.Text = "2010" Then
            myArray = Array("Abilene", "Amarillo", "Atlanta", "Austin", "Beaumont", "Brownwood", "Bryan", "Childress", "Corpus_Christi", "Dallas", "El_Paso", "Fort_Worth", "Houston", "Laredo", "Lubbock", "Lufkin", "Odessa", "Paris", "Pharr", "San Angelo", "San_Antonio", "Tyler", "Waco", "Wichita_Falls", "Yoakum")
         ElseIf cboYear.Text = "2011" Then
             myArray = Array("Abilene", "Amarillo", "Atlanta", "Austin", "Beaumont", "Brownwood", "Bryan", "Childress", "Corpus_Christi", "Dallas", "El_Paso", "Fort_Worth", "Houston", "Laredo", "Lubbock", "Lufkin", "Odessa", "Paris", "Pharr", "San Angelo", "San_Antonio", "Tyler", "Waco", "Wichita_Falls", "Yoakum")
         ElseIf cboYear.Text = "2012" Then
              myArray = Array("Abilene", "Amarillo", "Atlanta", "Austin", "Beaumont", "Brownwood", "Bryan", "Childress", "Corpus_Christi", "Dallas", "El_Paso", "Fort_Worth", "Houston", "Laredo", "Lubbock", "Lufkin", "Odessa", "Paris", "Pharr", "San Angelo", "San_Antonio", "Tyler", "Waco", "Wichita_Falls", "Yoakum")
         ElseIf cboYear.Text = "2012" Then
               myArray = Array("Abilene", "Amarillo", "Atlanta", "Austin", "Beaumont", "Brownwood", "Bryan", "Childress", "Corpus_Christi", "Dallas", "El_Paso", "Fort_Worth", "Houston", "Laredo", "Lubbock", "Lufkin", "Odessa", "Paris", "Pharr", "San Angelo", "San_Antonio", "Tyler", "Waco", "Wichita_Falls", "Yoakum")
         ElseIf cboYear.Text = "2013" Then
               myArray = Array()
         ElseIf cboYear.Text = "2014" Then
               myArray = Array()
       End If
      
       ElseIf cboStations.Text = "Urban" Then
        If cboYear.Text = "2010" Then
           myArray = Array("Abilene", "Amarillo", "Austin", "San_Antonio", "Waco", "Wichita_Falls")
        ElseIf cboYear.Text = "2011" Then
           myArray = Array("Beaumont", "Houston")
        ElseIf cboYear.Text = "2012" Then 'or just Else
            myArray = Array("Brownwood", "Bryan", "Childress", "Corpus_Christi", "El_Paso", "Lubbock", "Odessa", "Yoakum")
        End If
   
    End If
    
    For Each myElement In myArray
        cboDistrict.AddItem myElement
    Next

Re: Loop inside a loop or Nested Loop in VBA

MichelleCouden1 — Mon, 08 Apr 2013 17:20:59 GMT

Hey Ken, where are ya??????

Re: Loop inside a loop or Nested Loop in VBA

KenBuja — Mon, 08 Apr 2013 18:43:12 GMT

Try this. This will change the values of your districts if you select a new year or a new station.

Private Sub UserForm_Initialize()      Dim x As Integer      For x = 2010 To 2015         cboYear.AddItem x     Next         cboStations.AddItem "Annual"     cboStations.AddItem "Urban"      End Sub  Private Sub cboStations_Change()      If cboYear.Value <> "" Then         UpdateDistrict     End If      End Sub  Private Sub cboYear_Change()          If cboStations.Value <> "" Then         UpdateDistrict     End If      End Sub  Private Sub UpdateDistrict()      Dim myArray()     Dim myElement As Variant     cboDistrict.Clear          myArray = Array()          If cboStations.Text = "Annual" Then       If cboYear.Text = "2010" Or cboYear.Text = "2011" Or cboYear.Text = "2012" Then          myArray = Array("Abilene", "Amarillo", "Atlanta", "Austin", "Beaumont", "Brownwood", "Bryan", "Childress", "Corpus_Christi", "Dallas", "El_Paso", "Fort_Worth", "Houston", "Laredo", "Lubbock", "Lufkin", "Odessa", "Paris", "Pharr", "San Angelo", "San_Antonio", "Tyler", "Waco", "Wichita_Falls", "Yoakum") '      ElseIf cboYear.Text = "2011" Then '         myArray = Array("Abilene", "Amarillo", "Atlanta", "Austin", "Beaumont", "Brownwood", "Bryan", "Childress", "Corpus_Christi", "Dallas", "El_Paso", "Fort_Worth", "Houston", "Laredo", "Lubbock", "Lufkin", "Odessa", "Paris", "Pharr", "San Angelo", "San_Antonio", "Tyler", "Waco", "Wichita_Falls", "Yoakum") '      ElseIf cboYear.Text = "2012" Then '          myArray = Array("Abilene", "Amarillo", "Atlanta", "Austin", "Beaumont", "Brownwood", "Bryan", "Childress", "Corpus_Christi", "Dallas", "El_Paso", "Fort_Worth", "Houston", "Laredo", "Lubbock", "Lufkin", "Odessa", "Paris", "Pharr", "San Angelo", "San_Antonio", "Tyler", "Waco", "Wichita_Falls", "Yoakum") '      ElseIf cboYear.Text = "2012" Then '           myArray = Array("Abilene", "Amarillo", "Atlanta", "Austin", "Beaumont", "Brownwood", "Bryan", "Childress", "Corpus_Christi", "Dallas", "El_Paso", "Fort_Worth", "Houston", "Laredo", "Lubbock", "Lufkin", "Odessa", "Paris", "Pharr", "San Angelo", "San_Antonio", "Tyler", "Waco", "Wichita_Falls", "Yoakum") '      ElseIf cboYear.Text = "2013" Then '           myArray = Array() '      ElseIf cboYear.Text = "2014" Then '           myArray = Array()       End If          ElseIf cboStations.Text = "Urban" Then         If cboYear.Text = "2010" Then             myArray = Array("Abilene", "Amarillo", "Austin", "San_Antonio", "Waco", "Wichita_Falls")         ElseIf cboYear.Text = "2011" Then             myArray = Array("Beaumont", "Houston")         ElseIf cboYear.Text = "2012" Then 'or just Else             myArray = Array("Brownwood", "Bryan", "Childress", "Corpus_Christi", "El_Paso", "Lubbock", "Odessa", "Yoakum") '        ElseIf cboYear.Text = "2013" Then '            myArray = Array() '        ElseIf cboYear.Text = "2014" Then '            myArray = Array()         End If     End If          For Each myElement In myArray         cboDistrict.AddItem myElement     Next  End Sub

Re: Loop inside a loop or Nested Loop in VBA

MichelleCouden1 — Mon, 08 Apr 2013 19:33:43 GMT

Can't do the years like that because Urban only has the years of 2010, 2011, and 2012. I'll change them back. The code you sent me though still did not fix the "Type Mismatch" error that I am getting on the myArray = Array("Abilene", and so on...
That error is coming up on all myArray lines of code.
All we have to do is fix that error and I think it will work.

Re: Loop inside a loop or Nested Loop in VBA

KenBuja — Mon, 08 Apr 2013 20:07:50 GMT

This was the code I tested in an ArcGIS VBA project which worked correctly. Did you notice the change in the line

Dim myArray()

Can't do the years like that because Urban only has the years of 2010, 2011, and 2012.

This was the code you originally had in your attached text file which populated the years from 2010 through 2015.

Re: Loop inside a loop or Nested Loop in VBA

MichelleCouden1 — Mon, 08 Apr 2013 20:30:02 GMT

My bad I missed that. I changed it. Oh my god, Ken we are so close. Thank You so much, you are the only one that has gotten this to work. Thanks again. OK, let me see if I can explain this right. When I open the program and pick Annual all the years and districts go in the boxes. Perfect! When I open the program and pick Urban it loads all the years and proper districts. Perfect! The only thing is once I select Annual and then try to change my stations to Urban without closing out the program I get an error of "For loop not initialized" and then it highlights the For Each myElement line of code. IS there anyway to make it where the user can change their mind with the program open?

Re: Loop inside a loop or Nested Loop in VBA

LeoDonahue — Mon, 08 Apr 2013 20:48:07 GMT

Thank You so much, you are the only one that has gotten this to work.

Ken is the only one willing to help you at the moment. Go easy on him.

By chance are you going to send Ken a nice thank you or something like that? He's done alot of work for you lately. None of his posts are helpful in any way?