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:

And my Sub in a normal module is

Hi David,

I do not have excel at home (coz i am using iMac at home:). therefore could not go through all. But seems like you have a 0 division error.
Can you check whole the variables again, one of them should appear 0.
Cheers
Baha

How do I check them? When I run the sub, iHol.strShift = "" but that is the line it crashes on. The macro is only there to test that the class works, nothing is done to them apart from opening a message box.

Hi David,

I do not have excel at home (coz i am using iMac at home:). therefore
could not go through all. But seems like you have a 0 division error.
Can you check whole the variables again, one of them should appear
0. Cheers Baha

I assume *what you really mean is* - you haven't installed a Mac
version of MS Office on your iMac machine!<g

--
Garry

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

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

Typo...

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

--
Garry

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

[quote]
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. [quote]

Thank you, but both of those still have the same issue at the same point. Have I made a mistake in declaring the class?

In your class change

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

to

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

Regards,
Peter T



"davidman" wrote in message
...

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
--------------------




--
davidman

I knew there was something wrong with my code! Thanks.