Object Variables || Excel VBA Master Class || 3.3

Note: This webpage is designed to accompany the Youtube video posted above. The video offers detailed explanation on all topics; while this webpage will serve as a repository for the code presented in the video. Source code will be presented in the order of the sections within the video.

Data Types

Sub PrintCellValue_String()
    Dim strCustomerId As String
    strCustomerId = ThisWorkbook.Sheets("Sheet1").Range("B2").Value
    Debug.Print strCustomerId
End Sub

Sub PrintCellValue_Range()
    Dim rngCustomerId As Range
    Set rngCustomerId = ThisWorkbook.Sheets("Sheet1").Range("B2")
    Debug.Print rngCustomerId
End Sub

Sub PrintCellValue_Range_String()
    Dim strCustomerId As String
    Dim rngCustomerId As Range
    Set rngCustomerId = ThisWorkbook.Sheets("Sheet1").Range("B2")
    strCustomerId = rngCustomerId.Value
    Debug.Print strCustomerId
End Sub

Creating Object Variables

Declaration

Assignment

Sub RegisterMarks()
    Dim wsInput As Worksheet, wsOutput As Worksheet
    Set wsInput = ThisWorkbook.Sheets("Input")
    Set wsOutput = ThisWorkbook.Sheets("Output")
    wsOutput.Rows("2:" & wsOutput.Rows.Count).ClearContents
    With wsOutput
        .Range("A2").Value = wsInput.Range("D2").Value
        .Range("B2").Value = wsInput.Range("D3").Value
        .Range("C2").Value = wsInput.Range("D4").Value
        .Range("D2").Value = wsInput.Range("D5").Value
        .Range("E2").Value = wsInput.Range("A8").Value
        .Range("F2").Value = wsInput.Range("B8").Value
        .Range("G2").Value = wsInput.Range("C8").Value
        .Range("H2").Value = wsInput.Range("D8").Value
    End With
End Sub

Referencing Worksheets

Sub RegisterMarks_NewWorksheet()
    Dim wsInput As Worksheet
    Set wsInput = ThisWorkbook.Sheets("Input")
    Dim wsOutput As Worksheet
    Set wsOutput = ThisWorkbook.Worksheets.Add
    wsOutput.Name = "New Output"
    With wsOutput
        .Range("A1").Value = wsInput.Range("D2").Value
        .Range("B1").Value = wsInput.Range("D3").Value
        .Range("C1").Value = wsInput.Range("D4").Value
        .Range("D1").Value = wsInput.Range("D5").Value
        .Range("E1").Value = wsInput.Range("A8").Value
        .Range("F1").Value = wsInput.Range("B8").Value
        .Range("G1").Value = wsInput.Range("C8").Value
        .Range("H1").Value = wsInput.Range("D8").Value
    End With
End Sub

Referencing Workbooks

Sub RegisterMarks_NewWorkbook()

    Dim wsInput As Worksheet
    Set wsInput = ThisWorkbook.Sheets("Input")
    Dim wb As Workbook
    Set wb = Workbooks.Add
    With wb.Sheets("Sheet1")
        .Range("A1").Value = wsInput.Range("D2").Value
        .Range("B1").Value = wsInput.Range("D3").Value
        .Range("C1").Value = wsInput.Range("D4").Value
        .Range("D1").Value = wsInput.Range("D5").Value
        .Range("E1").Value = wsInput.Range("A8").Value
        .Range("F1").Value = wsInput.Range("B8").Value
        .Range("G1").Value = wsInput.Range("C8").Value
        .Range("H1").Value = wsInput.Range("D8").Value
    End With
    wb.SaveAs "C:\Youtube\Current\VBA_Masterclass\Demo\Marks.xlsx"
    wb.Close SaveChanges:=False
     
End Sub
Sub RegisterMarks_OpenWorkbook()
    Dim wsInput As Worksheet
    Set wsInput = ThisWorkbook.Sheets("Input")
    Dim wb As Workbook
    Set wb = Workbooks.Open("C:\Youtube\Current\VBA_Masterclass\Demo\Marks.xlsx")
    With wb.Sheets("Sheet1")
        .Cells.Clear
        .Range("A1").Value = wsInput.Range("D2").Value
        .Range("B1").Value = wsInput.Range("D3").Value
        .Range("C1").Value = wsInput.Range("D4").Value
        .Range("D1").Value = wsInput.Range("D5").Value
        .Range("E1").Value = wsInput.Range("A8").Value
        .Range("F1").Value = wsInput.Range("B8").Value
        .Range("G1").Value = wsInput.Range("C8").Value
        .Range("H1").Value = wsInput.Range("D8").Value
    End With
    wb.Save
    wb.Close SaveChanges:=False
    
End Sub

Referencing New Objects

Sub GroceryCollection()
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Grocery List")
    
    Dim collGrocery As Collection
    Set collGrocery = New Collection
    
    collGrocery.Add ws.Range("A1").Value
    collGrocery.Add ws.Range("A2").Value
    collGrocery.Add ws.Range("A3").Value
    
End Sub

Using Dim and New

Sub GroceryCollection()
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Grocery List")
    
'    Dim collGrocery As Collection
'    Set collGrocery = New Collection
    Dim collGrocery As New Collection
    
    collGrocery.Add ws.Range("A1").Value
    collGrocery.Add ws.Range("A2").Value
    collGrocery.Add ws.Range("A3").Value
    
End Sub

All About Nothing

Const Folder_Path As String = "C:\Youtube\Current\VBA_Masterclass\Demo\Dict Example\"
Sub Populate_All_Marks()
    Dim strFile As String
    strFile = Dir(Folder_Path & "*.xlsx")
    Dim wsAllMarks As Worksheet
    Set wsAllMarks = ThisWorkbook.Sheets("All_Marks")
    wsAllMarks.Rows("2:" & wsAllMarks.Rows.Count).ClearContents
    'Declare Variables as Workbook and Dictionary Objects
    Dim wb As Workbook
    Dim dict As Scripting.Dictionary
    Dim strFirstName As String, strLastName As String
    Dim lrow
    Do While strFile <> ""
        'Assign the Workbook Object by opening the file
        Set wb = Workbooks.Open(Folder_Path & strFile)
        'Create a new instance of a dictionary
        Set dict = New Scripting.Dictionary
        strFirstName = wb.Sheets(1).Range("D2").Value
        strLastName = wb.Sheets(1).Range("D3").Value
        dict.Add "Name", strFirstName & " " & strLastName
        dict.Add "Total Marks", Application.WorksheetFunction.Sum(wb.Sheets(1).Range("A8:D8"))
        lrow = wsAllMarks.Range("A1").CurrentRegion.Rows.Count + 1
        wsAllMarks.Range("A" & lrow).Value = dict("Name")
        wsAllMarks.Range("B" & lrow).Value = dict("Total Marks")
        wb.Close SaveChanges:=False
        strFile = Dir
        'Release the Workbook and Dictionary Object Variables
        Set wb = Nothing
        Set dict = Nothing
    Loop
End Sub

Sub CheckIfNothing()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Grocery List")
   
    'Dim collGrocery As Collection
    Dim collGrocery As New Collection
    
    If ws.Range("A1").Value <> "" Then
        'Set collGrocery = New Collection
        collGrocery.Add ws.Range("A1").Value
        collGrocery.Add ws.Range("A2").Value
        collGrocery.Add ws.Range("A3").Value
    End If
       
    If Not collGrocery Is Nothing Then
        MsgBox "Your grocery list has been filled."
    End If
    
End Sub

Related Posts