时间戳

首页 > 解决方案 > VBA 中的 For 循环，初始化变量

arrays - VBA 中的 For 循环，初始化变量

问题描述

我目前是一名开始学习数据科学和分析的学生。我目前正在开展一个项目，我们正在分析 Excel 中的股票数据并编写宏来操作数据。下面是我目前坚持的代码片段。

我正在尝试启动此 for 循环，但我需要将tickerVolumes变量初始化为零，我不确定如何启动此过程。我已经列出了他们要求我们执行的步骤以及其他一些代码。这是我正在从事的项目的描述以及他们要求我们做什么，所以基本上我正在重构我们在同一个工作表上使用的一些代码，但要检查更大范围的数据，在第一个项目中我们只做了 12 只股票，现在他们要我们重构代码以检查所有股票。

这就是他们想要的：

重构 Module2_VBA_Script 的挑战，以便您循环一次数据并收集所有信息。您重构的代码应该比在本模块中运行得更快。

在本次挑战中，您将编辑或重构模块 2 解决方案代码，以一次性遍历所有数据，以便收集您在本模块中所做的相同信息。然后，您将确定重构代码是否成功使 VBA 脚本运行得更快。最后，您将提交一份书面分析来解释您的发现。

重构是编码过程的关键部分。重构代码时，您并没有添加新功能；你只是想让代码更有效率——通过更少的步骤，使用更少的内存，或者改进代码的逻辑，让未来的用户更容易阅读。重构在工作中很常见，因为第一次尝试代码并不总是完成任务的最佳方式。有时，重构别人的代码将是您在工作中使用现有代码的切入点。


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
    
    tickerIndex = 0

    '1b) Create three output arrays
    
    Dim tickerVolumes() As Long
    Dim tickerStartingPrices() As Single
    Dim TickerEndingPrices() As Single
    
    
    ''2a) Create a for loop to initialize the tickerVolumes to zero.
    
    
    
    
        
    ''2b) Loop over all the rows in the spreadsheet.
    For i = 2 To RowCount
    
        '3a) Increase volume for current ticker
        
        
        '3b) Check if the current row is the first row with the selected tickerIndex.
        'If  Then
            
            
            
        '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
            
            

            '3d Increase the tickerIndex.
            
            
        '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
        
        
    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

Excel表格

在此处输入图像描述

2017 和 18 选项卡包含股票的所有数据，所有股票分析是我们的宏观输出选项卡，我需要制作一个类似的选项卡，但要循环遍历所有数据一次。

我已将我们用于第一个模块的宏与我们分析的 12 只股票一起附加。

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

yearValue = InputBox("What year would you like to run analysis on?")

    startTime = Timer
    

'1.Format the output sheet on the "All Stocks Analysis" worksheet.

    Worksheets("All Stocks Analysis").Activate

    Range("A1").Value = "All Stocks(" + yearValue + ")"
    
     'Create a header row
     Cells(3, 1).Value = "Year"
     Cells(3, 2).Value = "Total Daily Volume"
     Cells(3, 3).Value = "Return"
    
'2.Initialize an 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"


'3a.Initialize variables for the starting price and ending price.
    
    Dim startingPrice As Single
    Dim endingPrice As Single

'3b.Activate the data worksheet.
    
    Worksheets(yearValue).Activate

'3c.Find the number of rows to loop over.
    
    RowCount = Cells(Rows.Count, "A").End(xlUp).Row

'4.Loop through the tickers.

    For i = 0 To 11
    
        ticker = tickers(i)
        totalVolume = 0

'5.Loop through rows in the data.

   Worksheets(yearValue).Activate
    For j = 2 To RowCount
    
     
    '5a.Find the total volume for the current ticker.
    
    If Cells(j, 1).Value = ticker Then
        
        totalVolume = totalVolume + Cells(j, 8).Value
    
    End If
    
    '5b.Find the starting price for the current ticker.
    
    If Cells(j - 1, 1).Value <> ticker And Cells(j, 1).Value = ticker Then
        
        startingPrice = Cells(j, 6).Value
        
    End If
    
    '5c.Find the ending price for the current ticker.
    
    If Cells(j + 1, 1).Value <> ticker And Cells(j, 1).Value = ticker Then
            
            endingPrice = Cells(j, 6).Value
            
    End If
        
 Next j
 
 

'6.Output the data for the current ticker.

    Worksheets("All Stocks Analysis").Activate
    Cells(4 + i, 1).Value = ticker
    Cells(4 + i, 2).Value = totalVolume
    Cells(4 + i, 3).Value = (endingPrice / startingPrice) - 1
    
Next i

    endTime = Timer
    MsgBox "This code ran in" & (endTime - startTime) & "seconds for the year " & (yearValue)

End Sub

标签： arraysexcelvbafor-loopvariables

解决方案

现在，我不完全确定如何计算“回报”和“每日总交易量”。无论如何，我想出了这个解决方案：

Sub AllStocksAnalysisRefactored()
    
    'Declaring and setting StrYear.
    Dim StrYear As String
CP_Start:
    StrYear = InputBox("What year would you like to run the analysis on?", "Specify year")
    
    'Checking if the year request is valid.
    On Error Resume Next
    Select Case True
        Case Is = (StrYear = "")
            Exit Sub
        Case Is = (Worksheets(StrYear) Is Nothing)
            MsgBox "Sheet not found.", vbCritical, "Invalid input"
            GoTo CP_Start
    End Select
    On Error GoTo 0
    
    'Declaring and setting SinStarTime
    Dim SinStartTime As Single
    SinStartTime = Timer
    
    'Declarations.
    Dim WksOutput As Worksheet
    Dim DblRowsCount As Double
    Dim DblRow As Double
    Dim RngTickersList As Range
    Dim StrTickerSymbol(1 To 12) As String
