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

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

Barb Reinhardt



"jh" wrote:

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
.

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.

--
Regards,

OssieMac


"jh" wrote:

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
.

On Dec 7, 12:03*am, OssieMac
wrote:
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.

--
Regards,

OssieMac



"jh" wrote:
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
.

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

On Dec 7, 5:36*am, jh wrote:
On Dec 7, 12:03*am, OssieMac
wrote:





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.

--
Regards,

OssieMac

"jh" wrote:
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
.

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