VBA Class: Object Required 424

Question

This question is maybe more about flow control within a class and how to use class subs or defining the scope of the variables.

This class is intended to Take a predefined range as an input (Source range). There are various flags within this range that I will search for and use the class properties to call these values out as needed.

I'm not sure how or where I should call the Inner Class Sub (or if it is a class method) in order to harvest out that specific area within the range which satisfies the flag.

If you look at the code, I've been able to successful SET the pSourceRange in the code and Have used the Property Get Title (which returns Null) but I'm not sure how I should call the sub within the class to fish out that title from the range. Should this class be public or private? Is it a method? should it be ran after Range SET or Title GET statement?

I've tried pointing out the options I've tried (which are now commented out with error codes.

''''''''''''''''''''''Module Code''''''''''''''''''''''''''
Public Sub AA_StandardCode()
  dim testrange as range

  'test range does already exist as range
  Set TestRange = Range("A1:b100")

  Dim HarvestClass As New HarvestCode
  Set HarvestClass.SourceRange = testrange

  'Error Runtime 424: object required
  'HarvestClass.GrabStaticOuterCode(TestRange)

  MsgBox HarvestClass.Title
  ' a Null value is returned, implying nothing was entered  
End Sub

''''''''''''''''''''''Class Module Code''''''''''''''''''''
Option Explicit

'Static Contents
Private Const cTitle As String = "1111111,"

Private pSourceRange As Range
Private pTitle As String

Public Property Set SourceRange(Value As Range)
  Set pSourceRange = Value

  'Error Runtime 424: object required
  'GrabStaticOuterCode(pSourceRange)
End Property

Public Property Get Title() As String
  'Error Runtime 424: object required
  'GrabStaticOuterCode(pSourceRange)

  Title = pTitle
End Property

'''''''''''''''

Public Sub GrabStaticOuterCode(pSourceRange As Range)
  Dim rng As Range
  Dim strTest As String

  For Each rng In pSourceRange
    strTest = Mid(rng, 1, 4)

    Select Case strTest
      Case cTitle
        pTitle = Mid(strTest, 5, Len(strTest))
      Case Else
    End Select
  Next rng

End Sub

Here are some code fixes to get rid of runtime errors: Use `Set` before `TestRange = Range("A1:b100")`. Then in three spots remove the parentheses, e.g., `HarvestClass.GrabStaticOuterCode(TestRange)` becomes `HarvestClass.GrabStaticOuterCode TestRange`. — Doug Glancy, Sep 11 '16 at 15:40
I cleaned things up prior to posting but I did have the set before the testrange. (thanks for the check, I'll update it in the provided code. when I remove the parenthesis, i now get the message (invalid use of property). But won't i have to pass the source range as a parameter for the within ()? — The_BMan, Sep 11 '16 at 16:04
Please see my answer below. The code there runs but doesn't return anything. I don't know what your data looks like or what you are expecting to see in the message, but we can discuss that if needed. — Doug Glancy, Sep 11 '16 at 16:20

score 1 · Accepted Answer · answered Sep 11 '16 at 16:18

I'm not sure my comment is clear, so though this might not be a complete answer yet, I'm posting the cleaned-up code that runs. We can follow up from there:

Regular module

Public Sub AA_StandardCode()
Dim testrange As Range
Dim HarvestClass As New HarvestCode

Set testrange = Range("A1:b100")
Set HarvestClass.SourceRange = testrange
HarvestClass.GrabStaticOuterCode testrange
MsgBox HarvestClass.Title
End Sub

Class module

'Static Contents
Private Const cTitle As String = "1111111,"
Private pSourceRange As Range
Private pTitle As String

Public Property Set SourceRange(Value As Range)

Set pSourceRange = Value
GrabStaticOuterCode pSourceRange

End Property

Public Property Get Title() As String

GrabStaticOuterCode pSourceRange
Title = pTitle

End Property

Public Sub GrabStaticOuterCode(pSourceRange As Range)
Dim rng As Range
Dim strTest As String
For Each rng In pSourceRange
    strTest = Mid(rng, 1, 4)
    Select Case strTest
        Case cTitle
        pTitle = Mid(strTest, 5, Len(strTest))
        Case Else
    End Select
Next rng
End Sub

WOW, that worked. Thanks. I guess I am still a little confused why it does work. Q1.) is it correct to say that "GrabStaticOuterCode" is a method? Q2. Why don't I need () when calling the process? Q3. What's best practice for calling this command? should it be within the source Range set, the title Get, or just the module call? Q4. Why didn't it work before ( i know this one is kind of general?) — The_BMan, Sep 11 '16 at 17:07
1) Yes 2) See http://dailydoseofexcel.com/archives/2012/05/01/quick-vba-tip-parentheses/ and http://stackoverflow.com/q/5413765/293078. I'm not totally sure how they apply here, but when I get that error I always try removing the parens 3) I don't know 4) see #2. Since this answer worked for you, please accept it by clicking the checkmark next to it. — Doug Glancy, Sep 11 '16 at 17:41

VBA Class: Object Required 424

1 Answers1