VBA/Excel/Access/Word/Excel/Selection
Содержание
- 1 Code does / does not use With-End With
- 2 Counts columns in a multiple selection
- 3 Determining the type of selected range
- 4 Find next heading
- 5 Get selection address
- 6 Get the address of selection
- 7 Is the current selection a Range
- 8 Returns a count of all cells in a selection: using the Selection and Count properties
- 9 Returns a count of the number of columns in a selection
- 10 Returns a count of the number of rows in a selection
- 11 Selection move down
- 12 To add a range name based on a selection
- 13 Toggling a Boolean property
Code does / does not use With-End With
<source lang="vb">
Sub selectionDemo()
Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.WrapText = True Selection.Orientation = 0 Selection.ShrinkToFit = False Selection.MergeCells = False With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .ShrinkToFit = False .MergeCells = False End With
End Sub
</source>
Counts columns in a multiple selection
<source lang="vb">
Sub CountColumnsMultipleSelections()
AreaCount = Selection.Areas.Count If AreaCount <= 1 Then MsgBox "The selection contains " & Selection.Columns.Count & " columns." Else For i = 1 To AreaCount MsgBox "Area " & i & " of the selection contains " & Selection.Areas(i).Columns.Count & " columns." Next i End If
End Sub
</source>
Determining the type of selected range
<source lang="vb">
Sub Main()
Debug.Print AreaType(Selection)
End Sub Function AreaType(RangeArea As range) As String " Returns the type of a range in an area
Select Case True Case RangeArea.Count = 1 AreaType = "Cell" Case RangeArea.Count = Cells.Count AreaType = "Worksheet" Case RangeArea.Rows.Count = Cells.Rows.Count AreaType = "Column" Case RangeArea.Columns.Count = Cells.Columns.Count AreaType = "Row" Case Else AreaType = "Block" End Select
End Function
</source>
Find next heading
<source lang="vb"> Sub FindNextHeading() Do Until Left(Selection.Paragraphs(1).Style, 7) = "Heading" Selection.MoveDown Unit:=wdParagraph, _ Count:=1, Extend:=wdMove Loop End Sub </source>
Get selection address
<source lang="vb">
Sub GetSelectionAddress()
ActiveSheet.Names.Add Name:="MyRange2", RefersTo:="=" & Selection.Address()
End Sub
</source>
Get the address of selection
<source lang="vb">
Sub getSelectionAddress
Selection.Address
End Sub
</source>
Is the current selection a Range
<source lang="vb">
Sub EnterAvg()
If TypeName(Selection) <> "Range" Then Exit Sub
End Sub
</source>
Returns a count of all cells in a selection: using the Selection and Count properties
<source lang="vb">
Sub CountAllCells()
Dim myCount As Integer myCount = Selection.Count MsgBox "The total number of cell(s) in this selection is : " _ & myCount, vbInformation, "Count Cells"
End Sub
</source>
Returns a count of the number of columns in a selection
<source lang="vb">
Sub CountColumns()
Dim myCount As Integer myCount = Selection.Columns.Count MsgBox "This selection contains " & myCount & " columns", vbInformation, "Count Columns"
End Sub
</source>
Returns a count of the number of rows in a selection
<source lang="vb">
Sub CountRows()
Dim myCount As Integer myCount = Selection.Rows.Count MsgBox "This selection contains " & myCount & " row(s)", vbInformation, "Count Rows"
End Sub
</source>
Selection move down
<source lang="vb">
Sub loopDemo()
Dim i As Integer For i = 1 To ActiveDocument.Paragraphs.Count Application.StatusBar = "formatting" & i & " out of " & ActiveDocument.Paragraphs.Count & "..." Selection.MoveDown Unit:=wdParagraph, Count:=1, Extend:=wdMove Next i
End Sub
</source>
To add a range name based on a selection
<source lang="vb">
Sub AddName4()
Selection.Name = "MyRange4"
End Sub
</source>
Toggling a Boolean property
<source lang="vb">
Sub ToggleWrapText()
If TypeName(Selection) = "Range" Then Selection.WrapText = Not ActiveCell.WrapText End If
End Sub
</source>