в 16:33, 26 мая 2010

2010-05-26T16:33:00Z

Admin: 1 версия

2010-05-26T12:47:33Z

1 версия

Новая страница

==Adding Comments for a name==



<source lang="vb">

Sub addcomment()
ActiveWorkbook.Worksheets("Sheet7").Names("LocalOffice").rument = "text"
End Sub
</source>

==A name can also store the data stored in an array.==



<source lang="vb">

Sub NamedArray()
Dim myArray(10, 5)
Dim i As Integer, j As Integer
For i = 1 To 10
For j = 1 To 5
myArray(i, j) = i + j
Next j
Next i
Names.add name:="FirstArray", RefersTo:=myArray
End Sub
</source>

==Checking for the Existence of a Name==



<source lang="vb">

Function NameExists(FindName As String) As Boolean
Dim Rng As Range
Dim myName As String
On Error Resume Next
myName = ActiveWorkbook.Names(FindName).Name
If Err.Number = 0 Then
NameExists = True
Else
NameExists = False
End If
End Function

</source>

==Check Name existance==



<source lang="vb">

Sub Main()
If NameExists("SalesData") Then
MsgBox "Name Exists"
Else
MsgBox "Name does not exist"
End If
End Sub
Function NameExists(myName As String) As Boolean
Dim X As String
On Error Resume Next
X = Names(myName).RefersTo
If Err.Number <> 0 Then
NameExists = False
Err.Clear
Else
NameExists = True
End If
End Function
</source>

==Define a name by hard code the cell address==



<source lang="vb">

Sub DefineName1a()
ActiveWorkbook.names.Add Name:="NameArea", RefersToR1C1:="=sheet1!R1C1:R3C1"
End Sub
</source>

==Determining which Names Overlap a Range==



<source lang="vb">

Sub SelectionEntirelyInNames()
Dim sMessage As String
Dim nmName As name
Dim rngNameRange As range
Dim rng As range
On Error Resume Next
For Each nmName In Names
Set rngNameRange = Nothing
Set rngNameRange = nmName.RefersToRange
If Not rngNameRange Is Nothing Then
If rngNameRange.Parent.name = ActiveSheet.name Then
Set rng = Intersect(Selection, rngNameRange)
If Not rng Is Nothing Then
If Selection.Address = rng.Address Then
sMessage = sMessage & nmName.name & vbCr
End If
End If
End If
End If
Next nmName
If sMessage = "" Then
MsgBox "The selection is not entirely in any name"
Else
MsgBox sMessage
End If
End Sub
</source>

==Enables access to named ranges in arbitrary workbooks==



<source lang="vb">

Sub ChangeValueInNamedCell2()
Dim rng As Range
Set rng = Evaluate(ActiveWorkbook.names("CellXy").Name)
rng.Value = "This is a named cell."
Debug.Print rng.Value
End Sub
</source>

==Fill named range with values==



<source lang="vb">

Sub FillNameArea1()
Application.Goto Reference:=ActiveWorkbook.Names("NameArea").Name
Selection.value = "Test1"
End Sub
Sub FillNameArea2()
Evaluate(ActiveWorkbook.Names("NameArea").value).value = "Test2"
End Sub
</source>

==find out which names are overlapping the selected cells, regardless of whether they entirely contain the selected cells==



<source lang="vb">

Sub NamesOverlappingSelection()
Dim sMessage As String
Dim nmName As name
Dim rngNameRange As range
Dim rng As range
On Error Resume Next
For Each nmName In Names
Set rngNameRange = Nothing
Set rngNameRange = range(nmName.name)
If Not rngNameRange Is Nothing Then
If rngNameRange.Parent.name = ActiveSheet.name Then
Set rng = Intersect(Selection, rngNameRange)
If Not rng Is Nothing Then
sMessage = sMessage & nmName.name & vbCr
End If
End If
End If
Next nmName
If sMessage = "" Then
MsgBox "The selection is not entirely in any name"
Else
MsgBox sMessage
End If
End Sub
</source>

