Got a Class, got a wb - now what??

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

In a previous post (here: http://tinyurl.com/26ucf3), Doug Glancy
helped me tremendously. I want to run a series of macros on every new
and opened workbook. Doug gave me instructions on how to set up a
Class module and globally capture the New and Open events.

In the Class module, I have

Public WithEvents App As Application
Private Sub App_WorkbookOpen(ByVal wb As Workbook)
MsgBox "Somewhere, a workbook has opened"
End Sub

so "wb" is the object I want to deal with. That much I have figured
out!

What I don't know is how to use "wb" in another macro to do things to
the workbook. I could just put a whole macro code right there in the
class module. But for some reason I keep thinking that's not how I
should do it. I could be very wrong, but I thought I ought to ask
first. Any help is greatly appreciated.

Ed
 
Private Sub App_WorkbookOpen(ByVal wb As Workbook)
'MsgBox "Somewhere, a workbook has opened"
ProcessWorkbook wb
End Sub

Sub ProcessWorkbook(wb as workbook)
'do stuff with wb
with wb.worksheets(1)
.Range("A1").value="Processed!"
end with
End Sub
 
Hi, Tim. Thanks for the reply.
Private Sub App_WorkbookOpen(ByVal wb As Workbook)
'MsgBox "Somewhere, a workbook has opened"
ProcessWorkbook wb
End Sub
Click to expand...

So I _do_ put the pointer to another sub in the Class module.
Sub ProcessWorkbook(wb as workbook)
'do stuff with wb
with wb.worksheets(1)
.Range("A1").value="Processed!"
end with
End Sub
Click to expand...

And this macro is in a regular module elsewhere?

Ed
 
If the code is specific to your class then it makes sense to leave it in the
class module.
Extracting the workbook-processing from the event handler is beneficial if
you want to call it from another event handler or if you want the user of
the class to be able to call it directly, passing in a workbook reference.

Tim
 
Back
Top