Excel VBA Basics #16B ERRORS - Continue your macro Even with Errors Using On Error Resume Next

Continuing our lessons on ERROR HANDLING, we show how to continue the code, even after an error occurs. Excel can silently ignore any and all errors if you want it to using "On Error Resume Next". This is a two-edged sword, so you gotta know when and how to use it correctly, but very powerful, can be a real time saver.

Fantastic Developer Tools:

[ExcelVbaIsFun]

Merry Christmas everyone. I hope God blesses you richly this upcoming year. Remember, you ARE a success!! Believe it, be it! Blessings my friends!! Dan

[monamourjeanne]

excellent one
debugging is not often taught in other tuts
and the concluding part was great too!

[JanBolhuis]

Hé Daniel,

Thanks for all the good work.

I've learned a lot the last few days.

[codalisa]

Very intuitive, but can I ask "Why were square brackets used around table2?" Is it some sort of rule?

[johnbender]

Cool, I was looking for how to turn off the On Error Resume.
I have an instance that for some reason the first time I run the line of code it gives an error saying it wasn't found, but if I immediately run it again it works. So short term I just have the line twice and On Error Resume, but then further down past that, it leads to bad things having the On Error Resume still going.

[kartickchakraborty]

Sir, would you kindly tell me where my error lies in this Code? VBA shows me an error "Object doesn't support this property or method" at this line "For Each SingleFilm In MyFilms" in the Standard Module (Named - TypedCollection). I am unable to understand where my mistake lies? Earlier, When I tested the same code (almost) with Untyped Collections, VBA ran my code without an error. But, as I went from Untyped Collections to Typed Collections, VBA threw me an error. Please tell me how to get around this? Sir, just a few minutes ago, I came to know, when I use For...Next Loop, it gives me correct answer. But, I don't know, what's going on incase of For Each Loop?

Class Modules (Named - Film):


Option Explicit

Private Film_ID As Byte
Private Film_Title As String
Private Film_ReleasedDate As Date
Private Film_Length As Byte
Private Film_Genre As Genres

Public Enum Genres
Action = 1
Adventure
Animation
Biography
Comedy
Crime
Drama
Fantasy
Historic
Horror
Mystery
Romance
SciFi
Thriller
Western
End Enum

Private Sub Class_Initialize()
ID = 0
Title = "Not Set"
ReleasedDate = Date
Length = 0
Genre = -1
End Sub

Public Property Let ID(Value As Byte)
Film_ID = Value
End Property

Public Property Get ID() As Byte
ID = Film_ID
End Property

Public Property Let Title(Value As String)
Film_Title = Value
End Property

Public Property Get Title() As String
Title = Film_Title
End Property

Public Property Let ReleasedDate(Value As Date)
Film_ReleasedDate = Value
End Property

Public Property Get ReleasedDate() As Date
ReleasedDate = Film_ReleasedDate
End Property

Public Property Let Length(Value As Byte)
Film_Length = Value
End Property

Public Property Get Length() As Byte
Length = Film_Length
End Property

Public Property Let Genre(Value As Genres)
Film_Genre = Value
End Property

Public Property Get Genre() As Genres
Genre = Film_Genre
End Property

Public Property Let GenreText(Value As String)
Select Case Value
Case "Action"
Film_Genre = Genres.Action 'Instead of writing the Genre explicitly (Genres.Action), you can refer them by their Index Number.
Case "Adventure"
Film_Genre = Genres.Adventure
Case "Animation"
Film_Genre = Genres.Animation
Case "Biography"
Film_Genre = Genres.Biography
Case "Comedy"
Film_Genre = Genres.Comedy
Case "Crime"
Film_Genre = Genres.Crime
Case "Drama"
Film_Genre = Genres.Drama
Case "Fantasy"
Film_Genre = Genres.Fantasy
Case "Historic"
Film_Genre = Genres.Historic
Case "Horror"
Film_Genre = Genres.Horror
Case "Mystery"
Film_Genre = Genres.Mystery
Case "Romance"
Film_Genre = Genres.Romance
Case "Sci-Fi"
Film_Genre = Genres.SciFi
Case "Thriller"
Film_Genre = Genres.Thriller
Case "Western"
Film_Genre = Genres.Western
Case Else
Film_Genre = -1 'Incase of Invalid Genre
End Select
End Property

