Hi

I have written a class module but when I try to load more than one
variable Excel gives me a type 28 error, Google says that it is a
memory issue but if it won't even load two strings there must be an
error with my code, can someone point out where I have made the
mistake please?

My class module is:


Code:
--------------------
Private pEmpID As String
Private pStartDate As Date
Private pEndDate As Date
Private pStatus As String
Private pHolType As String
Private pShift As String

Public Property Let strEmpID(EmpID As String)
pEmpID = EmpID
End Property

Public Property Get strEmpID() As String
strEmpID = pEmpID
End Property

Public Property Let strStartDate(StartDate As Date)
strStartDate = StartDate
End Property

Public Property Get strStartDate() As Date
pStartDate = strStartDate
End Property

Public Property Let strEndDate(EndDate As Date)
strEndDate = EndDate
End Property

Public Property Get strEndDate() As Date
pEndDate = strEndDate
End Property

Public Property Let strStatus(Status As String)
strStatus = Status
End Property

Public Property Get strStatus() As String
pStatus = strStatus
End Property

Public Property Let strHolType(HolType As String)
strHolType = HolType
End Property

Public Property Get strHolType() As String
pHolType = strHolType
End Property

Public Property Let strShift(Shift As String)
strShift = Shift
End Property

Public Property Get strShift() As String
pShift = strShift
End Property
--------------------


And my Sub in a normal module is

Code:
--------------------
Sub Refusetest()
Dim iHol As clsHoliday
Set iHol = New clsHoliday
'With Sheets("MyTime")
iHol.strEmpID = "EmpID1"
iHol.strShift = "Shift1"
'iHol.strStartDate = #1/21/2015#
'iHol.strEndDate = #1/28/2015#
'iHol.strHolType = "Type1"
'End With
MsgBox iHol.strEmpID
End Sub
--------------------

I don't see where you destroy each instance of your class object that
this code creates. You new to clear its space in memory...

Sub Refusetest()
Dim iHol As clsHoliday
Set iHol = New clsHoliday

On Error GoTo Cleanup
'With Sheets("MyTime")
iHol.strEmpID = "EmpID1"
iHol.strShift = "Shift1"
'iHol.strStartDate = #1/21/2015#
'iHol.strEndDate = #1/28/2015#
'iHol.strHolType = "Type1"
'End With
MsgBox iHol.strEmpID

Cleanup:
Set iHol = Nothing
End Sub

...so you don't consume multiple blocks of memory. Optionally, you could
create a global instance at project startup and destroy it at
shutdown...

In a standard module named "m_OpenClose", declare all your global
variables:
Option Explicit

Public oHol As clsHoliday
'*Note data type prefix change from integer to object*

Sub Auto_Open()
InitGlobals
'//other startup code...
End Sub 'Auto_Open

Sub Auto_Close()
Set oHol = Nothing
End Sub 'Auto_Close

Sub InitGlobal()
' Initializes all global variables

Set oHol = New clsHoliday
'//init other public variables as required
End Sub 'InitGlobals

...then modify your procedure as follows...


Sub Setup_HolProps()
' Sets oHol property values
' I'd rewrite this to use values from a worksheet instead of
' hard-coding them here, so this procedure is reusable.

With oHol
.strEmpID = Range("EmpID").Value: .strShift = Range("Shift").Value
'oHol.strStartDate = Range("StartDate").Value
'.strEndDate = Range("EndDate").Value
'.strHolType = Range("HolType").Value
MsgBox .strEmpID
End With 'oHol
End Sub

...where the refs are to local scope (sheet-level) defined name ranges.
This will allow you to edit the worksheet, then process the changes.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion