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

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

Thanks Dave... that worked :)

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

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

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