VBA/Excel/Access/Word/Excel/Selection

Материал из VB Эксперт
Перейти к: навигация, поиск

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>