VBA/Excel/Access/Word/Excel/Range — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Версия 19:33, 26 мая 2010
Содержание
- 1 Adding Clickable Sorting to Worksheet Lists
- 2 Address, a read-only property, displays the cell address for a Range object in absolute notation (a dollar sign before the column letter and before the row number).
- 3 Assign the value in C1 in the active sheet to D10 in the sheet named Sales, in the active workbook
- 4 Assign the value of a property to a variable so it can be used in later code
- 5 Building the Table
- 6 Change the Value property for a range of any size: statement enters the number 123 into each cell in a range
- 7 Copies the contents of range A1:B3 to the clipboard:
- 8 Count property returns the number of cells in a range (all cells, not just the nonblank cells). It"s a read-only property.
- 9 Count the blank elements in a range
- 10 Deletes a range and then fills the resulting gap by shifting the other cells to the left:
- 11 Displays a message box that shows the value in cell A1 on Sheet1:
- 12 Expression refers to a cell one row below cell A1 and two columns to the right of cell A1: this refers to the cell commonly known as C2
- 13 Find in a range
- 14 HasFormula property
- 15 Highlights selected range
- 16 If the Range object consists of more than one cell, the Column property returns the column number of the first column in the range
- 17 If the Range object consists of more than one cell, the Row property returns the row number of the first row in the range.
- 18 If the Range object is not in the active worksheet in the active workbook
- 19 Modify multiple cells at once using a range reference (like A1:A2)
- 20 Read a range from InputBox
- 21 read the Value property only for a single- cell Range object: statement generates an error
- 22 Returns the type of a range in an area
- 23 Row property returns the row number of a single-cell range.
- 24 Select a range and activate another
- 25 Show Edit Ranges
- 26 Sum the elements in a range
- 27 The Column property returns the column number of a single-cell range;
- 28 The Text property returns a string that represents the text as displayed in a cell: the formatted value. The Text property is read-only.
- 29 To enter the name Florence into cell C10, you assign the name to the Value property of the Range object
- 30 Use object variables to represent the ranges
- 31 Value is the default property for a Range object.
Adding Clickable Sorting to Worksheet Lists
<source lang="vb">
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim mnDirection As Integer Dim mnColumn As Integer If Target.Column < 5 And Target.Row = 1 Then If Target.Column <> mnColumn Then mnColumn = Target.Column mnDirection = xlAscending Else If mnDirection = xlAscending Then mnDirection = xlDescending Else mnDirection = xlAscending End If End If Dim rg As Range Set rg = Me.Cells(1, 1).CurrentRegion rg.Sort Key1:=rg.Cells(1, mnColumn), _ Order1:=mnDirection, _ Header:=xlYes Set rg = Nothing End If
End Sub
</source>
Address, a read-only property, displays the cell address for a Range object in absolute notation (a dollar sign before the column letter and before the row number).
<source lang="vb">
Sub cellAddress()
MsgBox range(cells(1, 1), cells(5, 5)).Address
End Sub
</source>
Assign the value in C1 in the active sheet to D10 in the sheet named Sales, in the active workbook
<source lang="vb">
Sub valueDemo()
Worksheets("Sales").range("D10").value = range("C1").value
End Sub
</source>
Assign the value of a property to a variable so it can be used in later code
<source lang="vb">
Sub valueDemo2()
OpeningStock = range("M100").value range("M100").value = 100 ActiveSheet.printOut range("M100").value = OpeningStock
End Sub
</source>
Building the Table
<source lang="vb">
Sub MultiplicationTable()
" Build a multiplication table using a single formula Range("B1:M1").Value = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) Range("B1:M1").Font.Bold = True Range("B1:M1").Copy Range("A2:A13").PasteSpecial Transpose:=True Range("B2:M13").FormulaR1C1 = "=RC1*R1C" Cells.EntireColumn.AutoFit
End Sub
</source>
Change the Value property for a range of any size: statement enters the number 123 into each cell in a range
<source lang="vb">
Sub changeMain()
Worksheets("Sheet1").range("A1:C3").value = 123
End Sub
</source>
Copies the contents of range A1:B3 to the clipboard:
<source lang="vb">
Sub copyDemo()
range("A1:B3").Copy
End Sub
</source>
Count property returns the number of cells in a range (all cells, not just the nonblank cells). It"s a read-only property.
<source lang="vb">
Sub count()
MsgBox range("A1:C3").count
End Sub
</source>
Count the blank elements in a range
<source lang="vb">
Public Sub Array3()
Dim Data As Variant, X As Variant Dim Message As String, i As Integer Data = Range("A1:A20").Value i = 1 Do Debug.Print "Lower Bound = " & LBound(Data, i) Debug.Print "Upper Bound = " & UBound(Data, i) i = i + 1 On Error Resume Next X = UBound(Data, i) If Err.Number <> 0 Then Exit Do On Error GoTo 0 Loop Debug.Print "Number of Non Blank Elements = " & WorksheetFunction.CountA(Data)
End Sub
</source>
Deletes a range and then fills the resulting gap by shifting the other cells to the left:
<source lang="vb">
Sub deleteLeft()
range("C6:C10").delete xlToLeft
End Sub
</source>
Displays a message box that shows the value in cell A1 on Sheet1:
<source lang="vb">
Sub valueDemo()
MsgBox Worksheets("Sheet1").range("A1").value
End Sub
</source>
Expression refers to a cell one row below cell A1 and two columns to the right of cell A1: this refers to the cell commonly known as C2
<source lang="vb">
Sub offset()
range("A1").offset(1, 2).Select
End Sub
</source>
Find in a range
<source lang="vb">
Public Sub FindIt()
Dim aRange As Range Set aRange = Range("A1:A12").Find(what:="Jun",LookAt:=xlWhole, LookIn:=xlValues) If aRange Is Nothing Then MsgBox "Data not found" Exit Sub Else aRange.Resize(1, 3).Copy Destination:=Range("G1") End If
End Sub
</source>
HasFormula property
<source lang="vb">
Sub hasfor()
Dim FormulaTest As Boolean FormulaTest = range("A1:A2").hasFormula
End Sub
</source>
Highlights selected range
<source lang="vb">
Sub GetRange()
Dim Rng As Range On Error Resume Next Set Rng = Application.InputBox(prompt:="Enter range", Type:=8) If Rng Is Nothing Then MsgBox "Operation Cancelled" Else Rng.Select End If
End Sub
</source>
If the Range object consists of more than one cell, the Column property returns the column number of the first column in the range
<source lang="vb">
Sub columnRange()
MsgBox Sheets("Sheet1").range("A:F3").column
End Sub
</source>
If the Range object consists of more than one cell, the Row property returns the row number of the first row in the range.
<source lang="vb">
Sub rowRange()
MsgBox Sheets("Sheet1").range("A1:F3").row
End Sub
</source>
If the Range object is not in the active worksheet in the active workbook
<source lang="vb">
Sub rangeDemo()
Workbooks("Sales.xls").Worksheets("DataInput").Range("C10").Value = 10
End Sub
</source>
Modify multiple cells at once using a range reference (like A1:A2)
<source lang="vb"> Sub MyMacro() " Insert the text "Hello" in ten cells Range("A1:A10").Value = "Hello" End Sub </source>
Read a range from InputBox
<source lang="vb">
Public Sub SelectRange()
Dim aRange As Range On Error Resume Next Set aRange = Application.InputBox(prompt:="Enter range", Type:=8) If aRange Is Nothing Then MsgBox "Operation Cancelled" Else aRange.Select End If
End Sub
</source>
read the Value property only for a single- cell Range object: statement generates an error
<source lang="vb">
Sub main()
MsgBox Worksheets("Sheet1").range("A1:C3").value
End Sub
</source>
Returns the type of a range in an area
<source lang="vb">
Private Function AreaType(RangeArea As Range) As String
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>
Row property returns the row number of a single-cell range.
<source lang="vb">
Sub row()
MsgBox Sheets("Sheet1").range("F3").row
End Sub
</source>
Select a range and activate another
<source lang="vb">
Public Sub SelectAndActivate()
Range("B3:E10").Select Range("C5").Activate
End Sub
</source>
Show Edit Ranges
<source lang="vb">
Sub ShowEditRanges()
Dim ws As Worksheet, ual As UserAccessList, aer As AllowEditRange Set ws = ThisWorkbook.Sheets("Protection") For Each aer In ws.Protection.AllowEditRanges Debug.Print aer.Title, aer.Range.Address Next
End Sub
</source>
Sum the elements in a range
<source lang="vb">
Public Sub Array1()
Dim Data(10) As Integer Dim Message As String, i As Integer For i = LBound(Data) To UBound(Data) Data(i) = i Next i Debug.Print "Lower Bound = " & LBound(Data) Debug.Print "Upper Bound = " & UBound(Data) Debug.Print "Number of Elements = " & WorksheetFunction.Count(Data) Debug.Print "Sum of Elements = " & WorksheetFunction.Sum(Data)
End Sub
</source>
The Column property returns the column number of a single-cell range;
<source lang="vb">
Sub column()
MsgBox Sheets("Sheet1").range("F3").column
End Sub
</source>
The Text property returns a string that represents the text as displayed in a cell: the formatted value. The Text property is read-only.
<source lang="vb">
Sub text()
MsgBox Worksheets("Sheet1").range("A1").text MsgBox Worksheets("Sheet1").range("A1").value
End Sub
</source>
To enter the name Florence into cell C10, you assign the name to the Value property of the Range object
<source lang="vb">
Sub valueDemo()
Range("C10").Value = "Florence"
End Sub
</source>
Use object variables to represent the ranges
<source lang="vb">
Sub CopyRange3()
Set Rng1 = Workbooks("File1.xls").Sheets("Sheet1").range("A1") Set Rng2 = Workbooks("File2.xls").Sheets("Sheet2").range("A1") Rng1.Copy Rng2
End Sub
</source>
Value is the default property for a Range object.
<source lang="vb">
Sub def()
Worksheets("Sheet1").range("A1").value = 75 Worksheets("Sheet1").range("A1") = 75
End Sub
</source>