Debugging Using Arrays in VBA

Question

I need to write a program that stores names (located in the 2nd column) in an array when the name has an "X" in the 8th column, but I'm having trouble with putting names in the array. When I run it now, I get a blank value for the value in the array. After some debugging, I found out that the i value that tells which spot in the array is selected turns out to be 0, which is not what I wanted.

Here's the code:

Dim rowCount As Integer
Dim duplicateNames(100) As String
Dim duplicateNameCounter As Integer
    duplicateNameCounter = 0

'Count the number of rows'
rowCount = WorksheetFunction.CountA(Range("B1:B5000"))

'Find the names with an X next to them and put them in the array'
For i = 1 To 100
    If Cells(i, 8).Value = "X" Then
        MsgBox ("Found a name to put in the array!")
        duplicateNames(i) = Cells(i, 2).Value
        duplicateNameCounter = duplicateNameCounter + 1
    End If
Next i

'Show the contents of the array'
For i = 1 To duplicateNameCounter
    MsgBox ("Here's the slot in the array: " & i & vbNewLine & "Here's the name: " & duplicateNames(i))
Next i

This is my first time using arrays in VBA, so I think that's where my problem is. I have a background in C++ arrays, but these don't seem too different.

Any help would be appreciated. Thanks!

Would you just want duplicateNames(i) = Cells(i, 2).Value to be duplicateNames(duplicateNameCounter) = Cells(i, 2).Value — chancea
– chancea, Commented Jun 30, 2014 at 15:22

David Zemens · Accepted Answer · 2014-06-30 15:30:30Z

3

You're not incrementing duplicateNameCounter the same way you're incrementing your For Each loop. I think that is the problem.

Assume you have an "X" in row 1 and also in row 100, but that the rest of the cells in your column 8 are blank (or whatever, they don't have the "X").

At the end of this block, i will be 100, and there will be names only in slots 1 and 100. HOWEVER duplicateNameCounter will only be value of 2.

For i = 1 To 100
    If Cells(i, 8).Value = "X" Then
        MsgBox ("Found a name to put in the array!")
        duplicateNames(i) = Cells(i, 2).Value
        duplicateNameCounter = duplicateNameCounter + 1
    End If
Next i

So therefore when you do this, you're basically doing For i = 1 to 2, and that is going to not give you the results you expected -- because in order to display correctly the second duplicate, it would have to hit the 100th slot in the array, which it will never do.

For i = 1 To duplicateNameCounter
    MsgBox ("Here's the slot in the array: " & i & vbNewLine & "Here's the name: " & duplicateNames(i))
Next i

I think that the comment from @chancea above should resolve the problem.

edited Jun 30, 2014 at 15:30

answered Jun 30, 2014 at 15:23

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

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

3 Comments

chancea Over a year ago

From what I gathered I think it will solve the issue just I think he would also have to initialize duplicateNameCounter to 1 and not 0

David Zemens Over a year ago

Yes, good point @chancea that variable should initialize at 1, not 0.

nicholas79171 Over a year ago

Beautiful! Thanks much! That simply solved my problem. It's a simple, silly issue that just required a fresh pair of eyes to look at. Thanks much to you both!

CoveGeek · Accepted Answer · 2014-06-30 15:52:02Z

There were a few items that prevented successful execution of your code in excel. Option Explicit is recommended to be used because it will force you to declare all variables and is considered a good programming practice.

Option Explicit

Public Sub asdfasdfasdf()
Dim rowCount As Integer, i As Integer
Dim duplicateNames() As String
Dim duplicateNameCounter As Integer

Setting the counter to 0 allows the array of store values to not have empty values

duplicateNameCounter = 0

Your range formula was only looking for 5000 rows of data, so it has been changed to scan the entire column to prevent missed records

'Count the number of rows'
rowCount = WorksheetFunction.CountA(Range("B:B"))

You were not searching for lower and upper case X's on the test.

'Find the names with an X next to them and put them in the array'
For i = 1 To rowCount
    If Cells(i, 8).Value = "X" Or Cells(i, 8).Value = "x" Then
        duplicateNameCounter = duplicateNameCounter + 1

Resizing the array was added so that the size of the array would show the number of stores found

        ReDim Preserve duplicateNames(duplicateNameCounter)
        Debug.Print "Found a name to put in the array! " & Cells(i, 2).Value

You were not using the duplicateNameCounter value on the duplicateNames array.

        duplicateNames(duplicateNameCounter) = Cells(i, 2).Value
    End If
Next i

'Show the contents of the array'
For i = 1 To duplicateNameCounter
    MsgBox "Here's the slot in the array: " & i & vbNewLine & "Here's the name: " & duplicateNames(i)
Next i
End Sub

I wrote the debugging information to the immediate window to speed up running of the code, this make troubleshooting more efficient.

Collectives™ on Stack Overflow

Debugging Using Arrays in VBA

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related