Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Obviate need for reference to Extensibility?
Using Office 2007 and Win XP;
If possible, could someone please modify the following function in such a way that I would no longer have to reference Microsoft Visual Basic Extensibility? (watch for line wrapping) Public Function ProcedureExists(argProcedureName As String, argModuleName As String) As Boolean 'returns true if a procedure already exists: On Error Resume Next ProcedureExists = ActiveWorkbook.VBProject.VBComponents(argModuleNam e).CodeModule.ProcStartLine(argProcedureName, vbext_pk_Proc) < 0 End Function Thanks much in advance for your assistance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Obviate need for reference to Extensibility?
I thought that might be the case, but this forum would be the place to check. Thanks Jim! "Jim Thomlinson" wrote: You can't. You are trying to look into the VBE to analyze code. There is no late binding option for such a thing that I am aware of. -- HTH... Jim Thomlinson "XP" wrote: Using Office 2007 and Win XP; If possible, could someone please modify the following function in such a way that I would no longer have to reference Microsoft Visual Basic Extensibility? (watch for line wrapping) Public Function ProcedureExists(argProcedureName As String, argModuleName As String) As Boolean 'returns true if a procedure already exists: On Error Resume Next ProcedureExists = ActiveWorkbook.VBProject.VBComponents(argModuleNam e).CodeModule.ProcStartLine(argProcedureName, vbext_pk_Proc) < 0 End Function Thanks much in advance for your assistance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Obviate need for reference to Extensibility?
There's no problem at all to use Extensibility with Late Binding.
Simply change any object declarations to 'As Object' and replace any named constants with their intrinsic values, along the lines as I indicated in my other post (which I assume directly answers the original question). Regards, Peter T "XP" wrote in message ... I thought that might be the case, but this forum would be the place to check. Thanks Jim! "Jim Thomlinson" wrote: You can't. You are trying to look into the VBE to analyze code. There is no late binding option for such a thing that I am aware of. -- HTH... Jim Thomlinson "XP" wrote: Using Office 2007 and Win XP; If possible, could someone please modify the following function in such a way that I would no longer have to reference Microsoft Visual Basic Extensibility? (watch for line wrapping) Public Function ProcedureExists(argProcedureName As String, argModuleName As String) As Boolean 'returns true if a procedure already exists: On Error Resume Next ProcedureExists = ActiveWorkbook.VBProject.VBComponents(argModuleNam e).CodeModule.ProcStartLine(argProcedureName, vbext_pk_Proc) < 0 End Function Thanks much in advance for your assistance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Obviate need for reference to Extensibility?
I'll be darned... You are correct. I have always added the extensibility
library. It appears to be working without the reference. When do you need the reference? I have always gone by Chip's site for this kind of thing... Are there any objects, properties or methods that require the reference? http://www.cpearson.com/excel/vbe.aspx -- HTH... Jim Thomlinson "Peter T" wrote: There's no problem at all to use Extensibility with Late Binding. Simply change any object declarations to 'As Object' and replace any named constants with their intrinsic values, along the lines as I indicated in my other post (which I assume directly answers the original question). Regards, Peter T "XP" wrote in message ... I thought that might be the case, but this forum would be the place to check. Thanks Jim! "Jim Thomlinson" wrote: You can't. You are trying to look into the VBE to analyze code. There is no late binding option for such a thing that I am aware of. -- HTH... Jim Thomlinson "XP" wrote: Using Office 2007 and Win XP; If possible, could someone please modify the following function in such a way that I would no longer have to reference Microsoft Visual Basic Extensibility? (watch for line wrapping) Public Function ProcedureExists(argProcedureName As String, argModuleName As String) As Boolean 'returns true if a procedure already exists: On Error Resume Next ProcedureExists = ActiveWorkbook.VBProject.VBComponents(argModuleNam e).CodeModule.ProcStartLine(argProcedureName, vbext_pk_Proc) < 0 End Function Thanks much in advance for your assistance. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Obviate need for reference to Extensibility?
Actually, after testing, I find you are correct Peter. Initially, I thought of using the intrinsic constant in place of the argument, but I didn't know its value and I didn't try it. Thanks. "Peter T" wrote: There's no problem at all to use Extensibility with Late Binding. Simply change any object declarations to 'As Object' and replace any named constants with their intrinsic values, along the lines as I indicated in my other post (which I assume directly answers the original question). Regards, Peter T "XP" wrote in message ... I thought that might be the case, but this forum would be the place to check. Thanks Jim! "Jim Thomlinson" wrote: You can't. You are trying to look into the VBE to analyze code. There is no late binding option for such a thing that I am aware of. -- HTH... Jim Thomlinson "XP" wrote: Using Office 2007 and Win XP; If possible, could someone please modify the following function in such a way that I would no longer have to reference Microsoft Visual Basic Extensibility? (watch for line wrapping) Public Function ProcedureExists(argProcedureName As String, argModuleName As String) As Boolean 'returns true if a procedure already exists: On Error Resume Next ProcedureExists = ActiveWorkbook.VBProject.VBComponents(argModuleNam e).CodeModule.ProcStartLine(argProcedureName, vbext_pk_Proc) < 0 End Function Thanks much in advance for your assistance. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Obviate need for reference to Extensibility?
When? - well you'd want the reference to get the IntelliSense and to help
legibility while developping. But I can't imagine any scenario, properties, methods etc, that would fail without the ref, subject of course having adapted everything to Late Binding in the normal way. I guess Excel 97 is not so relevant now (I still use it) but any file that needs to be distributed to unknown versions would be better off without the ref. The XL97 Extensibility 5.0 ref is totally different. In theory I suppose there shouldn't be any internal differences in the 5.3 library between say XL2000 and XL2003 though of course there are VBA differences. FWIW here's a little routine I keep in my personal to add/remove the Extensibility reference (notice it's late binding !) ' Call Application.Run("Personal.xls!AddExtRef", ThisWorkbook) Sub AddExtRef(wb As Workbook, Optional bRemoveRef As Boolean) Dim bVBA6 As Boolean Dim objRefIDE As Object ' VBIDE.Reference Dim objRefs As Object ' VBIDE.References Const sGUID As String = "{0002E157-0000-0000-C000-000000000046}" #If VBA6 Then bVBA6 = True #End If Set objRefs = wb.VBProject.References On Error Resume Next ' attempt to find the Extensibility ref Set objRefIDE = objRefs("VBIDE") On Error GoTo 0 If Not objRefIDE Is Nothing Then If (bVBA6 < (objRefIDE.Minor = 3)) Or bRemoveRef Then ' incompatible Ext' library for current xl version Or bRemoveRef objRefs.Remove objRefIDE Set objRefIDE = Nothing End If End If If objRefIDE Is Nothing And Not bRemoveRef Then Set objRefIDE = objRefs.AddFromGuid(sGUID, 5, IIf(bVBA6, 3, 0)) End If 'C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB 5.3 xl9+ 'C:\Program Files\Common Files\Microsoft Shared\VBA\Vbeext1.olb v5.0 xl8 End Sub I run the following from the Intermediate window after ensuring the requisite project is active Call Application.Run("Personal.xls!AddExtRef", ThisWorkbook) Regards, Peter T "Jim Thomlinson" wrote in message ... I'll be darned... You are correct. I have always added the extensibility library. It appears to be working without the reference. When do you need the reference? I have always gone by Chip's site for this kind of thing... Are there any objects, properties or methods that require the reference? http://www.cpearson.com/excel/vbe.aspx -- HTH... Jim Thomlinson "Peter T" wrote: There's no problem at all to use Extensibility with Late Binding. Simply change any object declarations to 'As Object' and replace any named constants with their intrinsic values, along the lines as I indicated in my other post (which I assume directly answers the original question). Regards, Peter T "XP" wrote in message ... I thought that might be the case, but this forum would be the place to check. Thanks Jim! "Jim Thomlinson" wrote: You can't. You are trying to look into the VBE to analyze code. There is no late binding option for such a thing that I am aware of. -- HTH... Jim Thomlinson "XP" wrote: Using Office 2007 and Win XP; If possible, could someone please modify the following function in such a way that I would no longer have to reference Microsoft Visual Basic Extensibility? (watch for line wrapping) Public Function ProcedureExists(argProcedureName As String, argModuleName As String) As Boolean 'returns true if a procedure already exists: On Error Resume Next ProcedureExists = ActiveWorkbook.VBProject.VBComponents(argModuleNam e).CodeModule.ProcStartLine(argProcedureName, vbext_pk_Proc) < 0 End Function Thanks much in advance for your assistance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extensibility Library | Excel Programming | |||
Lost VBA extensibility library reference | Excel Programming | |||
VBA extensibility Library | Excel Programming | |||
VBA Extensibility | Excel Programming | |||
VBA Extensibility problem | Excel Programming |