excel利用VBA选择已定义名称的区域

来源:互联网 时间:2016-01-03

如果活动单元格位于某个已定义名称的区域中,可以用下面的VBA代码来选择这个区域,同时在状态栏中给出提示。

Public Sub SelectRange()

Dim RngName As String

Dim R As Range

Set R = ActiveCell

Dim Msg As String

Msg = "活动单元格不在已定义名称的区域中"

RngName = CellInNamedRange(R)

If RngName <> "" Then

Range(RngName).Select

Msg = "已选择的区域名称: " + RngName

End If

Application.StatusBar = Msg

End Sub

Public Function CellInNamedRange(Rng As Range) As String

Dim N As Name

Dim C As Range

Dim TestRng As Range

On Error Resume Next

For Each N In ActiveWorkbook.Names

Set C = Nothing

Set TestRng = N.RefersToRange

Set C = Application.Intersect(TestRng, Rng)

If Not C Is Nothing Then

CellInNamedRange = N.Name

Exit Function

End If

Next N

CellInNamedRange = ""

End Function

如果活动单元格位于多个已定义名称的区域中,将按照名称框下拉列表中的顺序选择第一个名称区域。

相关阅读:
Top