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

Userdefined Function and recalculatiing XL 2k3
 

Use ThisWorkbook, not Activeworkbook.

--

HTH

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


"Thomas Weber" wrote in message
...
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

Userdefined Function and recalculatiing XL 2k3
 

Bob Phillips schrieb:

Use ThisWorkbook, not Activeworkbook.



Many thanks. It works.

Thomas