First, functions called from worksheet formulas can't update other cells.

They can return something to the cell with the function.

=calculatethis()
worked for me if the other workbook was open. But not if that other workbook
was closed.

If you want to retrieve a value from a closed workbook in VBA, you can use one
of the techniques at John Walkenbach's site:

http://j-walk.com/ss/excel/eee/eee009.txt
Look for either: GetDataFromClosedFile or GetValue.

If you want to retrieve a value from a closed workbook from a worksheet cell,
you could use a UDF that Harlan Grove wrote:

http://www.google.com/groups?selm=hk...wsranger.c om

You may have to parse your value to pass it to Harlan's UDF, though.

Billy wrote:

Hi, that did work for the simple example like:

calculateThis = Evaluate("=sum(1+1)")

however when i try to use this solution to evaluate a formula that
refrences another workbook like this:

calculateThis = Evaluate("='C:\[book1.xls]Sheet1'!$A$1")

...It returnes error 2023

question #2
also another thing i noticed with UDF's that i can't explain is when i
try to say rangeA.value = rangeX.value the code simply stops and i
have no idea why no error message or anything.

Thank you
Bilal

Dave Peterson wrote in message ...
Maybe:

Option Explicit
Function calculateThis(Cell_A As Range)
calculateThis = Evaluate(Cell_A.Value)
End Function


Billy wrote:

Hi, I'm new to UDF but not VBA.

I'm trying to take the value from cell A and copy it over to cell B.

-The value in cell A is a formula that generates a formula.
example cell A value: ="=sum(200)"
the actual formula is more complicated than this however this is the
main idea for the sake of keeping it simple.

-The UDF looks like this:

Function calculateThis(Cell_A As Range)
calculateThis = Cell_A.Value
End Function

i've also tryed
calculateThis = Cell_A.formula
calculateThis.value = Cell_A.formula (this one just stops the code
execution)

-Cell B looks like this: = calculateThis(A1)

-the result i get is: =sum(200)

-the result i need is: 200

i need to get the answer and not the formula.

I kind of know what's happening but not sure how to fix it.

I hope my explanation was okay, any help would be appreciated.
thank you
Bilal

--

Dave Peterson