Im missing something

[grawrg]

I started writing up a small inventory program in excel. I made one previously but the code is all messy, so i decided to rewrite it. I use a form for all user interaction. I ran into an issue where some of my code was running an extra 73 times. I looked and looked and looked, but the code is so short how did i miss whatever is going on. I started over again and the i began to run into the issue again, only it repeats 69 times now...

This is code within the UserForm
-stockApp is the listbox that all the data loads into
-AppLoad displays the data to the listbox
-AppVal Changes the amount either +1 or -1 based on the option boxes
-I rewrote the For loop several times trying to fix it, this is just the one im using right now. it started as a While loop.

Private Sub CommandButton1_Click()
    CC.ColWidSet
    CC.AppLoad
End Sub
Private Sub stockApp_Click()
    CC.AppVal
    CC.AppLoad
End Sub

These three subs are in a module named CC

Sub AppLoad()
    Set SRC = Worksheets("Apple")
    X = 0
    Y = 0
    stockView.stockApp.AddItem
    stockView.stockApp.List(Y, 1) = "DEVICE TYPE---------"
    stockView.stockApp.List(Y, 0) = "STOCK COUNT---"
    For X = 0 To 60
        Y = Y + 1
        Set DEV = SRC.Range("A" & Y)
        Set CNT = SRC.Range("C" & Y)
        DevNum = DEV.Value
        CntNum = CNT.Value
        stockView.stockApp.AddItem
        stockView.stockApp.List(Y, 1) = DevNum
        stockView.stockApp.List(Y, 0) = CntNum
        If IsEmpty(SRC.Range("A" & Y + 1)) Then Exit For
        Next X
End Sub
Sub AppVal()
    Set SRC = Worksheets("Apple")
    INDEX = stockView.stockApp.ListIndex
    If stockView.AddOpt.Value = True Then
        SRC.Range("C" & INDEX).Value = SRC.Range("C" & INDEX).Value + 1
    End If
    If stockView.TakOpt.Value = True Then
        SRC.Range("C" & INDEX).Value = SRC.Range("C" & INDEX).Value - 1
    End If
End Sub
Sub ColWidSet()
    stockView.stockApp.ColumnWidths = "75"
    stockView.stockApp.TextAlign = fmTextAlignLeft
    stockView.stockSam.ColumnWidths = "75"
    stockView.stockSam.TextAlign = fmTextAlignLeft
    stockView.stockLG.ColumnWidths = "75"
    stockView.stockLG.TextAlign = fmTextAlignLeft
    stockView.stockHTC.ColumnWidths = "75"
    stockView.stockHTC.TextAlign = fmTextAlignLeft
End Sub

I noticed that if i disable AppLoad but leave AppVal enabled in stockApp_Click, the issue doesn't occur and everything adds properly. Once i re enable AppLoad the issue returns with 69 repeats of the AppVal code. Why and how???? please help, it's stupid frustrating and the answer is ganna make me fell retarded.

 

[Logit]

I would focus on this portion of your code :

[B][COLOR=#ff0000] For X = 0 To 60[/COLOR][/B]
        Y = Y + 1
        Set DEV = SRC.Range("A" & Y)
        Set CNT = SRC.Range("C" & Y)
        DevNum = DEV.Value
        CntNum = CNT.Value
        stockView.stockApp.AddItem
        stockView.stockApp.List(Y, 1) = DevNum
        stockView.stockApp.List(Y, 0) = CntNum
        If IsEmpty(SRC.Range("A" & Y + 1)) Then Exit For
 Next X

 

[grawrg]

I would focus on this portion of your code :

[B][COLOR=#ff0000] For X = 0 To 60[/COLOR][/B]
        Y = Y + 1
        Set DEV = SRC.Range("A" & Y)
        Set CNT = SRC.Range("C" & Y)
        DevNum = DEV.Value
        CntNum = CNT.Value
        stockView.stockApp.AddItem
        stockView.stockApp.List(Y, 1) = DevNum
        stockView.stockApp.List(Y, 0) = CntNum
        If IsEmpty(SRC.Range("A" & Y + 1)) Then Exit For
 Next X

I made a change from that For loop to a Do While loop. Now whenever stockApp_Click() runs AppVal it causes error 28 "Out of stack space". I have plenty of memory, and i haven't used very many variables.

X = 0
    Do While X = 0
        Y = Y + 1
        Set DEV = SRC.Range("A" & Y)
        Set CNT = SRC.Range("C" & Y)
        DevNum = DEV.Value
        CntNum = CNT.Value
        stockView.stockApp.AddItem
        stockView.stockApp.List(Y, 1) = DevNum
        stockView.stockApp.List(Y, 0) = CntNum
        If IsEmpty(SRC.Range("A" & Y + 1)) Then
            X = 1
        End If
    Loop

 

[Logit]

Paste Option Explicit at the top of your macro, then run the macro.

This will assist you in identifying any errant lines of code and also provide you with specific error message for that error.

You haven't DIM either X or Y at the beginning of your code.

Try:

Dim X As Long
Dim Y As Long

or

Dim X As Integer
Dim Y As Integer

 

[grawrg]

Paste Option Explicit at the top of your macro, then run the macro.

This will assist you in identifying any errant lines of code and also provide you with specific error message for that error.

You haven't DIM either X or Y at the beginning of your code.

Try:

Dim X As Long
Dim Y As Long

or

Dim X As Integer
Dim Y As Integer

I made some modifications to it and everything seems like it's a problem with excel itself.

Sub AppVal()
    Set SRC = Worksheets("Apple")
    Dim index As Integer
    index = stockView.stockApp.ListIndex
    If stockView.AddOpt.Value = True Then
        SRC.Range("C" & index).Value = SRC.Range("C" & index).Value + 1
    End If
    If stockView.TakOpt.Value = True Then
        SRC.Range("C" & index).Value = SRC.Range("C" & index).Value - 1
    End If
    Set DEV = SRC.Range("A" & index)
    Set CNT = SRC.Range("C" & index)
    DevNum = DEV.Value
    CntNum = CNT.Value
[COLOR=#ff0000]    stockView.stockApp.List(index, 1) = DevNum[/COLOR]
[COLOR=#ff0000]    stockView.stockApp.List(index, 0) = CntNum[/COLOR]
    Set SRC = Nothing
End Sub

I injected code from AppLoad into AppVal so that when i click the listbox only AppVal needs to be run. For some reason removing those two line stops the looping and allows everything to function except the listbox being updated with each click. Looking into the call stack i see that excel is looping my stockApp_Click() Sub for no reason i can identify. I assume this is some shortcoming of Microsoft excel.