'1a)Create a ticker index
    Dim BytTickerIndex As Byte
'1b) Create three output arrays (1 of 3)
    Dim LngTickerVolumes(1 To 12) As Long
'1b) Create three output arrays (2 of 3)
    Dim SinTickerStartingPrices(1 To 12) As Single
'1b) Create three output arrays (3 of 3)
    Dim SinTickerEndingPrices(1 To 12) As Single
    
'2a) Create a for loop to initialize the tickerVolumes to zero. <-THIS IS POINTLESS SINCE THEY ARE ALREADY SET ON 0. WHAT DOES IT MEAN?
    'Setting all LngTickerVolumes values to 0.
    'For BytTickerIndex = 1 To UBound(LngTickerVolumes())
        'LngTickerVolumes(BytTickerIndex) = 0
    'Next
    
    'Setting variables.
    Set WksOutput = Worksheets("All Stocks Analysis")
    Set RngTickersList = WksOutput.Cells(4, 1).Resize(UBound(StrTickerSymbol), 3)
    
    'Setting StrTickerSymbol.
    StrTickerSymbol(1) = "AY"
    StrTickerSymbol(2) = "CSIQ"
    StrTickerSymbol(3) = "DQ"
    StrTickerSymbol(4) = "ENPH"
    StrTickerSymbol(5) = "FSLR"
    StrTickerSymbol(6) = "HASI"
    StrTickerSymbol(7) = "JKS"
    StrTickerSymbol(8) = "RUN"
    StrTickerSymbol(9) = "SEDG"
    StrTickerSymbol(10) = "SPWR"
    StrTickerSymbol(11) = "TERP"
    StrTickerSymbol(12) = "VSLR"
    
    
    'Formattin WksOutput.
    With WksOutput
        .Range("A3:C3").Font.FontStyle = "Bold"
        .Range("A3:C3").Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Range("A1").Value = "All Stocks (" & StrYear & ")"
        .Cells(3, 1).Value = "Ticker"
        .Cells(3, 2).Value = "Total Daily Volume"
        .Cells(3, 3).Value = "Return"
    End With
    For DblRow = 1 To RngTickersList.Rows.Count
        RngTickersList.Cells(DblRow, 1) = StrTickerSymbol(DblRow)
    Next
    
    'Referring to the data worksheet.
    With Worksheets(StrYear)
        
        'Setting DblRowCount.
        DblRowsCount = .Cells(.Rows.Count, "A").End(xlUp).Row
        
'2b) Loop over all the rows in the spreadsheet.
        'Covering all the rows of the data set.
        For DblRow = 2 To DblRowsCount
            
            'Setting BytTickerIndex to match its position in StrTickerSymbol array.
            BytTickerIndex = Excel.WorksheetFunction.Match(.Cells(DblRow, 1).Value, RngTickersList.Columns(1), 0)

'3a) Increase volume for current ticker
            'Increasing the LngTickerVolumes value for the given ticker.
            LngTickerVolumes(BytTickerIndex) = LngTickerVolumes(BytTickerIndex) + .Cells(DblRow, 8).Value

'3b) Check if the current row is the first row with the selected tickerIndex. <-WHAT FOR? I GUESS TO RECORD THE STARTING PRICE?
            'Checking if this row contains the starting price and setting SinTickerStartingPrices accondingly.
            If Excel.WorksheetFunction.CountIf(.Range(.Cells(2, 1), .Cells(DblRow, 1)), .Cells(DblRow, 1).Value) = 1 Then
                SinTickerStartingPrices(BytTickerIndex) = .Cells(DblRow, 3).Value
            End If
            
'3c) check if the current row is the last row with the selected ticker <-WHAT FOR? I GUESS TO RECORD THE ENDING PRICE?
            'Checking if this row contains the ending price and setting SinTickerStartingPrices accondingly.
            If Excel.WorksheetFunction.CountIf(.Range(.Cells(DblRow, 1), .Cells(DblRowsCount, 1)), .Cells(DblRow, 1).Value) = 1 Then
                SinTickerEndingPrices(BytTickerIndex) = .Cells(DblRow, 6).Value
            End If
            
'If the next row’s ticker doesn’t match, increase the tickerIndex. <-THIS IS AUTOMATICALLY DONE WHEN SETTING BytTickerIndex AT THE BEGINNING OF THE FOR NEXT CYCLE
'3d Increase the tickerIndex.<---------------------------------------THIS IS AUTOMATICALLY DONE WHEN SETTING BytTickerIndex AT THE BEGINNING OF THE FOR NEXT CYCLE
            
        Next
        
    End With
    
'4) Loop through your arrays to output the Ticker, Total Daily Volume, and Return.
    'Referring to RngTickersList
    With RngTickersList
        
        'Covering each cell of RngTickersList.
        For DblRow = 1 To RngTickersList.Rows.Count
            'Reporting the results.
            .Cells(DblRow, 2).Value = LngTickerVolumes(DblRow)
            .Cells(DblRow, 3).Value = SinTickerEndingPrices(DblRow) / SinTickerStartingPrices(DblRow) - 1
            
            'Formatting.
            If .Cells(DblRow, 3).Value > 0 Then
                .Cells(DblRow, 3).Interior.Color = vbGreen
            Else
                .Cells(DblRow, 3).Interior.Color = vbRed
            End If
        Next
        
        'Formatting.
        .Columns(2).NumberFormat = "#,##0"
        .Columns(3).NumberFormat = "0.0%"
        .Columns(2).EntireColumn.AutoFit
        
    End With
    
    'Reporting execution time.
    MsgBox "This code ran in " & Timer - SinStartTime & " seconds for the year " & StrYear, , "Timer"
    
End Sub

它是否越来越接近您正在寻找的东西？

推荐阅读