VBA新增股票时动态更新ticker数组 运行仅显示表头如何解决

问题根源 #

• 你声明的ticker字符串数组未初始化也未填充任何股票代码值，后续所有调用ticker(tickerIndex)的判断都因匹配值为空直接跳过，不会触发任何数据写入逻辑
• RowCount计算逻辑错误：计算行数时你激活的是「All Stocks Analysis」汇总表，取到的是汇总表的行数而非待处理的年份数据表的行数，遍历范围完全错误
• totalVolume声明和使用冲突：你先将其赋值为单个数字0，后续又当做数组用totalVolume(tickerIndex)读取写入，存在类型不匹配问题
• 首个判断逻辑写反：你想从单元格提取股票代码存入ticker数组，实际写的是Cells(i, 1).Value = ticker(tickerIndex)，反而用空值覆盖了原表的股票代码
• 结果写入行号逻辑错误：用年份表的行号i+2当做汇总表的写入行号，会导致结果行混乱，就算有数据也会分散不对齐

修正后完整代码 #

Sub AllStockAnalysis()
    Dim ticker() As String
    Dim totalVolume() As Double
    Dim tickerIndex As Integer
    Dim startTime As Single
    Dim endTime As Single
    Dim yearValue As String
    Dim endingPrice As Double
    Dim startingPrice As Double
    Dim RowCount As Long
    Dim outputRow As Integer
    
    ' 获取要分析的年份
    yearValue = InputBox("What year would you like to run the analysis on?")
    startTime = Timer
    
    ' 切换到对应年份数据表计算行数
    Sheets(yearValue).Activate
    RowCount = Cells(Rows.Count, "A").End(xlUp).Row
    
    ' 先统计有多少个不同的股票代码，初始化数组大小
    Dim tickerCount As Integer
    tickerCount = 1
    For i = 2 To RowCount
        If Cells(i + 1, 1).Value <> Cells(i, 1).Value Then
            tickerCount = tickerCount + 1
        End If
    Next i
    ReDim ticker(tickerCount - 1) As String
    ReDim totalVolume(tickerCount - 1) As Double
    
    ' 遍历数据填充数组、计算成交量和收益率
    tickerIndex = 0
    ticker(tickerIndex) = Cells(2, 1).Value
    For i = 2 To RowCount
        ' 累加成交量
        totalVolume(tickerIndex) = totalVolume(tickerIndex) + Cells(i, 8).Value
        
        ' 取期初价格
        If Cells(i - 1, 1).Value <> Cells(i, 1).Value Then
            startingPrice = Cells(i, 3).Value
        End If
        
        ' 遇到股票代码切换，取期末价格写入汇总表
        If Cells(i + 1, 1).Value <> Cells(i, 1).Value Then
            endingPrice = Cells(i, 6).Value
            outputRow = 4 + tickerIndex ' 表头在第3行，从第4行开始写结果
            Sheets("All Stocks Analysis").Cells(outputRow, "A").Value = ticker(tickerIndex)
            Sheets("All Stocks Analysis").Cells(outputRow, "B").Value = totalVolume(tickerIndex)
            Sheets("All Stocks Analysis").Cells(outputRow, "C").Value = endingPrice / startingPrice - 1
            
            ' 切换到下一个股票代码
            tickerIndex = tickerIndex + 1
            If tickerIndex < tickerCount Then
                ticker(tickerIndex) = Cells(i + 1, 1).Value
            End If
        End If
    Next i
    
    ' 填充汇总表表头
    Worksheets("All Stocks Analysis").Activate
    Range("A1").Value = "All Stocks (" + yearValue + ")"
    Cells(3, 1).Value = "Ticker"
    Cells(3, 2).Value = "Total Daily Volume"
    Cells(3, 3).Value = "Return"
    
    endTime = Timer
    MsgBox "This code ran in " & (endTime - startTime) & " seconds for the year " & (yearValue)
  
End Sub

内容的提问来源于stack exchange，提问作者SAC1288