2

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!

Improve this question
2
  • 1
    Try Loop While repNames(i) <> vbNullString. Commented Jul 19, 2017 at 15:49
  • @HaoZhang sure, but that creates an infinite loop (given the example data) which will error (Subscript out of Range) once i > 9. Commented Jul 19, 2017 at 16:09

3 Answers 3

Reset to default
2

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.

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

1 Comment

I did actually, but it says because my rep is below 15 my upvotes wont show :(
1

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
Improve this answer

5 Comments

I had no idea about the vbNullString. Thank you very much :)
@DavidZemens Why? I can see why it would if x were 9. But x is specified as strictly higher than the number of elements. I agree, there should be logic to handle this and all in all, I would use a For Each like in your answer.
@AurelBílý huh? repnames is a base-1 array with an upper bound of 9. Why would repnames(i) fail when i = 9? It will certainly fail when i = 10, because 10 is a subscript out of range for that array with that upper-bound. x simply has nothing to do with this.
@DavidZemens What? Base-1 array makes no sense, did you mean one-dimensional? x has something to do with this because in OP's question, he does ReDim repNames(1 To x).
It is a base 1 array (lower bound == 1). I didn't notice (1 to x) though, so point taken. :)
1

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
Improve this answer

2 Comments

Awesome man, thanks so much. I program in java as well & typically would do a null test. Now I know thats discouraged in VBA!
@Matthew it's not discouraged, it just means something else in the VBA environment and it would be usually rare to check for Null under most circumstances unless I think if you're dealing with database nulls/NaNs etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.