в 16:33, 26 мая 2010

2010-05-26T16:33:00Z

Admin: 1 версия

2010-05-26T12:47:43Z

1 версия

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

==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>

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

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

в 16:33, 26 мая 2010

Admin: 1 версия