VBA/Excel/Access/Word/Data Type/Object Variable Set

Compromise with a combination of object variables and With...End With:

 
     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

eliminate the object variable when using With statement

 
     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

Identifying the type of a selected object: uses the TypeName function, which returns the type of the selected object (as a string):

 
Sub ShowSelectionType()
    Dim SelType As String
    SelType = TypeName(Selection)
    MsgBox SelType
End Sub

Object Data Types

 
Sub obj()
    Dim myObject As Range
    Set myObject = Range("A1:B10")
    myObject.Interior.Color = 16711680
End Sub

Object variable

 
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

Object Variables

 
     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

reference can be assigned to an object variable so that you can easily refer to the new object in later code

 
     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

The difference between object variables and regular variables

 
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

The Object Data Type Set

 
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

VBA has the ability to interact with Access to help create and edit objects

 
Sub createMenu()
    Dim myBar As CommandBar
    Set myBar = CommandBars.Add("My Command Bar")
End Sub

VBA knows that anything starting with a period is a property or a method of the object following the With

 
     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

With...End With

 
     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