Public Property Get GenreText() As String
Select Case Film_Genre
Case Genres.Action
GenreText = "Action"
Case Genres.Adventure
GenreText = "Adventure"
Case Genres.Animation
GenreText = "Animation"
Case Genres.Biography
GenreText = "Biography"
Case Genres.Comedy
GenreText = "Comedy"
Case Genres.Crime
GenreText = "Crime"
Case Genres.Drama
GenreText = "Drama"
Case Genres.Fantasy
GenreText = "Fantasy"
Case Genres.Historic
GenreText = "Historic"
Case Genres.Horror
GenreText = "Horror"
Case Genres.Mystery
GenreText = "Mystery"
Case Genres.Romance
GenreText = "Romance"
Case Genres.SciFi
GenreText = "Sci-Fi"
Case Genres.Thriller
GenreText = "Thriller"
Case Genres.Western
GenreText = "Western"
Case Else
GenreText = "Invalid Genre"
End Select
End Property

Public Sub ShowMessage()
Debug.Print "Show Message: This is a Method of Film Class"
End Sub

Class Modules (Named - Films):


Option Explicit

Private MultipleFilms As Collection

Private Sub Class_Initialize()
Set MultipleFilms = New Collection
End Sub

Public Sub Add(Item As Film, Optional Key As String)
If Key = Empty Then
MultipleFilms.Add Item:=Item
Else
MultipleFilms.Add Item:=Item, Key:=Key
End If
End Sub

Public Function GetFilm(IndexOrKey As Variant) As Film
Set GetFilm =
End Function

Public Property Get Count() As Long
Count = MultipleFilms.Count
End Property

Public Sub Remove(IndexOrKey As Variant)
MultipleFilms.Remove Index:=IndexOrKey
End Sub

Standard Modules (Named - TypedCollections):


Option Explicit

Sub CreateTypedCollection()
Dim MyFilms As Films
Set MyFilms = New Films

Dim LastRow As Long
LastRow = - 1

Dim MyRange As Range
Set MyRange = Sheet1.Range("A3").Offset(RowOffset:=1, ColumnOffset:=0).Resize(RowSize:=LastRow, ColumnSize:=1)

Dim Cell As Range

For Each Cell In MyRange
Dim SingleFilm As Film
Set SingleFilm = New Film

With SingleFilm
.ID = Cell.Offset(RowOffset:=0, ColumnOffset:=0).Value
.Title = Cell.Offset(RowOffset:=0, ColumnOffset:=1).Value
.ReleasedDate = Cell.Offset(RowOffset:=0, ColumnOffset:=2).Value
.Length = Cell.Offset(RowOffset:=0, ColumnOffset:=3).Value
.GenreText = Cell.Offset(RowOffset:=0, ColumnOffset:=4).Value
End With

MyFilms.Add Item:=SingleFilm, 'Key Property in Collection can only hold String Data Type

Set SingleFilm = Nothing 'This Statement is Optional. However, it is a good practice to destroy the Object after use.
Next Cell

Debug.Print "There are " & MyFilms.Count & " Films in the Collection."

Dim ButtonClicked As VBA.VbMsgBoxResult
ButtonClicked = Interaction.MsgBox _
(Prompt:="Do you want to see the name of the Films stored in Collection named MyFilms?", _
Buttons:=vbYesNo + vbQuestion, _
Title:="Confirmation")

If ButtonClicked = vbYes Then
Debug.Print

Dim Counter As Long
Counter = 1

For Each SingleFilm In MyFilms
Debug.Print Counter & vbTab & "|" & vbTab & SingleFilm.Title & vbTab & "|" & vbTab & SingleFilm.GenreText

Counter = Counter + 1
Next SingleFilm
Else
Rem Nothing is needed to do
End If
End Sub