Using Classes In Multiple Projects

[JackL]

Hi

I am trying to replicate the class example on Chip Pearson's site:

http://www.cpearson.com/excel/Classes.aspx

In particular I am interested in having a class that can be accessed by multiple workbooks.

Please can you let me know why this will not compile?

Thanks for your help!

[Andy Pope]

You need to read the instruction carefully.

Back in the project containing the class, create a procedure that will create and return a new instance of the class:

The GetClass function needs to be in a module not the class.

[JackL]

thanks Andy

[JackL]

I have a new problem now that is when I try to initialise the class in a different workbook it will not compile saying user defined type not defined.

Any idea what I have forgotten to do?

Thanks again

[Andy Pope]

Did you include a reference to the project with the class in it?

[JackL]

No - please let me know how to reference it.

Thanks

[Andy Pope]

Tools > References
The project name should be in the list.

[JackL]

Thanks!!!!!!!!!!!

[JackL]

I have a new problem. The module that is using the class saved in another workbook has an object that needs to be available to the class. The project will not compile because the class cannot reference the object error: variable not defined.

The object is set as public so I don't know what else to try. Is it even possible?

Thanks for your help
Jack

[Andy Pope]

What object?

Can you post your files. Both the one holding the class and the one trying to use it.

[JackL]

Please see attached files. It won't compile due to the 'fund' and 'ccy' objects not being available to the class.

Thanks...

[Andy Pope]

You need to pass the information to the class. It can not "see" the variables in the other project.

Add to new Set statements to the class.
Make the assignments in your main project.
Amend code in class to handle local references.

project code

Public fund As Object, ccy As Object
Private rr As Integer
Sub refresh()
Dim t As Long
Dim CCYbatch As JVProject.CJVccy

t = Timer

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    
    With Sheets("Sheet1")
        
        rr = 2
        Do Until rr > 30000
        
            Set fund = .Cells(rr, 12)
            Set ccy = .Cells(rr, 5)
            
            If fund = "" Then
                Exit Do
            End If
            
            Application.StatusBar = "Populating fund: " & fund & " currency: " & ccy
            
            Set CCYbatch = JVProject.GetClass()
            
            Set CCYbatch.fund = fund
            Set CCYbatch.ccy = ccy
            CCYbatch.Purchase_Bonds = 0
            .Cells(rr, 12).Offset(2, -3) = CCYbatch.Purchase_Bonds
            rr = rr + 84
        Loop
    End With
    
    .StatusBar = False
    .Calculation = xlCalculationAutomatic
End With
t = Timer - t
If t < 0 Then t = t + 1440
MsgBox Format("Macro took " & t, "MM:SS") & " seconds", , "Complete!"
End Sub

Class code

Private r As Integer
Public pPurchase_Bonds As Double
Private m_rngFund As Range
Private m_rngCCY As Range

Public Property Set Fund(RHS As Range)
    Set m_rngFund = RHS
End Property
Public Property Set CCY(RHS As Range)
    Set m_rngCCY = RHS
End Property


Public Property Get Purchase_Bonds() As Double

Purchase_Bonds = pPurchase_Bonds

End Property
Public Property Let Purchase_Bonds(Value As Double)
r = 2
With Sheets("PnS_DL")
    Do Until .Cells(r, 52) = m_rngFund
    r = r + 1
    Loop
    Do While .Cells(r, 52) = m_rngFund
        If .Cells(r, 6) = "FIXED INCOME" And .Cells(r, 43) = "B" And .Cells(r, 64) = m_rngCCY Then
            Value = Value + .Cells(r, 70)
        End If
    r = r + 1
    Loop
End With
    pPurchase_Bonds = Value
End Property

You should also add objects to allow referencing to local sheets.
Otherwise writing a class with specific sheet references and hoping the active workbook is relevant sort of defeats the point of using a class across projects. You might as well copy the whole class code over.

[JackL]

Thanks for all your help!