how to make a for Loop in an array in vba

Question

I have been try this over and over again but got the same output. I would like to make a loop in an array of strings but the code only visits the first value. This is my code I've tried:

name = Environ("username")
user1= Array("name.name1", "name.name2")
user2= Array("name1.name1", "name1.name2")

For i = 0 To Size ' I used Size coz I will add more names
    If name = user1(i) Then
        MsgBox "User1"
    ElseIf name = user2(i) Then
        MsgBox "User2"
    Else
        errorLog
    End If
Next

Thank you in advance for your help!

Bathsheba · Accepted Answer · 2016-02-04 08:01:15Z

1

Unless you've provided an explicit declaration and starting value for Size, when it's used in a for loop, it will assume a variant numeric type with a value 0.

So you get For i = 0 To 0: i.e. the loop will iterate once.

Writing Option Explicit at the top of your module helps reduce bugs like this.

If you want to get the size of an array, use LBound and UBound.

answered Feb 4, 2016 at 8:01

Bathsheba

235k35 gold badges376 silver badges504 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user5857081 Over a year ago

Thank you for giving me an idea. This really heps me :)

user5857081 Over a year ago

,But my problem now is, what if it is not identified either of the two arrays ?

score 0 · Accepted Answer · 2016-02-05 02:39:09Z

0

This is what I've done to solve my problem.

name = Environ("username")
Dim found as Boolean
found = false
user1= Array("name.name1", "name.name2")
user2= Array("name1.name1", "name1.name2")

'to check for user2
For i = 0 To UBound(user1)
    If name = user1(i) Then
        MsgBox "User1"
        found = true
    End If
Next
'to check for user2
For i = 0 To UBound(user2)
    If name = user2(i) Then
        MsgBox "User2"
        found = true
    End If
Next

'Else if the username is not on the list, I have added a Boolean found.
if found = false then 
    MsgBox "Unidentified Access!"
end If

Thank you so much for all your guidance!

edited Feb 5, 2016 at 2:39

answered Feb 4, 2016 at 8:16

user5857081

1 Comment

Bathsheba Over a year ago

I'd prefer to see For i = LBound(user1) to UBound(user1). In this way you don't hardcode the base of the arrays.

Collectives™ on Stack Overflow

how to make a for Loop in an array in vba

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related