в 16:33, 26 мая 2010

2010-05-26T16:33:00Z

Admin: 1 версия

2010-05-26T12:47:32Z

1 версия

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

==Activate first sheet==



<source lang="vb">

Sub GotoFirstSheet()
Dim i&
For i = 1 To Sheets.Count
If Sheets(i).Visible And TypeName(Sheets(i)) <> "Module" Then
Sheets(i).Select
Exit Sub
End If
Next i
End Sub
</source>

==Activate last sheet==



<source lang="vb">

Sub GotoLastSheet()
Dim i&
For i = Sheets.Count To 1 Step -1
If Sheets(i).Visible And TypeName(Sheets(i)) <> "Module" Then
Sheets(i).Select
Exit Sub
End If
Next i
End Sub
</source>

==changes the value of the Name property of the first worksheet in the first workbook of the Excel application:==



<source lang="vb">

Sub changeValue()
Application.Workbooks(1).Worksheets(1).name = "My Sheet"
End Sub
</source>

==Determines if a given worksheet name exists in a workbook==



<source lang="vb">

Private Function WorksheetExists(wb As Workbook, sName As String) _
As Boolean
Dim s As String
On Error GoTo WorksheetExistsErr
s = wb.Worksheets(sName).Name
WorksheetExists = True
Exit Function
WorksheetExistsErr:
WorksheetExists = False
End Function
</source>

==Get Worksheet name==



<source lang="vb">

Sub getWorkSheetName()
MsgBox ThisWorkbook.Worksheets(1).Name
End Sub
</source>

==Get Worksheets count in a Workbook==



<source lang="vb">

Sub getSheetCount()
MsgBox ThisWorkbook.Worksheets.Count
End Sub
</source>

==Gives indexes of sheets==



<source lang="vb">

Sub WorksheetIndex()
Dim i As Integer
For i = 1 To ThisWorkbook.Worksheets.Count
MsgBox ThisWorkbook.Worksheets(i).Name & " has Index = " _
& ThisWorkbook.Worksheets(i).Index
Next i
End Sub
</source>

==Group sheets together==



<source lang="vb">

Sub Groupsheets()
Dim stNames(1 To 2) As String
Dim i As Integer
stNames(1) = "Sheet2"
stNames(2) = "Sheet3"
Worksheets(stNames(1)).Select
For i = 1 To 2
Worksheets(stNames(i)).Select Replace:=False
Next i
End Sub
</source>

==Inserts a new sheet after each sheet==



<source lang="vb">

Sub InsertChartsAfterWorksheets()
Dim myWorksheet As Worksheet
Dim myChart As Chart

For Each myWorksheet In Worksheets
Set myChart = Charts.Add
myChart.Move After:=myWorksheet
Next myWorksheet
End Sub
</source>

==Loop through all worksheets in workbook and reset values in a specific range on each sheet.==



<source lang="vb">

Sub Reset_Values_All_WSheets()
Dim myWorksheet As Worksheet
Dim myRng As Range
Dim cel As Range

For Each myWorksheet In Worksheets
Set myRng = myWorksheet.Range("A1:A5, B6:B10, C1:C5, D4:D10")
For Each cel In myRng
If Not cel.HasFormula And cel.Value <> 0 Then
cel.Value = 0
End If
Next cel
Next myWorksheet
End Sub
</source>

==Reference a worksheet from a workbook==



<source lang="vb">

Sub referenceWorksheet()
Workbooks("MyWorkbook").Worksheets("Sheet2").Select
End Sub
</source>

==Reference worksheet across worksheet==



<source lang="vb">

Sub ReferAcrossWorksheets4()
With Sheets("Sheet1")
.Range(.Cells(1, 1), .Cells(10, 5)).Font.Bold = True
End With
End Sub
</source>

==Safely Deleting Worksheets Using the DeleteSheet Function==



<source lang="vb">

Function DeleteSheet(ws As Worksheet, bQuiet As Boolean) As Boolean
Dim bDeleted As Boolean
On Error GoTo ErrHandler
bDeleted = False
If CountVisibleSheets(ws.Parent) > 1 Then
If bQuiet Then Application.DisplayAlerts = False
bDeleted = ws.Parent.Worksheets(ws.Name).Delete
End If
ExitPoint:
Application.DisplayAlerts = True
DeleteSheet = bDeleted
Exit Function
ErrHandler:
bDeleted = False
Resume ExitPoint
End Function
Function CountVisibleSheets(wb As Workbook) As Integer
Dim nSheetIndex As Integer
Dim nCount As Integer
nCount = 0
For nSheetIndex = 1 To wb.Sheets.Count
If wb.Sheets(nSheetIndex).Visible = xlSheetVisible Then
nCount = nCount + 1
End If
Next
CountVisibleSheets = nCount
End Function
</source>

==Select Entire Sheet==



<source lang="vb">

Sub SelectEntireSheet()
Cells.Select
End Sub
</source>

==Using a Function to Check for the Existence of a Code Name==



<source lang="vb">

Function WorksheetCodeNameExists(wb As Workbook, sCodeName As String) As Boolean
Dim s As String
Dim ws As Worksheet
WorksheetCodeNameExists = False
For Each ws In wb.Worksheets
If StrComp(ws.CodeName, sCodeName, vbTextCompare) = 0 Then
WorksheetCodeNameExists = True
Exit For
End If
Next
Set ws = Nothing
End Function
</source>

==Using the Parent Property to Obtain a Reference to an Object"s Parent Object==



<source lang="vb">

Sub MeetMySingleParent()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
" Please meet my parent - Mrs. Workbook
Debug.Print ws.Parent.Name
Set ws = Nothing
End Sub
</source>

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

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

в 16:33, 26 мая 2010

Admin: 1 версия