VBA Overflow Error 6 - Receiving error calculating output of stock prices

Question

I am getting an error in the macro I'm working on for a bootcamp project. The idea is that I have refactored the code to make it more efficient, but I'm getting an Overflow error on one of the lines. I think it's due to it trying to divide 0, but I don't know where I've gone wrong in the loop that it is pulling data that would divide by 0. Any ideas? I'm getting the error on this line

Cells(4 + i, 3).Value = EndingPrices(i) / StartingPrices(i) - 1

in section 4 - '4) Loop through your arrays to output the Ticker, Total Daily Volume, and Return.

Sub AllStocksAnalysisRefactored()
    Dim startTime As Single
    Dim endTime  As Single

    yearValue = InputBox("What year would you like to run the analysis on?")
    startTime = Timer
    
    'Format the output sheet on All Stocks Analysis worksheet
    Worksheets("All Stocks Analysis").Activate
    
    Range("A1").Value = "All Stocks (" + yearValue + ")"
    
    'Create a header row
    Cells(3, 1).Value = "Ticker"
    Cells(3, 2).Value = "Total Daily Volume"
    Cells(3, 3).Value = "Return"

    'Initialize array of all tickers
    Dim tickers(12) As String
    
    tickers(0) = "AY"
    tickers(1) = "CSIQ"
    tickers(2) = "DQ"
    tickers(3) = "ENPH"
    tickers(4) = "FSLR"
    tickers(5) = "HASI"
    tickers(6) = "JKS"
    tickers(7) = "RUN"
    tickers(8) = "SEDG"
    tickers(9) = "SPWR"
    tickers(10) = "TERP"
    tickers(11) = "VSLR"
    
    'Activate data worksheet
    Worksheets(yearValue).Activate
    
    'Get the number of rows to loop over
    RowCount = Cells(Rows.Count, "A").End(xlUp).Row
    
    '1a) Create a ticker Index
    Dim tickerIndex As Integer
    tickerIndex = 0

    '1b) Create three output arrays
    Dim tickerVolumes(12) As Long    
    Dim StartingPrices(12) As Long    
    Dim EndingPrices(12) As Long
    
    ''2a) Create a for loop to initialize the tickerVolumes to zero.
    For i = 0 To 11
        tickerVolumes(i) = 0    
    Next i    
        
    ''2b) Loop over all the rows in the spreadsheet.
    For i = 2 To RowCount    
        '3a) Increase volume for current ticker
        If Cells(i, 1).Value = tickerIndex Then
            tickerVolumes(tickerIndex) = tickerVolumes(tickerIndex) + Cells(i, 8).Value  
        End If
            
        '3b) Check if the current row is the first row with the selected tickerIndex.
        'If  Then
        If Cells(i, 1) = tickerIndex And Cells(i - 1, 1).Value <> tickerIndex Then
            StartingPrices(tickerIndex) = Cells(i, 6).Value      
        End If    
        'End If
        
        '3c) check if the current row is the last row with the selected ticker
        'If the next row’s ticker doesn’t match, increase the tickerIndex.
        'If  Then
        If Cells(i, 1).Value = tickerIndex And Cells(i + 1, 1) <> tickerIndex Then
            EndingPrices(tickerIndex) = Cells(i, 6).Value
            '3d Increase the tickerIndex.
            tickerIndex = tickerIndex + 1
        End If
        'End If
    Next i
    
    '4) Loop through your arrays to output the Ticker, Total Daily Volume, and Return.
    For i = 0 To 11        
        Worksheets("All Stocks Analysis").Activate
        
        tickerIndex = i
        Cells(4 + i, 1).Value = tickers(i)
        Cells(4 + i, 2).Value = tickerVolumes(i)
        Cells(4 + i, 3).Value = EndingPrices(i) / StartingPrices(i) - 1 ' **this line is causing the error**
    Next i
    
    'Formatting
    Worksheets("All Stocks Analysis").Activate
    Range("A3:C3").Font.FontStyle = "Bold"
    Range("A3:C3").Borders(xlEdgeBottom).LineStyle = xlContinuous
    Range("B4:B15").NumberFormat = "#,##0"
    Range("C4:C15").NumberFormat = "0.0%"
    Columns("B").AutoFit

    dataRowStart = 4
    dataRowEnd = 15

    For i = dataRowStart To dataRowEnd        
        If Cells(i, 3) > 0 Then            
            Cells(i, 3).Interior.Color = vbGreen            
        Else        
            Cells(i, 3).Interior.Color = vbRed            
        End If        
    Next i
 
    endTime = Timer
    MsgBox "This code ran in " & (endTime - startTime) & " seconds for the year " & (yearValue)
End Sub

What are the values of `StartingPrices(i)` and `EndingPrices(i)` when it errors? — braX, Jul 13 '22 at 03:43
I highly recommend to read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba), the same is valid for `.Activate`. This is a bad practice and can easily end up in retrieving the values from the wrong worksheet. Make sure **every** `Range`, `Cells`, `Rows` or `Columns` object has a workbook/worksheet specified either like `ThisWorkbook.Worksheets("Sheet1").Range(…)` or like `Set ws = ThisWorkbook.Worksheets("Sheet1")` and then using `ws.Range(…)`. Apply this to your code if the errors are not gone update the question. — Pᴇʜ, Jul 13 '22 at 07:04

VBA Overflow Error 6 - Receiving error calculating output of stock prices

0 Answers0