VBA User-Defined Type Error when I define any Type

Question

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!

It's been a while since I worked with VBA so just guessing here: have you tried making the UDT Public? msdn.microsoft.com/en-us/library/aa266315%28v=vs.60%29.aspx — xxbbcc
– xxbbcc, Commented Nov 16, 2015 at 20:59
the Type needs to go in a module to be globally accessible with the Public keyword. Remove the Public keyword from the variables in your type as well — Sorceri
– Sorceri, Commented Nov 16, 2015 at 21:05
I actually have tried making the UDT Public and it still doesn't work, but my understanding is that the default is Public anyway? — MeMyselfandI
– MeMyselfandI, Commented Nov 17, 2015 at 0:30
Thanks for the update, it saved my bacon! It doesn't mention anywhere in the docs that the position matters — berkeleybross
– berkeleybross, Commented Aug 20, 2021 at 20:31

SierraOscar · Accepted Answer · 2015-11-16 21:16:49Z

2

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.

edited Nov 16, 2015 at 21:16

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

answered Nov 16, 2015 at 21:10

user5412293

Sign up to request clarification or add additional context in comments.

4 Comments

SierraOscar Over a year ago

Your answer is correct so I gave it an upvote, although for such a simple UDT I wouldn't bother creating a class for it. If it were more complex and it needed validation/events/custom methods, then absolutely create a class.

user5412293 Over a year ago

Thanks for the edit. Yes for something like this the UDF should be enough; but I don't know why I have the hunch that the user will want to create instances in the future :)

MeMyselfandI Over a year ago

You are probably right. I tend to expand. I'm going to try to lightweight for now. Thanks for the answer!

user5412293 Over a year ago

I am usually the same way. Also the class will bring something very valuable "Validation" a few checks come to my mind: IsDataComplete(); IsValidDate(); ...

SierraOscar · Accepted Answer · 2015-11-16 21:07:13Z

0

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

answered Nov 16, 2015 at 21:07

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

Collectives™ on Stack Overflow

VBA User-Defined Type Error when I define any Type

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related