Trust access to the VBA project: Excel bug?, workaround required.

[jh]

I'm fairly sure this is a VBA bug (comments welcome) and need a
workaround.

In Excel 2007 'm using the the function below in an add-in to return
the access oft the VBA project. It appears to work correctly.

Private Function IsVBProjectAvailable() As Boolean
Dim lngAccessTest As Long

On Error Resume Next
lngAccessTest = ThisWorkbook.VBProject.VBComponents.Count

If lngAccessTest > 0 Then
IsVBProjectAvailable = True
Else
IsVBProjectAvailable = False
End If
End Function


....UNTIL you open a workbook containing macros. At that point unless
you ENABLE the macros the function then returns FALSE. The reason I'm
fairly sure its a bug is that a) it's the wrong answer and b) if you
then open the VBE (have it closed before you start, and don't do
anything except open it) the function then correctly returns TRUE

Any comments or suggestions for a workaround, or perhaps (hopefully)
I'm missing something obvious??!!

Thanks
John

 

[Barb Reinhardt]

IIRC, you'll have issues if the project is password protected as well.

 

[OssieMac]

I should think it is doing exactly what it is supposed to do. If you open a
workbook and don't allow macros then for all practical purposes they don't
exist and therefore it should return false.

If you open the VBE then you immediatly have components. They are listed in
the Project explorer and don't necessarily have to have code in them to be
present. If you run the following little test you will see what I mean.

Sub MyVbComponents()
Dim comp
For Each comp In ThisWorkbook.VBProject.VBComponents
MsgBox comp.Name
Next comp
End Sub

As for a workaround, what exactly is it that you want to do? If I interpret
your question correctly it appears that you are trying to duplicate the
security that advises that the workbook contains macros.

 

[jh]

I should think it is doing exactly what it is supposed to do. If you opena
workbook and don't allow macros then for all practical purposes they don't
exist and therefore it should return false.

If you open the VBE then you immediatly have components. They are listed in
the Project explorer and don't necessarily have to have code in them to be
present. If you run the following little test you will see what I mean.

Sub MyVbComponents()
Dim comp
For Each comp In ThisWorkbook.VBProject.VBComponents
  MsgBox comp.Name
Next comp
End Sub

As for a workaround, what exactly is it that you want to do? If I interpret
your question correctly it appears that you are trying to duplicate the
security that advises that the workbook contains macros.

Many thanks Ollie, but my initial thoughts are that your assumptions
are incorrect.

I agree that for practical purposes you may consider that a macro
enabled workbook with macros disabled is 'out of scope' to the VBE,
but the function posted does not look at the active workbook:- It
looks at itself .... and those macros are obviously enabled and
available. We should also not confuse 'VBA Project not
trusted' (Application scope) and 'Macros not enabled' (Workbook scope)

I'm looking for a workaround that acknowledges that the trust is
enabled, but the macros (in that workbook) aren't.... for example is
their a function that returns whether or not the macros have been
enabled?

Kind regards
John

 

[jh]

Many thanks Ollie, but my initial thoughts are that your assumptions
are incorrect.

I agree that for practical purposes you may consider that a macro
enabled workbook with macros disabled is 'out of scope' to the VBE,
but the function posted does not look at the active workbook:- It
looks at itself .... and those macros are obviously enabled and
available. We should also not confuse 'VBA Project not
trusted' (Application scope) and 'Macros not enabled' (Workbook scope)

I'm looking for a workaround that acknowledges that the trust is
enabled, but the macros (in that workbook) aren't.... for example is
their a function that returns whether or not the macros have been
enabled?

Kind regards
John

Some more info .....

On the back of OssieMac's thoughts I did some more testing, and it
appears that even if you open up another workbook with NO macros after
the macro book, the function still returns FALSE (no access granted).
It is not until you close the macro workbook (or open the VBE) that
the function correctly returns the true state of the project access.
In reality I'm already using a workaround function to get the state of
the project access by looking at a count of components. I'm assuming
no one knows of a direct read-only call to to ascertain its value (a
Application.VBAProjectAccess type function) or another type of
workaround ???

Thanks
John

 

[OssieMac]

Hi John,

Found this in xl2007 help. Because it says it was added for xl2007 I would
suggest that nothing similar was previously available.

**************************
Extract from Help xl2007
Excel Developer Reference
Workbook.HasVBProject Property
Returns a Boolean that represents whether a workbook has an attached
Microsoft Visual Basic for Applications project. Read-only Boolean.
Version Information
Version Added: Excel 2007

Syntax

expression.HasVBProject

expression A variable that represents a Workbook object.

Remarks


This property is most useful in programmatically determining whether a
workbook needs to be saved into a macro-enabled file format. If saved in
another format, macros and code projects contained within the document may be
lost.

End of Extract
**********************************

I tested it with the following

Sub TestForVBA()
Dim wb As Workbook
Set wb = Workbooks("Workbook to test.xlsm")
MsgBox wb.HasVBProject
End Sub

It appeared to successfully return True if code existed and False if no
code. However, inserting a module without any actual code returned True.

Even though the workbook was saved as macro enabled, it needed a module
inserted or some code in a worksheet or thisworkbook before it returned true.

After inserting a module or some code in thisworkbook it returned true and
when all of it was removed/deleted it returned false.