Problem with UDF can't return the value of a formula

  • Thread starter Thread starter Billy
  • Start date Start date
B

Billy

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
 
Thanks Dave... that worked :)

Dave Peterson said:
Maybe:

Option Explicit
Function calculateThis(Cell_A As Range)
calculateThis = Evaluate(Cell_A.Value)
End Function
Click to expand...
 
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
 
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/[email protected]

You may have to parse your value to pass it to Harlan's UDF, though.
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 said:
Maybe:

Option Explicit
Function calculateThis(Cell_A As Range)
calculateThis = Evaluate(Cell_A.Value)
End Function
Click to expand...
Click to expand...
 
Hi,

thanks for the help dave, i feel like i'm closer to the solution now...

i tryed this:

*** CODE ********************************
Option Explicit

Private Function GetValue()
Const PATH As String = "n:\client rep report delivery\bayer\july04dm\sales reps"
Const FILE As String = "BAYER_ED_A101.XLS"
Const SHEET As String = "Territory_Summary"
Const REF As String = "C11"

' Retrieves a value from a closed workbook
Dim arg As String

' Make sure the file exists

If Dir(PATH & "\" & FILE) = "" Then
GetValue = "File Not Found"
Exit Function
End If

' Create the argument
arg = "'" & PATH & "[" & FILE & "]" & SHEET & "'!$c$11"

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function
*****************************************
i get an error message that there is an error in my formula.

i also tryed this and got the same message...
*****Code************************************
Public Sub GetDataFromClosedFile()
Dim filepath As String
Dim fileName As String
Dim sheetName As String
Dim strg As String

filepath = "n:\client rep report delivery\bayer\july04dm\sales reps"
fileName = "BAYER_ED_A101.XLS"
sheetName = "Territory_Summary"

strg = "'" & filepath & "\[" & fileName & "]" & sheetName & "'!$c$11"
MsgBox ExecuteExcel4Macro(strg)
End Sub
**************************************************
Bilal

Dave Peterson said:
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/[email protected]

You may have to parse your value to pass it to Harlan's UDF, though.
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Are you trying to use this =getvalue() in a worksheet cell?

If yes, then it won't work.

Try using Harlan Grove's function--you'll still have to parse your string to
pass it to Harlan's =Pull() Function.


Hi,

thanks for the help dave, i feel like i'm closer to the solution now...

i tryed this:

*** CODE ********************************
Option Explicit

Private Function GetValue()
Const PATH As String = "n:\client rep report delivery\bayer\july04dm\sales reps"
Const FILE As String = "BAYER_ED_A101.XLS"
Const SHEET As String = "Territory_Summary"
Const REF As String = "C11"

' Retrieves a value from a closed workbook
Dim arg As String

' Make sure the file exists

If Dir(PATH & "\" & FILE) = "" Then
GetValue = "File Not Found"
Exit Function
End If

' Create the argument
arg = "'" & PATH & "[" & FILE & "]" & SHEET & "'!$c$11"

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function
*****************************************
i get an error message that there is an error in my formula.

i also tryed this and got the same message...
*****Code************************************
Public Sub GetDataFromClosedFile()
Dim filepath As String
Dim fileName As String
Dim sheetName As String
Dim strg As String

filepath = "n:\client rep report delivery\bayer\july04dm\sales reps"
fileName = "BAYER_ED_A101.XLS"
sheetName = "Territory_Summary"

strg = "'" & filepath & "\[" & fileName & "]" & sheetName & "'!$c$11"
MsgBox ExecuteExcel4Macro(strg)
End Sub
**************************************************
Bilal

Dave Peterson said:
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/[email protected]

You may have to parse your value to pass it to Harlan's UDF, though.
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

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
Click to expand...
Click to expand...
Click to expand...
 
Hi, i couldn't find anywhere that explains what argument i should
pass.

You think Harlan would add that to the 20 lines of comments at the
start of his function :).

i tried sending this:

"='D:\VBA\Sales\REPORT CREATOR\[ED_A101.XLS]Territory'!$c$11"



Dave Peterson said:
Are you trying to use this =getvalue() in a worksheet cell?

If yes, then it won't work.

Try using Harlan Grove's function--you'll still have to parse your string to
pass it to Harlan's =Pull() Function.


Hi,

thanks for the help dave, i feel like i'm closer to the solution now...

i tryed this:

*** CODE ********************************
Option Explicit

Private Function GetValue()
Const PATH As String = "n:\client rep report delivery\bayer\july04dm\sales reps"
Const FILE As String = "BAYER_ED_A101.XLS"
Const SHEET As String = "Territory_Summary"
Const REF As String = "C11"

' Retrieves a value from a closed workbook
Dim arg As String

' Make sure the file exists

If Dir(PATH & "\" & FILE) = "" Then
GetValue = "File Not Found"
Exit Function
End If

' Create the argument
arg = "'" & PATH & "[" & FILE & "]" & SHEET & "'!$c$11"

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function
*****************************************
i get an error message that there is an error in my formula.

i also tryed this and got the same message...
*****Code************************************
Public Sub GetDataFromClosedFile()
Dim filepath As String
Dim fileName As String
Dim sheetName As String
Dim strg As String

filepath = "n:\client rep report delivery\bayer\july04dm\sales reps"
fileName = "BAYER_ED_A101.XLS"
sheetName = "Territory_Summary"

strg = "'" & filepath & "\[" & fileName & "]" & sheetName & "'!$c$11"
MsgBox ExecuteExcel4Macro(strg)
End Sub
**************************************************
Bilal

Dave Peterson said:
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/[email protected]

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

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
Click to expand...
Click to expand...
Click to expand...
 
Maybe this (included in the link to Harlan's function):

http://www.google.com/[email protected]

At the bottom of this post, you'll see a couple of examples:

Given your worksheet layout, call it using

=pull("'"&A3&"\["&B3&".xls]"&C3&"'!"&"B5")
or
=pull("'"&A3&"\["&B3&".xls]"&C3&"'!B5")




Hi, i couldn't find anywhere that explains what argument i should
pass.

You think Harlan would add that to the 20 lines of comments at the
start of his function :).

i tried sending this:

"='D:\VBA\Sales\REPORT CREATOR\[ED_A101.XLS]Territory'!$c$11"

Dave Peterson said:
Are you trying to use this =getvalue() in a worksheet cell?

If yes, then it won't work.

Try using Harlan Grove's function--you'll still have to parse your string to
pass it to Harlan's =Pull() Function.


Hi,

thanks for the help dave, i feel like i'm closer to the solution now...

i tryed this:

*** CODE ********************************
Option Explicit

Private Function GetValue()
Const PATH As String = "n:\client rep report delivery\bayer\july04dm\sales reps"
Const FILE As String = "BAYER_ED_A101.XLS"
Const SHEET As String = "Territory_Summary"
Const REF As String = "C11"

' Retrieves a value from a closed workbook
Dim arg As String

' Make sure the file exists

If Dir(PATH & "\" & FILE) = "" Then
GetValue = "File Not Found"
Exit Function
End If

' Create the argument
arg = "'" & PATH & "[" & FILE & "]" & SHEET & "'!$c$11"

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function
*****************************************
i get an error message that there is an error in my formula.

i also tryed this and got the same message...
*****Code************************************
Public Sub GetDataFromClosedFile()
Dim filepath As String
Dim fileName As String
Dim sheetName As String
Dim strg As String

filepath = "n:\client rep report delivery\bayer\july04dm\sales reps"
fileName = "BAYER_ED_A101.XLS"
sheetName = "Territory_Summary"

strg = "'" & filepath & "\[" & fileName & "]" & sheetName & "'!$c$11"
MsgBox ExecuteExcel4Macro(strg)
End Sub
**************************************************
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/[email protected]

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

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top