How to use Workbook in VBA class.

[pedie]

Hi 'm wanting to create a class which will be then used from the module to be referred to.
('m doing this because this code will be in add-in and will be using many workbooks. So will need to ensure that master workbook is hook to class workbook.)
I tried creating one class (xLtestCls) and tested as below
Please validate this code for me. I want to ensure that 'm doing the right thing.

Thanks in advance.

[/FONT][FONT=courier new]Option Explicit[/FONT]
[FONT=courier new]Private xlWb As Workbook[/FONT]
[FONT=courier new]Private xLWb_Name As String[/FONT]
[FONT=courier new]Property Let CurrentTemplate(varWb As Workbook)[/FONT]
[FONT=courier new]    Set xlWb = varWb[/FONT]
[FONT=courier new]End Property[/FONT]
[FONT=courier new]Property Get CurrentTemplate() As Workbook[/FONT]
[FONT=courier new]    Set CurrentTemplate = xlWb[/FONT]
[FONT=courier new]End Property

[/FONT][FONT=courier new]Sub test_1()[/FONT]
[FONT=courier new]Dim testCls As xLtestCls[/FONT]
[FONT=courier new]Windows("VBACLass.xlsm").Activate[/FONT]
[FONT=courier new]Set testCls = New xLtestCls[/FONT]
[FONT=courier new]testCls.CurrentTemplate = ActiveWorkbook[/FONT]
[FONT=courier new]
[/FONT]
[FONT=courier new]        Windows("Class_.xlsm").Activate[/FONT]
[FONT=courier new]        Call storeHistory(testCls.CurrentTemplate, ActiveWorkbook.Name & ". xTXT  . " & testCls.WbCode)[/FONT]
[FONT=courier new]            [/FONT]
[FONT=courier new]        Windows("VBACLass.xlsm").Activate[/FONT]
[FONT=courier new]        Call storeHistory(testCls.CurrentTemplate, ActiveWorkbook.Name & ". xTXT  . " & testCls.WbCode)[/FONT]
[FONT=courier new]        [/FONT]
[FONT=courier new]        Windows("Class_.xlsm").Activate[/FONT]
[FONT=courier new]        Call storeHistory(testCls.CurrentTemplate, ActiveWorkbook.Name & ". xTXT  . " & testCls.WbCode)[/FONT]
[FONT=courier new]
[/FONT]
[FONT=courier new]testCls.CurrentTemplate.Activate[/FONT]
[FONT=courier new]testCls.CurrentTemplate = Nothing[/FONT]
[FONT=courier new]
[/FONT]
[FONT=courier new]End Sub[/FONT]
[FONT=courier new]
[/FONT]
[FONT=courier new]

 

[Jerry Sullivan]

Hi Pedie,

Not sure if you were putting all that code in the Class Module, but the code for Sub test_1() should be in a Standard Code module.

Do you have a reason for wanting to create a Class?

Classes are good for modeling things that will have common Properties, Methods and Events. The class helps encapsulate the code to provide control.

It's not clear from your small example whether you would have any benefit from using a Class as the procedure is simply storing the Workbook name and a pointer to the Workbook object.

Classes may be used in Add-Ins, but you don't need to put Add-In code into a Class.

 

[pedie]

Hi Jerry, thanks for quick reply. test_1 is in Module. I'm afraid that since my code will be using 2-4 workbooks for completion of task it might create confusion as to which workbook is what
so thought if I should store it in class and use same master workbook it should work better.
please confirm if whatever i have is created correctly.

Thanks again.
Pedie

 

[Jerry Sullivan]

To your specific question of whether you have created it correctly here are some comments and modifications...

In your class module "xLtestCls"...

Option Explicit

Private xlWb As Workbook
Private xLWb_Name As String 'this currently isn't doing anything

Property Let CurrentTemplate(varWb As Workbook)
    Set xlWb = varWb
End Property

Property Get CurrentTemplate() As Workbook
    Set CurrentTemplate = xlWb
End Property

'--you need to add public property or method for WbCode if you are
'  going to call it from Test_1
Property Get WbCode() As String
    WbCode = "MyCode"
End Property

As a matter of style, many professional programmers use these conventions:
1. Naming class modules beginning with the prefix "C"
2. Name module scope variables with the prefix "m"

In a standard code module...

Sub test_1()

 Dim testCls As xLtestCls

 Set testCls = New xLtestCls
 
 '--activating each workbook instead of just referencing it is inefficient and rarely necessary
 testCls.CurrentTemplate = Workbooks("VBACLass.xlsm")

 '--you haven't posted the storeHistory procedure, but if I understand the parameters you could call like this...
 Call storeHistory(testCls.CurrentTemplate, "Class_.xlsm" & ". xTXT  . " & testCls.WbCode)

 '--the xLtestCls class code you posted doesn't have a public .WbCode property or method
 Call storeHistory(testCls.CurrentTemplate, "VBACLass.xlsm" & ". xTXT  . " & testCls.WbCode)

 Call storeHistory(testCls.CurrentTemplate, "Class_.xlsm" & ". xTXT  . " & testCls.WbCode)

 '--reference the object to remove it from memory instead of it's CurrentTemplate Property
 Set testCls = Nothing

End Sub

You could make all those revisions and it should work, however as previously noted for the task you've described there's no benefit of creating a Class instead of doing something simpler such as ...

Sub test_2()

 Dim wbkMaster as Workbook
 Dim sWbCode as String

 Set wbkMaster = Workbooks("VBACLass.xlsm")
 sWbCode="MyCode" 

 Call storeHistory(wbkMaster , "Class_.xlsm" & ". xTXT  . " &  sWbCode )
 Call storeHistory(wbkMaster , "VBACLass.xlsm" & ". xTXT  . " &  sWbCode ) 
 Call storeHistory(wbkMaster , "Class_.xlsm" & ". xTXT  . " &  sWbCode )

 Set wbkMaster  = Nothing

End Sub

 

[pedie]

Thanks again Jerry. Thank for helping me out and for your advice.
I'll try implementing this code and see if everything works out ok. will check back if anything comes up.

Thanks
Pedie