Thread: Stock price averages

I have been building this workbook to do stock price averages and some other features, but out of no where none of my macro buttons works and I have no idea why. The 7 day average was suppose to print in column e and when I added it it just made everything stop working the way it was. Any help would be appreciated. The txt. document is the data that I have been using to compute my data.

Hi Jaje,

I see that this is an assignment for a class, so all I'll say is

1. You've referenced a variable called AVG7COL (in two separate procedures), yet you've never declared it (or Const, like your other variables)

2. When writing code, always add Option Explicit at the top. This FORCES you to declare all variables. If you call a macro/module and there are issues, an error will occur and you'll see exactly why in Debug.

Hope that helps!

When you click Debug, your code window should appear, and the line highlighted in yellow is usually the one throwing an error.

Ok, so when clicking the 'Compute 7-day' button you get an error. When clicking debug, the lines of code highlighted in yellow are the calculation lines.

Sub Compute7DayAvg()
    Dim rowNdx As Integer
    
    rowNdx = FIRSTDATAROW + 2
    Do While Not IsEmpty(Cells(rowNdx, DATECOL).Value)

    ' The debug highlights the entire section below, up to the division by 7.
    ' It is all, technically, one line of code, so the whole "line" is highlighted.
    ' ---------------------------------------------------------------------------
        Cells(rowNdx, AVG7COL).Value = (Cells(rowNdx - 6, AVG1COL) _
                                        + Cells(rowNdx - 5, AVG1COL) _
                                        + Cells(rowNdx - 4, AVG1COL) _
                                        + Cells(rowNdx - 3, AVG1COL) _
                                       + Cells(rowNdx - 2, AVG1COL).Value _
                                    + Cells(rowNdx - 1, AVG1COL).Value _
                                    + Cells(rowNdx, AVG1COL).Value) / 7
    ' ---------------------------------------------------------------------------
        rowNdx = rowNdx + 1
    Loop
End Sub

When in debug mode, you can hover your mouse over any of the variables and a pop-up will appear with that variable's current value. So if you hover over "AVG7COL", you'll see that it's 5. The problem, you'll see by hovering, is that "rowNdx" has a value of 5, too.

When you plug 5 into the first bit of code after the = sign, you get:

(Cells(rowNdx - 6, AVG1COL) _

Which becomes

(Cells(5 - 6, 1) _

See the problem? There is no such cell address for "Cells(-1, 1)". This is occurring because at the beginning of this event you have a line:

rowNdx = FIRSTDATAROW + 2

It appears you copied that from the code that generates the 3-day averages, but forgot to change it to FIRSTDATAROW + 4. (Your 1-day average used just "rowNdx = FIRSTDATAROW", so I saw the pattern.)

Change the 2 to a 4, then run your code again. Notice that when you do, cells in column E appear to be formatted as Date. Change the format of those cells to Number (with or without decimals as necessary). Also, I believe you need to run the 1-day averages first, otherwise the results of the 7-day averages will be 0 because your calculation involves those values in column 1 (A).

Hopefully that helps explain it, and shows you how to debug a little better.

Good luck in your class!