==Generate named range from Range object==



<source lang="vb">

Sub BuildNameFromRange()
Dim rng As Range
Set rng = ActiveWorkbook.Sheets("sheet1").[A1].CurrentRegion
ActiveWorkbook.Names.Add Name:="NameArea", RefersTo:="=" + rng.Address(External:=True)
Debug.Print Range("NameArea").Address(External:=True)
End Sub
</source>

==Generate Range object from named range==



<source lang="vb">

Sub BuildRangeFromName()
Dim rng As Range
Set rng = Evaluate(ActiveWorkbook.names("NameArea").Value)
Debug.Print rng.Address(External:=True)
End Sub
</source>

==Go to a name==



<source lang="vb">

Sub GotoName2()
Application.Goto Reference:= ActiveWorkbook.names("NameArea").Name, Scroll:=True
End Sub
</source>

==Hide a name by setting its Visible property to False==



<source lang="vb">

Sub nameRef()
Names.add name:="StoreNumber", RefersTo:=v, Visible:=False
End Sub
</source>

==Hide the name after it has been created:==



<source lang="vb">

Sub vis()
Names("StoreNumber").Visible = False
End Sub
</source>

==Insert hidden name==



<source lang="vb">

Public Sub InsertHiddenName()
Names.Add Name:="PassWord", RefersTo:="Bazonkas", Visible:=False
End Sub
</source>

==Is Name In Workbook==



<source lang="vb">

Option Explicit
Public Function IsNameInWorkbook(ByVal Name As String) As Boolean
Dim X As String
Dim aRange As Range

Application.Volatile
On Error Resume Next
Set aRange = Application.Caller
Err.Clear

If aRange Is Nothing Then
X = ActiveWorkbook.Names(Name).Name
Else
X = aRange.Parent.Parent.Names(Name).Name
End If

If Err.Number = 0 Then IsNameInWorkbook = True
End Function
Public Sub TestName()
If IsNameInWorkbook(InputBox("What Name")) Then
MsgBox "Name exists"
Else
MsgBox "Name does not exist"
End If
End Sub
</source>

==Names Overlapping Selection==



<source lang="vb">

Public Sub NamesOverlappingSelection()
Dim Message As String
Dim aName As Name
Dim NameRange As Range
Dim aRange As Range

On Error Resume Next
For Each aName In Names
Set NameRange = Nothing
Set NameRange = Range(aName.Name)
If Not NameRange Is Nothing Then
If NameRange.Parent.Name = ActiveSheet.Name Then
Set aRange = Intersect(Selection, NameRange)
If Not aRange Is Nothing Then
Message = Message & aName.Name & vbCr
End If
End If
End If
Next aName
If Message = "" Then
Debug.Print "No Names are overlapping the selection"
Else
Debug.Print Message
End If
End Sub
</source>

==Range objects cannot be used with an worksheet object as superobject==



<source lang="vb">

Sub ChangeValueInNamedCell1()
Range("CellXy").Value = "CellXy"
Debug.Print Range("CellXy").Value
End Sub
</source>

==Retrieving Values Stored as a Workbook Name Using the Evaluate Method==



<source lang="vb">

Sub TestWorkbookNameValue()
Dim vValue As Variant
vValue = Application.Names("SalesTaxRate").RefersTo
Debug.Print "Value retrieved using RefersTo: " & vValue
End Sub
</source>

==Searching for a Name==



<source lang="vb">

Function IsNameInWorkbook(sName As String) As Boolean
Dim s As String
Dim rng As range
Application.Volatile
On Error Resume Next
Set rng = Application.Caller
Err.clear
If rng Is Nothing Then
s = ActiveWorkbook.Names(sName).name
Else
s = rng.Parent.Parent.Names(sName).name
End If
If Err.Number = 0 Then IsNameInWorkbook = True
End Function
Sub TestName()
If IsNameInWorkbook(InputBox("What Name")) Then
MsgBox "Name exists"
Else
MsgBox "Name does not exist"
End If
End Sub
</source>

