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

如果活动单元格位于某个已定义名称的区域中，可以用下面的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

文章内容收集于网络，希望能为广大朋友提供帮助。强国说学习-WPS之家(wps.qiangguoshuo.com)

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

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

如想转载该文章请注明出处：强国说学习-qiangguoshuo.com

转载声明：本站发布文章及版权归原作者所有，转载本站文章请注明文章来源！

本文链接：https://www.qiangguoshuo.com/excel/42337.html