vba 获取Excel VB中的当前单元格

Get the current cell in Excel VB

提问by Ben McCormack

I have a small script in Excel/VB that I'm trying to get working. All I want to do is select a dynamic range of data to copy but I can't seem to find any help/code on how to get the grid data (like A11).

我正在尝试使用 Excel/VB 中的一个小脚本。我想要做的就是选择要复制的动态数据范围，但我似乎找不到有关如何获取网格数据（如 A11）的任何帮助/代码。

Here is code I have from macro recording that selects the range of data:

这是我从宏录制中选择数据范围的代码：

Range("D291:D380").Select

I was hoping I could just do Range(Current).Selector something but that doesn't work.

我希望我能Range(Current).Select做点什么，但那行不通。

Any help is appreciated.

任何帮助表示赞赏。

回答by Ben McCormack

Have you tried:

你有没有尝试过：

For one cell:

对于一个单元格：

ActiveCell.Select

For multiple selected cells:

对于多个选定的单元格：

Selection.Range

For example:

例如：

Dim rng As Range
Set rng = Range(Selection.Address)

回答by Lance Roberts

If you're trying to grab a range with a dynamically generated string, then you just have to build the string like this:

如果您尝试使用动态生成的字符串获取范围，那么您只需要像这样构建字符串：

Range(firstcol & firstrow & ":" & secondcol & secondrow).Select

回答by Alex P

This may not help answer your question directly but is something I have found useful when trying to work with dynamic ranges that may help you out.

这可能无助于直接回答您的问题，但在尝试使用可能对您有所帮助的动态范围时，我发现这是有用的。

Suppose in your worksheet you have the numbers 100 to 108 in cells A1:C3:

假设在您的工作表中，单元格 A1:C3 中有数字 100 到 108：

          A    B    C  
1        100  101  102
2        103  104  105
3        106  107  108

Then to select all the cells you can use the CurrentRegionproperty:

然后选择所有单元格，您可以使用该CurrentRegion属性：

Sub SelectRange()
Dim dynamicRange As Range

Set dynamicRange = Range("A1").CurrentRegion

End Sub

The advantage of this is that if you add new rows or columns to your block of numbers (e.g. 109, 110, 111) then the CurrentRegion will always reference the enlarged range (in this case A1:C4).

这样做的好处是，如果您向数字块（例如 109、110、111）添加新的行或列，则 CurrentRegion 将始终引用扩大的范围（在本例中为 A1:C4）。

I have used CurrentRegion quite a bit in my VBA code and find it is most useful when working with dynmacially sized ranges. Also it avoids having to hard code ranges in your code.

我在我的 VBA 代码中使用了很多 CurrentRegion，发现它在处理动态大小的范围时最有用。它还避免了在代码中硬编码范围。

As a final note, in my code you will see that I used A1 as the reference cell for CurrentRegion. It will also work no matter which cell you reference (try: replacing A1 with B2 for example). The reason is that CurrentRegion will select all contiguous cells based on the reference cell.

最后要注意的是，在我的代码中，您将看到我使用 A1 作为 CurrentRegion 的参考单元格。无论您引用哪个单元格，它也将起作用（例如：尝试用 B2 替换 A1）。原因是 CurrentRegion 将根据参考单元格选择所有连续的单元格。

回答by Thomas

The keyword "Selection" is already a vba Range object so you can use it directly, and you don't have to select cells to copy, for example you can be on Sheet1 and issue these commands:

关键字“ Selection”已经是一个vba Range对象，因此您可以直接使用它，而不必选择要复制的单元格，例如您可以在Sheet1上并发出以下命令：

ThisWorkbook.worksheets("sheet2").Range("namedRange_or_address").Copy
ThisWorkbook.worksheets("sheet1").Range("namedRange_or_address").Paste

If it is a multiple selection you should use the Area object in a for loop:

如果是多选，则应在 for 循环中使用 Area 对象：

Dim a as Range
For Each a in ActiveSheet.Selection.Areas
    a.Copy
    ThisWorkbook.worksheets("sheet2").Range("A1").Paste
Next

Regards

问候

Thomas

托马斯

回答by James Heffer

Try this

尝试这个

Dim app As Excel.Application = Nothing
Dim Active_Cell As Excel.Range = Nothing

Try
            app = CType(Marshal.GetActiveObject("Excel.Application"), Excel.Application)
 Active_Cell = app.ActiveCell

Catch ex As Exception
            MsgBox(ex.Message)
            Exit Sub
        End Try

'             .address will return the cell reference :)

回答by Mistella

I realize this doesn't directly apply from the title of the question, However some ways to deal with a variable range could be to select the range each time the code runs -- especially if you are interested in a user-selected range. If you areinterested in that option, you can use the Application.InputBox (official documentation page here). One of the optional variables is 'type'. If the type is set equal to 8, the InputBox also has an excel-style range selection option. An example of how to use it in code would be:

我意识到这并不直接适用于问题的标题，但是处理可变范围的一些方法可能是每次代码运行时选择范围 - 特别是如果您对用户选择的范围感兴趣。如果你是感兴趣的是选项，你可以使用Application.InputBox（官方文档页面在这里）。可选变量之一是“类型”。如果类型设置为 8，则 InputBox 还具有 excel 样式的范围选择选项。如何在代码中使用它的一个例子是：

Dim rng as Range
Set rng = Application.InputBox(Prompt:= "Please select a range", Type:=8)

Note:

笔记：

If you assign the InputBox value to a none-range variable (without the Setkeyword), instead of the ranges, the values from the ranges will be assigned, as in the code below (although selecting multiple ranges in this situation may require the values to be assigned to a variant):

如果将 InputBox 值分配给非范围变量（没有Set关键字），而不是范围，则会分配范围中的值，如下面的代码所示（尽管在这种情况下选择多个范围可能需要值分配给一个变体）：

Dim str as String
str = Application.InputBox(Prompt:= "Please select a range", Type:=8)