==Searching for the Name of a Range==



<source lang="vb">

Sub TestNameOfRange()
Dim nmName As name
On Error Resume Next
Set nmName = Selection.name
If nmName Is Nothing Then
MsgBox " Selection has no name"
Else
MsgBox nmName.name
End If
End Sub
</source>

==Selection Entirely In Names==



<source lang="vb">

Public Sub SelectionEntirelyInNames()
Dim Message As String
Dim aName As Name
Dim NameRange As Range
Dim aRange As Range
On Error Resume Next

For Each aName In Names
Set NameRange = Nothing
Set NameRange = aName.RefersToRange
If Not NameRange Is Nothing Then
If NameRange.Parent.Name = ActiveSheet.Name Then
Set aRange = Intersect(Selection, NameRange)
If Not aRange Is Nothing Then
If Selection.Address = aRange.Address Then
Message = Message & aName.Name & vbCr
End If
End If
End If
End If
Next aName
If Message = "" Then
MsgBox "The selection is not entirely in any name"
Else
MsgBox Message
End If
End Sub
</source>

==Select named range==



<source lang="vb">

Sub GotoName1()
Application.Goto Reference:= _
ActiveWorkbook.names("NameArea").Name
End Sub
</source>

==specify that the Names collection belongs to a worksheet:==



<source lang="vb">

Sub nameAdd()
Worksheets("Sheet1").Names.add name:="F", RefersToR1C1:="=Sheet1!R1C1:R6C6"
End Sub
</source>

==Store formulas into names. The formula must start with an equals sign (=).==



<source lang="vb">

Sub addName()
Names.add name:="ItemsInA", RefersTo:="=COUNTA($A:$A)"
End Sub
</source>

==storing a formula in a name is the same as for a range==



<source lang="vb">

Sub addName()
Names.add name:="Product", RefersTo:="=OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A))"
End Sub
</source>

==Storing Values in Names==



<source lang="vb">

Sub var()
Dim v As Variant
v = 3.14159
Names.add name:="StoreNumber", RefersTo:=v
v = "Sales"
Names.add name:="StoreString", RefersTo:=v
End Sub
</source>

==use names to store numbers between sessions==



<source lang="vb">

Sub store()
NumofSales = 5123
Names.add name:="TotalSales", RefersTo:=NumofSales
"Or use this:
Names.add name:="TotalSales", RefersTo:=5123
End Sub
</source>

==use the Evaluate method equivalent to evaluate the name in VBA:==



<source lang="vb">

Sub itemA()
Names.add name:="ItemsInA", RefersTo:="=COUNTA($A:$A)"
MsgBox [ItemsInA]
End Sub
</source>

==Using named ranges of cells==



<source lang="vb">

Option Explicit
Sub DefineName1()
Range("A3:B5").Name = "TestArea"
Debug.Print Range("TestArea").Address(External:=True)
End Sub
</source>

==Using the Names Object to List All Named Ranges==



<source lang="vb">

Sub TestListNames()
ListWorkbookNames ThisWorkbook, ThisWorkbook.Worksheets(2).range("a2")
End Sub
Sub ListWorkbookNames(wb As Workbook, rgListStart As range)
Dim nm As name
For Each nm In wb.Names
rgListStart.value = nm.name
rgListStart.Offset(0, 1).value = """ & nm.RefersTo
Set rgListStart = rgListStart.Offset(1, 0)
Next
End Sub
</source>

← Предыдущая	Версия 16:33, 26 мая 2010
(нет различий)

VBA/Excel/Access/Word/Excel/Name - История изменений

в 16:33, 26 мая 2010

Admin: 1 версия