VB Editor complains of undefined variable for VBE

[Guest]

I have a project that uses an add in to deliver most of the functionality.
This included a proprietary save in which I store data on the sheet for the
next time.
A request was made to be able to "minimise" the form to look at the sheet. I
put this in but this then exposed a method by which the user can save using
excels save not using the forms methods that store vital data before the user
exits.

To catch this I need to put a workbook_beforesave() event in to the
worksheet itself to call a function in the addin to do the stores as if the
user had used the form save.


I have used code from http://www.cpearson.com/excel/vbe.htm but when I
compile/run I get variable not defined errors on the "vbext_pk_Proc"

Function ProcedureExists(ProcedureName As String, ModuleName As String) As
Boolean
On Error Resume Next
If ModuleExists(ModuleName) = True Then
ProcedureExists = ThisWorkbook.VBProject.VBComponents(ModuleName) _
.CodeModule.ProcStartLine(ProcedureName, vbext_pk_Proc) <> 0
End If
End Function

I tried Dim vbext_pk_Proc as ProcKind, but then I just got a user defined
type error

Looking in my object browser I don't seem to have the vbext_<> set of
declarations but I can't figure how to reference it.

Help!!!

 

[Guest]

Have you referenced The VBA Extensibility Tools as described in the article.

in the VBE Tools -> References -> Microsoft VB... (depending on you version)

HTH

 

[Tom Ogilvy]

Sounds like you need to instantiate application level events in your addin.

for documentation and some explanation on Application Level Events, you
might want to read Chip Pearson's site
http://www.cpearson.com/excel/appevent.htm

this reflects the method presented in VBA help, but see this simpler, more
logical method suggested by KeepitCool and Further endorsed by Jamie Collins
and which uses only the Thisworkbook class module (eliminating the need to
instantiate the class and which keeps everything in one place)

[KeepitCool, otherwise known as Jurgen Volkerink @ www.XLsupport.com ]

Some discussion:
http://groups.google.co.uk/[email protected]
the thread:
http://groups.google.co.uk/groups?threadm=xn0e0z6o6oic8600akeepitcoolnl@msnews.microsoft.com

Source of article below:
http://groups.google.co.uk/[email protected]

From: keepITcool ([email protected])
Subject: Re: Using Events with the Application Object & XL 2003


View this article only
Newsgroups: microsoft.public.excel.programming
Date: 2005-01-23 18:47:05 PST



What Chip doesn't mention and what I find a lot easier...

you dont need a separate class module..
Thisworkbook IS a class module so you can keep all your code
(and the withevents application variable) there.

'thisworkbook code module...
Option Explicit

Private WithEvents XlsEvents As Application

Private Sub Workbook_Open()
Set XlsEvents = Application
End Sub

Private Sub XlsEvents_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "You just opened " & Wb.Name
End Sub

 

[Guest]

Thanks Jim,

When I checked the references I had selected the wrong reference, now that
I've added in the extensibility reference it compiles.

Thanks for all the help and suggestions from Tom as well