Obviate need for reference to Extensibility?

[XP]

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(argModuleName).CodeModule.ProcStartLine(argProcedureName, vbext_pk_Proc) <> 0
End Function

Thanks much in advance for your assistance.

 

[Peter T]

Change vbext_pk_Proc to 0

Regards,
Peter T

 

[Jim Thomlinson]

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.

 

[XP]

I thought that might be the case, but this forum would be the place to check.
Thanks Jim!

 

[Peter T]

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

 

[Jim Thomlinson]

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

 

[XP]

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]

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