CODEWITHSUNDEEP
vbaexcel
Matthew
Matthew

Reputation: 867

Do-While loop (VBA) not looping

so I thought this would be a simple logical problem, but for the life of me I cannot find the issue with this code block. I have checked around on Stack for a solution, but all other do/while loop problems appear to be primarily with other languages.

What I am trying to do is simply loop through an array & add a new worksheet for each element in the array that is not null. Pretty simple right? Yet for some reason it simply loops through once and thats it.

Here is the code block:

Dim repNames() As String
Dim x As Integer
    x = 25
    ReDim repNames(1 To x)

    repNames(1) = "Ahern"
    repNames(2) = "Castronovo"
    repNames(3) = "Glick"
    repNames(4) = "Fields"
    repNames(5) = "Murphy"
    repNames(6) = "Sleeter"
    repNames(7) = "Vivian"
    repNames(8) = "Walschot"
    repNames(9) = "Wilson"

    Dim i As Integer
    i = 1

    Do                                          'Loop keeps creating only 1 new sheet. Should create 9.
        Worksheets.Add.Name = repNames(i)
        i = i + 2

    Loop While repNames(i) <> Null

I believe the problem is with this line: Loop While repNames(i) <> Null, but obviously the logical test seems to hold up.

Any help would be hugely appreciated!

Upvotes: 2

Views: 2515

Answers (3)

David Zemens
David Zemens

Reputation: 53623

As others note, Null is not the comparison you want to make. Testing anything for equivalence with Null will return Null -- even ?Null = Null returns Null, which is why your loop is exiting early. (Note: To test for a Null, you need to use the IsNull function which returns a boolean, but that is NOT how you test for an empty string.)

In VBA, to test for a zero-length string or empty string, you can use either "" or vbNullString constant, or some people use the Len function to check for zero-length.

Rectifying that error, as originally written in your code, your logical test should abort the loop if any item is an empty string, but none of the items are empty strings (at least not in the example data you've provided) so you end up with an infinite loop which will error once i exceeds the number of items in the repNames array.

This would be probably better suited as a For Each loop. 

Dim rep as Variant
For Each rep in repNames
    Worksheets.Add.Name = rep
Next

If you need to skip empty values, or duplicate values, you can add that logic as needed within the loop:

For Each rep in repNames
    If rep <> vbNullString 'only process non-zero-length strings
        Worksheets.Add.name = rep
    End If
Next

Etc.

Upvotes: 2

Robert Juneau
Robert Juneau

Reputation: 662

Here you go, I have changed the loop conditional, and changed i=i+2 to i=i+1. A regular while loop would be better than a do while encase the first element is empty

Dim repNames()
Dim x As Integer
x = 25
ReDim repNames(1 To x)

repNames(1) = "Ahern"
repNames(2) = "Castronovo"
repNames(3) = "Glick"
repNames(4) = "Fields"
repNames(5) = "Murphy"
repNames(6) = "Sleeter"
repNames(7) = "Vivian"
repNames(8) = "Walschot"
repNames(9) = "Wilson"

Dim i As Integer
i = 1

Do While repNames(i) <> ""                   
   Worksheets.Add.Name = repNames(i)
    i = i + 1

Loop

Upvotes: 1

Aurel B&#237;l&#253;
Aurel B&#237;l&#253;

Reputation: 7973

Firstly, you should be comparing to vbNullString. This loops multiple times:

' Declare variables
Dim repNames() As String
Dim x As Integer
Dim i As Integer

' Set data
x = 25
ReDim repNames(1 To x)
repNames(1) = "Ahern"
repNames(2) = "Castronovo"
repNames(3) = "Glick"
repNames(4) = "Fields"
repNames(5) = "Murphy"
repNames(6) = "Sleeter"
repNames(7) = "Vivian"
repNames(8) = "Walschot"
repNames(9) = "Wilson"

' Loop through items
i = 1
Do
  Worksheets.Add.Name = repNames(i)
  i = i + 2
Loop While repNames(i) <> vbNullString

There is one more problem – why i = i + 2 ? In your question you say you wanted the loop to execute 9 times, but using i = i + 2 skips every other item. If you indeed want to loop through every item:

Do
  Worksheets.Add.Name = repNames(i)
  i = i + 1
Loop While repNames(i) <> vbNullString

Upvotes: 1

Related Questions