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

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>