VBA Compile Error - Loops?

Hello Experts,

I am using an UserForm in Outlook, the code I am using behind the scenes is giving me a compile error.

I think what I'm doing is called a loop?  Why is it resulting in a compile error?  I took other loop code I had, and customized it for this - thinking it was a good use to avoid typing out code for each combo box I am using...

    Dim i As Integer

    For i = 1 To 5
    With cboReason(i)
        .AddItem "Test 1"
        .AddItem "Test 2"
        .AddItem "Test 3"
        .AddItem "Test 4"
        .AddItem "Test 5"
    End With
    Next i

Open in new window


Any ideas what I am doing wrong?

~ Geekamo
LVL 1
GeekamoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GeekamoAuthor Commented:
I am trying to avoid this mess...

    With cboReason1
        .AddItem "Test 1"
        .AddItem "Test 2"
        .AddItem "Test 3"
        .AddItem "Test 4"
        .AddItem "Test 5"
    End With

    With cboReason2
        .AddItem "Test 1"
        .AddItem "Test 2"
        .AddItem "Test 3"
        .AddItem "Test 4"
        .AddItem "Test 5"
    End With

    With cboReason3
        .AddItem "Test 1"
        .AddItem "Test 2"
        .AddItem "Test 3"
        .AddItem "Test 4"
        .AddItem "Test 5"
    End With

    With cboReason4
        .AddItem "Test 1"
        .AddItem "Test 2"
        .AddItem "Test 3"
        .AddItem "Test 4"
        .AddItem "Test 5"
    End With

    With cboReason5
        .AddItem "Test 1"
        .AddItem "Test 2"
        .AddItem "Test 3"
        .AddItem "Test 4"
        .AddItem "Test 5"
    End With

Open in new window

0
Kent DyerIT Security Analyst SeniorCommented:
I think this is what you are looking for..

Something like:
Dim i,j
For i = 1 To 5
	'With cboReason & i
	Wscript.Echo "cboReason" & i
	For j = 1 to 5
		Wscript.Echo ".AddItem Test " & j
		'.AddItem "Test " & j
	Next
Next

Open in new window


I know this is not in VBA, but should get you to the end-goal.

HTH,

Kent
0
ArkCommented:
For i = 1 To 5
      Dim cb
      Set cb = Me.Controls("ComboBox" & i)
      cb.AddItem "Test 1"
      cb.AddItem "Test 2"
      cb.AddItem "Test 3"
   Next
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Martin LissOlder than dirtCommented:
Dim i As Integer
For i = 1 To 5
    
      cboReason1.AddItem "Test " & i
      cboReason2.AddItem "Test " & i
      cboReason3.AddItem "Test " & i
      cboReason4.AddItem "Test " & i
      cboReason5.AddItem "Test " & i
    Next i

Open in new window

0
GeekamoAuthor Commented:
@ All,

Thank you all for your input.

@ kdyer,

When I ran your code, it doesn't like the "Wscript" part.

@ Ark,

Your code worked perfect.  Revised it just slightly...

    Dim i As Integer
    Dim cb

    For i = 1 To 5

        Set cb = Me.Controls("cboReason" & i)
        
        cb.AddItem "Apple"
        cb.AddItem "Egg"
        cb.AddItem "Bread"
        cb.AddItem "Cheese"
        cb.AddItem "Milk"
        
    Next

Open in new window


If you don't mind, I have a couple additional questions though...

1.)  Dim cb, I'm understanding is creating the variable.  But I notice it's missing the "As String,As Integer, As whatever".  What is the general rule of knowing when to use the "As" part and when not to? (I hope that made sense)

I think "As String" tells it, it's a string of letters.
And I think "As Integer" tells it, it's a string of ONLY numbers.

2.)  Reading through it, I get what it's doing - but could you possibly explain this a little further as to how you know to write the code that way?  I guess, why didn't mine work - and yours does?

@ MartinLiss,

While your code did work, it wasn't exactly what I was going for.  Either my question wasn't clear enough (which is usually the case) or you might have misunderstood what I was trying to accomplish.
0
GeekamoAuthor Commented:
@ Ark,

Btw, I also removed the "Me." part, and for whatever reason - it's still working perfectly.  What exactly is the "Me." doing?

    Dim i As Integer
    
    Dim cb

    For i = 1 To 5

        Set cb = Controls("cboReason" & i)

        cb.AddItem "Apple"
        cb.AddItem "Egg"
        cb.AddItem "Bread"
        cb.AddItem "Cheese"
        cb.AddItem "Milk"

    Next

Open in new window


~ Geekamo
0
ArkCommented:
VBA (and VB too) allow lazy declaration.
Dim cb
equals
Dim cb As Variant - any type of object. I declared it just to shorten the code.
You can also use
   For i = 1 To 5
       
        Me.Controls("cboReason" & i).AddItem "Apple"
        Me.Controls("cboReason" & i).AddItem "Egg"
        Me.Controls("cboReason" & i).AddItem "Bread"
        Me.Controls("cboReason" & i).AddItem "Cheese"
        Me.Controls("cboReason" & i).AddItem "Milk"
        
    Next

Open in new window

or
For i = 1 To 5
    With Me.Controls("cboReason" & i)
        .AddItem "Apple"
        .AddItem "Egg"
        .AddItem "Bread"
        .AddItem "Cheese"
        .AddItem "Milk"
    End With
Next

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ArkCommented:
Me points to the object itself, in this case Me = UserForm1. It's not required - you can ommit it
0
GeekamoAuthor Commented:
Great - Thank you Ark!
0
Kent DyerIT Security Analyst SeniorCommented:
I think the important point about the code I provided to you is that you have essentially two loops..  The loop i which is 1 to 5 and I added loop j which is also 1 to 5.  I tend to think of the of way out using the least amount of code..  I used VBS as the platform to test this on..  You should have been able to take the ideas and put them into your code.  It does not matter to me who gets the points - the point is to be able to help you.  Having you happy is what counts.

HTH,

Kent
0
GeekamoAuthor Commented:
@ Kent,

Thank you for your post. :)  Unless something is clearly spelled out for me, I'm lost! :) Ark's code was pretty straight forward - and right in-line with my current skill level.  (And even then, I had question! :) But for the most part, it looked pretty familiar with the code I was already dealing with.  Thank you again!

~ Geekamo
0
GeekamoAuthor Commented:
Thank you again Ark!  You've been a great help!
0
ArkCommented:
Glad I could help.
@kent
'With cboReason & i
is invalid syntax either with VBA and VB and especially with VBS since VBS doesn't understend controls (though it does understand 'with...end with'). But for [with] you need object (or UDT or Variant). Actually, in strongly typed languages (like C ) where variable type should exactly match this line of code should be
With CObj(CStr(cboReason) & CStr(i))

Open in new window

But you cannot convert cboReason (object) to string and string back to object (in classic VB and VBA at least, VB.Net strings are objects itself and Object.ToString does return string)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Outlook

From novice to tech pro — start learning today.