VBA/Excel/Access/Word/Data Type/Object Variable Set
Содержание
- 1 Compromise with a combination of object variables and With...End With:
- 2 eliminate the object variable when using With statement
- 3 Identifying the type of a selected object: uses the TypeName function, which returns the type of the selected object (as a string):
- 4 Object Data Types
- 5 Object variable
- 6 Object Variables
- 7 reference can be assigned to an object variable so that you can easily refer to the new object in later code
- 8 The difference between object variables and regular variables
- 9 The Object Data Type Set
- 10 VBA has the ability to interact with Access to help create and edit objects
- 11 VBA knows that anything starting with a period is a property or a method of the object following the With
- 12 With...End With
Compromise with a combination of object variables and With...End With:
<source lang="vb"> Sub NewWorkbook() Dim myWorkbook As Workbook, myWorksheet As Worksheet Set myWorkbook = Workbooks.add With myWorkbook Set myWorksheet = .Worksheets.add(After:=.Sheets(.Sheets.Count)) With myWorksheet .name = "January" .range("A1").value = "Sales Data" End With .SaveAs FileName:="YourExcel2007File.xlsx" End With End Sub </source>
eliminate the object variable when using With statement
<source lang="vb"> Sub NewWorkbook() With Workbooks.Add With .Worksheets.Add(After:=.Sheets(.Sheets.Count)) .Name = "January" .Range("A1").Value = "Sales Data" End With .SaveAs Filename:="YourExcel2007File.xlsx" End With End Sub </source>
Identifying the type of a selected object: uses the TypeName function, which returns the type of the selected object (as a string):
<source lang="vb">
Sub ShowSelectionType()
Dim SelType As String SelType = TypeName(Selection) MsgBox SelType
End Sub
</source>
Object Data Types
<source lang="vb">
Sub obj()
Dim myObject As Range Set myObject = Range("A1:B10") myObject.Interior.Color = 16711680
End Sub
</source>
Object variable
<source lang="vb">
Sub ObjectVariable()
Dim Rng As Range Set Rng = ThisWorkbook.Worksheets("Sheet1").Range("C10") Rng.Value = InputBox("Enter Sales for January") Rng.Offset(-1, 0).Value = "January Sales"
End Sub
</source>
Object Variables
<source lang="vb"> Sub ObjectVariable() Dim rng As range Set rng = ThisWorkbook.Worksheets("Sheet1").range("C10") rng.value = InputBox("Enter Sales for January") rng.offset(-1, 0).value = "January Sales" End Sub </source>
reference can be assigned to an object variable so that you can easily refer to the new object in later code
<source lang="vb"> Sub NewWorkbook() Dim myWorkbook As Workbook, myWorksheet As Worksheet Set myWorkbook = Workbooks.add Set myWorksheet = myWorkbook.Worksheets.add(After:=myWorkbook.Sheets(myWorkbook.Sheets.Count)) myWorksheet.name = "January" myWorksheet.range("A1").value = "Sales Data" myWorkbook.SaveAs FileName:="YourExcel2007File.xlsx" End Sub </source>
The difference between object variables and regular variables
<source lang="vb">
Sub sSub()
Dim intVar1 As Integer Dim intVar2 As Integer intVar1 = 5 intVar2 = intVar1 intVar1 = 10 Debug.Print intVar1 "Prints 10 Debug.Print intVar2 "Prints 5
End Sub
</source>
The Object Data Type Set
<source lang="vb">
Sub obj()
Dim myObject As Object Set myObject = range("A1:A15") myObject.Font.Bold = True Dim myRange As Excel.range Set myRange = range("A1:A15") myRange.Font.Bold = True
End Sub
</source>
VBA has the ability to interact with Access to help create and edit objects
<source lang="vb">
Sub createMenu()
Dim myBar As CommandBar Set myBar = CommandBars.Add("My Command Bar")
End Sub
</source>
VBA knows that anything starting with a period is a property or a method of the object following the With
<source lang="vb"> Sub NewWorkbook() Dim myWorksheet As Worksheet With Workbooks.Add Set myWorksheet = .Worksheets.Add(After:=.Sheets(.Sheets.Count)) myWorksheet.Name = "January" myWorksheet.Range("A1").Value = "Sales Data" .SaveAs Filename:="YourExcel2007File.xlsx" End With End Sub </source>
With...End With
<source lang="vb"> Sub NewWorkbook() Dim myWorkbook As Workbook, myWorksheet As Worksheet Set myWorkbook = Workbooks.add Set myWorksheet = myWorkbook.Worksheets.add(After:=myWorkbook.Sheets(myWorkbook.Sheets.Count)) myWorksheet.name = "January" myWorksheet.range("A1").value = "Sales Data" myWorkbook.SaveAs FileName:="YourExcel2007File.xlsx" With myWorkbook .Worksheets.add After:=.Sheets(.Sheets.Count) End With End Sub </source>