CODEWITHSUNDEEP
vbaexcel
MeMyselfandI
MeMyselfandI

Reputation: 11

VBA User-Defined Type Error when I define any Type

I'm new to VBA and I'm really ripping my hair out over this one. I'm working on an Excel Macro and any time I try to create a Type, I am unable to access it from within a function. I've copied and pasted similar examples from elsewhere online and get the same error (User-defined type not defined). I see that others have had similar errors, but none of the solutions seem to apply to this particular problem. Here is the (simplified) code:

Type ScheduleDate
    Public Resident As String
    Public SDate As Date
    Public Activity As String
End Type

Sub Generate_Schedule()
    Dim Sched As ScheduleDate
End Sub

Thanks in advance!

MTA: I figured this out! When I copied and pasted this here, I left out a function I had put above the Type. Apparently, VBA does not allow functions before Types. Always a risk of simplification, I suppose. Thanks for the help!

Upvotes: 1

Views: 2193

Answers (2)

user5412293
user5412293

Reputation:

Hi try something like this:

Option Explicit

Public Type ScheduleDate
     Resident As String
     SDate As Date
     Activity As String
End Type

Sub Generate_Schedule()

    Dim Sched As ScheduleDate

    Sched.Resident = "The resident"
    Sched.SDate = #11/16/2015#
    Sched.Activity = "My activity"

End Sub

Also if you are trying to group variables, I would suggest you to create a class. Every time I use a type I end up changing it to a class. See bellow an example of the class.

In the regular module:

Option Explicit

Sub Generate_Schedule()

    Dim Sched As New clsSchedule

    Sched.Resident = "The resident"
    Sched.ScheduleDate = #11/16/2015#
    Sched.Activity = "My activity"

End Sub

And now in a class module named "clsSchedule" :

Option Explicit

Private m_strResident As String
Private m_dtmScheduleDate As Date
Private m_strActivity As String

' -------------------------------
' Property Resident
' -------------------------------
Public Property Get Resident() As String
    Resident = m_strResident
End Property
Public Property Let Resident(ByVal strResident As String)
    m_strResident = strResident
End Property


' -------------------------------
' Property ScheduleDate
' -------------------------------
Public Property Get ScheduleDate() As Date
    ScheduleDate = m_dtmScheduleDate
End Property
Public Property Let ScheduleDate(ByVal dtmScheduleDate As Date)
    m_dtmScheduleDate = dtmScheduleDate
End Property


' -------------------------------
' Property Activity
' -------------------------------
Public Property Get Activity() As String
    Activity = m_strActivity
End Property
Public Property Let Activity(ByVal strActivity As String)
    m_strActivity = strActivity
End Property

I know it seems like more code but its all for good. Hope this helps.

Upvotes: 2

SierraOscar
SierraOscar

Reputation: 17647

Declare the scope for the type as a whole, not the individual properties:

Public Type ScheduleDate
    Resident As String
    SDate As Date
    Activity As String
End Type

Sub Generate_Schedule()
    Dim Sched As ScheduleDate
End Sub

Upvotes: 0

Related Questions