vba - 工作表中多个工作表的 VBA 循环

Sub Stocks() Dim Ticker As String Dim Total_Stock_Volume As Double Total_Stock_Volume = 0 Dim Summary_Table_Row As Integer Summary_Table_Row = 2 Dim lastRow As Long Dim ws As Worksheet For Each ws In Worksheets ws.Activate Next ws Set sht = Worksheet lastRow = ws.Cells(sht.Rows.Count, "A").End(xlUp).Row For i = 2 To lastRow Total_Stock_Volume = Total_Stock_Volume + ws.Cells(i, 7).Value If ws.Cells(i + 1, 1).Value <> ws.Cells(i, 1).Value Then Ticker = ws.Cells(i, 1).Value ws.Range("J" & Summary_Table_Row).Value = Ticker ws.Range("K" & Summary_Table_Row).Value = Total_Stock_Volume Summary_Table_Row = Summary_Table_Row + 1 Total_Stock_Volume = 0 End If Next i End Sub

This following code is what I would call a useless dumpster fire.

Dim ws As Worksheet
For Each ws In Worksheets
    ws.Activate
Next ws

Set sht = Worksheet
lastRow = ws.Cells(sht.Rows.Count, "A").End(xlUp).Row

The reason I call it that is because using ws.Activate is the equivalent of clicking the worksheet tab (hence useless in this case) and your ws variable is referencing only the last worksheet, which, if your intent is to set a reference to the last worksheet, can be done by using:

Set ws = Excel.Application.ThisWorkbook.Worksheets(YourLastWorksheetName)

Now, the following perplexes me for a few reasons:

Set sht = Worksheet

Which worksheet?
Which workbook are you referencing the worksheet from?
I didn't even know one could reference anything from Worksheet

Just to simplify things, I'm assuming that ws == sht because I'm assuming the goal was to test if you could work with 1 worksheet before you asked about iterating through multiple worksheets, so you can simplify this by setting 1 reference to the 1 worksheet you're working with - I previously did this with ws - instead of setting another reference to whatever worksheet sht is referencing.

Also, sht is not explicitly declared, so I would suggest writing Option Explicit in the very first line of every Module or Class Module you write because, at compile time, it will show you which variables you haven't explicitly declared, which will notify you if you have mispelled any variables by accident.

This bit is what I would call the dumpster fire:

lastRow = ws.Cells(sht.Rows.Count, "A").End(xlUp).Row

Because I'm assuming your goal was to work with 1 worksheet, this line of code looks like you're working with 2 poorly-referenced worksheets, which, somewhere down the line, may not work as you intend.

Here's how I would change your code:

Edit: This code compiled in Excel so it should work, but you'll have to test it for runtime errors.

'i like this because i know when i have
'incorrectly named a variable // otherwise
'VBA will just create the incorrectly-named variable
'and set its type to Variant
Option Explicit

'i like to explicitly state whether a sub or function
'will be able to be called from outsite the module (public)
'or if i want it to only be called from within the module (private)
Public Sub Stocks()
    'i prefer to keep all my 'Dim' statements in 1 block so they're
    'easier to find later when i need to change something
    Dim Total_Stock_Volume As Double
    Dim Summary_Table_Row As Integer
    Dim lastRow As Long
    Dim ws As Worksheet
    Dim j As Long
    Dim i As Long
    Dim Ticker As String

    'i prefer to clump my like assignments together in a block
    Summary_Table_Row = 2
    Total_Stock_Volume = 0

    'iterate through the collection of worksheets in your workbook
    For j = 1 To Excel.Application.ThisWorkbook.Worksheets.Count
        'set a reference to a worksheet // this will go through
        'the different worksheets in the workbook as the loop
        'progresses
        Set ws = Excel.Application.ThisWorkbook.Worksheets(j)

        With ws
            'this is a better way to get the last column in a worksheet
            lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        End With

        For i = 2 To lastRow
            'i prefer to explicitly cast anything i get from a cell to the type
            'i intend to use because .Value returns a Variant type by default
            Total_Stock_Volume = Total_Stock_Volume + CDbl(ws.Cells(i, 7).Value)

            If ws.Cells(i + 1, 1).Value <> ws.Cells(i, 1).Value Then
                'me explicitly casting the value of the cell to a string
                Ticker = CStr(ws.Cells(i, 1).Value)

                'i like with statements because it looks nicer to me.
                'i'm sure there's a better reason to use these, but that's
                'my reason!
                With ws
                    .Range("J" & Summary_Table_Row).Value = Ticker
                    .Range("K" & Summary_Table_Row).Value = Total_Stock_Volume
                End With
                Summary_Table_Row = Summary_Table_Row + 1

                'i'm unsure about the intention with this, so i'll leave it alone
                Total_Stock_Volume = 0
            End If
        Next i

    Next
End Sub

Hope it helps!

Edit: Added screenshots of what the output from the above code looks like when I run it. As I said in the comments, without further information about what you want this code to do, I can't really do anything else to help.

Sheet1:

Sheet2:

Sheet3:

vba - 工作表中多个工作表的 VBA 循环

问题描述

解决方案

推荐阅读