Custom Class In VBA

[Xiazer]

OK first off im used to Classes in C++, I read a few places and I am trying my best to set up a Class in VBA although my syntax may be off or something. Basically Im asking what i am doing wrong in trying to pass a class variable and it doesnt like what I am trying to do.

here is my code for custom class SSched

Option Explicit


Public NumWorkers As Integer
Public DaysOn As Integer
Public Daysoff As Integer
Public MinMan As Integer
Public UserCell As Variant
Public EndWeek As Boolean

Private Sub Class_Initialize()

UserCell = Array("G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "G")

End Sub

now here is the code when I try to call it

Private Sub cmdCalcWD_Click()

Dim SSheet As SSched

SSheet.NumWorkers = Cells(3, "AP").Value - 1
SSheet.DaysOn = Cells(6, "AO").Value
SSheet.Daysoff = Cells(7, "AO").Value

cmdClear_Click
SetDays SSheet.NumWorkers, SSheet.DaysOn, SSheet.Daysoff, 2

End Sub

the error I get when trying to set the vars is

Run-Time error '91':

Object variable or block variable not set

I dunno, am I calling it wrong? Also will I be able to pass the whole variable through functions? Thanks in advance

[Andy Pope]

Hi,

You need to create an instance of the object.

Dim SSheet As SSched

' create an instance of your class object
Set SSheet = New SSched

SSheet.NumWorkers = Cells(3, "AP").Value - 1
SSheet.DaysOn = Cells(6, "AO").Value
SSheet.Daysoff = Cells(7, "AO").Value

Cheers
Andy

Xiazer wrote:
> OK first off im used to Classes in C++, I read a few places and I am
> trying my best to set up a Class in VBA although my syntax may be off
> or something. Basically Im asking what i am doing wrong in trying to
> pass a class variable and it doesnt like what I am trying to do.
>
> here is my code for custom class -SSched
> Option Explicit
>
>
> Public NumWorkers As Integer
> Public DaysOn As Integer
> Public Daysoff As Integer
> Public MinMan As Integer
> Public UserCell As Variant
> Public EndWeek As Boolean
> Private Sub Class_Initialize()
> UserCell = Array("G", "H", "I", "J", "K", "L", "M", "N", "O",
> "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC",
> "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "G")
> End Sub
>
> -
> now here is the code when I try to call it
>
> -Private Sub cmdCalcWD_Click()
> Dim SSheet As SSched
>
> SSheet.NumWorkers = Cells(3, "AP").Value - 1
> SSheet.DaysOn = Cells(6, "AO").Value
> SSheet.Daysoff = Cells(7, "AO").Value
>
> cmdClear_Click
> SetDays SSheet.NumWorkers, SSheet.DaysOn, SSheet.Daysoff, 2
> End Sub-
>
> the error I get when trying to set the vars is
> - RUN-TIME ERROR '91':
>
> OBJECT VARIABLE OR BLOCK VARIABLE NOT SET-
>
> I dunno, am I calling it wrong? Also will I be able to pass the whole
> variable through functions? Thanks in advance
>
>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

[Xiazer]

Thanks, it works like a charm now.