遍历 Excel VBA 或 VSTO 2005 中的所有单元格

Iterating through all the cells in Excel VBA or VSTO 2005

提问by Ryan Liang

I need to simply go through all the cells in a Excel Spreadsheet and check the values in the cells. The cells may contain text, numbers or be blank. I am not very familiar / comfortable working with the concept of 'Range'. Therefore, any sample codes would be greatly appreciated. (I did try to google it, but the code snippets I found didn't quite do what I needed)

我需要简单地浏览 Excel 电子表格中的所有单元格并检查单元格中的值。单元格可能包含文本、数字或空白。我不太熟悉/不习惯使用“范围”的概念。因此，任何示例代码将不胜感激。（我确实尝试用谷歌搜索它，但我发现的代码片段并没有完全满足我的需要）

Thank you.

谢谢你。

采纳答案by betelgeuce

Sub CheckValues1()
    Dim rwIndex As Integer
    Dim colIndex As Integer
    For rwIndex = 1 To 10
            For colIndex = 1 To 5
                If Cells(rwIndex, colIndex).Value <> 0 Then _
                    Cells(rwIndex, colIndex).Value = 0
            Next colIndex
    Next rwIndex
End Sub

Found this snippet on http://www.java2s.com/Code/VBA-Excel-Access-Word/Excel/Checksvaluesinarange10rowsby5columns.htmIt seems to be quite useful as a function to illustrate the means to check values in cells in an ordered fashion.

在http://www.java2s.com/Code/VBA-Excel-Access-Word/Excel/Checksvaluesinarange10rowsby5columns.htm上找到了这个片段它似乎非常有用作为一个函数来说明在有序的单元格中检查值的方法时尚。

Just imagine it as being a 2d Array of sorts and apply the same logic to loop through cells.

想象一下它是一个二维数组，并应用相同的逻辑来循环单元格。

回答by cori

If you only need to look at the cells that are in use you can use:

如果您只需要查看正在使用的单元格，您可以使用：

sub IterateCells()

   For Each Cell in ActiveSheet.UsedRange.Cells
      'do some stuff
   Next

End Sub

that will hit everything in the range from A1 to the last cell with data (the bottom right-most cell)

这将命中从 A1 到带有数据的最后一个单元格（最右下角的单元格）范围内的所有内容

回答by Jon Fournier

If you're just looking at values of cells you can store the values in an array of variant type. It seems that getting the value of an element in an array can be much faster than interacting with Excel, so you can see some difference in performance using an array of all cell values compared to repeatedly getting single cells.

如果您只是查看单元格的值，则可以将这些值存储在变体类型的数组中。似乎获取数组中元素的值比与 Excel 交互要快得多，因此您可以看到使用所有单元格值的数组与重复获取单个单元格相比，性能存在一些差异。

Dim ValArray as Variant
ValArray = Range("A1:IV" & Rows.Count).Value

Then you can get a cell value just by checking ValArray( row , column )

然后你可以通过检查 ValArray( row , column ) 来获得一个单元格值

回答by theo

You can use a For Each to iterate through all the cells in a defined range.

您可以使用 For Each 来遍历定义范围内的所有单元格。

Public Sub IterateThroughRange()

Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range

Set wb = Application.Workbooks(1)
Set ws = wb.Sheets(1)
Set rng = ws.Range("A1", "C3")

For Each cell In rng.Cells
    cell.Value = cell.Address
Next cell

End Sub

回答by Cory Engebretson

For a VB or C# app, one way to do this is by using Office Interop. This depends on which version of Excel you're working with.

对于 VB 或 C# 应用程序，一种方法是使用 Office Interop。这取决于您使用的是哪个版本的 Excel。

For Excel 2003, this MSDN article is a good place to start. Understanding the Excel Object Model from a Visual Studio 2005 Developer's Perspective

对于 Excel 2003，这篇 MSDN 文章是一个很好的起点。 从 Visual Studio 2005 开发人员的角度理解 Excel 对象模型

You'll basically need to do the following:

您基本上需要执行以下操作：

• Start the Excel application.
• Open the Excel workbook.
• Retrieve the worksheet from the workbook by name or index.
• Iterate through all the Cells in the worksheet which were retrieved as a range.
• Sample (untested) code excerpt below for the last step.

• 启动 Excel 应用程序。
• 打开 Excel 工作簿。
• 按名称或索引从工作簿中检索工作表。
• 遍历工作表中作为范围检索的所有单元格。
• 最后一步的示例（未经测试）代码摘录如下。

    Excel.Range allCellsRng;
    string lowerRightCell = "IV65536";
    allCellsRng = ws.get_Range("A1", lowerRightCell).Cells;
    foreach (Range cell in allCellsRng)
    {
        if (null == cell.Value2 || isBlank(cell.Value2))
        {
          // Do something.
        }
        else if (isText(cell.Value2))
        {
          // Do something.
        }
        else if (isNumeric(cell.Value2))
        {
          // Do something.
        }
    }

For Excel 2007, try this MSDN reference.

对于 Excel 2007，试试这个 MSDN 参考。

回答by TheXenocide

There are several methods to accomplish this, each of which has advantages and disadvantages; First and foremost, you're going to need to have an instance of a Worksheet object, Application.ActiveSheet works if you just want the one the user is looking at.

有几种方法可以实现这一点，每种方法都有优点和缺点；首先，您需要有一个 Worksheet 对象的实例，如果您只想要用户正在查看的对象，Application.ActiveSheet 就可以工作。

The Worksheet object has three properties that can be used to access cell data (Cells, Rows, Columns) and a method that can be used to obtain a block of cell data, (get_Range).

Worksheet 对象具有三个可用于访问单元格数据的属性（Cells、Rows、Columns）和一个可用于获取单元格数据块的方法（get_Range）。

Ranges can be resized and such, but you may need to use the properties mentioned above to find out where the boundaries of your data are. The advantage to a Range becomes apparent when you are working with large amounts of data because VSTO add-ins are hosted outside the boundaries of the Excel application itself, so all calls to Excel have to be passed through a layer with overhead; obtaining a Range allows you to get/set all of the data you want in one call which can have huge performance benefits, but it requires you to use explicit details rather than iterating through each entry.

范围可以调整大小等，但您可能需要使用上面提到的属性来找出数据的边界在哪里。当您处理大量数据时，范围的优势变得显而易见，因为 VSTO 加载项托管在 Excel 应用程序本身的边界之外，因此对 Excel 的所有调用都必须通过一个有开销的层；获取 Range 允许您在一次调用中获取/设置您想要的所有数据，这可以带来巨大的性能优势，但它需要您使用明确的详细信息，而不是遍历每个条目。

This MSDN forum postshows a VB.Net developer asking a question about getting the results of a Range as an array

这篇 MSDN 论坛帖子展示了一位 VB.Net 开发人员询问有关将范围的结果作为数组获取的问题

回答by w4ik

You basically can loop over a Range

你基本上可以循环一个范围

Get a sheet

得到一张表

myWs = (Worksheet)MyWb.Worksheets[1];

Get the Range you're interested in If you really want to check every cell use Excel's limits

获取您感兴趣的范围如果您真的想检查每个单元格，请使用 Excel 的限制

The Excel 2007 "Big Grid" increases the maximum number of rows per worksheet from 65,536 to over 1 million, and the number of columns from 256 (IV) to 16,384 (XFD). from here http://msdn.microsoft.com/en-us/library/aa730921.aspx#Office2007excelPerf_BigGridIncreasedLimitsExcel

Excel 2007“大网格”将每个工作表的最大行数从 65,536 增加到超过 100 万，将列数从 256 (IV) 增加到 16,384 (XFD)。从这里http://msdn.microsoft.com/en-us/library/aa730921.aspx#Office2007excelPerf_BigGridIncreasedLimitsExcel

and then loop over the range

然后遍历范围

        Range myBigRange = myWs.get_Range("A1", "A256");

        string myValue;

        foreach(Range myCell in myBigRange )
        {
            myValue = myCell.Value2.ToString();
        }

回答by w4ik

My VBA skills are a little rusty, but this is the general idea of what I'd do.
The easiest way to do this would be to iterate through a loop for every column:

我的 VBA 技能有点生疏，但这是我要做的一般想法。
最简单的方法是为每一列迭代一个循环：

public sub CellProcessing()
on error goto errHandler

    dim MAX_ROW as Integer   'how many rows in the spreadsheet
    dim i as Integer
    dim cols as String

    for i = 1 to MAX_ROW
        'perform checks on the cell here
        'access the cell with Range("A" & i) to get cell A1 where i = 1
    next i

exitHandler:
    exit sub
errHandler:
    msgbox "Error " & err.Number & ": " & err.Description
    resume exitHandler
end sub

it seems that the color syntax highlighting doesn't like vba, but hopefully this will help somewhat (at least give you a starting point to work from).

似乎颜色语法突出显示不喜欢 vba，但希望这会有所帮助（至少给你一个工作的起点）。

• Brisketeer

• 牛腩匠

回答by Martin08

In Excel VBA, this function will give you the content of any cell in any worksheet.

在 Excel VBA 中，此函数将为您提供任何工作表中任何单元格的内容。

Function getCellContent(Byref ws As Worksheet, ByVal rowindex As Integer, ByVal colindex As Integer) as String
    getCellContent = CStr(ws.Cells(rowindex, colindex))
End Function

So if you want to check the value of cells, just put the function in a loop, give it the reference to the worksheet you want and the row index and column index of the cell. Row index and column index both start from 1, meaning that cell A1 will be ws.Cells(1,1) and so on.

所以如果你想检查单元格的值，只要把函数放在一个循环中，给它你想要的工作表的引用以及单元格的行索引和列索引。行索引和列索引都从 1 开始，这意味着单元格 A1 将是 ws.Cells(1,1) 等等。