Userdefined Function and recalculatiing XL 2k3

Hi all,
I am using something like that:

WorkBook A has the Document Property "Title" set to 'A'.
WorkBook B has the Document Property "Title" set to 'B'.

In each Workbook (A and B) is a VBA-function:

Function myGetBulitInProp(ByVal PropName As String)
myGetBuiltInProp=Null
On Error Resume Next
For Each prop in ActiveWorkbook.BuiltInDocumentProperties
If (prop.name = PropName) Then
myGetBuiltInProp=prop.Value
End If
Next
End Function

i.e. Cell A1 in Workbooks A and B contains the formula:
=myGetBuiltInProp("Title")
This results in the correct values for each Workbook.

The Problem:
Opening both WorkBook A and WorkBook B results in (displayed) Value 'B'
in Cell A1 in both WorkBooks (Document Properties are unchanged and
still correct).
Doing so with more than two Workbooks sets all the values to the one of
the last opened WorkBook.

I'm running Excel 2003 SP2 (Built 11.6560.6568) and yes, automatic
recalculation is activated.
Any ideas?

Thanx in advance

Thomas

Use ThisWorkbook, not Activeworkbook.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Thomas Weber" <thomas.weber@mechatronic.de> wrote in message
news:dm4c4b$k8k$1@ulysses.news.tiscali.de...
> Hi all,
> I am using something like that:
>
> WorkBook A has the Document Property "Title" set to 'A'.
> WorkBook B has the Document Property "Title" set to 'B'.
>
> In each Workbook (A and B) is a VBA-function:
>
> Function myGetBulitInProp(ByVal PropName As String)
> myGetBuiltInProp=Null
> On Error Resume Next
> For Each prop in ActiveWorkbook.BuiltInDocumentProperties
> If (prop.name = PropName) Then
> myGetBuiltInProp=prop.Value
> End If
> Next
> End Function
>
> i.e. Cell A1 in Workbooks A and B contains the formula:
> =myGetBuiltInProp("Title")
> This results in the correct values for each Workbook.
>
> The Problem:
> Opening both WorkBook A and WorkBook B results in (displayed) Value 'B'
> in Cell A1 in both WorkBooks (Document Properties are unchanged and
> still correct).
> Doing so with more than two Workbooks sets all the values to the one of
> the last opened WorkBook.
>
> I'm running Excel 2003 SP2 (Built 11.6560.6568) and yes, automatic
> recalculation is activated.
> Any ideas?
>
> Thanx in advance
>
> Thomas

Bob Phillips schrieb:

>Use ThisWorkbook, not Activeworkbook.
>
>
>
Many thanks. It works.

Thomas