Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Editor complains of undefined variable for VBE
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!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Editor complains of undefined variable for VBE
Have you referenced The VBA Extensibility Tools as described in the article.
in the VBE Tools - References - Microsoft VB... (depending on you version) HTH "Alan Glaister" wrote: 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!!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Editor complains of undefined variable for VBE
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/groups?se....microsoft.com the thread: http://groups.google.co.uk/groups?th....microsoft.com Source of article below: http://groups.google.co.uk/groups?se....microsoft.com From: keepITcool ) Subject: 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 -- Regards, Tom Ogilvy "Alan Glaister" wrote in message ... 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!!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Editor complains of undefined variable for VBE
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 "Jim Thomlinson" wrote: Have you referenced The VBA Extensibility Tools as described in the article. in the VBE Tools - References - Microsoft VB... (depending on you version) HTH "Alan Glaister" wrote: 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!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Functions Undefined | Excel Worksheet Functions | |||
Undefined function error | Excel Discussion (Misc queries) | |||
Excel complains about "C:\documents.xls", etc. when starting | Setting up and Configuration of Excel | |||
Undefined unction when using VLOOKUP | Excel Programming | |||
Variable Undefined When Blank | Excel Programming |